2-Tier With multiple Clients. RRS feed

  • Question

  • Hi Everyone,

    So here is my situation. I have a SQL 2008 server and SQL 2008 express clients. Each client has a subset of the database on the server created using filtered scopes. Here is the issue in general each client will have a seperate subset but there may be one or two instances where there are multiple clients with the same subset of data. When testing this situation I discovered 1 client will be able to sync data to the server but a second can't it is simply ignored. Different subsets works fine even if its a piece of common data but not two of the same subset on different clients.

    I think this is caused by the multiple clients using the same scopes. Up till now what I have  been doing is creating the client databases and then hoping I could create a backup(or a script, it doesn't make too much of a difference) and then distribute it to multiple clients if necessary.

    Do I need to create a separate scope for each client that might be using the same subset of data?




    Friday, September 9, 2011 7:15 PM


  • how are you provisioning the two clients having the same subset of data? are you backing up the first client and using the backup for the second client? if you are, you should run performpostrestorefixup on the second client.

    there should be no problem with multiple clients using the same scope.

    Saturday, September 10, 2011 12:46 AM

All replies

  • how are you provisioning the two clients having the same subset of data? are you backing up the first client and using the backup for the second client? if you are, you should run performpostrestorefixup on the second client.

    there should be no problem with multiple clients using the same scope.

    Saturday, September 10, 2011 12:46 AM
  • That worked perfectly thanks JuneT.
    Monday, September 12, 2011 9:55 AM
  • I may have marked this as answered a bit prematurely. The same situation still applies as above but I have discovered only some tables are pulling down data from the server. 

    Basically I sync with one client the data goes to the server. I sync with the second client and only some of the data is pulling down form the server. I should say that there are multiple table scopes and they are all provisioned in the same way (its done by a for loop so it is exactly the same code with only the scope name changing). 

    Any idea why this would be happening? And as a secondary question what is the recommended method for deploying provisioned databases.




    Tuesday, September 20, 2011 2:58 PM
  • do you have tables belonging to more than one scope?

    if you're database is Sql Express/Sql Server, just do a back up and do a PerformPostRestoreFixup when you restore at the target server.

    Wednesday, September 21, 2011 1:14 AM
  • Yes most of my tables belong to more than one scope i'm using filters on quite a few scopes to control what data each client can see.

    I'm pretty sure I tried your suggestion yesterday when I was trying to fix this but i'll give it another try maybe i missed something. In the meantime any other suggestions as to what might cause this kind of behaviour?



    Wednesday, September 21, 2011 8:08 AM
  • can you pinpoint what types of changes are missing? inserts? updates? deletes? 

    have you checked that your scope filters are not excluding these missing changes?

    do you have the same set of data included in  more than one scope?

    Wednesday, September 21, 2011 8:18 AM
  • It is mostly inserts and updates we are doing. I don;t think there are any deletes. I'll try and explain the situation in a little more detail to give you a better idea of the situation.

    We have people in several countries gathering data which is then synced back to a central server. A table on the server would have a definition similar to this.


    CREATE TABLE [table_name](

    [ID] [int] IDENTITY(0,1) NOT NULL,

    [CountryName] [nvarchar](50) NOT NULL,

    [date] [datetime2](7) NOT NULL,

    [data1] [smallint] NULL,

    [data2] [smallint] NULL,


    This holds data for all the countries we gather data from. Using a filtered scope I create a database for each country which only hold data applicable to that country. This is then given to the data gatherers in each country and used with our application to storing data on there local machine and for syncing back to the central server. I should also point out that there is a client database which contains data for all countries. When I create the client database I tell it to GetDescriptionForScope for all scopes rather than just those relating to a 1 country. 

    Because of this "allcountries" database there would be overlap in data between scopes, however I found in my tests that this worked fine. Entering data on a single country db syncing to the server and then doing a sync between an "allcountries" DB and the server brought all the data down to the all countries DB. The problem seems to be where I have 2 of the same single country DB. Change data in 1, sync to the server and the sync to the second and none of the data comes down to the 2nd client. 


    Wednesday, September 21, 2011 8:48 AM
  • so you have 2 single country DB in the same client? (e.g., CountryA db and CountryB db both on client A)

    how are you provisioning CountryA db and CountryB db?

    when you make a change to CountryB db, does it sync back to the server?

    Wednesday, September 21, 2011 9:01 AM
  • Sorry not 2 single country DB on 1 client. 2 Clients with the same single country DB. If we end up with multiple data gatherers for a single country. 

    The server is provisioned with the code found here. http://msdn.microsoft.com/en-us/library/ff928757(v=SQL.110).aspx

    I have two arrays 1 with the different country's and one with the tables. Then using 2 for loops I create a scope for each table with the country name as filter. Scope name would then be tablename-countryname. When I want to create a client for a single country I use this code http://msdn.microsoft.com/en-us/library/ff928758(v=SQL.110).aspx and just pass in the names of the scopes for a particular country. If I want to create the all countries db I pull down all the scopes.

    Wednesday, September 21, 2011 9:13 AM
  • Well I seem to have fixed the issue. After restoring the server and the two clients I was testing with from clean backups and running PerformPostRestoreFixup on all of them. Things seemed to work except one of my tables wasn't getting any data in it despite the sync statistics telling me it had downloaded the change. The change wasn't actually applying so I just had to add an applychangefailed event and set the apply action to RetryWithForceWrite and that seems to have fixed things. 

    Thanks for all your help JuneT. It was really appreciated.



    Wednesday, September 21, 2011 6:50 PM
  • Two last quick questions. Firstly why is it that there was no notification or warning that the changes weren't applying until I actually started catching them? Surely that's the kind of thing that should throw some sort of error? Secondly Any thoughts as to why one only one table would be failing to apply changes?
    Thursday, September 22, 2011 9:43 AM
  • You have to listen to the events to capture the failed syncs. A conflict is not necessarily an error. If sync framework throws an error for a conflict and cancels out a sync, that will be an expensive operation as the changes already applied would have to be rolled back.

    the number of failed changes should actually show up in the sync statistics.

    hard to tell why a single table wont sync without looking at the data but the ApplyChangesFailed event should give you an idea of the state of the data during sync.

    Friday, September 23, 2011 1:28 AM