SQL Server 2008R2 master-Slave deployment combat

Description

Due to the temporary to do OA of the master and slave environment, based on Windows 200R2 SQLServer2008 R2, because the search data are incomplete, a lot of important omissions, hands-on have done several times, filled the pit, finishing for forgetting and sharing, if there is mistake missing place welcome to communicate ~

First, configure the environment

Main Library: 10.8.11.214 database version: SQL Server2008 R2 server name: win-d4grpqked93
From library: 10.8.11.79 database version: QL Server2008 R2 server name: win-3me2dj8l9kt

Note the point:
Server name (hostname) It's a good idea to install the SQL Server 2008R2 database after you determine it so that you can ensure that the hostname, the instance name, remains the same
If the host name is modified after installing the sqlserver2008, the host name and the instance name will be inconsistent.
The following commands can be used to detect and repair

查看服务名称（默认安装时的实例名也是和主机名一致）和主机名是否一致    select @@servername    select serverproperty(‘servername‘)如果不一致请进行修改:    if serverproperty(‘servername‘) <> @@servername　　　　begin　　　　declare @server sysname　　　　set @server = @@servername　　　　exec sp_dropserver @server = @server　　　　set @server = cast(serverproperty(‘servername‘) as sysname)　　　　exec sp_addserver @server = @server , @local = ‘LOCAL‘　　　　End

Here are the results of my run:

Ii. Building master/Slave Preparation

1, master and slave two separate create sqladmin user join Administrators group delete the default Users group, and set "Password never Expires" and "User cannot change password" to create a password for sqladmin
2, the master-slave two sets of network sharing
Open Control Panel--Network and internet--> network and Sharing center--Advanced sharing settings:

3. Create a shared folder Db_backpup (sqladmin user can read and write the administrators owner) on the master/slave library for storing the master and slave backup log files separately.
Step together as follows:

Right-click "Db_bakcup"--shared--specific user--add sqladmin user Read and Write permissions
:

In the Advanced Sharing window, tick the "Share this folder" option, and then the stand-alone Permissions button to set permissions on the shared folder. You need to have the Sqladmin user have full control of the folder permissions, first remove the default "Erverone" user, and then add Sqladmin users, and in the "Sqladmin" permission to check the "Full Control", "Change" and "read" items, and then click two times "OK" button to save the sharing settings. Such as:

In the NTFS file system, you also need to set the user security permissions to the directory, if the security permissions are insufficient, the system will write to the backup file when the error is reported without permission.
On the Security tab, the single-machine edit button, in the Db_backup permissions interface, click Buttons, add Sqladmin users, and then select Full Control in Sqladmin permissions, and the stand-alone OK button to save permission information.
Such as:

4. Open SQL Server Configuration Manager from the primary database server and from the database server, respectively, to "Log on as" Sqladmin User for SQL Server service and SQL Server Agent service and start mode: Automatic
:

Restart the main library server!

III. Configuring SQL Server log Shipping

After the main library restarts, the Sqladmin user logs on to the system and can sqladmin the login database.
Configure on the primary database
On the primary database server, open the SQL Server Management Studio Management Console, connect to the local primary database server, and make sure that the user in the following user name has permissions to control the SQL Server server. Click the Connect button to connect to the local SQL Server database sample.
:

Right-click the database server instance, select the Properties option, in the popup server properties-win-d4grpqked93 interface, click Security on the left side of the machine, and then in the right window, select SQL Server and Windows Authentication mode and tick the "Enable Server Proxy account" option in the "Server proxy account". Enter the correct "proxy account" and "Password" and click the "OK" button to save the configuration information.
:

To configure the database Ufsystem properties to synchronize in the primary database server win-d4grpqked93
:

After setting up select transaction log shipping, tick the "Enable this database for the primary database in the non-log shipping Configuration" option, click "Backup Settings Button" in "Transaction log backup" to open the "Transaction log backup Settings" interface.

:

The main library configuration is paused to test from the library for access to the main library's network shared folder Db_backup folder
Operation on the Standby library:
Win+r, enter the network path to see if you can access \10.8.11.214\db_backup
:

Right-click the-ufsystem--Property--transaction log shipping--Backup settings--in the Job Schedule properties interface, verify that the schedule type is repeated for the test effect, set to 15 seconds to execute the job schedule. Finally confirm the "duration", according to your own 4 need to set, if you have been backed up, can be set to "no end date"
:


After the setting is complete and the transaction log backup settings interface is opened again after saving, the job name of the backup job becomes the Edit Job button, click in, and modify the owner to Sqladmin.
:

Click the Add button in the secondary database in the database properties interface to open the Secondary Database Settings window.


If you do not browse from the library, you need to start the SQL Server Browser service from the library

You may not be able to connect to the secondary database on the first connection, and you will get an error:
A, user XXX login failed
Workaround:
Using the sa login from the library, create a new query on master, enter
Create LOGIN [win-3me2dj8l9kt\sqladmin] from WINDOWS
b, user xxx is not in the sysadmin fixed server role
In the execution from the library
EXEC sp_addsrvrolemember ' win-3me2dj8l9kt\sqladmin ', ' sysadmin '
Setting up the secondary database after a successful connection
:


To view the data directory for a secondary database
:





After setting up Click OK button, in the database properties configuration interface to save the configured script to local, and finally click OK:


To this database master-slave configuration completed, you can operate in the main library, in the library to see whether the corresponding data synchronization, if there is no successful synchronization, you can view the status of the synchronization task from the library to see the reason for the failure.
The following is a successful synchronization, while the Ufsystem database on the main library has been synchronized from the library
:

Four, verify master-Slave synchronization
Create a table in the main library Ufsystem

Wait 15s later to view from the gallery

At this point you can see that the master-slave synchronization is completed

Reference Documentation:
https://yq.aliyun.com/articles/601067
Https://www.cnblogs.com/tatsuya/p/5025583.html
Https://www.cnblogs.com/brucewang/p/7825128.html

SQL Server 2008R2 master-Slave deployment combat