How to Automate the Initializing of the client database (best practice) RRS feed

  • General discussion

  • Hi,

    I want to create Win Forms Application in VS2008/VS2010 that will automate the initializing of the client database. It is Hub-and-Spokes 2-tier Sync architecture, utilizing SQL Server 2008 Enterprise and SQL Express 2008 R2.

    I must be able to:

    1. Backup the main (hub) server database.

    2. Create (restore) the database on the client from the backup created in step 1.

    3. Initialize the client database for first Synchronization. Currently, I do this manually by executing T-SQL scripts to create and polulate the SYNC_Anchor_NativeCT table with all table names in database, Sent and Received Anchors.

    I was looking at different options how to implement the automation: solely T-SQL scripting, SQL Server Management Objects (SMO) or ADO.NET

    Please advise what is the best practice in this scenario.

    Is there an utility from Sync Framework team that already does that Initialization of the client database?

    What API should I use in the 3 steps above (Sync Framework, SMO, ADO.NET or other)?

    Thank You,



    Wednesday, September 21, 2011 8:51 PM

All replies

  • you mentioned "anchors" so am assuming you're using the offline providers and is using a custom Sql Express Client Sync provider since there is no OOTB client sync provider for Sql Express.

    if you ask me, i'd keep the client initialization of the database out of my application. so i'd either use T-SQL (easy to debug and no need to compile stuff), or write a small utility using either ADO.NET or SMO.

    Out of curiosity, have you looked at using SqlSyncProvider instead? that provider has provisioning APIs to help out in initializing databases for sync.

    Thursday, September 22, 2011 1:56 AM
  • Yes, I am using custom Sql Express Client Sync provider.

    My first intention was as you suggested to write a small utility app that will execute the T-Sql scripts I have for the initialization of the client database. (utilizing SMO).

    But I was curious if there is already utility, API or some other tool that does the initialization of the client database out of the box. That was the reason I posted this discussion.

    I will look at the SqlSyncProvider provisioning APIs. Thanks for the suggestion. I did not know it has an API for the initialization of the client database.

    BTW, here's the UI of utility I plan to write. (I am following the Kanasz Robert codeproject example http://www.codeproject.com/KB/database/SQLServer_Backup_SMO.aspx )


    Thursday, September 22, 2011 1:00 PM