An introduction to the analysis of two ways to SqlServer2008 database synchronization (Sql JOB) _mssql2008

Here are two ways to synchronize your database:

　　1, the way SQL job

The way SQL job synchronizes a database is a SQL statement that synchronizes data from one data source to the target database. It is characterized by its flexibility to synchronize operations between databases in a SQL way. Automatic execution can be scheduled as a task during the time set. The disadvantage is that you need to write SQL to do the operation. Since it is the synchronization between 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 any other way I'm not sure?" There are several stored procedures: sp_droplinkedsrvlogin, sp_dropserver, sp_addlinkedserver, sp_addlinkedsrvlogin. The first two are connected by deleting the database, and the last two are connections between the databases.

Delete Connection stored procedure parameter usage as follows:

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 that applies the login mappings. The rmtsrvname data type is sysnameand has no default settings.

[@useself =] ' useself '

Determines the name of the login used to connect to the remote server. The useself data type is varchar (8)and the default setting is TRUE. A true value specifies that SQL Server-authenticated logins use their own credentials to connect to rmtsrvname, ignoring the rmtuser and rmtpassword parameters. The 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, the connection-linked server does not need to use any logins or passwords. For Windows NT authentication logins,useself is not valid unless the Microsoft Windows NT® Environment supports security account delegation, and the provider supports Windows authentication (You no longer need to create a mapping with a value of true , but the creation is still valid).

[ @locallogin =] ' LocalLogin '

Logins on the local server. The locallogin data type is sysnameand the default setting is NULL. 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 permission to access SQL Server directly to SQL Server or as members of a group that has been granted access.

[@rmtuser =] ' Rmtuser '

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

[@rmtpassword =] ' Rmtpassword '

The password associated with the rmtuser . The rmtpassword data type is sysnameand the default setting is NULL.

Return code value

0 (Success) or 1 (failed)

Comments:

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 to 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 mapping between remote logins on all logins and linked servers on the local server is created automatically by performing sp_addlinkedserver . The default mapping indicates that SQL Server uses locally logged on user credentials (equivalent to executing sp_ when the linked server's @useself is set to true ) when the login is connected to a linked server Representative Addlinkedsrvlogin). Using sp_addlinkedsrvlogin , you can only change the default mappings for specific local servers or 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 the 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 deleted. The server 's data type is sysnameand there is no default value. Server must already exist.

[@droplogins =] ' droplogins ' | Null

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

Return code value

0 (Success) or 1 (failed)

Comments

If a server has an associated remote and linked server login entry, when running sp_dropserver on that server, an error message is created that indicates that the associated login must be deleted before the remote or linked server is removed. When removing a server, use the droplogins parameter to remove all remote and linked server logins for the server.

sp_dropservercannot be performed 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 '

The connection stored procedure parameter usage is as follows:

A) sp_addlinkedserver

Create a linked server that allows access to distributed, heterogeneous queries against OLE DB data sources. After you use sp_addlinkedserver to create a linked server, the server can execute distributed queries. If the linked server is defined as Microsoft®sql Server™, remote stored procedures 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 create, theserver 's data type is sysname, and there is no default setting.

If you have more than one instance of SQL Server, theserver can be servername\instancename. The server for this link may be referenced as the data source for the following example:

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 add as a linked server. The product_name data type is nvarchar (128), and the default setting is NULL. If you are 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 OLE DB providers that are specified on the current computer. The provider_name data type is nvarchar (128), and the default setting is NULL. 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 the default setting is NULL. Data_source is passed as an DBPROP_INIT_DATASOURCE property to initialize the OLE DB provider.

When a linked server is created for the SQL Server OLE DB provider, you can specify data_source in the form of 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 running SQL Server,instancename is the name of a specific SQL Server instance to which the user will be connected.

[ @location = ] ' location '

The location of the database interpreted by the OLE DB provider. The location data type is nvarchar (4000), and the default setting is NULL. 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 the default setting is NULL. Provstr is passed as the Dbprop_init_providerstring property to initialize the OLE DB provider.

When a linked server is provided for the server OLE DB provider, the server keyword can be used as server=servername\InstanceName To specify an instance to specify the specific SQL Server instance. servername is the name of the computer on which SQL Server runs,instancename is the specific SQL Server instance name to which the user is connected.

[ @catalog = ] ' Catalog '

The directory used when the connection to the OLE DB provider is established. The catalog data type is sysnameand the default setting is NULL. 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 argument is specified, sp_addlinkedserver returns this message:

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

Returns this message using the appropriate OLE DB provider and parameter sp_addlinkedserver :

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

b) sp_addlinkedsrvlogin
Create or update mappings between logins on the local Microsoft®sql Server™ instance and remote logins 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 that applies the login mappings. The rmtsrvname data type is sysnameand has no default settings.
[@useself =] ' useself '
Determines the name of the login used to connect to the remote server. The useself data type is varchar (8)and the default setting is TRUE. A value of true specifies that SQL Server authenticated logins use their own credentials to connect to

rmtsrvname, ignoring rmtuser and rmtpassword parameters. The 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, the connection-linked server does not need to use any logins or passwords. For Windows NT authentication logins,useself is not valid  .

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

The 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 as a member of a group that has been granted access

Permissions for SQL Server.

[@rmtuser =] ' Rmtuser '

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

[@rmtpassword =] ' Rmtpassword '

The password associated with the rmtuser . The rmtpassword data type is sysnameand the default setting is NULL.
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 to access the table on behalf of that user. Use sp_addlinkedsrvlogin to specify local uniforms

The login credentials for the server to log on to the linked servers.
The default mapping between remote logins on all logins and linked servers on the local server is created automatically by performing sp_addlinkedserver . The default mapping describes a login that is connected to a linked server representative, SQL Server

Use user credentials for locally logged on (equivalent to executing sp_addlinkedsrvloginwhen the linked server's @useself is set to true ). You can only change a specific local server using sp_addlinkedsrvlogin

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 the
sp_addlinkedsrvlogin Create a preset login mapping.

 
 

  
  
 using Windows authentication mode, users connect to SQL Server.  
   
 
   
 Security account delegation is available on both the client and the sending server. 
   
 
   
 providers support Windows Authentication mode (for example, SQL Server running on Windows NT).  
  
 
 
The permissions of a separate object in the remote database are determined by the linked server when the mapping is performed using a linked server that is performing the sp_addlinkedsrvlogin definition on the local SQL server.

Instead of being 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

describes how to establish a connection, the following is a formal introduction to SQL job development. Introduced directly with the steps.
A) New job. The following figure:
A, create a new job. The following figure:
b, click the General tab of the New Job dialog box to fill in the name of the SQL job. The following figure

C, click the "New Job" dialog box, "Steps" tab, fill in the step name.







D, the first step in the job------establish a database connection operation. The following figure:




E, the next step in the job. The following figure:

E, set up job execution plan


F, click OK, complete the development of SQL job.
G, after the completion of the job is more than the newly developed work. As follows:


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