SQL Server R2 Master-Slave database synchronization (log-shipping method logs Shipping)

Precautions:

1, the main from the server to add new system users and set a good password;

2, the master-slave server is turned on the SQL Server Proxy service, and set to boot automatically

3. In database configuration management, set the login information of SQL Server service and SQL Server Proxy service to the system user added above and set the password. (Remember that the master and slave servers are required to set up, do not forget, I have forgotten, how to do not do)

4, the user shared files directory, share access requires a password, remember to access the share and remember the credentials, otherwise it will fail.

5, SQL Server Backup, is the main library database server automatically back up the database, generate backup files, at this interval, and then generate a backup of the log file (. trn file) from the library and then based on the log files obtained from the main library to restore operations, so that the master-slave database data consistent. Only if there is no data update, a log file (. trn) is generated and data is synchronized, affecting the network bandwidth.

6, from the library as far as possible not to detach, because sometimes in the process of being restored detached, after the separation is attached again, "cannot attach the database being restored." (Microsoft SQL Server, Error: 1824) "error.

First, the preparatory work:

Primary database server:

Operating system: Windows Server R2

Database: SQL Server R2

IP Address: 192.168.1.61

From the database server:

Operating system: Windows Server R2

Database: SQL Server R2

IP Address: 192.168.1.62

The main database server has WEBDB,WEBDB is the database we want to synchronize, there is only one Test table WEBDB, as shown in:

650) this.width=650; "Src=" https://s2.51cto.com/wyfs02/M00/8D/E3/wKioL1iuZini1LQyAABknghryQs592.png-wh_500x0-wm_ 3-wmp_4-s_437660573.png "title=" Clipboard.png "alt=" Wkiol1iuzini1lqyaabknghryqs592.png-wh_50 "/>

There is no database from the server database WEBDB,WEBDB database is the database we want to synchronize, as shown in:

650) this.width=650; "Src=" https://s2.51cto.com/wyfs02/M01/8D/E5/wKiom1iuZlLTW5cJAABkno3QR0M731.png-wh_500x0-wm_ 3-wmp_4-s_4125626165.png "title=" Clipboard.png "alt=" Wkiom1iuzlltw5cjaabkno3qr0m731.png-wh_50 "/>

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 same server as the primary database WEBDB, click Start-Run (or press the shortcut key Win+r), enter the command lurmgr.msc, hit enter, in the pop-up Local Users and Groups window, add the User:

650) this.width=650; "Src=" https://s5.51cto.com/wyfs02/M01/8D/E3/wKioL1iuZnDgkuacAAA-KqP7lcQ147.png-wh_500x0-wm_ 3-wmp_4-s_3546024336.png "title=" Clipboard.png "alt=" Wkiol1iuzndgkuacaaa-kqp7lcq147.png-wh_50 "/>

New user interface such as:

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M02/8D/E6/wKiom1iuZpLw8IsCAAAd2KAErjY808.png-wh_500x0-wm_ 3-wmp_4-s_3468612073.png "title=" Clipboard.png "alt=" Wkiom1iuzplw8iscaaad2kaerjy808.png-wh_50 "/>

Permissions to set sqladmin belong to administrators

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M02/8D/E3/wKioL1iuZqyBFOgLAAAkUU80xLg887.png-wh_500x0-wm_ 3-wmp_4-s_3473699005.png "title=" Clipboard.png "alt=" Wkiol1iuzqybfoglaaakuu80xlg887.png-wh_50 "/>

2. Also need to create the user sqladmin from the database and join the Administrators group, the others are deleted!

3. Create the shared folder Sqldataback and share permissions and NTFS permissions on the master-slave database server for storing the master and slave backup log files, as shown in:

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M02/8D/E3/wKioL1iuZtiAhRGzAABak-JHsvQ577.png-wh_500x0-wm_ 3-wmp_4-s_1109652288.png "title=" Clipboard.png "alt=" Wkiol1iuztiahrgzaabak-jhsvq577.png-wh_50 "/>

650) this.width=650; "Src=" https://s3.51cto.com/wyfs02/M00/8D/E6/wKiom1iuZvLyRmy0AAA21ENbvl4184.png-wh_500x0-wm_ 3-wmp_4-s_1034519000.png "title=" Clipboard.png "alt=" Wkiom1iuzvlyrmy0aaa21enbvl4184.png-wh_50 "/>

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

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M00/8D/E6/wKiom1iuZxWT50tNAAAilS_WSC4787.png-wh_500x0-wm_ 3-wmp_4-s_3793389218.png "title=" Clipboard.png "alt=" Wkiom1iuzxwt50tnaaails_wsc4787.png-wh_50 "/>

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M01/8D/E6/wKiom1iuZ0Wyx7UvAAAh98hycP0034.png-wh_500x0-wm_ 3-wmp_4-s_1653917993.png "title=" Clipboard.png "alt=" Wkiom1iuz0wyx7uvaaah98hycp0034.png-wh_50 "/>

The startup mode of the general SQL Server server is self-booting, the SQL Server Agent service is started manually and needs to be selected as self-booting in the service.

