SQL Server R2 Master-Slave database synchronization

First, the preparatory work:

Primary database server:

os:windows Server R2 db:sql server R2

Hostname:cxmasterdb

ip:192.168.1.224/24 dg:192.168.1.1

dns:192.168.1.19 dns:202.96.209.133

from the database server:

os:windows Server R2 db:sql server R2

Hostname:cxslavedb

ip:192.168.1.225/24 dg:192.168.1.1

dns:192.168.1.19 dns:202.96.209.133

The main database is Northwind, as shown in:

from Cxslavedb, there is no database Northwind, as shown in:

Second, create the administrator user and shared folder permissions for the SQL Server database, set up the SQL Server service and sql Server Agent login for sqladmin

1. Create the user sqladmin on the primary database Cxmasterdb server, as shown in:

win + R---> lusrmgr.msc enter as shown in:

set permissions for Sqladmin

2. In the same vein, create a user sqladmin from the database Cxslavedb server and join the Administrators group, and the others are deleted!

3. Create a shared folder Db_backpup and share permissions and NTFS permissions on the master-slave database server to hold the master and slave backup log files, as shown in:

4. Open SQL Server Configuration Manager from the primary database server and from the database server respectively, and the SQL Server service and SQL Server Agent service "Log on as" Sqladmin user with the startup mode: Automatic, as shown in:

iii. Configuring SQL Server log Shipping

1. Configure on the primary database server Cxmasterdb

connecting to a local SQL Server database server with Sqladmin

in the DB instance, configure the server authentication mode and the server proxy account

2. Configuration of the properties of the Northwind database in the primary database server Cxmasterdb

configuration of properties--options in the Northwind database

The properties of the Northwind database--The configuration of transaction log shipping

transaction log backup settings, as shown in:

in the, click Plan ... After that, the popup looks like this:

OK all the way! See so far!

to add a secondary DB instance and database

connecting to a secondary server instance and a secondary database

Secondary Database Settings---> Initialize secondary database

Note: The path to the folder for the data files and log files of the database in the secondary server cxslavedb is viewed below

Secondary Database Settings---> Copy files

in the hit Point plan (E) ... Then, as shown, set up a scheduled task to restore the log from the database:

Secondary Database Settings---> Restore transaction logs

in the hit Point plan (E) ... Then, as shown, set up a scheduled task to restore the log from the database:

Export configuration information to a file

after clicking OK in the, at a glance!

Four , verify that SQL Server R2 master-slave database is synchronized

1. We go from the server cxslavedb to see if there is a Northwind database, as shown, visible success!

2. Now we go to the main database server Cxmasterdb in the Northwind database to add the table as shown:

CREATE table name as employee Address Book

under Refresh, you will see the dbo. Employee Address Book, as shown in:

3. Now we go to the Northwind database Cxslavedb from the database server if there is a table dbo. Employee Address Book, as shown in:

thinking:

below we go to the primary database server Cxmasterdb in the Northwind database, delete the table dbo. Employee Address Book, look at the table dbo from the database cxslavedb the Northwind database. Is the employee Address book also deleted?

attached:

1. How to delete the Northwind (alternate/read-only) database

in the Database properties –-> options---> Status---> The database is read-only modified to FALSE! Can I delete it?

SQL ServerDatabaseSync http://www.atcto.net/Tech/SQL/2013-09-02/SQL,13090215154900000001.html