Tuesday, 9 June 2009

Here is how to create a synchronization with a database and cached data1

Here is a quick example of how to used the synchronization framework and cached data on the client:

1. Create a Windows Forms application
2. Under the tool bar select Data/Add New Data Server
3. Select database
4. Click the new connection button and select the database to connect to
5. Don't save the connection as
6. Select the tables to synchronize Select Enable local database caching (This creates a mini local database)
7. Select Synchronization Mode Incremental (Snap shot means every time the database is synchronized all data is downloaded ) Select Use SQL Server change tracking
8. Select Update server for incremntal changes
Save SQL scripts in the project for later use (This is useful if you move the database)
After this you will get a file like TEST_AynchCache.syncIf you double click on this you get a hyperlink to "Show Code Example".
This code can be used to perform the synchronization.
Here is an example of how to populate and synchronize a data grid view

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim syncAgent As TEST_SYNCHCacheSyncAgent = New TEST_SYNCHCacheSyncAgent()
Dim syncStats As Microsoft.Synchronization.Data.SyncStatistics = syncAgent.Synchronize()
End Sub

syncStats.TotalChangesDownloaded contains the number of changes that where made

My first steps at Synchronizing SQL2008 databases

Here are some steps I have taken in testing the synchronization of databases using SQL

Install SQL-Server Replication
1. Run the MS SQL Setup.exe
2. Select Installation in the left hand panel
3. Click New SQL Server stand-alone installation or add features to an existing installation
4. Setup Support Files
Click install
5. Setpup Support Rules click next
6. Click Add features to an existing instance of SQL 2008
7. Select Database Engine Service/SQL Server ReplicationNext
8. Disk space requirements
click next
9. Error and usage reporting
click next
10. Installation rules click next
11. Ready to install click Install
12. Installation progress click next
13. Complete click close

Setup the publisher
1. Open Microsoft SQL Server Manager Studio
2. Browse to Replication/Local Publications
3. right mouse click on Local Publications and select New Publication
4. New publication wizard click next
5. Select 'YourServerName' will act as its own Distributor; SQL Server will create a distribution database and log
6. Select Yes, Configure the SQL Server Agent to start automatically
7. Leave the snap shot folder in it's default location
C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\ReplDataNext8. Select the database that you want to synchronize
9. Publication Type.
Transactional publication:The Publisher streams transactions to the Subscribers after they receive an initial snapshot of the published data.

Transactional publication with updatable subscriptions:The Publisher streams transactions to SQL Server Subscribers after they receive an initial snapshot of the published data. Transactions originating at the Subscriber are applied at the Publisher.

Merge publication:The Publisher and Subscribers can update the published data independently after the Subscribers receive an initial snapshot of the published data. Changes are merged periodically. Microsoft SQL Server Compact Edition can only subscribe to merge publications.

Select Transactional publication with updatable subscriptions

10. Article
Select all the Tables including sysdiagrams(dbo)
11. Articles issued click next
12. Filter Table Rows click
13. Snap Shot Agent select Create a snap shot immediately and keep the snapshot available to initialize ssubscriptions
14. Agent Security
Click Security settings for SnapShot Agent and Queue Reader Agent
15. For both steps in 14 I selected
Run under the SQL Server Agent Service account (This is not recommended security best practice)
(I dont want to manage a lot of different service accounts so I choose just the account I use the one I configured for SQL Agent)
16. Wizard Actions Select
Cretae the publication
17. Click Next and the Create Publication wizard will start
18. Close

Setup the Subscription
(Before starting create a linked server to the publisher that uses the current security context, it's needed in step 15)
1. Open Microsoft SQL Server Manager Studio and connect to the target server
2. Browse to Replication/Local Subcriptions
3. right mouse click on Local Subscriptions and select New Subscription
4. New subscription wizard click next
5. Select
6. Open the SQL Server that has the published database
7. Select the database and publications that was just created
8. Distribution Agent Location
Select Run each agent at it's Subscriber (pull subscriptions)(The alternative is to run all agents at the distributor (push subscriptions) which can be easier to administer)
9. Subscribers Choose the Subscription database where the data will go(At this point you can also create a new database)
10. Distribution Agent SecurityClick the (...) button
11. Run under the SQL Server Agent Service account (This is not recommended security best practice)
(I dont want to manage a lot of different service accounts so I choose just the account I use the one I configured for SQL Agent)
12. Distribution Agent Security Click Next
13. Synchronization Schedule Select Run continously (the alternative is to run on demand or to define a schedule)
14. Updateable SubsscriptionsSelect Simultaneously commit changes (the alternative is to queue changes and commit when possible)
15. Login For Updateable scriptionsSelect Use a linked server or remote server that you have already defined
16. Initialize SbscriptionsSelect Immediately
17. Wizard actions select Create the subscriptions
After this something like this will apear
• Create subscription(s).
Create a subscription to publication 'TEST' from Publisher 'CHZUPRES913'.
Create subscriptions at the following Subscriber(s):
• Subscription database: TEST
• Agent location: Subscriber
• Agent schedule: Run continuously
• Agent process account: SQLServerAgent service account
• Connection to Distributor: Impersonate process account
• Connection to Subscriber: Impersonate process account
• Initialize: Immediately
• Replicate changes: Simultaneously commit changes
18 Creating Subscription(s)Close