SQL Server 2005 - Merge Replication Step by Step Procedure

Introduction
Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. Using replication, you can distribute data to different locations and to remote or mobile users over local and wide area networks, dial-up connections, wireless connections, and the Internet.
Replication is the process of sharing data between databases in different locations. Using replication, we can create copies of the database and share the copy with different users so that they can make changes to their local copy of database and later synchronize the changes to the source database.
Terminologies before getting started:
Microsoft SQL Server 2000 supports the following types of replication
Publisher is a server that makes the data available for subscription to other servers. In addition to that, publisher also identifies what data has changed at the subscriber during the synchronizing process. Publisher contains publication(s).
Subscriber is a server that receives and maintains the published data. Modifications to the data at subscriber can be propagated back to the publisher.
Distributor is the server that manages the flow of data through the replication system. Two types of distributors are present, one is remote distributor and the other one local distributor. Remote distributor is separate from publisher and is configured as distributor for replication. Local distributor is a server that is configured as publisher and distributor.
Agents are the processes that are responsible for copying and distributing data between publisher and subscriber. There are different types of agents supporting different types of replication.
Snapshot Agent is an executable file that prepares snapshot files containing schema and data of published tables and database objects, stores the files in the snapshot folder, and records synchronization jobs in the distribution database.
An article can be any database object, like Tables (Column filtered or Row filtered), Views, Indexed views, Stored Procedures, and User defined functions.
Publication is a collection of articles.
Subscription is a request for copy of data or database objects to be replicated.

Replication Types
Microsoft SQL Server 2005 supports the following types of replication:
• Snapshot Replication
• Transactional Replication
• Merge Replication
Snapshot Replication
• Snapshot replication is also known as static replication. Snapshot replication copies and distributes data and database objects exactly as they appear at the current moment in time.
• Subscribers are updated with complete modified data and not by individual transactions, and are not continuous in nature.
• This type is mostly used when the amount of data to be replicated is small and data/DB objects are static or does not change frequently.
Transactional Replication
• Transactional replication is also known as dynamic replication. In transactional replication, modifications to the publication at the publisher are propagated to the subscriber incrementally.
• Publisher and the subscriber are always in synchronization and should always be connected.
• This type is mostly used when subscribers always need the latest data for processing.
Merge replication
It allows making autonomous changes to replicated data on the Publisher and on the Subscriber. With merge replication, SQL Server captures all incremental data changes in the source and in the target databases, and reconciles conflicts according to rules you configure or using a custom resolver you create. Merge replication is best used when you want to support autonomous changes on the replicated data on the Publisher and on the Subscriber.
Replication agents involved in merge replication are snapshot agent and merge agent.
Implement merge replication if, changes are made constantly at the publisher and subscribing servers, and must be merged in the end.
By default, the publisher wins all conflicts that it has with subscribers because it has the highest priority. Conflict resolver can be customized
Before starting the replication process:
assume that we have 2 server:
• EGYPT-AEID: is the publisher server ( contains HRatPublisher )
• SPS: is the subscriber server ( contains HRatSubscriber ) use SQL server Authentication mode for login
on the publisher database i created table: Employees with fields of (ID, Name, Salary) to replicate its data to the subscriber server.
i will use publisher as subscriber also
Note: Check that SQL Server Agent is running on the publisher and the subscriber
Steps:
1. Open SQL Server Management Studio and login with SQL Server Authentication to configure Publishing, Subscribers, and Distribution

a- Configure the appropriate server as publisher or distributor.

b- Enable the appropriate database for merge replication

2- Create new local publication from DB-Server --> Replication --> Local Publications --> Right Click --> New Pub

then choose the database that contains the data or objects you want to replicate

then choose the replication type and then specify the SQL server versions that will be used by subscribers to that publication like SQL Server 2005, SQL mobile Edition, SQL for Win CE ....etc

after that manage the replication articles, data and db objects, by choosing objects to be replicated
Note: you can manage the replication properties for selected objects

Then add filters to published tables to optimize performance and then configure the snapshot agent


and configure the security for snapshot agent

finally rename the publication and click finish

3- create a new subscription for the created "MyPublication01" Publication by right click on MyPublication01 --> New Subscription
that to configure the "Merge Agent" for replication on the subscriber database


then choose one or more subscriber databases. you can add new SQL Server subscribers

then specify the Merge Agent security as mentioned above on "Agent Snapshot"
and so specify the synchronization schedule for each agent.
Schedules:
• Run Continuously: add schedule times to be auto run continuously
• Run on demand only: manually run the synchronization