650) this.width=650; "Src=" https://s3.51cto.com/wyfs02/M00/8D/E3/wKioL1iuZ3XBrpoiAAAgKhlWXZw941.png-wh_500x0-wm_ 3-wmp_4-s_2708710002.png "title=" Clipboard.png "alt=" Wkiol1iuz3xbrpoiaaagkhlwxzw941.png-wh_50 "/>

Third, Configuring SQL Server log Shipping

1. Configure on the primary database server

Connect to the local SQL Server database server with sqladmin (I tried this, I can log in with a administrator user)

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M02/8D/E6/wKiom1iuZ5KQ2NnzAABjshCDsqM818.png-wh_500x0-wm_ 3-wmp_4-s_2130550171.png "title=" Clipboard.png "alt=" Wkiom1iuz5kq2nnzaabjshcdsqm818.png-wh_50 "/>

In the DB instance (where I have a DB instance SQL2008, right-click on the. \sql2008), right-click-Properties-Security, configure server Authentication mode and server proxy account, where the proxy account is the Sqladmin account set up in front of us.

650) this.width=650; "Src=" https://s3.51cto.com/wyfs02/M01/8D/E3/wKioL1iuZ6zgNGFxAABtmf6BOIs414.png-wh_500x0-wm_ 3-wmp_4-s_908975694.png "title=" Clipboard.png "alt=" Wkiol1iuz6zgngfxaabtmf6bois414.png-wh_50 "/>

2. Configure the properties information for the WEBDB database in the primary database server.

Right-click on the WEBDB database-Properties, open the Database Properties window, select the Options tab, select the recovery mode as "full" (the default is also complete), as

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M02/8D/E3/wKioL1iuZ8fAq6GdAABpra_WQug325.png-wh_500x0-wm_ 3-wmp_4-s_4248379442.png "title=" Clipboard.png "alt=" Wkiol1iuz8faq6gdaabpra_wqug325.png-wh_50 "/>

Then select the "Transaction log Shipping" option in the Properties window of the database, tick "Enable this database as the primary database in the log shipping configuration", then click the "Backup Settings" button:

650) this.width=650; "Src=" https://s3.51cto.com/wyfs02/M02/8D/E6/wKiom1iuZ-HjcDoDAAB0gwRiWmg744.png-wh_500x0-wm_ 3-wmp_4-s_1897389672.png "title=" Clipboard.png "alt=" Wkiom1iuz-hjcdodaab0gwriwmg744.png-wh_50 "/>

In the Transaction log Backup Settings window, set such as:

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M02/8D/E6/wKiom1iua_fgWO0MAAI8hoRm_48525.jpg-wh_500x0-wm_ 3-wmp_4-s_1663367056.jpg "title=" clipboard.jpg "alt=" Wkiom1iua_fgwo0maai8horm_48525.jpg-wh_50 "/>

In the, click the "Schedule" button after the popup as shown:

650) this.width=650; "Src=" https://s3.51cto.com/wyfs02/M01/8D/E6/wKiom1iuaB-QNHNqAABtMZvhPjM945.png-wh_500x0-wm_ 3-wmp_4-s_2965075791.png "title=" Clipboard.png "alt=" Wkiom1iuab-qnhnqaabtmzvhpjm945.png-wh_50 "/>

If you need to synchronize all the time, in the duration, select No end date. Always click OK, see so far!

650) this.width=650; "Src=" https://s3.51cto.com/wyfs02/M00/8D/E6/wKiom1iuaDzz8sO9AACaNhhdd24853.png-wh_500x0-wm_ 3-wmp_4-s_665567934.png "title=" Clipboard.png "alt=" Wkiom1iuadzz8so9aacanhhdd24853.png-wh_50 "/>

The previous arrow points to the backup schedule that we just backed up, set up, and click the arrow to point to the "Add" button to connect to the secondary DB instance from the server.

650) this.width=650; "Src=" https://s2.51cto.com/wyfs02/M00/8D/E3/wKioL1iuaJ3zhL-XAABiaw_nIEU102.png-wh_500x0-wm_ 3-wmp_4-s_2470364257.png "title=" Clipboard.png "alt=" Wkiol1iuaj3zhl-xaabiaw_nieu102.png-wh_50 "/>

After you connect, you can see the secondary DB instance and the secondary database name. The secondary database can select an existing database, or you can enter a name, create a new database, default secondary database name, and the name of the primary database that needs to be synchronized.

650) this.width=650; "Src=" https://s2.51cto.com/wyfs02/M02/8D/E6/wKiom1iuaMPQJFKfAABMzegwQgI813.png-wh_500x0-wm_ 3-wmp_4-s_2705712145.png "title=" Clipboard.png "alt=" Wkiom1iuampqjfkfaabmzegwqgi813.png-wh_50 "/>

In the Initialize Secondary Database tab, click on the "Restore Options" button to eject the Restore Options window, such as:

