Two ways to SqlServer2008 database synchronization (SQL JOB)

Original: Two ways of SqlServer2008 database synchronization (SQL JOB)

Database synchronization is a relatively common feature. The following combination of my own experience of the collation, if there is incomplete or wrong understanding of the place to look Daniel not to enlighten. Here are two ways to synchronize your database:

　　1. SQL Job Mode

The way SQL jobs synchronizes a database is through an SQL statement that synchronizes data from one data source to the target database. The characteristic is that it can flexibly operate the synchronization between the databases in the way of SQL. It can be executed automatically as a task schedule at the time it is set. The disadvantage is that you need to write SQL to do the work. Since it is the synchronization between the databases, the connection between the databases is involved. Establishing a connection is the first step in synchronization. SQL Server establishes a connection that can be established through system stored procedures "is there another way I'm not sure yet". There are several stored procedures: sp_droplinkedsrvlogin, sp_dropserver, sp_addlinkedserver, sp_addlinkedsrvlogin. The first two are to delete the connection between the database, and the latter two are to establish a connection between the databases.

Delete Connection stored procedure parameters use the following:

A) sp_droplinkedsrvlogin

Grammar

sp_addlinkedsrvlogin [ @rmtsrvname = ] ‘rmtsrvname‘
[ , [ @useself = ] ‘useself‘ ]
[ , [ @locallogin = ] ‘locallogin‘ ]
[ , [ @rmtuser = ] ‘rmtuser‘ ]
[ , [ @rmtpassword = ] ‘rmtpassword‘ ]

Parameters

[@rmtsrvname =] ' rmtsrvname '

is the name of the linked server where the login mapping is applied. The data type of rmtsrvname is sysnameand there is no default setting.

[@useself =] ' useself '

Determines the login name used to connect to the remote server. The useself data type is varchar (8)and is set to TRUE by default. A value of true specifies that SQL Server verifies that the login uses its own credentials to connect to rmtsrvname, while ignoring rmtuser and rmtpassword Parameters. A value of false specifies that the rmtuser and rmtpassword parameters are used to connect to a specific locallogin rmtsrvname . If rmtuser and Rmtpassword are also set to NULL, no login or password is required to connect to the linked server. useself true for Windows NT authentication logons is not valid unless the Microsoft Windows NT® Environment supports security account delegation and the provider supports Windows authentication (It is no longer necessary to create a mapping with a value of true , but the creation is still valid).

[ @locallogin =] ' LocalLogin '

Login on the local server. The locallogin data type is sysnameand is set to NULL by default. A NULL value specifies that this entry will be applied to all local logins connected to rmtsrvname . If the value is not NULL, then locallogin can be a SQL Server login or a Windows NT user. Windows NT users must be granted access to SQL Server directly or through their members of the group to which they have been granted access.

[@rmtuser =] ' Rmtuser '

When useself is false , the user name used to connect to the rmtsrvname isrmtuser with the data type sysname , the default setting is NULL.

[@rmtpassword =] ' Rmtpassword '

The password associated with the rmtuser . The rmtpassword data type is sysnameand is set to NULL by default.

Return code value

0 (Success) or 1 (failed)

notes:

When a user logs on to a local server and executes a distributed query to access a table on a linked server, the local server must log on to the linked server and access the table on behalf of that user. Use sp_addlinkedsrvlogin to specify logon credentials for the local server to log on to the linked server.

The default mappings between all logins on the local server and telnet on the linked server are created automatically by performing sp_addlinkedserver . The default mapping describes when you connect to a linked server on behalf of a login, and SQL Server uses the locally logged on user credentials (the equivalent of setting the linked server's @useself to true when you execute sp_ Addlinkedsrvlogin). Use sp_addlinkedsrvlogin to change only the default mappings for a specific local server or to add new mappings. To remove the default mapping or any other mappings, use sp_droplinkedsrvlogin.

When all of the following conditions exist, SQL Server can automatically use the Windows NT security credentials (Windows NT user name and password) of the user who is issuing the query to connect to the linked server instead of having to use sp_ Addlinkedsrvlogin Create a preset login mapping.

Usage examples:

EXEC sp_droplinkedsrvlogin ' DBLink ', Null

b) sp_dropserver

Remove the server from the list of known remote and linked servers on the local microsoft®sql Server™.

Grammar

sp_dropserver [ @server = ] ' Server '
[ , [ @droplogins = ] { ' droplogins ' | NULL}]

Parameters

[@server =] ' Server '

The server that will be removed. The data type of the server is sysname, and there is no default value. The server must already exist.

[@droplogins =] ' droplogins ' | Null

Indicates that if droploginsis specified, those related remote and linked server logins will also be deleted for the server. The @droplogins data type is char (TEN)with a default value of NULL.