and then next up to final step, then click finish
you can check the errors from "Replication Monitor" by right click on Local Replication --> Launch Replication Monitor
Advantages in Replication:
Users can avail the following advantages by using replication process:
• Users working in different geographic locations can work with their local copy of data thus allowing greater autonomy.
• Database replication can also supplement your disaster-recovery plans by duplicating the data from a local database server to a remote database server. If the primary server fails, your applications can switch to the replicated copy of the data and continue operations.
• You can automatically back up a database by keeping a replica on a different computer. Unlike traditional backup methods that prevent users from getting access to a database during backup, replication allows you to continue making changes online.
• You can replicate a database on additional network servers and reassign users to balance the loads across those servers. You can also give users who need constant access to a database their own replica, thereby reducing the total network traffic.
• Database-replication logs the selected database transactions to a set of internal replication-management tables, which can then be synchronized to the source database. Database replication is different from file replication, which essentially copies files.
Replication Performance Tuning Tips:
• By distributing partitions of data to different Subscribers.
• When running SQL Server replication on a dedicated server, consider setting the minimum memory amount for SQL Server to use from the default value of 0 to a value closer to what SQL Server normally uses.
• Don’t publish more data than you need. Try to use Row filter and Column filter options wherever possible as explained above.
• Avoid creating triggers on tables that contain subscribed data.
• Applications that are updated frequently are not good candidates for database replication.
• For best performance, avoid replicating columns in your publications that include TEXT, NTEXT or IMAGE data types.
Thanks to "D J Nagendra", i used his article about sql 2000 replication from codeproject.com view to build the sql 2005 replication version.

Setting Up Master-Master Replication With MySQL 5 On Debian Etch

Since version 5, MySQL comes with built-in support for master-master replication, solving the problem that can happen with self-generated keys. In former MySQL versions, the problem with master-master replication was that conflicts arose immediately if node A and node B both inserted an auto-incrementing key on the same table. The advantages of master-master replication over the traditional master-slave replication are that you don't have to modify your applications to make write accesses only to the master, and that it is easier to provide high-availability because if the master fails, you still have the other master.
I do not issue any guarantee that this will work for you!

1 Preliminary Note
In this tutorial I will show how to replicate the database exampledb from the server server1.example.com with the IP address 192.168.0.100 to the server server2.example.com with the IP address 192.168.0.101 and vice versa. Each system is the slave of the other master and the master of the other slave at the same time. Both systems are running Debian Etch; however, the configuration should apply to almost all distributions with little or no modifications.

2 Installing MySQL 5.0
If MySQL 5.0 isn't already installed on server1 and server2, install it now:
server1/server2:
apt-get install mysql-server-5.0 mysql-client-5.0
To make sure that the replication can work, we must make MySQL listen on all interfaces, therefore we comment out the line bind-address = 127.0.0.1 in /etc/mysql/my.cnf:
server1/server2:
vi /etc/mysql/my.cnf
[...]
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address = 127.0.0.1
[...]
Restart MySQL afterwards:
server1/server2:
/etc/init.d/mysql restart
Then check with
server1/server2:
netstat -tap | grep mysql
that MySQL is really listening on all interfaces:
server1:~# netstat -tap | grep mysql
tcp 0 0 *:mysql *:* LISTEN 2671/mysqld
server1:~#
Afterwards, set a MySQL password for the user root@localhost:
server1/server2:
mysqladmin -u root password yourrootsqlpassword
Next we create a MySQL password for root@server1.example.com:
server1:
mysqladmin -h server1.example.com -u root password yourrootsqlpassword
Now we set up a replication user slave2_user that can be used by server2 to access the MySQL database on server1:
server1:
mysql -u root -p
On the MySQL shell, run the following commands:
server1:
GRANT REPLICATION SLAVE ON *.* TO 'slave2_user'@'%' IDENTIFIED BY 'slave2_password';
FLUSH PRIVILEGES;
quit;
Now we do the last two steps again on server2:
server2:
mysqladmin -h server2.example.com -u root password yourrootsqlpassword
mysql -u root -p
GRANT REPLICATION SLAVE ON *.* TO 'slave1_user'@'%' IDENTIFIED BY 'slave1_password';
FLUSH PRIVILEGES;
quit;

3 Some Notes
In the following I will assume that the database exampledb is already existing on server1, and that there are tables with records in it. We will set up replication of exampledb to server2, and afterwards we set up replication of exampledb from server2 to server1.
Before we start setting up the replication, we create an empty database exampledb on server2:
server2:
mysql -u root -p
CREATE DATABASE exampledb;
quit;

