Real-time backup of SQL Server databases

Synchronously update existing resources using the Data Processing Techniques
Concept of zookeeper
Copying a group of resources from one source to multiple sources, it is an effective way to send a copy of data to multiple storage sites. Using NLP technology, you can send a copy of the data to multiple servers, this information can be shared across a range of authorizable regions for different servers. The technical license can ensure that the information at different locations is updated synchronously, and the consistency of the information is guaranteed.
Basic elements of SQL statement execution include
Publishing Server, Indexing Server, distribution server, publications, articles
How SQL statement execution works
SQL Server uses publications and marketing methods to handle marketing activities. The server where the source data is located is the Publishing Server, which sends the table data. The Publishing Server copies all the changes in the information to be posted to the distribution server. The distribution server contains a distribution data volume, you can receive all changes to the resource, save these changes, and then send these changes to the disaster recovery server.
Type
SQL Server provides three types of technical skills:
1. snapshot used (we will use it later)
2. event handling
3. Combined zookeeper
As long as you have figured out the above concepts, you have a certain understanding of the concept. Next, let's take a look at our next step.
First, configure the Publishing Server
(1) specify a [server] Access Point in the Selection
(2) From the [tools] drop-down list, select the [producer, producer, server, and Distribution] commands
(3) The system generates a caller's comment [next] And then looks at the prompt until the operation is completed.
(4) When the publishing server is configured, the system will add a render connector to the structure of the server. At the same time, a distribution is also generated)
2. Create a publication
(1) Select the specified server
(2) Select the [build and manage development] command from the [development Scripts] sub-function table in the [tools] menu. At this time, the system will generate a caller
(3) Select the materials to be created for the publication, and then click [production and development].
(4) The [next] system will generate a speaker in the prompt of the [Creation and release phases. The content on the client side is the three types of linear regression. We are now the first to select the default snapshot producer (the other two can be used for help)
(5) A single [next] system requires that you specify the types of data servers that can be accessed, sqlserver allows you to perform data transfer between different data sources, such as oracle or access. However, here we choose to run the "SQL Server 2000" Database Server
(6) simply [next] The system will compile the target speaker of a fixed article, that is, the table to be published.
(7) then [next] until the operation is completed. After the creation of the publications, the creation of the publications becomes a shared resource.
Third design engineer
(1) selected zookeeper Server
(2) From the [tools] drop-down list, select the [partition tables] sub-function table [request partition tables].
(3) perform the [next] operation in accordance with the ticket until the system prompts "checking the operation route of the SQL Server proxy service, the precondition for the begin operation is that the SQL Server proxy service must have been moved.
(4) single commit [complete]. Complete the zookeeper operation.
After completing the above steps, it is actually successful. But how can we know if zookeeper is successful? This method can be used to quickly check whether the operation is successful. Publish content under the publication server-publish content on the right-click Publish content-publish activity-then run the agent immediately and then click the agent. program-based failover sets the Failover level to be generated every day, every minute, at 0:00:00 and 23:59:59. Next we will determine whether zookeeper has successfully opened C: under/program files/Microsoft SQL Server/MSSQL/repldata/UNC/xiaowangzi_database_database, check whether there is a shard with the same time as the case name. If you still don't believe it, open your information. Check the table on the designated website of your server.

GT:
/* -- Material resources
Route the information in one item to another.
If a column is marked in the object resource, it will not be blocked.

Using metadata: the structure of the database has changed, and you want to upgrade the database.
In this way, you can create an empty folder based on the new data structure, and then
Route all the resources of the website to the new website.
/* -- Usage example

Exec p_copydb 'source data', 'object data'
Exec p_copydb 'Acc _ wubei', 'Acc _ demonstration item 8'
--*/

If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [p_copydb] ') and objectproperty (ID, n' isprocedure') = 1)
Drop procedure [DBO]. [p_copydb]
Go

Create proc p_copydb
@ O_dbname sysname, -- Information about the resource -- source information
@ N_dbname sysname, -- the information of the received item -- the object Resource
@ Cleardb bit = 0 -- clear object Resource
As
Declare @ SQL nvarchar (4000)

-- Disable the appointment bundle to prevent unexpected data bursts.
Set @ SQL = 'Clare # TBC cursor for select name, tbname = object_name (parent_obj)
From '+ @ n_dbname +' .. sysobjects where xtype in ('C', ''f '')'
Exec (@ SQL)
Declare @ name sysname, @ tbname sysname
Open # TBC
Fetch next from # TBC into @ name, @ tbname
While @ fetch_status = 0
Begin
Set @ SQL = 'alter table' + @ n_dbname + '... [' + @ tbname + '] nocheck constraint [' + @ name + ']'
Exec (@ SQL)
Fetch next from # TBC into @ name, @ tbname
End
Close # TBC

-- Collect metadata
Declare @ sql1 varchar (8000)
Set @ SQL = 'Clare # TB cursor for select a. Name from'
+ @ O_dbname + '.. sysobjects a inner join'
+ @ N_dbname + '.. sysobjects B on A. Name = B. Name
Where a. xtype = ''u'' and B. xtype = ''u '''
Exec (@ SQL)
Open # TB
Fetch next from # TB into @ tbname
While @ fetch_status = 0
Begin
Select @ sql1 =''
, @ SQL = 'select @ sql1 = @ sql1 + '', ['' + A. Name + '']'' from (
Select name from '+ @ o_dbname +' .. syscolumns where ID in
(Select ID from '+ @ o_dbname +' .. sysobjects where name = ''' + @ tbname + ''')
) A inner join (
Select name from '+ @ n_dbname +' .. syscolumns where status <> 0x80 and ID in
(Select ID from '+ @ n_dbname +' .. sysobjects where name = ''' + @ tbname + ''')
) B on A. Name = B. name'
Exec sp_executesql @ SQL, n' @ sql1 nvarchar (4000) out', @ sql1 out

Select @ sql1 = substring (@ sql1, 2,8000)
Exec ('insert into '+ @ n_dbname +' .. ['+ @ tbname +'] ('+ @ sql1
+ ') Select' + @ sql1 + 'from' + @ o_dbname + '... [' + @ tbname + ']')
If @ error <> 0
Print ('insert into '+ @ n_dbname +' .. ['+ @ tbname +'] ('+ @ sql1
+ ') Select' + @ sql1 + 'from' + @ o_dbname + '... [' + @ tbname + ']')
Fetch next from # TB into @ tbname
End
Close # TB
Deallocate # TB

-- Submit an appointment with the user after the information is completed
Open # TBC
Fetch next from # TBC into @ name, @ tbname
While @ fetch_status = 0
Begin
Set @ SQL = 'alter table' + @ n_dbname + '... [' + @ tbname + '] Check constraint [' + @ name + ']'
Exec (@ SQL)
Fetch next from # TBC into @ name, @ tbname
End
Close # TBC
Deallocate # TBC
Go

Backup Database SYS to disk = '// ip/shared object/a. Bak' with init
Probably permission issues !!!
For example:
Slave to fileserver
Then, your manual must have sufficient permissions on fileserver!
-------------------- ^ ---------------------------------
1. using SQL Server to create a new SQL user may be a little larger.
2. The same user on the fileserver has the right to access a certain region.
3. The sqluser password of the two machines is the same (convenient)
4. Change sqlserver to sqluser Registration
(Management tools --> services --> MSSQL scripts --> adequacy --> specify secret and secret)
5. // 192. 168. *. */file metadata/javasbak. Bak.
-----------------------------------------------------------
The ticket is as follows:
If both of you are windows 2ks
Your machine uses the same administrator password, and the SQL server uses the Administrator Password
When you configure the hosts file, you can configure the following parameters: // ip address /.......

If the peer is 98, you need to share it all!
When you configure the hosts file, you can upload: // ip /.......

 

1. Configure the Publishing Server

On the Publishing Server, perform the following steps:

(1) select [configuration Publishing, subscription server and Distribution] from the [copy] sub-menu in the [tools] drop-down menu to see the configuration publishing and distribution wizard
(2) [next] You can select the distributor as the distributor or another SQL Server.
(3) [next] set the snapshot folder
The default value is // servername/d $/program files/Microsoft SQL Server/MSSQL/repldata.
** (4) [next] custom configuration
You can select:
Yes. Let me set the attributes of the distribution database to enable the Publishing Server or set the publishing settings.
No, use the following default settings

Custom settings are recommended.
(5) [next] use the default value to set the name and location of the Distribution Database
(6) [next] enable the Publishing Server and select as the Publishing Server
(7) [next] select the database and release type to be released
(8) [next] Select register subscription Server
(9) [next] complete configuration

Ii. Create a publication

On the Publishing Server
(1) Select the [create and manage release] command from the [copy] sub-menu in the [tools] menu.
(2) Select the database for which you want to create the publication, and click [Create release]
(3) In the prompt dialog box of the [Create release wizard], click [next]. A dialog box is displayed.
The dialog box contains three types of copies. Now we select the first one, that is, the default snapshot release (the other two can be checked for help)

(4) Click [next] system requirements to specify the database server type that can subscribe to the release,
Sqlserver allows data replication between different databases, such as oracle or access.
But here we choose to run the database server "SQL Server 2000"

(5) Click [next]. A dialog box for defining the document is displayed, that is, the table to be published.

NOTE: If transaction publishing is selected, only tables with primary keys can be selected in this step.

(6) Select the release Name and description.

** (7) custom publishing attributes

Selection provided by the Wizard:
Yes. I will filter custom data, enable Anonymous subscription, and other custom attributes.
No create a release according to the specified Method

We recommend that you use a custom method.

(8) [next] Select a method for filtering and Publishing
(9) [next] You can select whether anonymous subscription is allowed

1) if you select "signed Subscription", you need to add a subscription server to the Publishing Server.
Method: [tools]-> [copy]-> [configure attributes of publishing, subscription server, and Distribution]-> Add in [subscription server]

Otherwise, a prompt will be displayed when you request subscription on the subscription server: you cannot subscribe anonymously if you change the subscription to publish.
If anonymous subscription is still required, use the following solution:
[Enterprise Manager]-> [copy]-> [publish content]-> [properties]-> [subscription options] Select allow anonymous request subscription

2) if you select Anonymous subscription, the above prompt will not appear When configuring the subscription server.

(10) [next] configuring snapshot Agent Scheduling
(11) [next] complete configuration

After the publication is created, the database for creating the publication becomes a shared database.

Configuration of three subscription servers

(1) configuration of forced subscription
On the Publishing Server
[Enterprise Manager]-> [copy]-> [publish content]-> [properties]-> [subscription]-> [force create]
Then the force subscription wizard appears.
Choose subscription Server> subscription database name> set distribution Agent Scheduling> initialize subscription> Start required services
(The agent of the Publishing Server must be started.)-> complete.

(2) configuration of request subscription

On the subscription server, perform the following operations:

(1) select [request subscription] from the [tools] drop-down menu in the [copy] submenu
(2) select [request a new subscription] to open the request subscription wizard.
(3) [next] Select a registered release
(4) [next] select the database to create the subscription
(5) [next] select Anonymous subscription or signed Subscription
(6) [next] Select initialization Architecture and Data for initialization subscription
(7) [next] select the storage location of the snapshot file. Generally, the default snapshot folder of the release server is used.
** (8) [next] You can set the interval between subscription and update content for the distribution agent scheduling.
(9) [next] The system will prompt you to check the running status of the proxy service of the Publishing Server
(10) [next] complete configuration

Choose Windows Control Panel> Administrative Tools> services> Distributed Transaction Coordinator> Properties> Start

Choose Windows Control Panel> Administrative Tools> services> SQLServerAgent> Properties> Start

-- Manual synchronization is supported for less data, making it easier to control

-- Use a trigger to instantly synchronize instances of two tables:

-- Test environment: SQL2000, remote host name: xz, User name: SA, password: None, Database Name: Test

-- Create a test table. The primary key cannot be used as the ID column because normal updates cannot be performed.
-- Create a table on a remote host
If exists (select * From DBO. sysobjects where id = object_id (n' [test] ') and objectproperty (ID, n'isusertable') = 1)
Drop table [test]

Create Table Test (ID int not null constraint pk_test primary key
, Name varchar (10 ))
Go

-- The following operations are performed on the local machine:
-- Create tables and perform synchronization on the local machine
If exists (select * From DBO. sysobjects where id = object_id (n' [test] ') and objectproperty (ID, n'isusertable') = 1)
Drop table [test]

Create Table Test (ID int identity (1, 1) primary key
, Name varchar (10 ))
Go

-- Create a synchronization trigger
Create trigger t_test on Test
For insert, update, delete
As
Set xact_abort on
-- Start the MSDTC Service of the remote server
Exec master .. xp_mongoshell 'isql/s "xz"/u "sa"/P ""/Q "Exec master .. xp_cmdshell ''net start MSDTC '', no_output" ', no_output

-- Start the MSDTC Service of the Local Machine
Exec master.. xp_mongoshell 'net start MSDTC ', no_output

-- Performs distributed transaction processing. If the table uses the ID column as the primary key, use the following method:
Begin Distributed Transaction
Delete from OpenRowSet ('sqloledb', 'xz '; 'sa'; '', test. DBO. Test)
Where ID in (select ID from deleted)
Insert into OpenRowSet ('sqloledb', 'xz '; 'sa'; '', test. DBO. Test)
Select * From inserted
Commit tran
Go

-- Data insertion Test
Insert into test
Select 1, 'A'
Union all select 2, 'bb'
Union all select 3, 'C'
Union all select 4, 'dd'
Union all select 5, 'AB'
Union all select 6, 'bc'
Union all select 7, 'ddd'

-- Delete data test
Delete from test where ID in (1, 4, 6)

-- Update Data Test
Update Test Set Name = Name + '_ 100' where ID in (123)

-- Display Test Results
Select * from test a full join
OpenRowSet ('sqloledb', 'xz '; 'sa'; '', test. DBO. Test) B on A. ID = B. ID

-- Regularly synchronize data on the server (local data modifications are synchronized to the server)

-- Example:
-- Test environment: SQL Server2000, remote server name: xz, User name: SA, no password, Test Database: Test
-- Tables on the server (query analyzer is connected to a table created on the server)
If exists (select * From DBO. sysobjects where id = object_id (n' [user] ') and objectproperty (ID, n'isusertable') = 1)
Drop table [user]

Create Table [user] (ID int primary key, number varchar (4), name varchar (10 ))
Go

-- Perform the following operations on the LAN (Local Machine)
-- The state field is an additional field set for auxiliary updates. Field Value Description: NULL indicates a new record, 1 indicates a modified record, and 0 indicates a record without changes.
If exists (select * From DBO. sysobjects where id = object_id (n' [user] ') and objectproperty (ID, n'isusertable') = 1)
Drop table [user]
Go
Create Table [user] (ID int identity (1, 1) primary key, number varchar (4), name varchar (10), State bit)
Go
-- Create a trigger to maintain the value of the state field
Create trigger t_state on [user]
After update
As
Update [user] Set state = 1
From [user] A join inserted B on A. ID = B. ID
Where a. State is not null
Go

-- To facilitate synchronization, create a connection server to the server to be synchronized
-- The remote server name is xz, the user name is SA, and no password is required.
If exists (select 1 from Master .. sysservers where srvname = 'srv _ lnk ')
Exec sp_dropserver 'srv _ lnk ', 'droplogins'
Go
Exec sp_addmediaserver 'srv _ lnk ', '', 'sqloledb', 'xz'
Exec sp_add1_srvlogin 'srv _ lnk ', 'false', null, 'sa'
Go

-- Create a stored procedure for synchronization
If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [p_synchro] ') and objectproperty (ID, n' isprocedure') = 1)
Drop procedure [DBO]. [p_synchro]
Go
Create proc p_synchro
As
/* -- The MSDTC Service (required for Distributed Transaction Processing) is unstable.
-- Set xact_abort on
-- Start the MSDTC Service of the remote server
-- Exec master .. xp_mongoshell 'isql/s "xz"/u "sa"/P ""/Q "Exec master .. xp_cmdshell ''net start MSDTC '', no_output" ', no_output

-- Start the MSDTC Service of the Local Machine
-- Exec master.. xp_cmdshell 'net start MSDTC ', no_output

-- Performs distributed transaction processing. If the table uses the ID column as the primary key, use the following method:
-- Begin Distributed Transaction
--*/
-- Synchronize Deleted Data
Delete from srv_lnk.test.dbo. [user]
Where id not in (select ID from [user])

-- Synchronize new data
Insert into srv_lnk.test.dbo. [user]
Select ID, number, name from [user] Where state is null
 
-- Synchronize modified data
Update srv_lnk.test.dbo. [user] Set
Number = B. number, name = B. Name
From srv_lnk.test.dbo. [user]
Join [user] B on A. ID = B. ID
Where B. State = 1
 
-- Update the local flag after synchronization
Update [user] Set state = 0 where isnull (State, 1) = 1
-- Commit tran -- if distributed transaction processing is enabled, add
Go

-- Create a job and regularly execute the data synchronization Stored Procedure
If exists (select 1 from MSDB .. sysjobs where name = 'data ')
Execute MSDB. DBO. sp_delete_job @ job_name = 'data'
Exec MSDB .. sp_add_job @ job_name = 'data'

-- Create a job
Declare @ SQL varchar (800), @ dbname varchar (250)
Select @ SQL = 'exec p_synchro '-- Data Processing Command
, @ Dbname = db_name () -- Name of the database for processing data

Exec MSDB .. sp_add_jobstep @ job_name = 'data ',
@ Step_name = 'data synchronization ',
@ Subsystem = 'tsql ',
@ Database_name = @ dbname,
@ Command = @ SQL,
@ Retry_attempts = 5, -- number of retries
@ Retry_interval = 5 -- Retry Interval

-- Create Scheduling
Exec MSDB .. sp_add_jobschedule @ job_name = 'data ',
@ Name = 'schedule ',
@ Freq_type = 4, -- 4 every day, 8 weeks, 16 months
@ Freq_interval = 1, -- number of days for job execution
@ Freq_subday_type = 0, -- whether to execute the statement repeatedly. 0x1 indicates the specified time, 0x4 minutes, and 0x8 hours.
@ Freq_subday_interval = 1, -- recurrence
@ Freq_recurrence_factor = 0, -- if the execution is repeated, set it to 1; otherwise, set it to 0.
@ Active_start_time = 00000 -- start execution
Go