Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you the log transmission of SQL Server on how to achieve Standby. The article is rich in content and analyzes and describes it from a professional point of view. I hope you can get something after reading this article.

How to implement log delivery and synchronous replication in hot standby availability under SQLSERVER:

1. Use the Enterprise version of the Log Shipping Wizard (integrated in the Database maintenance Wizard)

two。 This is achieved by writing a script and scheduling it as a normal SQL Server agent job

3. Use synchronous replication

Clustering is an effective solution for achieving high availability, and sometimes it is counterproductive. Moreover, it is also very expensive. Therefore, database administrators can use log staging instead of clustering to provide high availability.

Log transfer is a process in which transaction log files in a database can be transferred to the backed-up database in turn, thus creating an "almost" hot backup for the database. Log transfer is set up in the database engine of SQL Server 2000 and processed in it. So it automatically completes the process of restoring to the backup server without requiring manual action by the database administrator. Only if your product server fails, you need to manually complete the recovery process to the backup server. (note: although log transfer is available in both SQL Server 7.0 and 2005, this article focuses on SQL Server 2000.)

Why does ◆ use log transfer?

Log transfer is a measure to solve high availability, and it is very effective. Also as a high availability measure, the biggest advantage of log transfer compared to clusters is that it is much cheaper. This is because there are hardware requirements for using clustering capabilities, but log transfers are not required.

Log transfers take place between the database and the database, not between the server and the server; this makes it possible to store the backup database on a server that you have used for other purposes. However, if the transfer fails, there may be a problem, then you can switch to the backup database, this option is available. Log transfer is relatively easy to install. SQL Server provides a very complete wizard to help you install this process. Log transfer allows you to save redundant data distributed in different geographic locations, which is difficult to do with SQL Server's clustering feature. This feature is outstanding because when your data center is hit by a disaster, you can still recover it from the backup server. In the same data center, if you use clustering, you will get into trouble.

Another advantage of log transfer is that you can use the backup database as a reporting database, which is a good choice for many companies. But if you decide to use this backup database for reporting, you must pay attention to its limitations. When using logs from the original database, SQL Server requires that a unique channel be specified, so reporting cannot occur at the same time while the log file is being applied.

Relevant factors to be considered when using log transfer in ◆

When using log transfer as a high-availability scenario, we must consider the following factors. Since there is an incubation period from the original database to the backup database, it is not necessarily a feasible solution for your company to achieve high availability. The incubation period is set by the database administrator, and the time is shortened as needed, but it can never be avoided.

The recovery feature is not set in the log transfer, which means that the logs are temporarily unavailable when they are transferred to the backup server. Therefore, the database administrator must complete a series of operations before putting the backup database online, including storing the backup tags that have been stored in the original database on the backup data server. Once all the tags are stored, the database must be restored and put online.

Once all the databases are online, all applications that need to access the database need to change their links. If you can't point the application to the database you just recovered as soon as possible, your previous efforts will be wasted.

An instance of SQL Server can be used to monitor log transfers. This instance can be in the original database, the backup database, or a separate database. Any version of SQL Server can be used for SQL Server monitoring.

Note: database login must be performed at the same time between the original database and the backup database

Procedure:

Log Shipping Operations Guide

Version: 1.0

Create Log Shipping.. three

Monitor.. nine

Delete Log shipping.. ten

Role Change.. thirteen

Create Log Shipping

1. SQLServer node 1 Tonym and Tonym02 must be in the same domain, and both SQL1 and SQL2 must use the domain account to start the SQLServer service and SQLServerAgent service.

two。 Delete the local connection in the enterprise manager, apply Server Name to register the local server Tonym, and the secondary server Tonym02

3. Create a new shared folder NorthwindBackupShare01 on the SQL1 server and give Full permission to start the SQL Server account. Create a new folder ReceiveSQL2Logs on the SQL1 server to receive logs delivered from the SQL2 during database role conversion.

Create a new shared folder NorthwindBackupShare02 on the SQL2 server and give Full permission to start the SQL Server account. Create a new folder ReceiveSQL1Logs on the SQL2 server to receive logs delivered from the database SQL1.

4. Set the server to which you want to apply Log Shipping to the full recovery model.

5. Right-click New maintenance Plan on Database Maintenance Plans and select the database for LogShipping. Only one database is allowed to be selected at a time.

6. Remove Back up the database as part of the maintenance plan to ensure the uniqueness of the maintenance plan (recommended)

7. Specify the database log backup path.

8. Specify the shared folder where the log files are stored.

9. Add the destination database.

Server Name is the destination name

Transaction Log Destination Directory fills in the receiving path from SQL1 to log files on SQL2.

Destination Database chooses to create a new database (specify the data file and log file storage path) or apply an existing database

Database Load State

No recovery mode: users will not be able to query data, only for backup use.

Standby mode: set to read-only mode, as long as it is not for log save, you can query.

Terminate users in database (Recommended): when saving back a database or transaction log file, the rollback program will be the only user of the database.

Allow database to assume primary role: allows role switching between primary and secondary servers.

Select the shared directory path of the new primary server after the role conversion.

