So here is the scenario. You have a central Sitecore repository and several developers that work on code separately. Periodically they want to synchronize their Master databases with the main repository where the content editors are working, in order to get the latest content and structures.

The Sitecore Developer manual teaches us that when setting up a local development environment we should create "throw away" copies of the Sitecore databases. The idea is that we use local databases to develop against, but periodically, the main site will evolve and we need to grab fresh copies of it, so the latest items, templates and layouts/sublayouts are available.

Doing so can be quite labor intensive and if you have an inexperienced team, quite onerous.

The process involves first creating a backup of the Sitecore Master database at the Staging server – where the content editors put their changes. Then zipping it up and sending a copy to the developers, or allowing them to fetch one from a file share/FTP/source control. Then each developer needs to Restore the database and reapply permissions to that database. Not complicated, for your average developer, but still quite tedious. For inexperienced team members, not used to the meanderings of SQL Server, the process can be quite daunting indeed. So in those cases, a better alternative should be sought.

SQL Server Replication

Enter SQL Server Replication. Replication solves the problem of periodic updates. It does all the work for you, so you don't have to. It works even if your developers are using SQL Server Express. You can set up a job to pull, and update as often or as little as you need.

So what kind of replication should you set up?

Let's review the available three basic types:

1. Snapshot Replication

In Snapshot Replication data changes are not tracked. Each time a snapshot is applied it completely overwrites the existing data. A snapshot is basically a copy of all the data and objects specified in the publication. Snapshots can operate in a Push or Pull mode. By their very nature, they flow from the Publisher to the Subscribers, unidirectional.

1. Transactional Replication

In Transactional Replication a snapshot is stored in the distribution folder and synchronization jobs are recorded in the Distributor. Incremental changes made at the Publisher flow to Subscribers according to a pre-defined schedule. These changes are identified by monitoring the transaction log of each database marked for replication. The Publisher will stream the changes to the Subscribers.

1. Merge Replication

In Merge Replication incremental data changes are tracked based off of an initial snapshot, much like the other two. A Merge Agent will monitor changes at both the Publisher and the Subscriber and merge the changes together on both ends. This is great for a distributed application in which field agents are entering data and later uploading it to the central system, such as sales people or onsite auditors.

In a developer environment, I would recommend that you use Snapshot Replication. You don't want developers making accidental changes to the Master database. You just want periodic updates. And regular frequency isn't so much an issue. In the beginning stages of development you may see two to three synchronizations daily, but once the application is pretty settled you might drop down to once or twice a week.

Setting up Replication

Setting up Snapshot Replication allows you to create regular images that the Developers can Pull when they want, therefore minimizing network traffic and chance of accidentally uploading unwanted changes to the main Master database. Transaction Replication would be an alternative option, if you want more regular and frequent changes – see your own specific situation – but for the most part, Snapshot Replication will work best in most scenarios, for our given purpose, which is to keep development environments updated.

Setting it up is rather trivial. Microsoft provides a nice wizard for you, to make the job easy.

Let's start with the basics. For this demonstration I've set up a default Sitecore 6.2 installation, on both my local workstation and my central server. I've tested connection to both and verified everything works on both ends.

I now need to make changes to my server version and ensure those changes are reflected on my local copy, without having to do backup/restores.

