I had run into tonnes of problems installing & configuring Merge Replication mention between MS SQL Server and SQL Server Mobile, but finally got everything working. So thought of writing this article which would help many of the newbies trying to do this. If you're not a newbie, this is probably not the right place for you!
Thanks to Phil for all the help!
Please note that this article is only a supplement to the following article:
http://msdn2.microsoft.com/en-us/library/ms171908.aspx
First of all, Merge Replication is about two-way synchronization between data (tables. Does it synchronize anything else?) across two SQL Servers. I'm going to mention the synchronization in SQL Server on Desktop and SQL server Mobile or SQL Server CE (as Microsoft calls the newer versions of it). I've done it only using SQL Server Management Studio, plus also tested it on real Windows Mobile Device (WM6, HTC Touch) with .Net Compact Framework 2.0 installed, using C# code for initiating Synchronization from device, though in this article, the process is described only using SQL Server Management Studio.
What do you need?
- SQL Server Standard Edition or higher: Please note, that you should either install MS-SQL Server Standard Edition or Developer Edition. You cannot create a publication using SQL Server Express Edition. Although I guess it can act as a subscriber (I'm not very sure). Enterprise Edition definitely supports all of these, plus publication using Oracle Database as well, being a super-set of other editions, but be careful! The Enterprise Edition cannot be installed on Windows XP Professional SP2. It needs Windows Server 2003 SP1 as the minimum OS. On the other hand, Microsoft does allow the Evaluation Version of the Enterprise Edition to be installed on Windows XP SP2. For more details, refer to the Installation requirements of SQL Server Enterprise Edition. It mentions that the Enterprise Edition doesn't support all the platforms which the Evaluation Edition Supports! (I spent nearly a day figuring that out why was the Enterprise Edition not installing on my Laptop running Windows XP Pro SP2!). I've personally tested it on both Standard Edition as well as Evaluation Edition (Enterprise)
Microsoft recently released SP1 for SQL Server 2005 (possibly all editions). I've not installed that and instead used the default one available on the web-link given in "Installing MS-SQL Servet" section below.
- IIS: that's easy.. to be done from Add Remove Windows Components from appwiz.cpl (Add Remove Programs in Control Panel)
- SQL Server Mobile or SQL Server CE with corresponding version of Server Tools. E.g. if you have installed SQL Server CE 3.5, then you must also have installed SQL Server CE Server Tools 3.5. Please note that the names are not accurate. For me it worked for SQL Server CE 3.5 & corresponding Server Tools
- Windows XP Pro SP2 with administrative log-on access
Installing MS SQL Server
If you're a newbie, consider downloading the evaluation version from Microsoft Technet or MSDN. I did it from Technet, without understanding the real difference :)
http://www.microsoft.com/sql/downloads/trial-software.mspx
You can try downloading the Self-Extracting Executable and run it. It asks for location to Extract the files. For this article, lets assume you extracted it to c:\SQLEVAL\
You need to launch c:\SQLEVAL\Servers\default.hta and click on the Install: Database, SDK etc link. It should launch the set up.
Warning: If you already have any other version of SQL Server Installed, you must do this way:
Open command prompt. Change directory to C:\SQLEVAL\Servers\ and then run
C:\SQLEVAL\Server> setup.exe SKUUPGRADE=1
Also, if you already have SQL Server Express Management Studio with very limited capabilities, that might interfere with MSSQL Server Management Studio which is a part of the Installation Tools package. If you cannot access the full management studio, I would suggest to try and remove the workstation components or SQLEXPRESS completely before installing this.
For only replication usage, the following components need to be installed: Database Engine, Integration Services & Workstation Components. You need not install Analysis and Reporting Services.
While installing, make sure that all three Services run automatically. Also, when it asks which account to use for running the following: (1) MSSQLSERVER (2) SQL Agent (3) Integration Services etc, all the services should be run by the user: LocalSystem (builtin account). At least for me, it mattered! It can be done later as well Strictly from SQL Server Configuration Manager. Please don't change the logon account for these services from Microsoft Services (services.msc), else you'll run into problems. Microsoft has already mentioned this in its knowledge base which I came across whileI ran into this problem :)
If you're running the above services as any other account, go to Programs -> SQL Server 2005 -> Configuration Tools -> SQL Server Configuration Manager -> SQL Server 2005 Services. On the right pane, whatever services you see, all of them should be using LocalService account. I'm not sure of the reason. Make sure that each of the services are restarted to reflect the change in logon account.
For authentication options, I checked to use Windows authentication during installation, and not SQL Server Authentication.
There isn't much where you might go wrong in installing IIS or SQL Server Mobile or its tools, so I'm skipping that portion.
Replication Configuration
After you've installed everything,just launch SQL Server Management Studio from Programs -> SQL Server 2005 -> SQL Server Management Studio
Depending upon what services are installed, it may give lots of server options. Depending upon what it shows, you may either choose Database Engine or name of the MSSQLServer, which you might have specified during installation. I guess it is usually the same as the network name of the machine on which you've installed it. In this I'm assuming my machine name to be KUNDAN-LAPTOP, and so is the name of the database server.
Make sure you create a snapshot share (\\KUNDAN-LAPTOP\snapshot), create two user-accounts: snapshot_agent and IUSR_Machine-Name(IUSR_KUNDAN-LAPTOP) with appropriate permissions as in the main article:
http://msdn2.microsoft.com/en-us/library/ms171908.aspx
Create a new database, say ReplTestDB with say one table named tblIDName with two fields: ID & name. Pump in some data in the table(say 3 rows for now). Right Click on this database-> Properties->Permissions->The two users: snapshot_agent and IUSR_Machine-Name should have access to the following role: Connect & Connect Replication. I’m not sure if it would be really required for IUSR account to have connect replication role, but for the snapshot_agent account, it definitely makes sense!
In the object explorer -> Security -> Logins -. right click to create two logins one after the other snapshot_agent & IUSR_Machine-Name and for each login, select the database: ReplTestDB, and give db_owner and public role.
Then go ahead and create a publication as mentioned in the main article above. Let us name the Publication: ReplTestPub. You should right-click on the publication and go to properties->publication access list->Click on Add. You should be able to see two accounts to add: snapshot_agent & IUSR_Machine-Name. Select both and add.
Now, its the time to run snapshot_agent. Right click on the publication -> view snapshot status -> start. You shouldn't be seeing any error (hopefully!). If you see, then refer to Object Explorer->SQL Server Agent->Error Logs->Current.
If you see errors after running the snapshot agent, the best deal would be to Add "snapshot_agent" account to "administrators" group. I don't know why this should do the trick, but it worked for me on two different machines, even though the "snapshot_agent" account was added to "publication access list" in the properties of the publication
If it worked fine up to here for you, then chances are it won't give any problem from here onwards.
Now go ahead and configure web-synchronization wizard as mentioned in the main article.
Next, create an SQL Mobile Subscription by creating an SQL mobile Database, and configuring the subscription as mentioned in the main article. After doing this, you can just right click on the subscription -> Synchronize now. I should work! (hopefully). The result should be that the table should be created with the same row values as the table in the Server Database
To verify it indeed works, you can open a new query window and execute an insert query in the SQL Mobile Database, and then synchronize the publication again as above. Now you should be able to see the new rows just inserted in the table in the SQL Server Database! This would prove that synchronization worked in the direction from Mobile to Server.
To verify the synchronization from Server to Mobile, add a row in that table in the server database, run the snapshot agent again (by right clicking on the publication -> view snapshot status -> start), and then right click on the subscription in SQL Mobile Database -> Synchronize. Then in the Query, you can do a Select * from table-name query to see that the new rows are updated in the SQL Mobile database as well!
Well! thats about it!!
Troubleshooting
The overall process of Setting up replication is at least cumbersome. There are a couple of things that can go wrong. Some of these are as follows:
- Your installation of SQL Server 2005 may not be right. Please note that the free version of SQL Server (which is SQL server 2005 Express Edition) does not support publication. It can only be a subscriber which can synchronize with a SQL Server Standard/Developer/Enterprise Edition Publication, but you cannot create a publication in SQL Server Express for a SQL Server CE/Mobile or any other to subscriber to synchronize with it! So be ready to either shell off few 100 odd bucks, or better, for trail purposes, use the 90-days fully functional evaluation version.
- If the Server is indeed running, and installed correctly, you should be able to see the following in the SQL Server Configuration Manager (Programs-> SQL Server 2005->Configuration Tools->SQL Server Configuration manager): MSSQLSERVER, SQL Server Agent, SQL Server Integration Services. Make sure all of these services are running as the user: Local Service. If not, please change the logon account associated with these process one after the other. If you want to completely play safe, you may want to restart your computer and make sure these process are running before you even attempt to try replication.
- After you create a snapshot share on your computer, make sure the user: snapshot_agent and the user: IUSR_Machine-Name are able to browse the network-share \\machine-name\snapshot_share. Usually, that would mean that even if the snapshot is a local share, you must be connected on the workgroup-network/domain. So if you're in a company, make sure you're at least able to connect other workgroup/domain computers.
- After you create the database which you want to publish, make sure you add the users: snapshot_agent and IUSR_Machine-Name to the logins in SQL Server Management Studio
- After you create the publication, make sure you add these two users to Publication Access List (in Publication Properties), give these users the dbowner permission, and permission to "connect" and "connect and replicate"
- For starting the snapshot_agent, don't get disappointed by the initial error message, if any, by right-clicking on the publication->view snapshot-agent status. The chances are all high that initially the Publication access list is not setup correctly. After you do this rihgt, then click on "Start" and then it should work fine.