9.Initialize the Destination Database: select the most recent data or create a new backup. For large databases, it is more efficient to use backup. However, make sure that the logs after the slave backup exist in the log shared directory on the master server.

10. Set the frequency of log backups on the primary server.

11. Set how often the secondary server replicates the backup log and loads the backup log, and how long the log files remain on the secondary server.

12. Set a reasonable delay time for log backup and log save work. When the critical time is exceeded, the log shipping monitor dialog box will give you a warning message.

13. To specify the monitoring server, you should specify a third server independent of the primary server and the secondary server as the monitoring server, or specify the secondary server as the monitoring server.

Click Next to specify the name of the maintenance plan. Finish, start the creation of Log shipping.

After Log Shipping is created, the information related to Log Shipping is stored in seven tables of msdb:

Log_shipping_plans

Log_shipping_plan_databases

Log_shipping_databases

Log_shipping_plan_history

Log_shipping_monitor

Log_shipping_primaries

Log_shipping_secondaries

two。 You can see the status information of Log shipping backup, copy, load and other actions under the management of the monitoring server.

Delete Log shipping

1. Select the log shipping maintenance plan on the primary server, open the properties, select the [Log shipping] settings page, and click [Remove Log Shipping]. This action removes SQL Server Agent backup and rollback work from the secondary server and clears all related data from the log transfer table. In addition, the relevant information of the log shipping monitoring program will also be cleared. However, this action will properly retain the backup of the transaction log of SQL Server Agent on the primary server. This work is removed only when the database maintenance plan is deleted. If you want to remove the log shipping monitor from the monitoring server, delete the data in the log_shipping_primaries and log_shipping_secondarie tables (located in the msdb database of the monitoring server) manually.

If you set log shipping in the database maintenance plan, you have allowed the destination database to be the new log shipping source database. When you delete the maintenance plan for the primary server, its database maintenance plan and backup of transaction log files are still retained on the secondary server. These items are deleted by directly deleting the database maintenance plan related to log shipping on the secondary server.

Role Alter

1. Create a login synchronization DTS package on the master server.

two。 Open Enterprise Manager and connect to the primary server. Expand the Enterprise Manager tree to the Data Transformation Services group and select Local Packages. Right-click "Local Packages" and select "New Package". Choose 16 Transfer Logins Task from the Task menu. Select the primary server at the source, and select the secondary server on the destination tab. On the Logins tab, select to transfer the logins associated with a specific database, or to transfer all logins to the server. (it is recommended for our environment to use all logins that transport this server)

3. Save the DTS package on the primary server.

4. Specify the DTS synchronization time (at least once a week).

5. Synchronous login account SID

1. Bcp master..syslogins out localpath\ syslogins.dat / N / S current_primary_server / U sa / P sa_password.

The exported syslogins information will be used later.

two。 Downgrade the main server. Run the following stored procedure on the primary server.

Use master

Exec msdb..sp_change_primary_role

@ db_name = 'current_primary_dbname'

@ backup_log = 1

@ terminate = 1

@ final_state = 3

@ access_level = 1

3. Upgrade the secondary server. Run the following stored procedure on the secondary server.

Use master

Exec msdb..sp_change_secondary_role

@ db_name = 'current_secondary_dbname'

@ do_load = 1

@ force_load = 1

@ final_state = 1

@ access_level = 1

@ terminame = 1

@ keep_replication = 0

@ stopat = null

The stored procedure qualifies the database as single-user mode. Obviously no user is accessing the database, but it tells me that the database is currently in use and the solution is to re-execute the stored procedure.

4. Notify the monitoring server that the role has changed and run the following stored procedure on the monitoring server.

Use master

Exec msdb..sp_change_monitor_role

@ primary_server = 'current_primary_server_name'

@ secondary_server = 'current_secondary_server_name'

@ database = 'current_secondary_dbname'

@ new_source = 'new_source_directory'

5. Resolve login account on secondary server

Use master

Exec sp_resolve_logins

@ dest_db = 'dbname'

@ dest_path = 'destination_path'

@ filename = 'filename' (from step 1 export)

6. Connect database access and permissions. Link the parsed login account after transfer to the corresponding database user and its permissions. (SQL BOOK Online is missing this step)

Use sourcename

Exec sp_change_users_login 'update_one',' username', 'LoginName'

1. Remove the log shipping feature from the database maintenance plan of the new primary server.

two。 Delete the database maintenance plan on the primary server.

3. Delete the database maintenance plan on the secondary server.

4. Maintain all transaction logs.

5. Establish a new database maintenance plan on the new primary server, specifying the location of the new secondary server, the destination database location, and the appropriate location for the transaction log.

6. Restart all activities on the new primary server.

After you have successfully set up a role swap and built a new log shipping pair server, Enterprise Manager's log shipping monitor may tell you that the new secondary server database is not synchronized with the new primary server database (out of sync). You will receive this report if the time difference between the most recently loaded transaction Log and the most recently backed up transaction Log exceeds the out-of-sync setting. You need to copy the backup log of the new primary server to the synchronous backup path of the new secondary server. By the time the last backup data is loaded, the log shipping monitor will return to its normal error-free state.

The above is the editor for you to share how SQL Server to achieve Standby log delivery, if you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.