Ok, so we're nearly there but with some caveats, which I'll list first.

1. In order to deploy an updatable subscription the tables need to have a uniqueidentifier column. If you should happen to have one of these, then that's all good but, if you don't, then you will need to manually create one at the subscriber and populate it with data....

ALTER TABLE myTable

ADD [msrepl_tran_version] [uniqueidentifier] NULL;

UPDATE myTable

SET msrepl_tran_version = NEWID();

2. At the time you create the subscription any EXISTING data at your subscriber won't be pushed back to the publisher, so you'd need to be able to manually sync it first.

Ok, with those out of the way and, assuming they're not showstoppers the rest is really easy 😀

You need to create your Publication as 'Transactional..with updatable subscriptions'

You will choose your tables and be prompted at this point if your publication doesn't have a uniqueidentifier column (if you don't it will create [msrepl_tran_version] for you)

Next you can create your filter so...WHERE Client = 'B'

After you've gone through the next steps of security settings etc it is important that you only select 'Generate a script file with steps to create the publication'. ENSURE 'Create the publication' is UNCHECKED:exclamationmark:

Run through the rest of the steps and then open the sql script it has created. You only need to change one thing to get your desired behaviour. The exec sp_addarticle proc has a parameter @pre_creation_cmd; change this to 'none'.

You can now execute the script and the publication will be created.

Create the subscription in the usual way.

You will see that three triggers have been created on your subscription table and you will need to edit each these because they contain code to constrain the subscription to the filter as follows:

if exists (select * from inserted where not ( [ClientGroup] = 'B' ))

begin

exec sys.sp_MSreplraiserror 21034

goto FAILURE

end

By removing the 'error' call here - you can simply replace it with a RETURN call, you will bypass this issue.

Tweakery, as I said, but it is achievable.