650) this.width=650; "Src=" https://s3.51cto.com/wyfs02/M01/8D/E6/wKiom1iuaNihXqcPAAAZ7syAgRQ761.png-wh_500x0-wm_ 3-wmp_4-s_4111960985.png "title=" Clipboard.png "alt=" Wkiom1iuanihxqcpaaaz7syagrq761.png-wh_50 "/>

Note: This folder is the folder from the server's local folder, which is the secondary database (the synchronization library) where the data files and log files need to be stored. If you do not fill in, the default data storage path from the DB instance on the server is used.

Click on the "Copy Files" tab and enter the destination folder for copying files from the server (note the local folder from the server and the shared folder that we set up from the server earlier). The deletion time, also need to be set with our synchronization frequency, the more frequent the synchronization frequency, the deletion of the copy file interval is also shorter.

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M01/8D/E3/wKioL1iuaPqjVPOyAABJ1EP5ovA361.png-wh_500x0-wm_ 3-wmp_4-s_3779366315.png "title=" Clipboard.png "alt=" Wkiol1iuapqjvpoyaabj1ep5ova361.png-wh_50 "/>

Click on the Schedule button, pop up the "Job Schedule Properties" window, set the synchronization interval from the server and whether there is an end date, click OK.

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M00/8D/E6/wKiom1iuaRfRBtaQAABPoBakc3o092.png-wh_500x0-wm_ 3-wmp_4-s_3739344520.png "title=" Clipboard.png "alt=" Wkiom1iuarfrbtaqaabpobakc3o092.png-wh_50 "/>

Select the "Restore Transaction" Log tab and select "Standby mode", such as:

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M02/8D/E6/wKiom1iuaS3ScIaBAABLH01O9Jk981.png-wh_500x0-wm_ 3-wmp_4-s_1469004777.png "title=" Clipboard.png "alt=" Wkiom1iuas3sciabaablh01o9jk981.png-wh_50 "/>

At the same time, click on the "Schedule" button, pop Up "job Schedule Properties", select the appropriate information as needed, click OK.

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M02/8D/E3/wKioL1iuaUeD_8xZAABOAOqHAq8841.png-wh_500x0-wm_ 3-wmp_4-s_3863646102.png "title=" Clipboard.png "alt=" Wkiol1iuaued_8xzaaboaoqhaq8841.png-wh_50 "/>

Export more of our configuration information to a file, such as:

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M00/8D/E6/wKiom1iuaVqwHYSWAACq91H8QA0612.png-wh_500x0-wm_ 3-wmp_4-s_925236750.png "title=" Clipboard.png "alt=" Wkiom1iuavqwhyswaacq91h8qa0612.png-wh_50 "/>

After you save the configuration script, click the OK button to start saving the log and transfer the configuration, you can see the following interface if you succeed.

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M00/8D/E3/wKioL1iuaW7BmdwPAAA40pUvdt8360.png-wh_500x0-wm_ 3-wmp_4-s_2057395301.png "title=" Clipboard.png "alt=" Wkiol1iuaw7bmdwpaaa40puvdt8360.png-wh_50 "/>

Note: If you fail, you will be prompted with the appropriate error message after you can view the error message. After the error message, click OK, the database properties of the window will be closed, we again open in the properties, "transaction log Shipping" will not see our configuration of the master-slave data configuration information, we can reconfigure. However, we can also execute our saved script in the new query window through our saved script information. After executing the script, you can see the master-slave backup information we set in the transaction log shipping option in the database properties, correct the error, and re-determine the execution.

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

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

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M00/8D/E3/wKioL1iuaYTyVF3-AABC5kAvcCI236.png-wh_500x0-wm_ 3-wmp_4-s_2874428229.png "title=" Clipboard.png "alt=" Wkiol1iuaytyvf3-aabc5kavcci236.png-wh_50 "/>

2. Now let's go to the main database server and add the table as shown in the WEBDB database:

Create Table name is Sys_news

650) this.width=650; "Src=" https://s2.51cto.com/wyfs02/M01/8D/E6/wKiom1iuaaPBS7auAABZTQTieIw866.png-wh_500x0-wm_ 3-wmp_4-s_94947604.png "title=" Clipboard.png "alt=" Wkiom1iuaapbs7auaabztqtieiw866.png-wh_50 "/>

3. Now we go to the WEBDB database from the database server to see if there is a table sys_news, as shown in:

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M01/8D/E6/wKiom1iuacHzqZMpAABSPz-zslQ571.png-wh_500x0-wm_ 3-wmp_4-s_411152620.png "title=" Clipboard.png "alt=" Wkiom1iuachzqzmpaabspz-zslq571.png-wh_50 "/>

Did not see, because we set the scheduled task is 15 minutes, 15 minutes did not arrive, so there is no synchronization up. We'll have it in 15 minutes. So when synchronizing, be sure to plan the synchronization time.

Note: How to delete the WEBDB (Standby/read-only) database

Right---Properties--------------the database is read-only and modified to FALSE!

This article is from the "Why A Smile" blog, please be sure to keep this source http://helicon.blog.51cto.com/3926609/1900517

SQL Server R2 Master-Slave database synchronization (log-shipping method logs Shipping)