Introduction

This tutorial intends to show as you can use a transactional replication without changing the initial snapshot, i.e., without carrying out the initial synchronization between the Publisher and Subscriber. It is presupposed you have knowledge of the replication process, particularly of the configuration of a transactional type replication.

Sometimes it is not feasible to synchronize subscriptions during the transactional replication. For a while I tried to carry out transactional replication but unsuccessfully. Actually, my DB was very heavy with large tables and low network bandwidth.

For such cases there is a solution! It is possible to use copies of the published DB and restore the data in Subscriber. This way we avoid the exchange of the initial snapshot over the network, which is in fact the heaviest part of the process. The following transactions are much lighter.

However, configuring the transactional replication this way, some attention must be paid to the details. Focus on the detailed explanation that will follow of how to carry out this configuration.

Before Starting

In the CLAUDIA server, which will act as Publisher, I have created a new DB. It is termed as cars and it will keep information about cars. It is constituted by the Items table with the design presented in table 1.

Table 1 Design Table Items

The field Id is defined as PK and as Identity (identity seed = identity increment = 1). Data was introduced in the table and its aspect changed into the one presented in table 2.

Table 2 Data present in Items Table

Now the replication process should be configured.

Configure Transactional Replication

The transactional replication stores and directs to Subscriber transactions in a series. So that this becomes possible it is necessary that the DBs in Subscriber and Publisher are synchronized. In the transactional replication, this synchronization is done sending an initial snapshot to Subscriber.

Doing the synchronization manually, it is possible to reduce the time that the snapshot process takes to the time that making the DB backup takes.

Distributor and Publisher

In this tutorial, the CLAUDIA server is expected to have been already configured as Publisher and Distributor.

Configure Publishing and Distribution

At this point we should indicate the DB which contains the information to be replicated and the destination server of this information.

Publication Databases

We should start by indicating that the new DB is available to be replicated. This way, using the wizard Configure Publishing and Distribution, you enable the DB cars to do the transactional replication (Trans) in the Publication Database tab. (see Figure 1)

Figure 1 Configure Publishing and Distribution Wizard

Subscriber

In the same wizard, in the Subscribers tab the TORMENTAS server is qualified as

Subscriber. (see Figure 2)

Figure 2 Configure Publishing and Distribution Wizard

Subscribers

We should now indicate that we intend to replicate the data, i.e., to create a new publication.

Create and Manage Publications

New Publication

To create a new publication it was used the wizard Create and Manage Publications represented in figure 3.

Figure 3 Create Publication Wizard

The new publication was termed as cars_pub as it belongs to the transactional type and with a single article, the Items table.

Figure 4 New publication cars_pub

Assure synchronism between Publisher and Subscriber

The DB should be published in the Single User Mode in order to prevent its alteration. This way, it is guaranteed that the Publisher will be synchronized with the Subscriber. Before carrying out the stored procedure sp_dboption all the replication agents linked to the DB in question should be stopped. Otherwise this operation cannot be carried out. See Figures 5 and 6.

Figure 5 Parando os agents da replicao

The mode of the DB cars should be changed into Single User:

Figure 6 Colocando a DB no Modo Single User atravs

do Query Analyser

After carrying out this operation with success the DB graphically indicates its new Mode. See Figure 7.

Figure 7 DB no Modo Single User

Backup

There are two methods to make the backup of the published DB. The first method uses the Transaction Log Backup and it assumes that a DB's Full Backup has been already made. The second method uses the Full Backup of the published DB. This is the favorite method when we work with a small DB or if the DB hasn't been configured for the Full Recovery Mode.

Transaction Log

When this method is used, it takes less time for the DB to be in Single User Mode. It also takes less time to make a backup of the Transaction Log than to make a full backup. To use this method, the DB must be using the Full Recovery Mode.

This method was not tested in this tutorial.

Full Backup

If the Full Backup is the method being used, a backup of the published DB should be made.

Figure 8 Backup da BD

Figure 9 Indicando um backup complete da BD

Subscription

Push Subscription

At this point the data's addressee should be configured. For that a new subscription of the Push type was created, indicating the TORMENTAS server as destination. The destination DB was created at the same as the subscription and it is termed cars as well.

Schedule Distribution Agent

Now the schedule set of the Distribution Agent. It is crucial to schedule it so that the whole conFiguretion process of the replication without initial snapshot is concluded. This way, you should disable a continuous update and schedule this agent. It is suggested that the initial date is tomorrow (15-08-2006). The Figures 10 and 11 show what has been said before.

Figure 10 Create Push

Subscription Wizard - Set Distribution Agent Schedule

Figure 11 Create Push

Subscription Wizard - Edit Recurring Job Schedule

Initialize Subscription

Another fundamental aspect is to indicate the source and destination, i.e., the Publisher and Subscriber are already synchronized and ready for the replication and therefore, there is no need to apply the initial snapshot.

Therefore, on being asked about the initialization of the subscription, you should answer "No, the Subscriber already has the

schema and data". See Figure 12.

Figure 12 Create Push Subscription Wizard - Initialize Subscription

Replication Agents in Details Pane

Now, in the Details Pane, the following agents for the cars_pubs publications appear.

Figure 13 Details on car_pubs publication