Return code value

0 (Success) or 1 (failed)

Notes

If a server has associated remote and linked server login entries, when running sp_dropserver on that server, an error message will be caused stating that the relevant login must be deleted before the remote or linked server is deleted. When removing a server, use the droplogins parameter in order to remove all remote and linked server logins for the server.

sp_dropservercannot be executed within a user-defined transaction.

Permissions

Only members of the sysadmin or setupadmin fixed server role can perform sp_dropserver.

Usage examples:

EXEC sp_dropserver ' DBLink '

To establish a connection stored procedure parameter, use the following:

A) sp_addlinkedserver

Create a linked server that allows access to distributed, heterogeneous queries against OLE DB data sources. After you create a linked server using sp_addlinkedserver , this server can execute distributed queries. If the linked server is defined as Microsoft®sql Server™, the remote stored procedure can be executed.

Grammar

sp_addlinkedserver [ @server = ] ‘server‘
[ , [ @srvproduct = ] ‘product_name‘ ]
    [ , [ @provider = ] ‘provider_name‘ ]
    [ , [ @datasrc = ] ‘data_source‘ ]
    [ , [ @location = ] ‘location‘ ]
    [ , [ @provstr = ] ‘provider_string‘ ]
    [ , [ @catalog = ] ‘catalog‘ ]

Parameters

[ @server = ] ' Server '

The local name of the linked server to be created, the data type ofserver is sysname, and there is no default setting.

If there are multiple instances of SQL Server, theserver can be servername\instancename. The linked server may be referenced as a data source for the following example:

SELECT *FROM    

If data_sourceis not specified, the server is the actual name of the instance.

[ @srvproduct = ] ' Product_Name '

The product name of the OLE DB data source to be added as a linked server. The product_name has a data type of nvarchar, which is set to NULL by default. In the case of SQL Server, you do not need to specify provider_name,data_source, location, provider_string and directories.

[ @provider = ] ' provider_name '

The unique program identifier (PROGID) of the OLE DB provider that corresponds to this data source. provider_name must be unique for an OLE DB provider that is installed on the current computer. The provider_name has a data type of nvarchar, which is set to NULL by default. The OLE DB provider should be registered with the given PROGID in the registry.

[ @datasrc = ] ' Data_source '

The name of the data source interpreted by the OLE DB provider. The data_source data type is nvarchar (4000)and is set to NULL by default. Data_source is passed as a Dbprop_init_datasource property to initialize the OLE DB provider.

When the linked server is created for the SQL Server OLE DB provider, you can specify data_source as servername\InstanceName . It can be used to connect to a specific instance of SQL Server running on a specific computer. servername is the name of the computer that is running SQL Server, andinstancename is the name of the specific instance of SQL Server that the user will be connected to.

[ @location = ] ' Location '

The location of the database that the OLE DB provider interprets. The data type of location is nvarchar (4000), which is set to NULL by default. location is passed as the Dbprop_init_location property to initialize the OLE DB provider.

[ @provstr = ] ' provider_string '

An OLE DB provider-specific connection string that identifies a unique data source. The provider_string data type is nvarchar (4000)and is set to NULL by default. Provstr is passed as the Dbprop_init_providerstring property to initialize the OLE DB provider.

When you provide a linked server for the server OLE DB provider, you can use the Server keyword as server=servername\InstanceName To specify the instance to specify a specific SQL Server instance. servername is the name of the computer on which SQL Server is running, andinstancename is the specific SQL Server instance name that the user is connected to.

[ @catalog = ] ' Catalog '

The directory to use when establishing a connection to the OLE DB provider. The data type of catalog is sysname, which is set to NULL by default. Catalog is passed as the Dbprop_init_catalog property to initialize the OLE DB provider.

Return code value

0 (Success) or 1 (failed)

result set

If no parameters are specified, sp_addlinkedserver returns this message:

Procedure ‘sp_addlinkedserver‘ expects parameter ‘@server‘, which was not supplied.

This message is returned with the sp_addlinkedserver of the appropriate OLE DB provider and parameters:

 Server added.
用法示例：
 EXEC sp_addlinkedserver ' DBLink ', ', ' SQLOLEDB ', ' 10.0.222.12 '

b) sp_addlinkedsrvlogin
Creates or updates a mapping between logins on the local Microsoft®sql Server™ instance and telnet on the linked server.
Grammar
sp_addlinkedsrvlogin [ @rmtsrvname = ] ‘rmtsrvname‘
[ , [ @useself = ] ‘useself‘ ]
[ , [ @locallogin = ] ‘locallogin‘ ]
[ , [ @rmtuser = ] ‘rmtuser‘ ]
[ , [ @rmtpassword = ] ‘rmtpassword‘ ]
Parameters
[@rmtsrvname =] ' rmtsrvname '
is the name of the linked server where the login mapping is applied. The data type of rmtsrvname is sysnameand there is no default setting.
[@useself =] ' useself '
Determines the login name used to connect to the remote server. The useself data type is varchar (8)and is set to TRUE by default. A value of true specifies that SQL Server verifies that the login uses its own credentials to connect to the