4 Setting Up Replication
Now we set up master-master replication in /etc/mysql/my.cnf. The crucial configuration options for master-master replication are auto_increment_increment and auto_increment_offset:
• auto_increment_increment controls the increment between successive AUTO_INCREMENT values.
• auto_increment_offset determines the starting point for AUTO_INCREMENT column values.
Let's assume we have N MySQL nodes (N=2 in this example), then auto_increment_increment has the value N on all nodes, and each node must have a different value for auto_increment_offset (1, 2, ..., N).
Now let's configure our two MySQL nodes:
server1:
vi /etc/mysql/my.cnf
Search for the section that starts with [mysqld], and put the following options into it (commenting out all existing conflicting options):
[...]
[mysqld]
server-id = 1
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 1

master-host = 192.168.0.101
master-user = slave1_user
master-password = slave1_password
master-connect-retry = 60
replicate-do-db = exampledb

log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db = exampledb

relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index

expire_logs_days = 10
max_binlog_size = 500M
[...]
Then restart MySQL:
server1:
/etc/init.d/mysql restart
Now do the same on server2:
server2:
vi /etc/mysql/my.cnf
[...]
server-id = 2
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 2

master-host = 192.168.0.100
master-user = slave2_user
master-password = slave2_password
master-connect-retry = 60
replicate-do-db = exampledb

log-bin= /var/log/mysql/mysql-bin.log
binlog-do-db = exampledb

relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index