Now the DB can be taken out of the Single User Mode as the process is configured and all the alterations will be directed to the DB distribution.

Figure 14 Retirar a BD do Modo Single User atravs das Propriedades

It is verified that now it gives error if you try to use the Query Analyzer.

Restore DB

It is not enough to indicate that the DBs are synchronized in order to obtain a transactional replication without changing the initial snapshot. It is necessary to synchronize them effectively.

As it has been said before, it was made a (full) backup of the DB in question. Now it is necessary to send this file to the Subscriber server machine.

Regarding this tutorial the machines are close to each other and therefore it was feasible only to do a Copy + Paste. If the machines are far from each other, there is the possibility to record the backup in a disk and send it per airmail.

For that: Run

Figure 15 Enviar o ficheiro de backup para a mquina do Subscriber

Simply with a CTRL+C and CTRL+V you will move a copy of that backup file.

In order to do the DB's restore, the Distribution Agent should not be running. Otherwise it gives an error.

Figure 16 Restore Database no Subscriber

The wizard showed in Figure 17 will appear.

Figure 17 Restore DB as cars

It is important to indicate that the restore will be carried out from the disk (Device); afterwards the path to the backup file will be indicated and it should be shown that it is a full backup.

After receiving the message the restore is successfully done you can confirm that in the destination, TORMENTAS server, it was created an Items table, which contains the same information. This way the synchronism between the Publisher and Subscriber will be obtained.

It is to highlight that the DB in Subscriber may appear in the Single User Mode. Therefore that should be altered back to multi-user.

Stored Procedures

In a transactional replication, during the initial snapshot, 3 stored procedures per article are generated, one for each type of transaction. Particularly in this tutorial, there is the Items article, and the following stored procedures would be generated:

• sp_MSins_Items (inserts);
• sp_MSdel_Items (deletes);
• sp_MSupd_Items (updates).

As the synchronization process is being done manually, we also have to write the stored procedures or generate them. Fortunately there is already a stored procedure to generate them.

Script custom stored procedures at Publisher

Now it is necessary to create the procedures of Insert, Update and Delete that are used during the replication. For that, in case we are working with a SQL Server 2000 (without immediate updating and queued Subscribers) we only need to run the stored procedure sp_scriptpublicationcustomprocs in Publisher. This stored procedure should be carried out in the published DB.

Figure 18 Gerar os precedures Insert, Update e

Delete com o stored procedure sp_scriptpublicationcustomprocs

It created the code showed in Figure 19.

Figure 19 Cdigo gerado com sp_scriptpublicationcustomprocs

Applying scripts at Subscriber

To apply the created scripts to the Subscriber we should paste the code created

previously in Query Analyzer and execute it on the subscriber. Figure 20 shows this operation. It should be highlighted that this should be carried out over the DB in question.

Figure 20 Executando os stored procedures no

Subscriber

And that's all!

Now all the alterations carried out over the published DB will be reflected in the

correspondent DB in the Subscriber.

Last Note

The field Id of the Items table had the enabled Identity property, i.e., yes. As the Items table is precisely equal it will also possess that enabled property. This is due to the method that is being used (backup+restore). It is important to highlight that if there is the delivery of the initial snapshot to the Subscriber, the objects of the DB are not transferred to the Subscriber.

However, The Identity property to Yes, causes error during the replication. See Figure 21.

Figure 21 Erro gerado pela propriedade Identity

It is necessary to disable that property in Subscriber. (see Figure 22)

Figure 22 Disable the Identity property

Testing

The following some captures for testing. Three types of transactions carried out in Publisher and reflected in Subscriber will be presented.

Replication Insert

The Figure 23 shows the insert of a row in the Items table in Publisher.

Figure 23 Insert on Publisher

Automatically, the replication agents identify the insert. (see Figure 24)

Figure 24 Agentes da replicao detectam o insert

And in the Subscriber, the insert is reflected. (See Figure 25)

Figure 25 Insert reflected on the Subscriber

Replication delete and update

The following figures show the other two possibilities, a delete and the update. If you delete (row with Id=1) and update as in Figure 26.

Figure 26 Delete and Update on the Publisher

Figure 27 Agentes da replicao detectam o delete e o update

Figure 28 Delete and Update reflected on the Subscriber

Conclusion

The experience tells me that the transactional replication is an excellent process to keep one (or more) synchronized copy of a certain DB . With little effort (there is the possibility to configure the whole process resorting to wizards only) it is possible to make the replication work.

However, when it concerns a DB with a considerable dimension or/and the network that links the Publisher and Subscriber has a reduced BW, it becomes complicated to pass the initial snapshot through it. The process showed in this tutorial gets round this situation. Through an easy and quick way we can initiate a replication without submitting the initial synchronization.

Nevertheless source and destination have to be synchronized. This way a manual synchronization is done. For that, the backup of the transaction log or of the DB itself can be used and then applied in the destination. Now we can avoid the network and resort, for instance, to a USB disc. Or even use the network and resort, for example, to the ftp. It is to highlight that despite the fact the ftp is over the network, the backup of a DB can be softer than its snapshot, as this includes, besides the data (.bcp), the schemas (.sch) and constraints (.idx).