So, start at the server. (Note: I'm going to use SQL 2008 for this, but the process is similar with both SQL 2005 and 2000. In this case, I'm using my local SQL 2008 Management Studio to connect to both my SQL 2008 local installation and the server's SQL 2005 installation.)

Publication

In the SQL Management Studio locate the Sitecore databases. They are usually called Sitecore_Core, Sitecore_Master and Sitecore_Web, but in this case I'm using a 6.2 build and by default they are called Build_Core, Build_Master and Build_Web. Below those databases is where you will find a Replication node in the tree. Expand it and you will find the Local Publications and the Local Subscriptions.

What we will do is set up a Local Publication in the server and a Local Subscription in the local workstation.

Right-click on the Local Publications and select "New Publication". That launches the wizard.

Yes, the New Publication Wizard. Could Microsoft make it anymore friendly for you?

Now comes the time to select the databases for replication.

I would suggest sticking to the Master database. You probably already have all the security credentials and all the Core items you will need. If you ever do need to get updates on that it is easy to do a backup/restore for that. But if you find yourself doing lots of customization, you may want to include Core too. For now we'll stick with Master.

Next comes the type of replication. Stick with the suggested Snapshot publication or choose your own, of you know what you are doing.

The next step is to select the tables/views you want to keep updated. Now to be honest, I haven't given it much thought – are there Sitecore tables that don't need to be updated? There might be. I just play it safe and keep them all updated. So check the top-level Tables checkbox and move on.

Now, if you want to go crazy and figure out some funky filters to reduce the amount of data to synchronize then go ahead – this is the spot to do it. Since my purpose is simply to reduce workload and keep untrained developers from having to get complicated with backup/restore, I leave the filters empty. In a backup/restore scenario, it would all come across anyway.

Now you get a few options. How often do you want to update the snapshot? You can choose option 1 and do only manual updates, but if you depend on busy SQL Admin you may want to create regular snapshots to give you the most flexibility. Once a day, once an hour – it's up to you and how often you see yourself making updates. Your workflow may vary. I would suggest that once daily is probably enough.

But the scheduler sure gives you lots of choices in the matter.

Now some security settings. The Snapshot Agent will require some credentials to run the job with. Make sure those credentials have enough privileges.

You'll need a Windows account, at a minimum.

Finally you're ready to create the job. You can also choose to generate a script file so you can recreate the job later, if needed.

The very last step is to name the publication. Choose a friendly name that tells Subscribers what the publication is about. The database name will be listed, so you just need to specify whether it's the Daily Update/Hourly Update/Developer Refresh – whatever suits your fancy.

When the job is completed you get a nice report telling you whether it succeeded or failed. If it failed it is likely a permission issue, or you don't have SQL Server Agent running.

If all goes well your publication will be listed under the Local Publications node of your server.

Now we need to make some tweaks…

A Snapshot publication will by default be created in the SQL Replication path (typically C:\MSSQL\MSSQL.1\MSSQL\ReplData or something like that.)

But, the Subscribers need to be access it via the network. That path will not work. So some changes are needed. That can either be a file share (UNC path) or an FTP location. And appropriate read/write rights need to be given, both to the Publisher and Subscriber accounts.

Right-click and go to the Properties of the Publication. Click on the Snapshot node or the FTP Snapshot node. Point the image to a share or ftp site.

Now that we have the Publication complete, we can have the local workstations Subscribe to it.

But first let's make some changes to the main Sitecore installation, so we can validate that the changes took place.

We start with a default Sitecore installation, on both the local workstation and the server.

We'll add a couple of Content Items and a User-Defined Template, just for show. These changes are on my server, called Sitecore, not on my local workstation, where Sitecore is running there as "localhost."

Now that we have changes, let's set up the Subcription.

On my local workstation I will locate the Replication section, much as I did with the server. Only this time I will right-click on the Local Subscriptions node and select New Subscriptions.

Again, you are greeted by a friendly wizard.

Use the wizard to locate the Server and select he appropriate Publication.

If you choose to follow my recommendations, choose the Pull option.

Next we want to choose the database on the local workstation that will map to the Publication. In my case they both have the same name, but you could choose a different name, if your local was different than the server's.

The next step is important, though a bit confusing. You need to select some credentials that will allow you to connect to the Distributor and the Subscriber. Click on the ellipsis button and a new screen will show you options.

You'll need to work with your DBA and SysAdmins to get this right.

You need to pick a Window account that has enough privileges on your LOCAL machine. That means that you must see that account in the Logins area of your local SQL Server. That same account must also be allowed on the Server and must be a member of the Publication Access List. This account will read changes from the server and update your local machine, so it will require high level privileges. Check the MSDN documentation if you're not sure.

Now comes another choice. My recommendation is to run this job on demand only – in true Pull mode. But if your situation calls for it, by all means Run continuously and set up a schedule.

The next choice is whether to run the job immediately or at first synchronization. That means, when you MANUALLY run the job. I prefer the later, because I can complete the definition now and then run it manually later, which will give me better access to the logs should anything not work right. Usually due to a permission issue or SQL Server Agent not being started.

Finally the job is created.

Click Next then Finish. A success message will report at the end, if all went well. If it didn't, check permissions and that SQL Server Agent is running.

So what if you are running SQL Express and you have no SQL Server Agent?

Well, that's another reason not to run immediately and run at the first synchronization.

Access the properties of the Subscription. At the bottom there is a Synchronization option. "Use Windows Synchronization Manager" should be checked, as detailed in MSDN documentation.

So, now that we have it, how do we run it?

Find the Local Subscription and right-click. Select View Synchronization Status.

A pop-up window shows you status and a "Start" button. That initiates the synchronization. Run this manually whenever you want a new update.

If you get errors here, look for permissions issues. View Job History and select the nodes that fail. There will be some error messages. If nothing is clear, check the last successful node. It will likely, at the bottom of the log, display a login/permission error of some sort.

So now you check Sitecore Content Editor and see if there are any changes. And there are none.

What?!?!?

Why didn't it work?

It did. Remember, Sitecore CACHES content. In order to get the changes you have to clear the cache. How? Plenty of ways. Make a change in the web.config, start/stop the website, or if you are feeling elegant, use the Sitecore Cache Admin screen:

It is found at http://localhost/sitecore/admin/cache.aspx and you MUST be logged in with an administrator Sitecore account or you will get an access denied message.

Click the Clear All button to clear the cache and then start your Sitecore Content Editor again.

Voila'!