expire_logs_days = 10
max_binlog_size = 500M
[...]
server2:
/etc/init.d/mysql restart
Next we lock the exampledb database on server1, find out about the master status of server1, create an SQL dump of exampledb (that we will import into exampledb on server2 so that both databases contain the same data), and unlock the database so that it can be used again:
server1:
mysql -u root -p
On the MySQL shell, run the following commands:
server1:
USE exampledb;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
The last command should show something like this (please write it down, we'll need it later on):
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000009 | 98 | exampledb | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql>
Now don't leave the MySQL shell, because if you leave it, the database lock will be removed, and this is not what we want right now because we must create a database dump now. While the MySQL shell is still open, we open a second command line window where we create the SQL dump snapshot.sql and transfer it to server2 (using scp):
server1:
cd /tmp
mysqldump -u root -pyourrootsqlpassword --opt exampledb > snapshot.sql
scp snapshot.sql root@192.168.0.101:/tmp
Afterwards, you can close the second command line window. On the first command line window, we can now unlock the database and leave the MySQL shell:
server1:
UNLOCK TABLES;
quit;
On server2, we can now import the SQL dump snapshot.sql like this:
server2:
/usr/bin/mysqladmin --user=root --password=yourrootsqlpassword stop-slave
cd /tmp
mysql -u root -pyourrootsqlpassword exampledb < snapshot.sql
Afterwards, we must find out about the master status of server2 as well and write it down:
server2:
mysql -u root -p
USE exampledb;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000009 | 783 | exampledb | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql>
Then unlock the tables:
server2:
UNLOCK TABLES;
and run the following command to make server2 a slave of server1 (it is important that you replace the values in the following command with the values you got from the SHOW MASTER STATUS; command that we ran on server1!):
CHANGE MASTER TO MASTER_HOST='192.168.0.100', MASTER_USER='slave2_user', MASTER_PASSWORD='slave2_password', MASTER_LOG_FILE='mysql-bin.000009', MASTER_LOG_POS=98;
Finally start the slave:
server2:
START SLAVE;
Then check the slave status:
server2:
SHOW SLAVE STATUS;
It is important that both Slave_IO_Running and Slave_SQL_Running have the value Yes in the output (otherwise something went wrong, and you should check your setup again and take a look at /var/log/syslog to find out about any errors):
mysql> SHOW SLAVE STATUS;
+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master |
+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Waiting for master to send event | 192.168.0.100 | slave2_user | 3306 | 60 | mysql-bin.000009 | 98 | slave-relay.000002 | 235 | mysql-bin.000009 | Yes | Yes | exampledb | | | | | | 0 | | 0 | 98 | 235 | None | | 0 | No | | | | | | 0 |
+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
1 row in set (0.00 sec)

mysql>
Afterwards, you can leave the MySQL shell on server2:
server2:
quit
Now the replication from server1 to server2 is set up. Next we must configure replication from server2 to server1.
To do this, we stop the slave on server1 and make it a slave of server2:
server1:
mysql -u root -p
STOP SLAVE;
Make sure that you use the values of the SHOW MASTER STATUS; command that you ran on server2 in the following command:
server1:
CHANGE MASTER TO MASTER_HOST='192.168.0.101', MASTER_USER='slave1_user', MASTER_PASSWORD='slave1_password', MASTER_LOG_FILE='mysql-bin.000009', MASTER_LOG_POS=783;
Then start the slave on server1:
server1:
START SLAVE;
Then check the slave status:
server1:
SHOW SLAVE STATUS;
It is important that both Slave_IO_Running and Slave_SQL_Running have the value Yes in the output (otherwise something went wrong, and you should check your setup again and take a look at /var/log/syslog to find out about any errors):
mysql> SHOW SLAVE STATUS;
+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master |
+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Waiting for master to send event | 192.168.0.101 | slave1_user | 3306 | 60 | mysql-bin.000009 | 783 | slave-relay.000002 | 235 | mysql-bin.000009 | Yes | Yes | exampledb | | | | | | 0 | | 0 | 783 | 235 | None | | 0 | No | | | | | | 0 |
+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
1 row in set (0.00 sec)

mysql>
Afterwards you can leave the MySQL shell:
quit
If nothing went wrong, MySQL master-master replication should now be working. If it isn't, please check /var/log/syslog for MySQL errors on server1 and server2.

How do you set up master-slave replication in MySQL?

Before we go into how to set up master-slave replication in MySQL, let us talk about some of the reasons I have set up master-slave replication using MySQL.
1) Offload some of the queries from one server to another and spread the load: One of the biggest advantages to have master-slave set up in MySQL is to be able to use master for all of the inserts and send some, if not all, select queries to slave. This will most probably speed up your application without having to diving into optimizing all the queries or buying more hardware.
2) Do backups from slave: One of the advantages people overlook is that you can use MySQL slave to do backups from. That way site is not affected at all when doing backups. This becomes a big deal when your database has grown to multiple gigs and every time you do backups using mysqldump, site lags when table locks happen. For some sites, this could mean that site goes down for few secs to minutes. If you have slave, you just take slave out of rotation (should be built into code) and run backups off the slave. You can even stop slave MySQL instance and copy the var folder instead of doing mysqldump.
Ok let us dive into how to setup master-slave replication under MySQL. There are many configuration changes you can do to optimize your MySQL set up. I will just touch on very basic ones to get the replication to work. Here are some assumptions:
Master server ip: 10.0.0.1
Slave server ip: 10.0.0.2
Slave username: slaveuser
Slave pw: slavepw
Your data directory is: /usr/local/mysql/var/
Put the following in your master my.cnf file under [mysqld] section:
# changes made to do master
server-id = 1
relay-log = /usr/local/mysql/var/mysql-relay-bin
relay-log-index = /usr/local/mysql/var/mysql-relay-bin.index
log-error = /usr/local/mysql/var/mysql.err
master-info-file = /usr/local/mysql/var/mysql-master.info
relay-log-info-file = /usr/local/mysql/var/mysql-relay-log.info
datadir = /usr/local/mysql/var
log-bin = /usr/local/mysql/var/mysql-bin
# end master
Copy the following to slave’s my.cnf under [mysqld] section:
# changes made to do slave
server-id = 2
relay-log = /usr/local/mysql/var/mysql-relay-bin
relay-log-index = /usr/local/mysql/var/mysql-relay-bin.index
log-error = /usr/local/mysql/var/mysql.err
master-info-file = /usr/local/mysql/var/mysql-master.info
relay-log-info-file = /usr/local/mysql/var/mysql-relay-log.info
datadir = /usr/local/mysql/var
# end slave setup
Create user on master:
mysql> grant replication slave on *.* to slaveuser@'10.0.0.2' identified by 'slavepw';
Do a dump of data to move to slave
mysqldump -u root --all-databases --single-transaction --master-data=1 > masterdump.sql
import dump on slave
mysql < masterdump.sql
After dump is imported go in to mysql client by typing mysql. Let us tell the slave which master to connect to and what login/password to use:
mysql> CHANGE MASTER TO MASTER_HOST='10.0.0.1', MASTER_USER='slaveuser', MASTER_PASSWORD='slavepw';
Let us start the slave:
mysql> start slave;
You can check the status of the slave by typing
mysql> show slave status\G
The last row tells you how many seconds its behind the master. Don’t worry if it doesn’t say 0, the number should be going down over time until it catches up with master (at that time it will show Seconds_Behind_Master: 0) If it shows NULL, it could be that slave is not started (you can start by typing: start slave) or it could be that it ran into an error (shows up in Last_errno: and Last_error under show slave status\G).