Microsoft® describes replication as a set of solutions that allow you to copy, distribute, and potentially modify data across your enterprise. Simply put it’s involves a mechanism that makes it possible to synchronize data across multiple database copies in any enterprise.

We’ve been running MSSQL 2000 replication for a while now to isolate our Reporting user functions from the Read/Write transactions folks.Recently, we began migrating from MSSQL 2000 to MSSQL 2012. The migration process of the Databases and applications required that we setup replication between MSSQL 2000 and MSSQL 2012 temporarily.

Microsoft documentation makes it clear that Transactional Replication between these two versions is not supported:

a) A Subscriber to a transactional publication can be any version within two versions of the Publisher version. For example: a SQL Server 2000 Publisher can have SQL Server 2008 Subscribers; and a SQL Server 2008 Publisher can have SQL Server 2000 Subscribers.

b) A Subscriber to a merge publication can be any version less than or equal to the Publisher version.

After some testing though, I was able to get Transactional Replication working between MSSQL 2000 and MSSQL 2012, with an MSSQL 2008 server in between.
A few quick terminologies that help describe the Replication process:

a) Publisher: A source server that makes data available for replication.
b) Subscriber: A target server that receives the replicated data from a publisher.
c) Distributor: A database that manages the entire replication process. It could be hosted on the Publisher server, subscriber server or it’s own stand alone server.
d) Publication: Simply describes the objects to be replicated.

As a first step to setting up replication between MSSQL 2000 and MSSQL 2012, the source server is configured as Publisher and Distributor, the Publication is created during the same process. In the next step,  a separate MSSQL 2008 R2 Server is setup as the subscriber to the MSSQL 2000 Server and Publisher to the MSSQL 2012 Server .

Configure Source Server as Publisher and Distributor Server.

The Initial Publisher server is running on a Windows Server 2003 Server 64bit platform with MSSQL 2000 SP4. I have chosen to configure transactional replication on the NorthernLights test database.

Open MSSQL 2000 Enterprise Manager and expand the server and databases folder.

On the menu tab, select Tools, Replication, Configure Publishing, Subscribers and Distribution and click next on the window:

Select the “Make ‘DBS01’ it’s own distributor; sql server will create it’s own distribution database and log” radio button:

Click next and select ‘Yes’ to keep the default snapshot folder. You could also choose to change the snapshot location:

At the ‘Customize the configuration’ page, select to keep the default configurations except you intend to make further changes.

Click Finish on the next page to complete the distribution database creation and configuring the server as a publisher.

Configure Publication on the MSSQL 2000 Publisher Server.

Still in Enterprise Manager, expand the Replication folder, right click on the Publication sub-folder and select the New Publication command:

Click next to select a Publication Database. In this case the NorthernLights test database:

Click next to select Transactional Replication in the ‘Select Publication Type Windows’:

In the ‘Specify Subscriber Types’ windows, select MSSQL Server 2000.

In the ‘Specify Articles’ window, I elected to publish all articles by turning on the ‘Publish All’ check box. In addition, I made changes that will also copy the Primary keys of each table to the MSSQL 2008 Subscriber by clicking on the ‘Article Defaults’ button, selecting ‘Table Articles’, on the Default Table Articles Properties’ window,turn on the ‘Include declared referential integrity’ check box and click ok :

Click next on the Article Issues page. And enter a name for the Publication in the ‘Select Publication Name’ page. I entered the following ‘NorthernLights-SQL2008 Publication’ name. Click next.

In the ‘Customize the Properties of the Publication’ page, I selected the ‘No create the Publication as specified’ radio button. Click next and click finish to complete the Publication configuration.

Configure Subscription on the MSSQL 2000 Publisher Server.

We’ll start by adding a new MSSQL 2008 Subscriber Server to the Publisher . Right-click on the Replication folder and select the ‘Configure Publishing, Subscribers and Distribution ‘ command.

Select the ‘New’ button, select SQL Server database option. In the ‘Registered SQL Server’ properties page, browse and select the MSSQL 2008 server you intend to use as the subscriber.In my case that would be the DBS00 Server. Clicking on the OK button, you will observe the following prompt:

Since we are trying to create a subscription to a MSSQL 2008 Subscriber server, the reqiurement at this point is to manage the subscription configuration from a SQL Server 2008 Management Studio.Click Yes to register and add the MSSQL 2008 Server ‘DBS00’ in Enterprise Manager. Log into the MSSQL 2008 subscriber and configure subscription on the MSSQL 2000 Publisher server using SQL Server Management Studio on the subscriber.

From MSSQL 2008 SQL Management studio (subscriber), connect to the publisher ‘DBS01’, expand the Local Publication sub-folder, right-click on the Publication and select the ‘New Subscription’ command. Click next to choose the Publication for which you want to create a subscription:

Click next. I selected to run all agents at the MSSQL 2000 Server . On the ‘Subscriber’ page, I selected DBS00, the MSSQL 2008 Server as Subscriber:

At the subscriber DB drop down, select to create a new Subscription database,  ‘NorthernLightsRepl’.

Click next and select to run the Agents continuously. At the Initialize Subscriptions screen, I selected to initialize at first synchronization.Click next and Finish to create the subscription.

Right-click on the newly created subscription to reinitialize, turn on the ‘Generate new Snapshot’ check box. Right-click again to open the Replication Monitor. A look at the Replication monitor will show the Agents’ status:

The snapshot agent generated 13 articles that were copied by the Distributor agent to the MSSQL 2008 Subscriber DBS00. Expanding the Database folder on the on DBS00 (MSSQL 2008 Subscriber) will show the confirm the NorthernLightsRepl db and it’s tables have been copied over successfully. We can also confirm that the Primary keys were also copied over:

In Part 2 of this post, we will setup the second part of the replication process to the MSSQL 2012 target (subscriber).