SQL Server 2005: The Replication GUI
This article describes the changes to replication from the point of view of the SQL Server 2005 GUI. In case you haven’t already been checking out the new version, ‘Microsoft SQL Server Management Studio’ is the new graphical replacement for ‘Enterprise Manager’. It’s probably true to say that unlike many other administrative tasks, most DBAs intensively use the graphical interface to administer replication. With this in mind, Replication DBAs will want to discover how to perform their original tasks using the new GUI, as well as finding out about all the new functionality available. Future articles will begin to address all the new bells and whistles, but this article focuses on how to use the new interface to carry out original replication tasks.
Microsoft SQL Server Management Studio
If we initially focus on the ‘Object Explorer’ in the Management Studio, we can see some changes immediately. In the example below, there is a transactional publication called ‘TransParentsTable’ in the database ‘PaulsPublisher’ and a subscription in ‘PaulsSubscriber’ to the same publication. So, what has changed? Firstly, there is no hand under the replicated database to indicate the database is enabled for replication. Secondly, in Enterprise Manager, a publication exists in 3 separate places: a folder under the database icon, the replication folder and the replication monitor. Below we can see that this has been reduced to one occurrence in the replication folder. This is a big simplification and also removes the irritating inconsistency between the right-click menus in the three different Enterprise Manager publication icons.
Left-clicking on the publication yields the following ‘Summary’ details in the right-hand pane. This lists the snapshot and log-reader agent’s latest history, and the current agent status.
The corresponding Summary pane for the distribution agent is currently unavailable, but presumably this will display the corresponding information for the distribution agent. Note that the Summary pane is a HTML page and there is no option to right-click the displayed icons. So, these are not the real agents, just a historical display and in that way things are done quite differently to Enterprise Manager as the agents themselves are no longer accessible via the replication/publication folders. Of course we know that the replication agents are essentially just jobs running the appropriate replication executable and are therefore still available through the SQL Server Agent Jobs folder, but this involves trawling through all the other administrative jobs. So, to access just the agents, we must use the Replication Monitor which has been changed substantially and is now a stand-alone application (see section below).
If we right-click the replication folder, we have the following menu
This is very similar to the replication monitor right-click menu from Enterprise Manager, and also the tools menu, the latter of which has now been removed – so this is the now only way we can get access to the publisher and distributor properties. They have been separated into 2 different options to make things simpler, but essentially this is the same form as in SQL Server 2000.