title: SQL Replication with advanced filter on data
body: <p>I want to replicate my local, internal database (publisher) to a public database (subscriber) with some restrictions: Not all rows/columns/data from the internal database should be replicated/pushed to the public database. Most important, some personal user data must be filtered (they must be excluded in the public database).</p>
  
  <p><strong>Technology</strong></p>
  
  <ul>
  <li>Microsoft SQL Server 2014</li>
  </ul>
  
  <p><strong>Example Table PERSON</strong></p>
  
  <p>Source table on the internal database (publisher):</p>
  
  <pre><code>ID | NAME         | IS_PUBLIC | SOCIALNR | MARITAL_STATUS | MARITAL_STATUS_PUBLIC 
  ---+--------------+-----------+----------+----------------+---------------------
  0  | Hans Muster  | 1         | 1234556  | married        | 1
  2  | John Doe     | 1         | 5432167  | single         | 0
  9  | Ingrid Meyer | 0         | 6789989  | single         | 0
  </code></pre>
  
  <p><strong>1. Filter: SOCIALNUMBER</strong></p>
  
  <p>The column <em>SOCIALNR</em> should not be replicated at all. This can be achieved by using a column filter on the table. WORKS!</p>
  
  <p><a href="https://i.stack.imgur.com/LfHRZ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/LfHRZ.png" alt="replication column filter"></a></p>
  
  <p>Target table on the public database (subscriber):</p>
  
  <pre><code>ID | NAME         | IS_PUBLIC | MARITAL_STATUS | MARITAL_STATUS_PUBLIC 
  ---+--------------+-----------+----------------+---------------------
  0  | Hans Muster  | 1         | married        | 1
  2  | John Doe     | 1         | single         | 0
  9  | Ingrid Meyer | 0         | single         | 0
  </code></pre>
  
  <p><strong>2. Filter: Person if IS_PUBLIC</strong></p>
  
  <p>A person should only be replicated, if the field <em>IS_PUBLIC</em> is set to true. If not, the person should not be public, so all his data should not be replicated. This can be achieved by using a row filter on the table. WORKS!</p>
  
  <p><a href="https://i.stack.imgur.com/XKqNm.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/XKqNm.png" alt="replication row filter"></a></p>
  
  <p>Target table on the public database (Subscriber):</p>
  
  <pre><code>ID | NAME         | IS_PUBLIC | MARITAL_STATUS | MARITAL_STATUS_PUBLIC 
  ---+--------------+-----------+----------------+---------------------
  0  | Hans Muster  | 1         | married        | 1
  2  | John Doe     | 1         | single         | 0
  </code></pre>
  
  <p><strong>3. Filter: MARTIAL_STATUS if public</strong></p>
  
  <p>And now the specialty and the reason for this question: A person marital status should only be replicated, if the flag <em>MARTIAL_STATUS_PUBLIC</em> is set to true. If the flag is false, the value in <em>MARTIAL_STATUS</em> should be null or empty or whatever. All other person data should be replicated regardless of the flag.</p>
  
  <p>Target table on the public database (Subscriber):</p>
  
  <pre><code>ID | NAME         | IS_PUBLIC | MARITAL_STATUS | MARITAL_STATUS_PUBLIC 
  ---+--------------+-----------+----------------+---------------------
  0  | Hans Muster  | 1         | married        | 1
  2  | John Doe     | 1         | NULL           | 0
  </code></pre>
  
  <p>Is there a possibility to fulfill this requirement?</p>