SQL Server 2008 Database Replication To Achieve Database Synchronization Backup

SITEMAPPED WEB DIRECTORY » Article Details

SQL Server 2008 Database Replication To Achieve Database Synchronization Backup
Date Added: January 12, 2018 06:37:23 AM

Author: Elliot Gibson

Category: Computers: Software

Enterprise software hotspot article CSS Cascading and Inheritance Usage Manual Oracle table to modify the skills introduced Copy is through the release / subscription mechanism for multiple data between the synchronization, we use it for the database synchronization backup. Synchronous backup here refers to the backup server and the master server for real-time data synchronization, under normal circumstances only use the primary database server, the backup server only in the main server failure to put into use. It is a better backup than the file backup database. When choosing a database synchronization backup solution, we evaluated two ways: SQL Server 2008 database mirroring and SQL Server 2008 database replication. The advantage of database mirroring is that the system can automatically discover the primary server failure and automatically switch to the mirror server. But the disadvantage is the configuration is complex, the mirror data in the database is not visible (in Management Studio, can only see the mirror database is mirrored, can not carry out any database operation, the simplest query is not OK. Would like to see the real time backup solutions, mirror the database Whether the data is correct. Only the mirror database can be switched to the main database can be seen). If you want to use the database image, it is strongly recommended that killkill write SQL Server 2005 mirror build manual, we are in accordance with this article to complete the database image deployment test. Eventually, we chose SQL Server 2008 database replication. Here through an example and we learn how to deploy SQL Server 2008 database replication. Test environment: Windows Server 2008 R2 + SQL Server 2008 R2 (English version), two servers, one main database server CNBlogsDB1, one backup database server CNBlogsDB2. Copying Principle: We are using a transaction-based replication. The primary database server generates a snapshot, the backup library server reads and loads the snapshot, and then keeps the transaction log from the primary database server. See below: Image from SQL Server Books Online Installation and configuration steps: First, in two servers installed on SQL Server 2008 R2, the main installation components: Database Engine (including SQL Server Replication), Management Tools. Second, the main database server (publishing server) configuration: 1. In the main database server CNBlogsDB1 create a new sample database CNBlogsDemo (note Recovery mode to use the default value Full, only this mode can be transaction copy), and then create a test table, such as: CNBlogsTest. 2. Set the folder where the snapshot is stored: Before creating a release, set up the folder where the snapshot is stored, create a snapshot file that will be generated in the folder, and the subscriber needs to load the snapshot file when it is initialized. Select the Replication 'Local Publications' attribute, select Publishers in the window that appears, as shown in the following figure: Click the red box at the button, the Settings window appears: Set the snapshot file storage path in the Default Snapshot Folder. 3. Create a publication in the primary database server: In the Replication 'Local Publications' select New Publication, a wizard appears. First select the database to publish CNBlogsDemo, and then select the publication type Transational publication, as shown below: Click Next to get the error: Originally all the tables to be copied need to have a primary key, just built CNBlogsTest table, there is no built key. Build the primary key, and restart the wizard on it. Then select the object to copy: Click Next, enter the Snapshot Agent window, select Create a snapshot immediately and keep the snapshot available to initialize subscriptions, see below: Next, enter Agent Security: Select the Security Settings, the appropriate account settings: One is to set up a Windows account to run Snapshot Agent, where we select the same account as SQL Server Agent. One is set up to connect the server's SQL account, we are here with the main database server sa account. Continue: OK, Next, Next, for this release from a name: Click Finish, it began to officially create a release, create a success will appear the following window: Then look at the snapshot folder, you will see unc folder, snapshot file in this folder. Here to consider such a question, how to let the subscriber through the network to access the snapshot folder. We have toss some time on this issue, would like to share the way through the folder, but do not want to open anonymous sharing, toss for a long time, did not get the server to access the shared folder user authentication problem.