rmtsrvname, while ignoring the rmtuser and rmtpassword parameters. A value of false specifies that the rmtuser and rmtpassword parameters are used to connect to a specific locallogin rmtsrvname .

If rmtuser and Rmtpassword are also set to NULL, no login or password is required to connect to the linked server. For Windows NT authentication logins,useself is not valid if true .

Unless the Microsoft Windows NT® Environment supports security account delegation, and the provider supports Windows authentication (there is no longer a need to create a mapping with a value of true , the creation is still valid).
[ @locallogin =] ' LocalLogin '
Login on the local server. The locallogin data type is sysnameand is set to NULL by default. A NULL value specifies that this entry will be applied to all local logins connected to rmtsrvname . If the value is not NULL,

Then locallogin can be either a SQL Server login or a Windows NT user. Windows NT users must be granted direct access to SQL Server or through them as members of the group to which they have been granted access

Permissions for SQL Server.

[@rmtuser =] ' Rmtuser '

When useself is false , the user name used to connect to rmtsrvname ,rmtuser is sysname, and the default setting is Null.

[@rmtpassword =] ' Rmtpassword '

The password associated with the rmtuser . The rmtpassword data type is sysnameand is set to NULL by default.
Return code value
0 (Success) or 1 (failed)
Notes
When a user logs on to a local server and executes a distributed query to access a table on a linked server, the local server must log on to the linked server and access the table on behalf of that user. Use sp_addlinkedsrvlogin to specify local service

Login credentials for the linked server.
The default mappings between all logins on the local server and telnet on the linked server are created automatically by performing sp_addlinkedserver . Default mapping description when connecting to a linked server on behalf of a login, SQL Server

The user credentials that are used to log on locally (equivalent to executing sp_addlinkedsrvloginwhen the @useself of the linked server is set to true ). Use sp_addlinkedsrvlogin to change only a specific local server

The default mapping or add a new mapping. To remove the default mapping or any other mappings, use sp_droplinkedsrvlogin.
When all of the following conditions exist, SQL Server can automatically use the Windows NT security credentials (Windows NT user name and password) of the user who is issuing the query to connect to the linked server instead of having to use
sp_addlinkedsrvlogin Create a preset login mapping.

  
 

   
  
 
   
Using Windows authentication mode, the user connects to SQL Server.

 
   
Security account delegation is available on both the client and the sending server.

 
   
The provider supports Windows Authentication mode (for example, SQL Server running on Windows NT).
 
  
  
 
After authenticating with a linked server that uses mappings (this mapping is performed on a local SQL server sp_addlinkedsrvlogin definition), the permissions of individual objects in the remote database are determined by the linked server.

Instead, it is determined by the local server.
sp_addlinkedsrvlogincannot be executed from a user-defined transaction.
Permissions
Only members of the sysadmin and securityadmin fixed server roles can perform sp_addlinkedsrvlogin.
usage ExamplesEXEC sp_addlinkedsrvlogin ' DBLink ', ' false ', NULL, ' ILM ', ' 111111

describe how to establish a connection, the following is a formal introduction to SQL job development. Described directly with the steps.
A) Create a new job. Such as:
A, create a new job. Such as:
b, click the General tab of the New Job dialog box and fill in the name of the SQL job. Such as
C, click on the "Steps" tab of the "New Job" dialog box to complete the step name.



D. The first step in the job------establish a database connection operation. Such as:

E. The next step in the operation. Such as:
E. Set the job execution plan
F, click OK to complete the SQL job development.
G, after the completion of the job is more than just the development of the job. As follows:

H, after the completion of production, you can immediately let it execute. If you do not let it execute, it will also be executed at the set frequency. Test job:

2, publish, subscribe.

The next section of the release subscription is introduced. There's a lot of stuff in this section. Ha ha

The parameters of several stored procedures refer to the data. Interested in learning more can refer to the following address:

Reference:

Http://www.yesky.com/imagesnew/software/tsql/ts_sp_drop_9c1e.htm

Http://www.yesky.com/imagesnew/software/tsql/ts_sp_drop_59v2.htm

Http://www.yesky.com/imagesnew/software/tsql/ts_sp_adda_8gqa.htm

Http://www.yesky.com/imagesnew/software/tsql/ts_sp_adda_6e26.htm

Two ways to SqlServer2008 database synchronization (SQL JOB)