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 SQL Server database administrator user and shared folder permissions, set the SQL Server service and the SQL Server Agent logon user as 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 log the SQL Server service and SQL Server Agent service as sqladmin users and The startup mode is: 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:
Below is a look at the path to the folder for the data files and log files of the database in the secondary server Cxslavedb
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!
Third, 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 drop the table dbo into the Northwind database of the primary database server Cxmasterdb . Employee Address Book
Look at the table in the Northwind database cxslavedb from the database dbo. is the employee Address book also deleted?
Report:
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 you delete it???
Original source: http://beyondhdf.blog.51cto.com/229452/1228517
SQL Server R2 master-Slave database synchronization