Transactional Replication in SQL Server 2000
Microsoft SQL Server 2000
An Introduction to Replication and
Step by Step process to Setup Transactional Replication
By
K Madhu
Introduction. This article gives a brief description of Replication and a Step by Step process of configuring Transactional Replication. I have not included the screen shot of setting up because it will occupy lot of space. I have given the Screen Name in quotes to identify which screen I am mentioning.
1. Introduction to Replication. Replicating Business data across an organization involves much more than simply copying a piece of information from one site to another. A replication system must address all of the following business needs :
(a) High Availability of Data :- The Replication system should be reliable and should not expose business operation to computer system failure.
(b) Consistent Information Delivery :- The distribution system must protect the integrity of data.
(c) High Performance :- The replication system must not burden the source of the data, must use networks efficiently, and must allow each site to optimize access methods to local data.
(d) Easy centralized Administration :- Administrators must be able to easily manage all the distributed components of the replication system from a single desktop.
(e) Heterogeneous Data Source Access :- The replication system should be able to move data across different vendors data sources.
(f) Local Autonomy :- Each site which receives replicate data should be free to decide which set of data it wishes to receive, how it will view the data, how it will access the data, and how it will modify the data.
3. What is Replication : Only a few a years ago , corporate data resided in a central location. Remote departments accessed the information they needed by establishing direct connections to the central sites, or by requesting printed reports from central MIS. The connections however were expensive, unreliable, and limited in number while the reports were inflexible and not timely.
Open systems brought inexpensive and powerful computing resources to all corners of an enterprise. The ability to share corporate information effectively using these new resources became an important competitive advantage for organizations. The question enterprises face today is not “Why distribute and share corporate data” , but rather “How can one distribute information effectively”.
4. Importance of Replication : In the present competitive environment, organization quickly discovered that a reliable replication system must do much more than simply copy a piece of data. The system must also :-
· Maintain the integrity of the data at the transaction level
· Deliver data quickly and efficiently across the network
· Allow distributed sites to modify data
· Be easy to monitor and manage
· Transfers data in any direction across heterogeneous data sources
5. Important factors for Planning Replication. When planning for replication you should consider the following factors :-
(a) Autonomy. You have to consider the level of autonomy or server independence that you want to support at each site. Determine what data needs to be replicated and at what frequency.
(b) Latency. It determines the time delay between updates. Ie. The time taken for a change at the publishing server to be made available at the subscribing server. Less Latency high data consistency.
(c) Data Consistency. Data consistency is the critical factor. It can be accomplished through data convergence or transaction consistency. Replication can ensure data consistency , but you will have to keep potential latency in mind . How data consistency is managed and maintained is somewhat dependent on the replication method you select.
Data Convergence means that all sites eventually end up with the same values. However , the values are not necessarily the same as they would be if all of the changes had taken place on one server
Transactional Consistency. Is a little different. The results at any server are the same as if all transaction were executed on a single server
(d) Schema consistency. The best part of SQL Server 2000 is that it supports Schema changes during relocation provided it is done through SP_Repl schema change stored procedure or through Replication GUI.
(e) You should consider your geographic location and connectivity available.
5. SQL Server 2000 as a Replication Server. Microsoft SQL Server comes with the replication server where as other vendors is not. For other vendors like Sybase it is a separate product altogether.
6. Components of Replication.
Publisher
Distributor
Publisher
Subscriber
Subscriber
Publisher. The source database is maintained at the Publisher. It is a server that makes the data available for subscription to other servers. It also identifies what data has changed at the subscriber during the synchronizing process.
Distributor. The Distributor collects them from the publisher’s transaction log. There are two types of replication Local The changes to the published data are stored on the Distributor’s Distribution Database where they are held until forwarded to Subscribers. The Distributor can be on the same server as the Publisher or can be at a different server. It depends upon your setting up and the server configuration. The distribution database stores any transaction awaiting distribution and tracks the replication status. A distributor can server multiple Publishers and Subscribers (see pict)
Subscriber. Subscriber do what it literally means. By subscribing to a publication which has one or more articles (Tables or any objects like sps , views etc) the Subscriber is subscribing to all of the articles in the publication. The subscriber can not subscribe to individual articles alone. Ie. It has the destination database. Subscription can be either PUSH or PULL
7. Type of Replication . MSSQL Server supports various types of replication. The type can be decided by answering following question.
Connectivity :- Sometime you will not have connectivity and you even want to replicate.
To Reduce Latency:- In some situation the data updates may required to be real time.
What is the tolerance for data on one or more of the replicated servers.
All the above points keeping in mind you can decide the type of Replication.
Snapshot Replication. When do Snapshot replication, a “Picture” is taken at the source of all of the data to be replicated. The destination database data will be replaced with this data. ( I am not going into detail of Snapshot as this article is aimed to address transactional replication).
Transactional Replication Is also known as dynamic replication. Here the modification to the publication at the publisher are propagated to the subscriber incrementally. Any logged changes to the published articles, such as Insert,Update and delete statements are tracked and replicated to the subscriber. All transaction are applied to the subscriber in the same order as it was applied to the publisher. In Transactional replication the modification are applied to the subscriber more or less real-time updates.
Merge Replication. Merge Replication provides advantageous of both snapshot and transactional replication. After the initial snapshot applied to the subscriber both the publisher and subscriber changes are tracked by the SQL Server. The changes will be synchronized on scheduled basis or on demand. ( I am not going into detail of Merge replication as this article is aimed to address transactional replication).
8. How Transactional Replication Works.
Log Reader Agent After a database is set up for transactional replication, its transactional log is monitored for changes to published tables. The log reader agent then has responsibility for copying the transactions marked for replication from the publisher to the distributor..
Distribution Agent This is responsible updating the data from distributor to the subscribers.
9. The Process of Transactional Replication
When you configure Transactional replication it copies the schema and data first time by the snapshot agent. After the initial snapshot has been completed transactional replication follows these basic steps :-
(a) Modifications are posted to the publisher’s transaction log
(b) The Log Reader Agent reads the transaction log and identifies changes marked for replication
(c) Changes taken from the transaction log are written to the distribution database on the distributor
(d) The distribution agent applies the changes to the appropriate database tables.
(e) You can set up the log reader agent to read the transaction log continuously or on a schedule basis.
10. When to use Transactional Replication :You should use Transactional replication when you want to reduce latency and provide Subscribers with relatively near real-time information. It normally required LAN connection.
Step by step procedure to configure Transactional Replication
Organizational Setting Up
Server A - This is the server where the Publisher (Source Database ) situated. You can also configure Distributor on the same server. So here I am configuring Publisher and Distributor on the Same Server.
Server B - This is the server Subscriber . Both publisher and subscriber are SQLServer 2000.
Login.
In the ideal scenario Server A and Server B will have their own Database Administrator (SA) and Password. So you have to have one login in Server B (ie in the subscriber server) for the replication purpose. Register the Server B in Server A using that login and password.
Configuring Distributor
(a) From the EM (Enterprises Manager) select Tools à Replication à Configure Publishing Subscriber and Distributor.
(b) Now you will get a “Welcome to the Configure Publishing and Distribution wizard” welcome Screen.
© Click Next
(d) Now you will get a “Configure Publishing and Distribution wizard for Server A” Screen. In this screen you will get all the Distributor server name which is already there n the same domain. Either you can select the existing server as your Distributor or Configure a new one. Here we are configuring Server A as Distributor. So select the first option “Make Server A its own distributor: SQL Server will create a distribution database and log” option from this window.
(e) click Next
(f) if You have not changed the SQL Server Agent startup account from system account to any of your windows domain user account the following message will popup “SQL Server Agent on Server A currently uses system Account which causes replication between server fail ……”. If you found this message first you will have to configure SQL Server Agent in some other account (see Book on line SQL Server Agent help). It is assumed that you have a dedicated domain account to start sql server agent and it added into your SQL Server Login also.
(g) Now You will get a window called “Specify Snapshot Folder” Here you can define the folder as you required. By default SQL Server Installed path will come.
(h) Click next
(i) Now, you will get a warning message. If you have kept the default folder . Click “Yes”.
(j) Now you will get “Customise the configuration “ window. Here you have two option .
· Yes, Let me set the distribution ……
When you click this option you can give your own distribution database name and distribution database data files path.
· No, Use the Default setting.
If you click this option the distribution database will be named as Distribution and files will be stored in the SQL Server default path.
(k (a) ) if you click “NO” option in the above screen you will get “Completing the configure Publishing and distribution wizard”.
(k (b)) if you click “Yes” option in the above screen , you will get “Provide Distribution Database Information Screen” where you can change the default configuration of distribution database.
(k © ) After the need full changes been done, click next.
(k (d) ) Now you will get “Enable Publisher” window, where by default Server A will be checked. Click next.
(k (e) ) Now you will get “Enable Publication Database” window from where you can select the Database which has to be Published (ie. Used as source database in replication). Here you can select either Transactional Replication or Merge Replication. Not Snapshot. Here I would Prefer, not to create Publisher or subscriber from this window. Just do not check any thing here click next, next and finish.
(l) Click Finish to create Distribution Database.
(m) You will get a confirmation window stating that the Server A is now a distributor ……. Etc.
(n) Click Close.
After the configuration of distribution database, the Replication monitor toll will be added Automatically to your server A. Now you can see that a new distribution database which you have defined earlier, is created which will act as a repository of data to be replicated.
Configuring Publication
After configuring distributor, the second step is to publish your Source database. To configure Publication do the following steps :-
(a) in the EM click on Tools à Replication à Create and Manage
Publication option
(b) You will get “Create and Manage Publication of Server A” window ,
click on “Create Publication “
(c) You will get “Welcome to the Create Publication wizards” Screen .
(d) Check Show Advance Option and click Next
(e) In the “Choose Publication Database” window select the database which is to be published and click next.
(f) Now you will get “Select Publication Type” window from where you can select the publication type. As this article is for Transactional Replication I would select Transactional replication. Click Next.
(g) click “Immediate Updating” In the “Updatable Subscriptions” window. If you select “Queued Updating” then the changes will be replicated to Subscriber as per the Schedule. Press Next.
(h) Now You will get “Specify Subscriber Types” Windows , select the suitable option from this window. Here I am select “Server Running SQL Server 2000”. Click Next
(i) Now you have “Specify Article” Window , where you can select which all are the object you want to replicate. You can find a Button called “Article Default” . Click on this button .
I. Now you have a window called “Default Table/object Article Property” and it has three Tabs (incase of table object). It depends on what object you have selected).
II. In the “General” Tab you can change the owner of the Destination Table if needed.
III. In the “Command” Tab of this window you can change the Naming convention of the Triggers which will be created.
IV. In the “Snapshot “ window of this table you have so many options. By default “Drop the existing table and re-create it” Radio Button will be selected. You can check the User Triggers check box if you want to replicate the triggers also. If you want to uncheck some object you can do that here.
(j) click Next in the “Specify Article” Window after doing the need full change as per mentioned in sl. h(a) , h(b) , h(c),h(d).
(k) Now you have “Article Issue” window where you no need to do any thing, just click next.
(l) In the “Select Publication Name and Description” window you can give the appropriate name like TransReplServerAtoServerBDatabaseName etc. Click Next
(m) Now you have “Customise the property of the Publication” window where You have two option.
· “Yes I will filter …” : If you want to apply any filter to the source table you can select in this window. Ie. If you want to replicate some selected column/row of the source table then select “Vertical / Horizontal Filtering as you needed.
· “No , Create the Publication….”
Click Next
(n) In the “Completing the Create Publication wizard” click “Finish”.
After configuring the Publisher, your published data base will have Shared Symbol.
Configuring Subscription
After configuring Publisher, next step is to configure Subscription. To configure Subscription do the following steps. You have two types of Subscription. Push, Pull Subscription. Here were are configuring Push Subscription. :-
in the EM click on Tools à Replication à Push Subscription to others
(a) in the “Create and Manage Publication on ServerA” window , expand the published database. Select the Required Publication from this window and click on “Push New Subscription” .
(b) In the “welcome to Push Subscription Wizards” window check “Show Advance options in this wizard” and click Next.
(c) “Choose Subscriber “ window select ServerB from the Servers List shown in the list.
(d) In the “choose Destination Database” window give the Subscription Name and click on “Browse and Create” button to select the destination database in ServerB. Now you will get a list of all user defined database in the ServerB and you can select the destination database from the list and click OK.
(e) Click Next.
(f) In the “Set Distribution Agent Location” window you have two options
· Run the Agent at the distributor
· Run the agent at the subscriber
Select the Run the agent at the distributor.
Click Next
(g) in the “Distribution Agent Schedule” window You have two options
· Continuously : By selecting this option the changes will be replicated to the subscriber near real time.
· Using the Following schedule : if you have selected this option , the changes will be updated in the target database as per the schedule.
Select Continuously and click next.
(h) In the “Initialize Subscription” window you have two options
· Yes Initialize the schema and data : When you select this option you have another option “Start the Snapshot agent ….” Check this box also to start the replication immediately.
· No the Subscriber already ……. :
When you configure the subscriber first time check the first option and click Next.
(h) In the “Updatable Subscription “ window you have two option
· None – the changes are not Replicated : This is the most used option
· Immediate Updating : if you are selecting this option you should be careful
(i) in the “Start Required Service” window the “SQL Server Agent” box will be already checked if the Agent is running. Otherwise you can check this box and click Next
(j) in the “Completing Push Subscription wizards” window click finish
The Subscription configuration is completed. You will get a confirmation box saying that “Subscription were created on ServerB successfully at the following ……” . Close the window.
Conclusion. In brief, Transactional replication give you a near real-time data at the subscriber. Once you have configured Replication, you can make script of the configuration you have made. SQL Server gives us a way of generating replication scripts , which are Transact – SQL scripts based on the replication configuration what you have already made . This scripts let us document our configuration and give us an easy way to recover and reinstall replication. If you need to set up multiple servers with identical configuration, you can configure graphically in one server, generate a script and use the script to configure the remaining servers.
Author :
K Madhu
DBA
DOP, Indian Navy
e-mail ID - madhuotp@Yahoo.com
An Introduction to Replication and
Step by Step process to Setup Transactional Replication
By
K Madhu
Introduction. This article gives a brief description of Replication and a Step by Step process of configuring Transactional Replication. I have not included the screen shot of setting up because it will occupy lot of space. I have given the Screen Name in quotes to identify which screen I am mentioning.
1. Introduction to Replication. Replicating Business data across an organization involves much more than simply copying a piece of information from one site to another. A replication system must address all of the following business needs :
(a) High Availability of Data :- The Replication system should be reliable and should not expose business operation to computer system failure.
(b) Consistent Information Delivery :- The distribution system must protect the integrity of data.
(c) High Performance :- The replication system must not burden the source of the data, must use networks efficiently, and must allow each site to optimize access methods to local data.
(d) Easy centralized Administration :- Administrators must be able to easily manage all the distributed components of the replication system from a single desktop.
(e) Heterogeneous Data Source Access :- The replication system should be able to move data across different vendors data sources.
(f) Local Autonomy :- Each site which receives replicate data should be free to decide which set of data it wishes to receive, how it will view the data, how it will access the data, and how it will modify the data.
3. What is Replication : Only a few a years ago , corporate data resided in a central location. Remote departments accessed the information they needed by establishing direct connections to the central sites, or by requesting printed reports from central MIS. The connections however were expensive, unreliable, and limited in number while the reports were inflexible and not timely.
Open systems brought inexpensive and powerful computing resources to all corners of an enterprise. The ability to share corporate information effectively using these new resources became an important competitive advantage for organizations. The question enterprises face today is not “Why distribute and share corporate data” , but rather “How can one distribute information effectively”.
4. Importance of Replication : In the present competitive environment, organization quickly discovered that a reliable replication system must do much more than simply copy a piece of data. The system must also :-
· Maintain the integrity of the data at the transaction level
· Deliver data quickly and efficiently across the network
· Allow distributed sites to modify data
· Be easy to monitor and manage
· Transfers data in any direction across heterogeneous data sources
5. Important factors for Planning Replication. When planning for replication you should consider the following factors :-
(a) Autonomy. You have to consider the level of autonomy or server independence that you want to support at each site. Determine what data needs to be replicated and at what frequency.
(b) Latency. It determines the time delay between updates. Ie. The time taken for a change at the publishing server to be made available at the subscribing server. Less Latency high data consistency.
(c) Data Consistency. Data consistency is the critical factor. It can be accomplished through data convergence or transaction consistency. Replication can ensure data consistency , but you will have to keep potential latency in mind . How data consistency is managed and maintained is somewhat dependent on the replication method you select.
Data Convergence means that all sites eventually end up with the same values. However , the values are not necessarily the same as they would be if all of the changes had taken place on one server
Transactional Consistency. Is a little different. The results at any server are the same as if all transaction were executed on a single server
(d) Schema consistency. The best part of SQL Server 2000 is that it supports Schema changes during relocation provided it is done through SP_Repl schema change stored procedure or through Replication GUI.
(e) You should consider your geographic location and connectivity available.
5. SQL Server 2000 as a Replication Server. Microsoft SQL Server comes with the replication server where as other vendors is not. For other vendors like Sybase it is a separate product altogether.
6. Components of Replication.
Publisher
Distributor
Publisher
Subscriber
Subscriber
Publisher. The source database is maintained at the Publisher. It is a server that makes the data available for subscription to other servers. It also identifies what data has changed at the subscriber during the synchronizing process.
Distributor. The Distributor collects them from the publisher’s transaction log. There are two types of replication Local The changes to the published data are stored on the Distributor’s Distribution Database where they are held until forwarded to Subscribers. The Distributor can be on the same server as the Publisher or can be at a different server. It depends upon your setting up and the server configuration. The distribution database stores any transaction awaiting distribution and tracks the replication status. A distributor can server multiple Publishers and Subscribers (see pict)
Subscriber. Subscriber do what it literally means. By subscribing to a publication which has one or more articles (Tables or any objects like sps , views etc) the Subscriber is subscribing to all of the articles in the publication. The subscriber can not subscribe to individual articles alone. Ie. It has the destination database. Subscription can be either PUSH or PULL
7. Type of Replication . MSSQL Server supports various types of replication. The type can be decided by answering following question.
Connectivity :- Sometime you will not have connectivity and you even want to replicate.
To Reduce Latency:- In some situation the data updates may required to be real time.
What is the tolerance for data on one or more of the replicated servers.
All the above points keeping in mind you can decide the type of Replication.
Snapshot Replication. When do Snapshot replication, a “Picture” is taken at the source of all of the data to be replicated. The destination database data will be replaced with this data. ( I am not going into detail of Snapshot as this article is aimed to address transactional replication).
Transactional Replication Is also known as dynamic replication. Here the modification to the publication at the publisher are propagated to the subscriber incrementally. Any logged changes to the published articles, such as Insert,Update and delete statements are tracked and replicated to the subscriber. All transaction are applied to the subscriber in the same order as it was applied to the publisher. In Transactional replication the modification are applied to the subscriber more or less real-time updates.
Merge Replication. Merge Replication provides advantageous of both snapshot and transactional replication. After the initial snapshot applied to the subscriber both the publisher and subscriber changes are tracked by the SQL Server. The changes will be synchronized on scheduled basis or on demand. ( I am not going into detail of Merge replication as this article is aimed to address transactional replication).
8. How Transactional Replication Works.
Log Reader Agent After a database is set up for transactional replication, its transactional log is monitored for changes to published tables. The log reader agent then has responsibility for copying the transactions marked for replication from the publisher to the distributor..
Distribution Agent This is responsible updating the data from distributor to the subscribers.
9. The Process of Transactional Replication
When you configure Transactional replication it copies the schema and data first time by the snapshot agent. After the initial snapshot has been completed transactional replication follows these basic steps :-
(a) Modifications are posted to the publisher’s transaction log
(b) The Log Reader Agent reads the transaction log and identifies changes marked for replication
(c) Changes taken from the transaction log are written to the distribution database on the distributor
(d) The distribution agent applies the changes to the appropriate database tables.
(e) You can set up the log reader agent to read the transaction log continuously or on a schedule basis.
10. When to use Transactional Replication :You should use Transactional replication when you want to reduce latency and provide Subscribers with relatively near real-time information. It normally required LAN connection.
Step by step procedure to configure Transactional Replication
Organizational Setting Up
Server A - This is the server where the Publisher (Source Database ) situated. You can also configure Distributor on the same server. So here I am configuring Publisher and Distributor on the Same Server.
Server B - This is the server Subscriber . Both publisher and subscriber are SQLServer 2000.
Login.
In the ideal scenario Server A and Server B will have their own Database Administrator (SA) and Password. So you have to have one login in Server B (ie in the subscriber server) for the replication purpose. Register the Server B in Server A using that login and password.
Configuring Distributor
(a) From the EM (Enterprises Manager) select Tools à Replication à Configure Publishing Subscriber and Distributor.
(b) Now you will get a “Welcome to the Configure Publishing and Distribution wizard” welcome Screen.
© Click Next
(d) Now you will get a “Configure Publishing and Distribution wizard for Server A” Screen. In this screen you will get all the Distributor server name which is already there n the same domain. Either you can select the existing server as your Distributor or Configure a new one. Here we are configuring Server A as Distributor. So select the first option “Make Server A its own distributor: SQL Server will create a distribution database and log” option from this window.
(e) click Next
(f) if You have not changed the SQL Server Agent startup account from system account to any of your windows domain user account the following message will popup “SQL Server Agent on Server A currently uses system Account which causes replication between server fail ……”. If you found this message first you will have to configure SQL Server Agent in some other account (see Book on line SQL Server Agent help). It is assumed that you have a dedicated domain account to start sql server agent and it added into your SQL Server Login also.
(g) Now You will get a window called “Specify Snapshot Folder” Here you can define the folder as you required. By default SQL Server Installed path will come.
(h) Click next
(i) Now, you will get a warning message. If you have kept the default folder . Click “Yes”.
(j) Now you will get “Customise the configuration “ window. Here you have two option .
· Yes, Let me set the distribution ……
When you click this option you can give your own distribution database name and distribution database data files path.
· No, Use the Default setting.
If you click this option the distribution database will be named as Distribution and files will be stored in the SQL Server default path.
(k (a) ) if you click “NO” option in the above screen you will get “Completing the configure Publishing and distribution wizard”.
(k (b)) if you click “Yes” option in the above screen , you will get “Provide Distribution Database Information Screen” where you can change the default configuration of distribution database.
(k © ) After the need full changes been done, click next.
(k (d) ) Now you will get “Enable Publisher” window, where by default Server A will be checked. Click next.
(k (e) ) Now you will get “Enable Publication Database” window from where you can select the Database which has to be Published (ie. Used as source database in replication). Here you can select either Transactional Replication or Merge Replication. Not Snapshot. Here I would Prefer, not to create Publisher or subscriber from this window. Just do not check any thing here click next, next and finish.
(l) Click Finish to create Distribution Database.
(m) You will get a confirmation window stating that the Server A is now a distributor ……. Etc.
(n) Click Close.
After the configuration of distribution database, the Replication monitor toll will be added Automatically to your server A. Now you can see that a new distribution database which you have defined earlier, is created which will act as a repository of data to be replicated.
Configuring Publication
After configuring distributor, the second step is to publish your Source database. To configure Publication do the following steps :-
(a) in the EM click on Tools à Replication à Create and Manage
Publication option
(b) You will get “Create and Manage Publication of Server A” window ,
click on “Create Publication “
(c) You will get “Welcome to the Create Publication wizards” Screen .
(d) Check Show Advance Option and click Next
(e) In the “Choose Publication Database” window select the database which is to be published and click next.
(f) Now you will get “Select Publication Type” window from where you can select the publication type. As this article is for Transactional Replication I would select Transactional replication. Click Next.
(g) click “Immediate Updating” In the “Updatable Subscriptions” window. If you select “Queued Updating” then the changes will be replicated to Subscriber as per the Schedule. Press Next.
(h) Now You will get “Specify Subscriber Types” Windows , select the suitable option from this window. Here I am select “Server Running SQL Server 2000”. Click Next
(i) Now you have “Specify Article” Window , where you can select which all are the object you want to replicate. You can find a Button called “Article Default” . Click on this button .
I. Now you have a window called “Default Table/object Article Property” and it has three Tabs (incase of table object). It depends on what object you have selected).
II. In the “General” Tab you can change the owner of the Destination Table if needed.
III. In the “Command” Tab of this window you can change the Naming convention of the Triggers which will be created.
IV. In the “Snapshot “ window of this table you have so many options. By default “Drop the existing table and re-create it” Radio Button will be selected. You can check the User Triggers check box if you want to replicate the triggers also. If you want to uncheck some object you can do that here.
(j) click Next in the “Specify Article” Window after doing the need full change as per mentioned in sl. h(a) , h(b) , h(c),h(d).
(k) Now you have “Article Issue” window where you no need to do any thing, just click next.
(l) In the “Select Publication Name and Description” window you can give the appropriate name like TransReplServerAtoServerBDatabaseName etc. Click Next
(m) Now you have “Customise the property of the Publication” window where You have two option.
· “Yes I will filter …” : If you want to apply any filter to the source table you can select in this window. Ie. If you want to replicate some selected column/row of the source table then select “Vertical / Horizontal Filtering as you needed.
· “No , Create the Publication….”
Click Next
(n) In the “Completing the Create Publication wizard” click “Finish”.
After configuring the Publisher, your published data base will have Shared Symbol.
Configuring Subscription
After configuring Publisher, next step is to configure Subscription. To configure Subscription do the following steps. You have two types of Subscription. Push, Pull Subscription. Here were are configuring Push Subscription. :-
in the EM click on Tools à Replication à Push Subscription to others
(a) in the “Create and Manage Publication on ServerA” window , expand the published database. Select the Required Publication from this window and click on “Push New Subscription” .
(b) In the “welcome to Push Subscription Wizards” window check “Show Advance options in this wizard” and click Next.
(c) “Choose Subscriber “ window select ServerB from the Servers List shown in the list.
(d) In the “choose Destination Database” window give the Subscription Name and click on “Browse and Create” button to select the destination database in ServerB. Now you will get a list of all user defined database in the ServerB and you can select the destination database from the list and click OK.
(e) Click Next.
(f) In the “Set Distribution Agent Location” window you have two options
· Run the Agent at the distributor
· Run the agent at the subscriber
Select the Run the agent at the distributor.
Click Next
(g) in the “Distribution Agent Schedule” window You have two options
· Continuously : By selecting this option the changes will be replicated to the subscriber near real time.
· Using the Following schedule : if you have selected this option , the changes will be updated in the target database as per the schedule.
Select Continuously and click next.
(h) In the “Initialize Subscription” window you have two options
· Yes Initialize the schema and data : When you select this option you have another option “Start the Snapshot agent ….” Check this box also to start the replication immediately.
· No the Subscriber already ……. :
When you configure the subscriber first time check the first option and click Next.
(h) In the “Updatable Subscription “ window you have two option
· None – the changes are not Replicated : This is the most used option
· Immediate Updating : if you are selecting this option you should be careful
(i) in the “Start Required Service” window the “SQL Server Agent” box will be already checked if the Agent is running. Otherwise you can check this box and click Next
(j) in the “Completing Push Subscription wizards” window click finish
The Subscription configuration is completed. You will get a confirmation box saying that “Subscription were created on ServerB successfully at the following ……” . Close the window.
Conclusion. In brief, Transactional replication give you a near real-time data at the subscriber. Once you have configured Replication, you can make script of the configuration you have made. SQL Server gives us a way of generating replication scripts , which are Transact – SQL scripts based on the replication configuration what you have already made . This scripts let us document our configuration and give us an easy way to recover and reinstall replication. If you need to set up multiple servers with identical configuration, you can configure graphically in one server, generate a script and use the script to configure the remaining servers.
Author :
K Madhu
DBA
DOP, Indian Navy
e-mail ID - madhuotp@Yahoo.com
posted by Madhu K Nair at
9:06 AM
0 Comments:
Post a Comment
Subscribe to Post Comments [Atom]
<< Home