SQL SERVER replication

逆天飞扬

SQLServer Replication

SqlServer Replication Scripting Setup

1.大概的一个拓扑,我们将三种角色分别安装在三台不同的sqlserver 服务器上
1.1distributor
1.2publisher, publication, article
1.3subscriber , subscription
1.4Agents , Schedulers

2.简单的一个实现,以snapshotreplication 为例子

2.1distributor script :
我们会在distributor角色的sqlserver 服务器上，将这台服务器设置为distributor,建立distribution数据库，并配置一个允许使用它作为 distributor的publisher。

2.1.1sp_adddistributor 第一次执行的时候，必须指定password,这个password是distributor_admin密码。在 publisher连接distributor的时候，也必须指定这个distributor_admin的密码，用来通信。下面这个例子其实还需要为sp_adddistributor参数@password赋值.

2.1.2三大要素：distributor所用到的Instance, 以serverName\instanceName命名;distributor 用到的数据库distribution(名字可以更改);Snapshot 用到的存储路径.

--This script uses sqlcmd scripting variables. They are in the form
--$(MyVariable). For information about how to use scripting variables
--on the command line and in SQL Server Management Studio, see the
--"Executing Replication Scripts" section in the topic
--"Programming Replication Using System Stored Procedures".
--Install the Distributor and the distribution database.
DECLARE@distributor AS sysname;
DECLARE@distributionDB AS sysname;
DECLARE@publisher AS sysname;
DECLARE@directory AS nvarchar(500);
DECLARE@publicationDB AS sysname;
--Specify the Distributor name.
SET@distributor = $(DistPubServer);
--Specify the distribution database.
SET@distributionDB = N'distribution';
--Specify the Publisher name.
SET@publisher = $(DistPubServer);
--Specify the replication working directory.
SET@directory = N'\\' + $(DistPubServer) + '\repldata';
--Specify the publication database.
SET@publicationDB = N'AdventureWorks2008R2';
--Install the server MYDISTPUB as a Distributor using the defaults,
--including autogenerating the distributor password.
USEmaster
EXECsp_adddistributor @distributor = @distributor;
--Create a new distribution database using the defaults, including
--using Windows Authentication.
USEmaster
EXECsp_adddistributiondb @database = @distributionDB,
@security_mode= 1;
GO
--Create a Publisher and enable AdventureWorks2008R2 for replication.
--Add MYDISTPUB as a publisher with MYDISTPUB as a local distributor
--and use Windows Authentication.
DECLARE@distributionDB AS sysname;
DECLARE@publisher AS sysname;
--Specify the distribution database.
SET@distributionDB = N'distribution';
--Specify the Publisher name.
SET@publisher = $(DistPubServer);
USE[distribution]
EXECsp_adddistpublisher @publisher=@publisher,
@distribution_db=@distributionDB,
@security_mode= 1;
GO

2.2publication script , article script

2.2.1首先要做的两点，就是：一启动publisher的角色；二配置要使用的distributor. 这里使用到的存储过程sp_replicationdboption.

2.2.2在第一步里指定的replicationdatabase, 执行sp_addpublication来添加publication.

--Create a new transactional publication with the required properties.
EXECsp_addpublication
@publication= @publication,
@status= N'active',
@allow_push= N'true',
@allow_pull= N'true',
@independent_agent= N'true';
--Create a new snapshot job for the publication, using a defaultschedule.
EXECsp_addpublication_snapshot
@publication= @publication,
@job_login= @login,
@job_password= @password,
--Explicitly specify the use of Windows Integrated Authentication(default)
--when connecting to the Publisher.
@publisher_security_mode= 1;
GO

2.2.3添加article

DECLARE@publication AS sysname;
DECLARE@table AS sysname;
DECLARE@filterclause AS nvarchar(500);
DECLARE@filtername AS nvarchar(386);
DECLARE@schemaowner AS sysname;
SET@publication = N'AdvWorksProductTran';
SET@table = N'Product';
SET@filterclause = N'[DiscontinuedDate] IS NULL';
SET@filtername = N'filter_out_discontinued';
SET@schemaowner = N'Production';
--Add a horizontally and vertically filtered article for the Producttable.
--Manually set @schema_option to ensure that the Production schema
--is generated at the Subscriber (0x8000000).
EXECsp_addarticle
@publication= @publication,
@article= @table,
@source_object= @table,
@source_owner= @schemaowner,
@schema_option= 0x80030F3,
@vertical_partition= N'true',
@type= N'logbased',
@filter_clause= @filterclause;
--(Optional) Manually call the stored procedure to create the
--horizontal filtering stored procedure. Since the type is
--'logbased', this stored procedures is executed automatically.
EXECsp_articlefilter
@publication= @publication,
@article= @table,
@filter_clause= @filterclause,
@filter_name= @filtername;
--Add all columns to the article.
EXECsp_articlecolumn
@publication= @publication,
@article= @table;
--Remove the DaysToManufacture column from the article
EXECsp_articlecolumn
@publication= @publication,
@article= @table,
@column= N'DaysToManufacture',
@operation= N'drop';
--(Optional) Manually call the stored procedure to create the
--vertical filtering view. Since the type is 'logbased',
--this stored procedures is executed automatically.
EXECsp_articleview
@publication= @publication,
@article= @table,
@filter_clause= @filterclause;
GO

2.3subscription script 以pushsubscription 为例子.所有的操作都在publisher,publication 里面执行。

2.3.1判断publication是不是可以被push或者pull
Sp_helppublication
2.3.3添加pushsubscription
Sp_addsubscription
2.3.4添加pushdistributor agent
Sp_addpushsubscription_agent
默认是一天执行一次snapshotpush over ，那么怎么去修改这个同步间隔呢？
Sp_add_schedule

3.监控健康指标
3.1Replication Monitor

4.移除replication, 察看distribution数据库元数据的更改

4.1先移除subscriber和subscription
Sp_dropsubscription(publication database), sp_subscription_cleanup(subscriber database)

4.2再移除publisher和publication

4.3再移除distributor
4.4细节解说

Todisable publishing and distribution
Stopall replication-related jobs. For a list of job names, see the "AgentSecurity Under SQL Server Agent" section of ReplicationAgent Security Model.
Ateach Subscriber on the subscription database, executesp_removedbreplicationto remove replication objects from the database. This storedprocedure will not remove replication jobs at the Distributor.
Atthe Publisher on the publication database, executesp_removedbreplicationto remove replication objects from the database.
Ifthe Publisher uses a remote Distributor, execute sp_dropdistributor.
Atthe Distributor, execute sp_dropdistpublisher.This stored procedure should be run once for each Publisherregistered at the Distributor.
Atthe Distributor, execute sp_dropdistributiondbto delete the distribution database. This stored procedure should berun once for each distribution database at the Distributor. This alsoremoves any Queue Reader Agent jobs associated with the distributiondatabase.
Atthe Distributor, execute sp_dropdistributorto remove the Distributor designation from the server.

当Publisher,Distributor, Subscriber都配置好以后，我们可以通过下面的脚本来查看各个服务器充当的角色，以及配置的属性:

1.查看服务器的角色:

selectserver_id,name,is_remote_login_enabled,is_system,is_publisher,is_subscriber,is_distributor,is_nonsql_subscriberfrom sys.servers ;
selectname,is_published,is_subscribed,is_merge_published,is_distributorfrom sys.databases where name in( 'siebeldb','distribution')

配置原理详细解析:

1.Distributor: Distributor在每一个replication环境中都必须存在，它的作用是保存snapshotreplication, 在publisher,subscriber之间通信，从publisher接收replication，并分发到各个subscriber上。在配置distributor的时候，特别需要注意的两件事就是配置保存snapshotreplication文件的文件夹，第二就是指定是否是远程distributor，一般来说都会用一个专门的server来做distributor，来隔离与业务系统的资源利用冲突。

Step1  指定一个server为distributor角色
        sp_adddistributor[ @distributor= ] 'distributor'   
[, [ @heartbeat_interval= ] heartbeat_interval ]
[, [ @password= ] 'password' ]
[, [ @from_scripting= ] from_scripting ]

[@distributor=]'distributor'Isthe distribution server name. distributoris sysname,with no default. This parameter is only used if setting up a remoteDistributor. It adds entries for the Distributor properties in themsdb..MSdistributortable.

如果distributor对于publisher来说是台远程服务器，不是本机instance上的服务器，那么我们就需要配置@distributor这个参数。相关属性的值也会被记录在MSDB..Msdistributor表里面。特别需要注意的是，这个命令是运行在distributor上面的，而不是用来给publisher添加一个distributor。这个命令会将本服务器标示为distributor，并且在MSDB..Msdistributor里面添加一个distributor的配置信息，如果是单独的一台服务器，那么就会给这个服务器贴一个专门是distributor的标签。既然已经涉及到服务器层面，这就意味着整个instance就只能配置一台数据库专用来做distribution.

[@heartbeat_interval=]heartbeat_interval
Isthe maximum number of minutes that an agent can go without logging aprogress message. heartbeat_intervalis int,with a default of 10 minutes. A SQL Server Agent job is created thatruns on this interval to check the status of the replication agentsthat are running.

这里的心跳设置用来检查replication各个代理之间通信的状况，默认是10分钟。当新加一个distributor之后会自动创建一个Job来实现心跳的功能。这个属性会在MSDB..Msdistributor表里面记录下来。

[@password=]'password']
Isthe password of the distributor_adminlogin. passwordis sysname,with a default of NULL. If NULL or an empty string, password is resetto a random value. The password must be configured when the firstremote distributor is added. distributor_adminlogin and passwordare stored for linked server entry used for a distributorRPC connection, including local connections. If distributoris local, the password for distributor_adminis set to a new value. For Publishers with a remote Distributor, thesame value for passwordmust be specified when executing sp_adddistributorat both the Publisher and Distributor. sp_changedistributor_passwordcan be used to change the Distributor password.

如果Distributor是建立在远程服务器上的，那么我们需要为distributor_admin这个用户设定一个密码。这个密码在publisher配置distributor的时候，也需要指定。

[@from_scripting=] from_scripting
Identifiedfor informational purposes only. Not supported. Future compatibilityis not guaranteed.
返回值：0-成功；1-失败

上面的脚本还只是用来配置一个角色，但是这个角色对应的数据库还没有真正建立起来，下面的脚本就是用来创建distributor数据库的，而且事先我们不需要这个数据库存在。

这里要思考的问题是：1同一个instance上面可以创建多少个distribution数据库？2同一个distribution数据库可以支撑多少个publisher？

Step2 创建一个distribution数据库

下面这个脚本是运行在distributor上的，为distributor创建一个distribution数据库,这个数据库的名字当然是可以任意指定的。前提是必须先运行sp_adddistributor配置distributor.

sp_adddistributiondb[ @database= ] 'database'   
    [, [ @data_folder= ] 'data_folder' ]   
    [, [ @data_file= ] 'data_file' ]   
    [, [ @data_file_size= ] data_file_size ]   
    [, [ @log_folder= ] 'log_folder' ]   
    [, [ @log_file= ] 'log_file' ]   
    [, [ @log_file_size= ] log_file_size ]   
    [, [ @min_distretention= ] min_distretention ]   
    [, [ @max_distretention= ] max_distretention ]   
    [, [ @history_retention= ] history_retention ]   
    [, [ @security_mode= ] security_mode ]   
    [, [ @login= ] 'login' ]   
    [, [ @password= ] 'password' ]   
    [, [ @createmode= ] createmode ]  
    [, [ @from_scripting = ] from_scripting ]  

[@min_distretention=]min_distretention
Isthe minimum retention period, in hours, before transactions aredeleted from the distribution database. min_distretentionis int,with a default of 0 hours.
[@max_distretention=]max_distretention
Isthe maximum retention period, in hours, before transactions aredeleted. max_distretentionis int,with a default of 72 hours. Subscriptions that have not receivedreplicated commands that are older than the maximum distributionretention period are marked as inactive and need to be reinitialized.RAISERROR 21011 is issued for each inactive subscription. A value of0means that replicated transactions are not stored in the distributiondatabase.

@min_distretention,@max_distretention,指的是为重复事务保留的最小，最大时间长。如果某一个subscriber连最大值之前的重复日志都没有接收的话，这个subscriber就被标识为inactive，需要重新reinitialized.

[@history_retention=]history_retention
Isthe number of hours to retain history. history_retentionis int,with a default of 48 hours.
[@security_mode=]security_mode
Isthe security mode to use when connecting to the Distributor.security_modeis int,with a default of 1. 0specifies SQL Server Authentication; 1specifiesWindows Integrated Authentication.
指定为0，表示采用的安全认证方式是SQLSERVER帐户认证；如果为1，说明采用的是WindowsServer认证方式。值得思考的是，我们怎么可以指定这个数据库是既可以用Windows认证也可以用SQLServer帐户认证？

[@login=]'login'
Isthe login name used when connecting to the Distributor to create thedistribution database. This is required if security_modeis set to 0.loginissysname,with a default of NULL.
只有当Security_Mode指定为0的时候，需指定这个参数值。

[@password=]'password'
Isthe password used when connecting to the Distributor. This isrequired if security_modeis set to 0.passwordis sysname,with a default of NULL.

[@createmode=]createmode
createmodeis int,with a default of 1, and can be one of the following values.
这个参数默认是1，创建数据库或者使用现有的数据库，然后在这些数据库上调用instdist.sql来创建一些replication的对象，包括原数据表，存储过程，试图等。

虽说我们可以在配置这个distributor对应的数据库时候，直接创建数据库，但是我们还是倾向于先常规的创建一个数据库，指定一些特别的参数，然后配置这个数据库为distributordatabase.

当然我们要查询是不是distributor搭建成功，来决定是否需要配置其它的项目：

execsp_get_distributor ;

返回一个数据集;
-Distribution db installed: distribution数据库是不是创建完成
-Is distribution publisher: distribution, publisher是不是在同一个instance上面
-Has remote distribution publisher:是否有远程服务器数据库作为Publisher存在

搭建完成distributor的时候我们需要再次检查属性配置或者我们新接手了一个repliaction环境需要熟悉这些配置，我们可以用下面的T-SQL脚本:
-返回distributor,distribution数据库,working directory的配置信息:sp_helpdistributor;
-返回指定distribution数据库的信息:sp_helpdistributiondb

这两个脚本可以返回很多有用的信息，比如snapshotfile location(working directory), history cleanup agent job ,distribution cleanup agent job, 用来给publisher配置distributor的servername, login name等。

如果需要更改这些属性，可以用下面的脚本来执行：
1.At the Distributor, execute sp_changedistributor_propertytomodify Distributor properties.
2.At the Distributor, execute sp_changedistributiondbtomodify distribution database properties.
3.At the Distributor, execute sp_changedistributor_passwordtochange the Distributor password.
4.At the Distributor, execute sp_changedistpublishertochange the properties of a Publisher using the Distributor.

Step3 指定一个publisher为distributor支持的publisher

sp_adddistpublisher[ @publisher= ] 'publisher'   
        ,[ @distribution_db= ] 'distribution_db'   
    [, [ @security_mode= ] security_mode ]   
    [, [ @login= ] 'login' ]   
    [, [ @password= ] 'password' ]   
    [, [ @working_directory= ] 'working_directory' ]   
    [, [ @trusted= ] 'trusted' ]   
    [, [ @encrypted_password= ] encrypted_password ]   
    [, [ @thirdparty_flag = ] thirdparty_flag ]  
    [, [ @publisher_type = ] 'publisher_type' ]  

这个脚本可以在distributor角色服务器的任何数据库上执行，因为distributingreplication(分发副本)这个操作是服务器级别的，所以这个脚本一系列操作的本质也是作用在服务器上，因此在哪个数据内执行这个命令就无所谓了。

1.@publisher这个参数尤其要注意，暂不敢确定到底是Publisher的instance名称还是其它
2.@thirdparty_flag, @publisher_type两个参数用来指定publisher是不是非SQLSERVER数据库
3.@working_directory，就是snapshotreplication存在的地方

Step4 为publisher配置一个可用的distributor（仅用在分布式replication中）

指定一个被distributor支持的publisher,和为publibsher配置一个可用的distributor是一个连通的过程，相当于企业之间互相签订合同一样，双方各执一份。在publisher上配置一个distributor的命令和在distributor上配置一个distributor一样：

sp_adddistributor[ @distributor= ] 'distributor'   
    [, [ @heartbeat_interval= ] heartbeat_interval ]   
    [, [ @password= ] 'password' ]   
[, [ @from_scripting= ] from_scripting ]  
这里password就起到认证授权的作用了。

因为每增加一个distributor都会往sys.servers表里写入一个象征性的服务器名一样，这里面的服务器名带着各自的instance名称，同时我们会给distributor所在的服务器定义一个名称，比如repl_distributor。这个名称很重要，暂时我们可以理解为是一台服务器的名称，其它服务器或者客户端都可以通过这个名称来连接，实际上是用在replication架构中。

Step5 配置publisher,distributor,subscriber数据库的元数据

sp_replicationdboption[ @dbname= ] 'db_name'   
        ,[ @optname= ] 'optname'   
        ,[ @value= ] 'value'   
    [, [ @ignore_distributor= ] ignore_distributor ]  
    [, [ @from_scripting = ] from_scripting ]  

@optname,可以是:
-Merge publish: 指定数据库可以用作mergepublication
-Publish:除了mergepublish之外的publication
-Subscriber:指定数据库为subscription数据库
-Sync with backup
@value:false, 丢弃某个数据库的角色；true，设定某个数据库的角色
@ignore_distributor:false，可以不连接distributor来更新信息，比如若想丢弃publication数据库角色，而distributor又连不通，就需要false.

2Publisher :通过publication，我们可以将publisher数据库里的数据以及数据库对象发布到其它服务器的指定数据库里面，有些对象在mergepublication中不适用，比如storedprocedures –Execution(Transact-SQL, CLR), Indexed Views asTables，除此之外，其它对象都可以在三种publication中使用,这些对象可以是:

Tables,Partitioned Tables, Stored Procedures – Definition (Transact-SQLand CLR), Views, Indexed Views, User-Defined Types (CLR),User-Defined Functions (Transact-SQL and CLR), Alias Data Types, Fulltext indexes, Schema Objects (constraints, indexes, user DMLtriggers, extended properties, and collation).

在没有通过查找MSDN来获取publish细节之前，我们先自我假设下，正确搭建一个Publisher来publish一些Publication,大概需要哪些步骤？
-正确搭建一个distributor
-在distributor上增加一个publisher的认证：sp_adddistpublisher
-在publisher上指定我们要做publish的数据库
-在publisher上配置我们要使用的distributor
-创建一份publication
-发布publication

这是replication架构的前半部分配置，之后还需要配置subscriber和subscription。最后需要配置的是读写分离的逻辑实现，概念参见《构建高性能Web站点》郭欣（著），采用了HAProxy机制，对读写SQL进行路由分离，写的SQL都放到publisher服务器上，而读SQL都转到subscriber服务器上，对subscriber做NetworkLoad Balance.

步骤一写出来就知道自己往什么方向着手了，创建一份publication是有讲究的，怎么创建，哪些对象是可以被published的，都有哪些方式，怎么做全量，怎么做增量，publication的定义都存储在哪里?Publication的pipeline，过程控制的点，频率怎么控制，延迟如何管控，在本节都需要详细地阐述。

Publisher可以是和distributor在同一个instance上面，也可以是远程服务器上的某一个instance。搭建本地publisher和远程publisher的区别在哪里?

首先我们先来看下本地Publisher怎么搭建：
-先搭建一个distributor，配置distributor服务器属性，创建distribution数据库
-在distributor上指定一个publisher的认证sp_adddistpublisher
-在distributor上指定一个数据库为publication数据库，设定某一种特定的publication方式，snapshotreplication, transaction replication,merge replication

接着再分析下远程Publisher怎么搭建:
-先搭建一个distributor，配置distributor服务器属性，创建distribution数据库
-在distributor上指定一个publisher的认证sp_adddistpublisher
-在publisher上配置一个distributor,指定一个数据库为publication数据库，设定某一种特定的publication方式，snapshotreplication, transaction replication,merge replication

Distributor和publisher其实就类似内容生产商与分销商，必须一一签订合同。本地关系就简单些，远程关系就双方人手一份合同，有趣的是授权认证的方式，如果双方是通过私人掮客签订合同的，那么需要同一个掮客来回搭线，类似于securitymodel为0，使用SQLSERVER 帐户密码；如果双方是通过中介公司签订合同的，那么同一个公司的任何业务员都能办理这桩事，所以securitymodel可以设置为1，使用Windows帐户认证。

假如我现在已经搭好了distributor，也指定了publisher，无论是本地的还是远程的，那么这些元数据存储在哪里？既然distributor,publisher都签订了合同，那么双方都应该有保存，这份保存就是我们要找的东西。

-GUI方式:SSMS – Replication-Replication Monitor ; SQL Commandprompt：sqlmonitor.exe
-T-SQL方式：
sp_replmonitorhelppublisher
sp_replmonitorhelppublisher[ [ @publisher = ] 'publisher' ]
[, [ @refreshpolicy = ] refreshpolicy ]
@publisher可以指定一个具体的服务器名，也可以不设置，返回包括所有的publisher。返回的结果集要注意的就是status字段，它有6个可能值，意思分别是:1: started; 2:succeeded; 3:In progress; 4:Idle ;5:Retrying ;6:Failed.
execsp_helpdistributor

既然我们说了是双方合同，那么publisher上也应该有副本存在，sp_helpdistributor就可以帮我们查询到使用的distributor信息。

关于publication:
usedistribution
go
execsp_replmonitorhelppublication

上面这个脚本只能用来看哪些Publication存在，以及对应的Job，但是publication具体定义看不到.

上面的说明讨论都还只是第一步，还没有进入到创建publication阶段，所以称之为准备阶段。接下来就讨论publication阶段了。
不同的publication使用的命令方式不同，总体上分为两种:snapshot或者transactionalpublication；mergepublication. 在创建snapshot,transactional publication的时候我们使用sp_addpublication.

sp_addpublication[ @publication = ] 'publication'  
    [, [ @taskid = ] tasked ]  
    [, [ @restricted = ] 'restricted' ]  
    [, [ @sync_method = ] 'sync_method' ]  
    [, [ @repl_freq = ] 'repl_freq' ]  
    [, [ @description = ] 'description' ]  
    [, [ @status = ] 'status' ]  
    [, [ @independent_agent = ] 'independent_agent' ]  
    [, [ @immediate_sync = ] 'immediate_sync' ]  
    [, [ @enabled_for_internet = ] 'enabled_for_internet' ]  
    [, [ @allow_push = ] 'allow_push'  
    [, [ @allow_pull = ] 'allow_pull' ]  
    [, [ @allow_anonymous = ] 'allow_anonymous' ]  
    [, [ @allow_sync_tran = ] 'allow_sync_tran' ]  
    [, [ @autogen_sync_procs = ] 'autogen_sync_procs' ]  
    [, [ @retention = ] retention ]  
    [, [ @allow_queued_tran= ] 'allow_queued_updating' ]  
    [, [ @snapshot_in_defaultfolder= ] 'snapshot_in_default_folder' ]  
    [, [ @alt_snapshot_folder= ] 'alternate_snapshot_folder' ]  
    [, [ @pre_snapshot_script= ] 'pre_snapshot_script' ]  
    [, [ @post_snapshot_script= ] 'post_snapshot_script' ]  
    [, [ @compress_snapshot= ] 'compress_snapshot' ]  
    [, [ @ftp_address = ] 'ftp_address' ]  
    [, [ @ftp_port= ] ftp_port ]  
    [, [ @ftp_subdirectory = ] 'ftp_subdirectory' ]  
    [, [ @ftp_login = ] 'ftp_login' ]  
    [, [ @ftp_password = ] 'ftp_password' ]  
    [, [ @allow_dts = ] 'allow_dts' ]  
    [, [ @allow_subscription_copy = ] 'allow_subscription_copy' ]  
    [, [ @conflict_policy = ] 'conflict_policy' ]  
    [, [ @centralized_conflicts = ] 'centralized_conflicts' ]   
    [, [ @conflict_retention = ] conflict_retention ]  
    [, [ @queue_type = ] 'queue_type' ]  
    [, [ @add_to_active_directory = ] 'add_to_active_directory' ]  
    [, [ @logreader_job_name = ] 'logreader_agent_name' ]  
    [, [ @qreader_job_name = ] 'queue_reader_agent_name' ]  
    [, [ @publisher = ] 'publisher' ]   
    [, [ @allow_initialize_from_backup = ] 'allow_initialize_from_backup']  
    [, [ @replicate_ddl = ] replicate_ddl ]  
    [, [ @enabled_for_p2p = ] 'enabled_for_p2p' ]  
    [, [ @publish_local_changes_only = ] 'publish_local_changes_only' ]  
    [, [ @enabled_for_het_sub = ] 'enabled_for_het_sub' ]  
    [, [ @p2p_conflictdetection = ] 'p2p_conflictdetection' ]  
    [, [ @p2p_originator_id = ] p2p_originator_id  
    [, [ @p2p_continue_onconflict = ] 'p2p_continue_onconflict'  
    [, [ @allow_partition_switch = ] 'allow_partition_switch'  
    [, [ @replicate_partition_switch = ]'replicate_partition_switch'  

定义好一个publication，就要为publication增加article的定义，然后创建一个publication的snapshot，执行完这个snapshot的生成，接着就可以做transactional的增量了。
所以这里的问题就在于publication是否定为Transactional还是Snapshot了。不论是否定义publication为Transactional还是Snapshot，我们都需要做的一步就是生成这份Publication的Snapshot.

-Sp_addpublication来定一个publication
怎么控制这个publication是snapshot,transactional, merge publication? 首先在增加publication之前，我们应该先指定一个发布数据库，使用sp_repliactiondboption来启用某一个数据库的发布功能。

如果我们需要建立的是transactionalreplication,我们还需要创建一个logReaderAgent(Job).
执行sp_addlogreader_agent来添加这个AgentJob，执行sp_helplogreader_agent可以查询到我们新建的这个Agentjob. 每一个publicationdatabase只能有一个同账户的logreader agent job, 如果是非sqlserver数据库的publiactiondatabase，那么必须设定特定账户用户及密码，而且连接publisher的授权认证模式也必须是0，@publisher_security_mode= 0.

接下来我们添加publication.执行sp_addpublication,设定是否可以被pull,push, 设置@repl_freq，为snapshotreplication设置snapshot,为transactionalreplication设置continuous.Transactionalreplication是默认值

这里摘抄MSDN上的一个小例子：
--To avoid storing the login and password in the script file, thevalues
--are passed into SQLCMD as scripting variables. For information about
--how to use scripting variables on the command line and in SQL Server
--Management Studio, see the "Executing Replication Scripts"section in
--the topic "Programming Replication Using System StoredProcedures".
DECLARE@publicationDB AS sysname;
DECLARE@publication AS sysname;
DECLARE@login AS sysname;
DECLARE@password AS sysname;
SET@publicationDB = N'AdventureWorks';
SET@publication = N'AdvWorksProductTran';
--Windows account used to run the Log Reader and Snapshot Agents.
SET@login = $(Login);
--This should be passed at runtime.
SET@password = $(Password);
--Enable transactional or snapshot replication on the publicationdatabase.
EXECsp_replicationdboption
  @dbname=@publicationDB,
  @optname=N'publish',
  @value= N'true';
--Execute sp_addlogreader_agent to create the agent job.
EXECsp_addlogreader_agent
  @job_login= @login,
  @job_password= @password,
  --Explicitly specify the use of Windows Integrated Authentication(default)
  --when connecting to the Publisher.
  @publisher_security_mode= 1;
--Create a new transactional publication with the required properties.
EXECsp_addpublication
  @publication= @publication,
  @status= N'active',
  @allow_push= N'true',
  @allow_pull= N'true',
  @independent_agent= N'true';
--Create a new snapshot job for the publication, using a defaultschedule.
EXECsp_addpublication_snapshot
  @publication= @publication,
  @job_login= @login,
  @job_password= @password,
  --Explicitly specify the use of Windows Integrated Authentication(default)
  --when connecting to the Publisher.
  @publisher_security_mode= 1;
GO

-Sp_addpublication_snpashot来定义一个publication的snapshot
为什么要定义这个publication的snapshot,既然可以生成snapshot,为什么还需要定义它，而不直接从publication中生成snapshot?连着上面的分析，可知默认情况下，publication是transactional级别的，所以并没有一个全量的snapshot给到我们新建的transactionalpublication, 所以这里要为transactionalpublication新建一个snapshot.其二，无论publication定义为哪种publication,都只是定义，没有实际执行的执行者。

这个地方隐藏的一点就是，当执行完这个命令之后，就新建了一个snapshotagent job.
这个snapshot的agentjob创建，必须是在publisherdatabase上进行，当我们为某一个publication创建一个snapshotagentj job的时候，publisher通过与distributor通信，把这个新建的agentjob注册到了distributor。如果是在非Publisher数据库上执行，则会出现下面的错误:
Msg18757, Level 16, State 1, Proceduresp_MSrepl_addpublication_snapshot, Line 76
Unableto execute procedure. The database is not published. Execute theprocedure in a database that is published for replication.

-Sp_addarticle来往publication里面添加需要复制的对象和数据
这么说来，publication与article之间是一对多的关系。而article是肯定不能被包含在不同的publication之间的，如果被包含在不同的publication之间，同步逻辑该怎么写？

我们以MSDN上的一个例子来阐述:
https://msdn.microsoft.com/en-us/library/ms173857.aspx
为sp_addarticle指定的参数主要有:
@publication:一个数据库可能有多个publication,在这里我们要指定某一个具体的数据库里已有的publication;
@article:指定一个article名称；
@source_object：来指定要同步的对象
@destination_table:如果targettable/stored procedure不是同一个名字，就需要指定；
@type有很多种，比如logbased，同步表的时候就要指定logbased了；
@ins_cmd,@del_cmd,@upd_cmd就是用来更改同步逻辑的。详细参考:SpecifyHow Changes Are Propagated for Transactional Articles( https://msdn.microsoft.com/en-us/library/ms152489.aspx).

DECLARE@publication    AS sysname;
DECLARE@table AS sysname;
DECLARE@filterclause AS nvarchar(500);
DECLARE@filtername AS nvarchar(386);
DECLARE@schemaowner AS sysname;
SET@publication = N'AdvWorksProductTran';
SET@table = N'Product';
SET@filterclause = N'[DiscontinuedDate] IS NULL';
SET@filtername = N'filter_out_discontinued';
SET@schemaowner = N'Production';
--Add a horizontally and vertically filtered article for the Producttable.
--Manually set @schema_option to ensure that the Production schema
--is generated at the Subscriber (0x8000000).
EXECsp_addarticle
  @publication= @publication,
  @article= @table,
  @source_object= @table,
  @source_owner= @schemaowner,
  @schema_option= 0x80030F3,
  @vertical_partition= N'true',
  @type= N'logbased',
  @filter_clause= @filterclause;
--(Optional) Manually call the stored procedure to create the
--horizontal filtering stored procedure. Since the type is
--'logbased', this stored procedures is executed automatically.
EXECsp_articlefilter
  @publication= @publication,
  @article= @table,
  @filter_clause= @filterclause,
  @filter_name= @filtername;
--Add all columns to the article.
EXECsp_articlecolumn
  @publication= @publication,
  @article= @table;
--Remove the DaysToManufacture column from the article
EXECsp_articlecolumn
  @publication= @publication,
  @article= @table,
  @column= N'DaysToManufacture',
  @operation= N'drop';
--(Optional) Manually call the stored procedure to create the
--vertical filtering view. Since the type is 'logbased',
--this stored procedures is executed automatically.
EXECsp_articleview
  @publication= @publication,
  @article= @table,
  @filter_clause= @filterclause;
GO

Msg156, Level 15, State 1: Incorrect syntax near the keyword 'from'.
Msg21745, Level 16, State 1, Procedure sp_MSrepl_articleview, Line 301
Cannotgenerate a filter view or procedure. Verify that the value specifiedfor the @filter_clause parameter of sp_addarticle can be added to theWHERE clause of a SELECT statement to produce a valid query.
Msg20027, Level 11, State 1, Procedure sp_MSrepl_articlecolumn, Line 181
Thearticle 'region' does not exist.
如果没有使用任何的条件筛选，我们就不要在sp_addarticle里面指定filter,要不然就出现上面的错误。

-Sp_startpubliaction_snapshot来生成一份publicationsnapshot
这份publicationsnapshot是由什么来生成的，如果是snapshotreplication,那么可以理解每次replication都是全量抽取的，如果是transactionalrepliaction,则这份snapshot应该只生成一次。
由于snapshot生成一次耗时，耗网络宽带，如果基于数据库作snapshot还要锁表，不适合用来做日常的同步更新，这里我们要设置snapshotAgent job只跑一次。

在执行这段脚本的时候，特别要注意job的执行者对workingdirectory有可读可写权限，如果没有，我们可以修改这workingdirectory :

execsp_changedistpublisher
@publisher= 'VM-B9CB-CD02\MSSQLSERVER2014',
@property= 'working_directory',
@value= 'E:\Data_BU\snapshot' ;
--D:\data\data_srv\dbagroup\SQL2014\MSSQL12.MSSQLSERVER2014\MSSQL\ReplData
上面的脚本就是用来改写workingdirectory.

-TransactionalPublication，SnapshotPublication是怎么驱动replication的
我们知道replication，可以用push,pull的方式来进行，那么在publisher服务器上我们定义好publication,添加完我们需要同步的对象与数据,到这里配置Publication的工作就结束了
当subscription也配置好之后，我们就可以启动相应的AgentJob来启用replication.

3Subscriber：从订阅者的角度来说，主要做的事情就是pullpublication,就是主动拉取publication.那么Publisher要推publication该怎么配置?一份subscription把publisher与subscriber连接起来，首先在distributor上肯定是定义一些subscribers，subscriber上是不是也需要授权distributor可以访问?然后为每个subscriber配置一些subscription，在这个步骤中应该可以指定这份subscription是pull还是push形式，频率有多少。
-Synchronizationagent : 同步代理Job在pullsubscription时候，运行在subscriber服务器上，在pushsubcription的时候，运行在distributor服务器上：

不管哪种subscription方式，总有一样事情是先要在publisher上验证的，那就是要subscribe的publication,它是不是支持pull或者push: 在publisher上，执行execsp_helppublication来确定，需要修改这份publication使其支持push,pull，那么需要执行sp_changepublication

Pullsubscription: 在subscriber上，执行sp_addpullsubscription.这一步仅仅是定义subscription，要完成同步，还需要一个自动化的Job,这个Job就需要sp_addpullscription_agent来定义了，这个时候可以定义Job的同步频率。表面上看pullsubscription是subscriber单方面的动作，但从完整性可追溯性上来看，还需要在publisher中保存一份他俩之间的“业务关系”，sp_addsubscription。

--This script uses sqlcmd scripting variables. They are in the form
--$(MyVariable). For information about how to use scripting variables  
--on the command line and in SQL Server Management Studio, see the
--"Executing Replication Scripts" section in the topic
--"Programming Replication Using System Stored Procedures".
--Execute this batch at the Subscriber.
DECLARE@publication AS sysname;
DECLARE@publisher AS sysname;
DECLARE@publicationDB AS sysname;
SET@publication = N'AdvWorksProductTran';
SET@publisher = $(PubServer);
SET@publicationDB = N'AdventureWorks2012';
--At the subscription database, create a pull subscription
--to a transactional publication.
USE[AdventureWorks2012Replica]
EXECsp_addpullsubscription
  @publisher= @publisher,
  @publication= @publication,
  @publisher_db= @publicationDB;
--Add an agent job to synchronize the pull subscription.
EXECsp_addpullsubscription_agent
  @publisher= @publisher,
  @publisher_db= @publicationDB,
  @publication= @publication,
  @distributor= @publisher,
  @job_login= $(Login),
  @job_password= $(Password);
GO
这个地方还要补一个sp_addsubscription来在publisher上注册下这份subscription.

PushSubscription:在publisher上，执行sp_addsubscription,sp_addpushsubscription_agent，定义一份pushsubscription, 并且在distributor上增加一个同步的Job用来将publication推送到subscriber上面。

--This script uses sqlcmd scripting variables. They are in the form
--$(MyVariable). For information about how to use scripting variables  
--on the command line and in SQL Server Management Studio, see the
--"Executing Replication Scripts" section in the topic
--"Programming Replication Using System Stored Procedures".
DECLARE@publication AS sysname;
DECLARE@subscriber AS sysname;
DECLARE@subscriptionDB AS sysname;
SET@publication = N'AdvWorksProductTran';
SET@subscriber = $(SubServer);
SET@subscriptionDB = N'AdventureWorks2012Replica';
--Adda push subscription to a transactional publication.
USE[AdventureWorks2012]
EXECsp_addsubscription
  @publication= @publication,
  @subscriber= @subscriber,
  @destination_db= @subscriptionDB,
  @subscription_type= N'push';
--Addan agent job to synchronize the push subscription.
EXECsp_addpushsubscription_agent
  @publication= @publication,
  @subscriber= @subscriber,
  @subscriber_db= @subscriptionDB,
  @job_login= $(Login),
  @job_password= $(Password);
GO

对于每一个subscriber来说，针对每一份Publication只能订阅一次，不管是push方式还是pull方式，重复订阅就会有错误:
Msg14058, Level 16, State 1, Procedure sp_MSrepl_addsubscription, Line983
Cannotcreate the subscription because the subscription already exists inthe subscription database. Only one subscription to the samepublication is allowed in each subscription database. Drop thesubscription and add it again if necessary. If the problem persists,replication metadata might be incorrect; see Books Online fortroubleshooting information.

PushSubscription的distributoragent是建立在Publisher上面的，一旦启用就continous的执行，所以只要每一次产生新的snapshot，或者transaction都能直接同步到subscriberdatabase中去。

我们来看看这个pushsubscription distributor agent有哪些步骤:
Step1Distribution Agent Startup Message:在distributiondatabase中的MSdistribution_history
记录这次的同步。
Step2Run Agent – Replication Distributor:没有T-SQL命令来执行这一步骤，我们从AgentJob 上唯一能得到的信息是:-Subscriber [VM-B9CB-CD02\MSSQLSERVER2014] -SubscriberDB [lenistest5]-Publisher [VM-B9CB-CD02\MSSQLSERVER2014] -Distributor[VM-B9CB-CD02\MSSQLSERVER2014] -DistributorSecurityMode 1-PublisherDB [lenistest] -Continuous.
这个地方用distrib.exe来执行这一步。详细的概念这里有:
https://msdn.microsoft.com/en-us/library/ms147886.aspx.
Step3Detect nonlogged agent shutdown

当我们需要给publication新加一个article时，整个过程是这样的：
1.sp_addarticle新加一个article到publication
2.sp_changepublication修改immediate_sync为false,@force_invalidate_snapshot为1,@force_reinit_subscription为1
3.sp_addsubscription给subscriptiondatabase新加这个article的subscription.

为存储过程添加article时，格式是这样的:
execsp_addarticle
@publication= 'lenistest_snapshot',
@article= 'dbo.getregionname',
@source_object= 'getregionname',
@source_owner= 'dbo',
@type= 'proc schema only'

-对象及其数据的同步方式:数据的同步机理与对象的同步机理。数据是通过什么逻辑被同步到subscriber对应的对象上，而对象的定义修改是怎么同步的
Snapshotrepliaction:读取publication的完整性数据，调用bcp命令写到distributor的snapshot文件夹里面，然后同步到subscriptiondatabase上面，重写所有 同步对象及其数据

Transactionreplication:先执行一边snapshotreplication, 然后logreader agent会读取每一次的publication的事务操作，读到distributiondatabase里面，等subscriber来拉或者publisher的push时间来推，这个pull,push频率都是在建立AgentJob的时候都可以控制好了。这里的transactionrepliation可以看作是增量操作，先前的snapshotreplication是一次全量操作，在执行第一次全量操作之前，transactionreplication是会等待他的第一次snapshotrepliaction完成的。我们要监控的就是哪些subscriber正在等待完成它的第一次snapshotreplication . 这种replication需要一个限制，就是在sourcetable上必须定一个pk.

-同步审计:什么方法用来确保同步对象与数据是健康的，延迟是可以接受的，一致性也没有问题？Continuous的设置方法可以持续的接收snapshot或者transactionreplication.

4Summary

-Rolesand Databases: 角色与数据库
Publisher– Publication database
Distributor– Distribution database
Subscriber– Subscription database

-ReplicationAgents: replication中使用到的Job:这里少一个pullsubscription的Job
SnapshotAgent:
LogReader Agent :只在transactionalreplication中使用
DistributionAgent: 只在transactionalreplication中使用
MergeAgent:
QueueReader Agent bi-directional transactional replication
Replicationmaintenance jobs:

5Issues in replication

-当有数据库被用作repliaction的时候，我们不能drop掉这个数据库：
Msg3724, Level 16, State 3, Line 78
Cannotdrop the database 'lenistest' because it is being used forreplication.
这个时候，我们要移除这个数据库在replication中的角色:
execsp_replicationdboption
@dbname= 'lenistest',@optname = 'publish',@value = false;
在replication架构下移除数据库的角色，需要用到replication一系列的命令，也需要符合一定的条件，比如移掉所有的publication,subscription,最后才能禁用数据库的publication角色，最后才能被drop.

在搭建distributor的时候，我们要为数据库指定授权认证方式，这需要重新回顾SQLSERVER安全相关内容。

-安全认证:解决的是访问数据库服务的权限。使用Windows账户认证和使用SQLServer账户认证。两种方式最后还是会对应到SQLSERVER内置的一些账户或新建一些对应帐户。

-授权访问:解决的是使用数据库对象及其数据的权限。可以只读，可以只写，也可以两者都有。核心解决方案是采用角色与用户映射机制。

如果让我作为一名DBA来设计一套授权方案，我会设计出这些角色:
只读访问者：只能访问已有数据；
可读可写访问者：可以读取所有数据，也可以修改任何数据；
数据库所有者：可以读取，修改任何数据，还可以创建其他数据库对象，比如表，试图，存储过程，用户，给用户赋予一定的权限，比如读写，创建对象等。

当这些角色设计好之后，我们再创建相关的用户，比如John,Lewis等，给他们赋予上面创建的这些角色。这些用户如果是用SQLSERVER账户登陆的，登录名就是用户名；如果是用Windows账户登陆的，登录名需要和用户名做映射。

赋予到用户或者角色上面的操作权限，我们可以认为是固定的，比如读，写，修改，删除，完全控制等。仅有操作权限也不够，这些权限是作用在数据库对象上面的，因为我们还要对每个角色或者用户的可访问数据库对象也有个限制。所以授权的过程其实就是对数据库对象及其权限作了一个矩阵。我们可以假设数据库对象也分门别类在矩阵里面，可以指定某一类数据库对象也可以特指某一个数据库对象。
我们看到数据库里会有一些内置角色，这些角色在我们创建数据库的时候就已经自动创建好了，那么我就来分析下这些内置角色的功能:通常我们将这些内置角色称之为fixeddatabase roles,可以自定义的那些角色就称之为flexibledatabase roles. 这些内置的fixeddatabase roles分别有:db_owner, db_securityadmin, db_accessadmin, db_backupoperator,db_ddladmin, db_datawriter, db_datareader, db_denydatawriter,db_denydatareader. 已经在这些角色里面的用户可以将别的用户加入到这个角色里面。有趣的是，我们可以建立flexibledatabase role，将这些fixeddatabase roles加到这些flexibledatabase roles上面，这样更加灵活的来添加新用户。
通过下面命令，我们可以基本可以看到这些内置角色对应的权限了:

execsp_helpdbfixedrole
execsp_dbfixedrolepermission ;
execsp_helprole ;
execsp_helprolemember ;

上面的sp_helprolemember只能显示有member的role而没有member的role就不显示了。接下来的脚本可以显示所有role的membership.

SELECTDP1.name AS DatabaseRoleName,
isnull(DP2.name, 'No members') AS DatabaseUserName
FROMsys.database_role_members AS DRM
RIGHTOUTER JOIN sys.database_principals AS DP1
ONDRM.role_principal_id = DP1.principal_id
LEFTOUTER JOIN sys.database_principals AS DP2
ONDRM.member_principal_id = DP2.principal_id
ORDERBY DP1.name;

如果需要判断自己是不是属于某个role，只要is_member(),比如：
selectis_member('db_owner');

我们改写下，如果要知道自己的账户到底归属于哪些role，可以这样写：

setnocount on ;
ifnot exists( select 1 from tempdb.sys.tables where upper(name) like'%TEMPMYROLE%')
createtable #tempMyRole(RoleName varchar(200),RoleId int, IsAppRole int) ;
truncatetable #tempMyRole ;
insertinto #tempMyRole (RoleName,RoleId,IsAppRole)
execsp_helprole ;
declare@rolemember bit = 0;
declare@rolename varchar(200) ;
declaremy_rolemem cursor
forselect RoleName as rolemember from #tempMyRole ;
openmy_rolemem ;
fetchnext from my_rolemem into @rolename ;
while@@fetch_status = 0
begin
set@rolemember = 0 ;
select@rolemember = is_member(@rolename) ;
select@rolename +': '+convert(varchar,@rolemember) ;
fetchnext from my_rolemem into @rolename ;
end
closemy_rolemem
deallocatemy_rolemem ;
droptable #tempMyRole ;

创建role,增加rolemember的命令有CREATERole, Alter Role, Drop Role, sp_addRole, sp_dropRole,sp_addRoleMember, sp_dropRoleMember. 接着我们就可以用Grant,Deny, Revoke来给角色赋予权限。所有的角色和用户都可以用sys.database_role_members,sys.database_principals来查询到。

增加一个RoleMember的脚本可以这么写，但是加了这个RoleMember之后，并且给这个RoleMember加入了db_owner角色，并不意味着这个用户就可以立即登陆数据库使用了，因为SQLServer服务器端的认证还没有给这个用户加上：

createuser regionowner for login [NAM\HY88838] ;
go
execsp_addrolemember 'db_owner','regionowner' ;

上面我们为windowsaccount[NAM\ HY88838]创建一个数据库用户regionowner,并给这个用户赋予db_owner的角色。但是这个windowsaccount并不能够登陆数据库服务器。

上面是在数据库层面来设计角色与用户的架构，这可以为数据应用层面的权限关系。除此之外我们还需要考虑SERVER层面的授权认证关系。和数据库层面的权限架构一样，Server层面的权限架构也有角色与用户之分，只不过名字更绕口一些，serverrole与login .

为数据库增加一个使用者，第一步就是要新建一个login,下面我们模拟下步骤：

如果是windows账户:
createlogin [NAM\ HY88838] from windows ;
go
如果是SQLSERVER账户:
createlogin huangyun
withpassword = 'Hoover123456.'

有了login，我们就可以为这个login赋予serverrole和databaserole了：

alterserver role sysadmin add [NAM\ HY88838]
go
execsp_addrolemember @rolename = 'sysadmin',@membername = 'regionowner' ;

用下面的脚本来验证，我们的新用户是不是已经拥有这个权限了：

SELECTSRM.role_principal_id, SP.name AS Role_Name,
SRM.member_principal_id,SP2.name AS Member_Name
FROMsys.server_role_members AS SRM
JOINsys.server_principals AS SP
ONSRM.Role_principal_id = SP.principal_id
JOINsys.server_principals AS SP2
ONSRM.member_principal_id = SP2.principal_id
ORDERBY SP.name, SP2.name
SELECT
perms.state_descAS State,
permission_nameAS [Permission],
obj.nameAS [on Object],
dPrinc.nameAS [to User Name],
sPrinc.nameAS [who is Login Name]
FROMsys.database_permissions AS perms
rightJOIN sys.database_principals AS dPrinc
ONperms.grantee_principal_id = dPrinc.principal_id
leftJOIN sys.objects AS obj
ONperms.major_id = obj.object_id
LEFTOUTER JOIN sys.server_principals AS sPrinc
ONdPrinc.sid = sPrinc.sid;

在这个地方我们尤其要注意的就是理解serverrole所对应的databaserole:

SELECT
perms.state_descAS State,
permission_nameAS [Permission],
obj.nameAS [on Object],
dPrinc.nameAS [to User Name],
sPrinc.nameAS [who is Login Name]
FROMsys.database_permissions AS perms
rightJOIN sys.database_principals AS dPrinc
ONperms.grantee_principal_id = dPrinc.principal_id
leftJOIN sys.objects AS obj
ONperms.major_id = obj.object_id
LEFTOUTER JOIN sys.server_principals AS sPrinc
ONdPrinc.sid = sPrinc.sid;

-先解决授权方案，再决定安全认证。授权方案创建了角色与用户，授予读写权限。安全认证就是将各种Windows账户对应到各个角色或者用户上来。

我们一起来看下MSDN上这文章，可以很好的帮助我们理解怎么从头到尾的来创建一个数据库用户：牢记一个概念，login必须要映射(mappedto)到一个数据库用户才能访问这个数据库
https://msdn.microsoft.com/en-us/library/aa337562.aspx?f=255&MSPPError=-2147217396

SQLSERVER可以创建2中大类的用户，一是windows认证用户，二是SQLSERVER认证用户。Windows用户可以分为ActiveDirectory组用户，和Windows组用户。如果SQLSERVERInstance两种都支持，那在安装实例的时候就要支持mixed认证。实际上wnidows认证总是启用的，所以认证模式主要是配置是否支持SQLSERVER认证。

这里引出来一个问题，我们怎么对一个现存的SQLSERVER实例来确定他到底支持哪种认证方式?

SELECTCASE SERVERPROPERTY('IsIntegratedSecurityOnly')
WHEN1 THEN 'Windows Authentication'
WHEN0 THEN 'Windows and SQL Server Authentication'
ENDas [Authentication Mode]

有一种读取注册表的方式来验证：
DECLARE@AuthenticationMode INT
EXECmaster.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'LoginMode',@AuthenticationMode OUTPUT
SELECTCASE @AuthenticationMode
WHEN1 THEN 'Windows Authentication'
WHEN2 THEN 'Windows and SQL Server Authentication'
ELSE'Unknown'
ENDas [Authentication Mode]

以上的方法来源于这篇文章:
https://www.mssqltips.com/sqlservertip/2191/how-to-check-sql-server-authentication-mode-using-t-sql-and-ssms/

既然有server和database权限之分，那么各自对应的权限分别有哪些?
比如server上有alterlogin, drop user等，database级别有alterindex, create table等。我们怎么知道我们有了哪些权限，或者我们被赋予的角色都有哪些权限?
检查数据库用户的权限：
execsp_dbfixedrolepermission
SELECTdistinct DB_NAME() AS 'DBName'
,p.[name]AS 'PrincipalName'
,p.[type_desc]AS 'PrincipalType'
,p2.[name]AS 'GrantedBy'
,dbp.[permission_name]
,dbp.[state_desc]
,so.[Name]AS 'ObjectName'
,so.[type_desc]AS 'ObjectType'
FROM[sys].[database_permissions] dbp LEFT JOIN [sys].[objects] so
ONdbp.[major_id] = so.[object_id] LEFT JOIN [sys].[database_principals]p
ONdbp.[grantee_principal_id] = p.[principal_id] LEFT JOIN[sys].[database_principals] p2
ONdbp.[grantor_principal_id] = p2.[principal_id]

检查服务器级别的权限:
SELECT* FROM sys.fn_builtin_permissions('SERVER') ORDER BY permission_name;

上面的脚本用来查看服务器有哪些权限。

execsp_helpsrvrole ;
execsp_helpsrvrolemember ;
execsp_srvrolepermission ;
selectis_srvrolemember('sysadmin') ;

上面的脚本用来查看login的权限。

简单讨论下如何给用户授权：Grant

Simplifiedsyntax for GRANT
GRANT{ ALL [ PRIVILEGES ] }
|permission [ ( column [ ,...n ] ) ] [ ,...n ]
[ON [ class :: ] securable ] TO principal [ ,...n ]
[WITH GRANT OPTION ] [ AS principal ]

这里要注意的是withgrant option, 就是把“授权给别人”这个权限也付给这个principal. 比如:

USEAdventureWorks2012;
GRANTREFERENCES (BusinessEntityID) ON OBJECT::HumanResources.vEmployee
TOWanida WITH GRANT OPTION;

更详细的介绍可以看这里：
https://msdn.microsoft.com/en-us/library/ms187965.aspx

我们再简单得回顾下整个搭建的过程，不涉及具体的代码，只有完整的步骤，而且我们以snapshotreplication为例，其他两种类型的replication,transactional replication, merge replication我们会再开段落来讨论。

Distributor角色的配置:

·在指定的SQLServer instance上面，指定当前的数据库服务器为distributor角色，配置相应的远程用户密码，心跳等属性；
·在已经指定为distributor的SQLServer Instance上面,指定特定的数据库为distribution数据库；
·添加远程的SQLSERVER Instance为publisher

Publisher角色的配置:

·在指定的SQLServer Instance上面，指定特定的数据库为publictation的数据库；
·添加刚才配置的Distributor，测试刚才在distributor上创建的帐户密码可用；
·创建一份publication,设定具体的replication类型为snapshotreplication，允许Publication支持pull,push;
·将所有用于replication的数据库对象以及数据作为article添加到publication里面去，如果需要新增article，则需要使原先的snapshot实效，并且重新生成snapshot;
·为新加的publication，生成一份snapshot,同时建立这个snapshot的agentjob, 新建立的agentjob就是源源不断产生snapshot的Job，并且将snapshot写入到distributor上的snapshotworking directory

uselenistest
go
execsp_replicationdboption
@dbname='lenistest'
,@optname='publish'
,@value=true;
go
execsp_addpublication
@publication= 'lenistest_snapshot'
,@allow_push= N'true'
,@allow_pull= N'true'
,@repl_freq= N'snapshot'
,@status= N'active'
,@independent_agent= N'false'
,@immediate_sync=N'false';
go
execsp_addarticle
@publication= 'lenistest_snapshot'
,@article='dbo_country'
,@source_object='country'
,@source_owner='dbo'
,@type='logbased';
go
execsp_addpublication_snapshot
@publication= 'lenistest_snapshot'
,@publisher_security_mode= 1
,@job_login= 'NAM\ HY88838'
,@job_password='Hoover123456.';
go

Subscriber角色的配置:

·在指定SQLServer Instance上面，开启特定数据库的subscriber功能；
·在publisher的publication数据库上，执行添加一份subscription的操作，在这份subscription中指定Publication，article，subscriber,subscription database，并且指定使用push方式；
·在publisher上面，添加一个pushsubscription的agentjob, 用来接收snapshot然后推送到subscriber上。

uselenistest5
go
execsp_replicationdboption
@dbname='lenistest5'
,@optname='subscribe'
,@value=true;
go
--在publisher上面执行下面脚本
uselenistest
go
execsp_addsubscription
@publication= 'lenistest_snapshot'
,@subscriber= 'VM-B9CB-CD02\MSSQLSERVER2014'
,@destination_db= 'lenistest5'
,@sync_type='automatic'
,@status='subscribed'
,@subscription_type= 'push'
go
execsp_addpushsubscription_agent
@publication= 'lenistest_snapshot'
,@subscriber= 'VM-B9CB-CD02\MSSQLSERVER2014'
,@subscriber_db='lenistest5'
,@subscriber_security_mode= 1
,@job_login='NAM\ HY88838'
,@job_password= 'Hoover123456.' ;
go

在实际使用过程中，如果我们需要新建新加一个article,那么怎么同步，这里在创建publication的时候，使用sp_addpublication有两个参数值的注意：

@independent_agent,@immediate_sync
Specifiesif the synchronization files for the publication are created eachtime the Snapshot Agent runs. immediate_synchronizationis nvarchar(5),with a default of FALSE. If true,the synchronization files are created or re-created each time theSnapshot Agent runs. Subscribers are able to get the synchronizationfiles immediately if the Snapshot Agent has completed before thesubscription is created. New subscriptions get the newestsynchronization files generated by the most recent execution of theSnapshot Agent. independent_agentmust be truefor immediate_synchronizationto be true.If false,the synchronization files are created only if there are newsubscriptions. You must call sp_addsubscriptionfor each subscription when you incrementally add a new article to anexisting publication. Subscribers cannot receive the synchronizationfiles after the subscription until the Snapshot Agents are startedand completed.

上面这段描述我们可以这样理解：如果我们专为某一个snapshotpublication指定一个AgentJob, 这样我们就可以指定@immediate_sync为true,这么做的作用在于，任何一个新添加的article,在下一次snapshotagentjob运行得时候就会自动生成同步文件。反过来，必须使用sp_addsubscription才能生成新加article的同步文件。

如果我们需要将publishsubscribe都停掉，怎么办？

uselenistest5
go
execsp_replicationdboption
@dbname='lenistest5'
,@optname='subscribe'
,@value=false;
go

uselenistest
go
execsp_replicationdboption
@dbname='lenistest'
,@optname='publish'
,@value=false;

go

我们来看下是怎么给现存的publication添加一份新的article：我们建立的subscription是push类型的，所以新建article的时候，我们都需要在publication数据库上执行。 尽管我们创建subscription的时候指定订阅publication下所有的article，事实上这份subscription并不包含新建的的这份article,所以我们还是要为这份article创建下新的subscription. 所以上面两个参数的作用就显现了。我们将@immediate_sync设置为false了，所以必须执行sp_addsubscription来重生成新加article的snapshot了。
use lenistest
go
create tabledbo.region(regionidint, regionnamevarchar(20)) ;
go
insert intodbo.region(regionid,regionname) values(1,'APAC') ;
go
exec sp_addarticle
   @publication = 'lenistest_snapshot'
   ,@article='dbo_region'
   ,@source_object='region'
   ,@source_owner='dbo'
   ,@type='logbased' ;
go
use lenistest
go
exec sp_addsubscription
   @publication = 'lenistest_snapshot'
   ,@article = 'dbo_region'
   ,@subscriber = 'VM-B9CB-CD02\MSSQLSERVER2014'
   ,@destination_db= 'lenistest5'
   ,@sync_type='automatic'
   ,@status ='subscribed'
   ,@subscription_type= 'push'
我们来看下，如果设置@immediate_sync为true，那么新加的article是不是会立即同步? 要注意当@immediate_sync为true的时候，@independent_agent也要为true.
uselenistest
go
exec sp_replicationdboption
       @dbname='lenistest'
       ,@optname='publish'
       ,@value=true ;
go
exec sp_addpublication     
       @publication = 'lenistest_snapshot'
       ,@allow_push= N'true'
       ,@allow_pull= N'true'
       ,@repl_freq= N'snapshot'
       ,@status = N'active'
,@independent_agent = N'true'
       ,@immediate_sync =N'true';
go
exec sp_addarticle
       @publication = 'lenistest_snapshot'
       ,@article='dbo_country'
       ,@source_object='country'
       ,@source_owner='dbo'
       ,@type='logbased' ;
go
exec sp_addpublication_snapshot
       @publication = 'lenistest_snapshot'      
       ,@publisher_security_mode= 1
       ,@job_login= 'NAM\YH33688'
       ,@job_password='Hoover123456.' ;
go
uselenistest5
go
exec sp_replicationdboption
       @dbname='lenistest5'
       ,@optname='subscribe'
       ,@value=true ;
go
--在publisher上面执行下面脚本
uselenistest
go
exec sp_addsubscription
       @publication = 'lenistest_snapshot'
       ,@subscriber= 'VM-B9CB-CD02\MSSQLSERVER2014'
       ,@destination_db= 'lenistest5'
       ,@sync_type='automatic'
,@status =null
       ,@subscription_type= 'push'
go
exec sp_addpushsubscription_agent
       @publication = 'lenistest_snapshot'
       ,@subscriber= 'VM-B9CB-CD02\MSSQLSERVER2014'
       ,@subscriber_db='lenistest5'
       ,@subscriber_security_mode= 1
       ,@job_login='NAM\YH33688'
       ,@job_password= 'Hoover123456.';
go
这里有两个地方要注意:
1 sp_addsubscription中的@status要设置为NULL:
Msg14129, Level 16, State 1, Procedure sp_MSrepl_addsubscription_article, Line 291
The@status parameter value must be NULL for 'automatic' sync_type when you addsubscriptions to an immediate_sync publication.
2 当新加一个article的时候，需要强制先前的snpshot失效: 这一步执行完后，snapshot就重新开始同步了
exec sp_addarticle
       @publication = 'lenistest_snapshot'
       ,@article='dbo_bookshops'
       ,@source_object='bookshops'
       ,@source_owner='dbo'
       ,@type='logbased'
       ,@force_invalidate_snapshot= 1;
go
Msg20607, Level 16, State 1, Procedure sp_MSreinit_article, Line 189
Cannotmake the change because a snapshot is already generated. Set@force_invalidate_snapshot to 1 to force the change and invalidate the existingsnapshot.
下面来讨论下如何搭建transactional replication, snapshot replication是transactionalreplication, merge replication的基础，要实现其他两个replication,必须先由snapshot来为整个数据库产生一份snapshot.以这份snapshot为基础，其它两种replication才能做增量。
Snapshot replication在产生的时候，会不会对性能有影响呢，如果整个数据库体量很大，并发很频繁，长时间的锁住很多表作snapshot，肯定对并发有一定影响。但是如果这份snapshot可以建立在databasesnapshot上，是不是并发就没有问题呢？Snapshotreplication在产生的时候，如果采用了read uncommitted的隔离级别，那也就不存在锁的影响；如果访问的是databasesnapshot呢，会不会对原表有影响，特别是锁的处理？
关于mergereplication, 在目前移动开发技术成熟的条件下，我想不出这类replication的使用场景了。
uselenistest
go
exec sp_replicationdboption
       @dbname='lenistest'
       ,@optname='publish'
       ,@value=true ;
go
exec sp_addlogreader_agent
       @job_login = 'NAM\YH33688'
       ,@job_password='Hoover123456.'
       ,@publisher_security_mode= 1 ;
go
exec sp_addpublication     
       @publication = 'lenistest_snapshot'
       ,@allow_push= N'true'
       ,@allow_pull= N'true'
       ,@repl_freq= N'continuous'
       ,@status = N'active'
       ,@independent_agent= N'true'
       ,@immediate_sync=N'true';
go
exec sp_addarticle
       @publication = 'lenistest_snapshot'
       ,@article='dbo_country'
       ,@source_object='country'
       ,@source_owner='dbo'
       ,@type='logbased' ;
go
exec sp_addpublication_snapshot
       @publication = 'lenistest_snapshot'      
       ,@publisher_security_mode= 1
       ,@job_login= 'NAM\YH33688'
       ,@job_password='Hoover123456.' ;
go
uselenistest5
go
exec sp_replicationdboption
       @dbname='lenistest5'
       ,@optname='subscribe'
       ,@value=true ;
go
--在publisher上面执行下面脚本
uselenistest
go
exec sp_addsubscription
       @publication = 'lenistest_snapshot'
       ,@subscriber= 'VM-B9CB-CD02\MSSQLSERVER2014'
       ,@destination_db= 'lenistest5'
       ,@sync_type='automatic'
       ,@status =null
       ,@subscription_type= 'push'
go
exec sp_addpushsubscription_agent
       @publication = 'lenistest_snapshot'
       ,@subscriber= 'VM-B9CB-CD02\MSSQLSERVER2014'
       ,@subscriber_db='lenistest5'
       ,@subscriber_security_mode= 1
       ,@job_login='NAM\YH33688'
       ,@job_password= 'Hoover123456.';
go
上面的脚本用来创建transactionalreplication。这里有几个地方要注意 :
1 sp_addlogreader_agent必须放在sp_addpublication之前，原因是transaction replication会隐式得创建一个log reader agent job。否则会出现下面的错误：
Msg21831, Level 16, State 1, Procedure sp_MSrepl_addlogreader_agent, Line 184
Thelogreader agent already exists. Use 'sp_changelogreader_agent' to change anysettings/properties.
2 在移除这类replication的时候，要加上sp_replflush，否则出现：
Msg18752, Level 16, State 1, Procedure sp_replcmds, Line 37
Onlyone Log Reader Agent or log-related procedure (sp_repldone, sp_replcmds, andsp_replshowcmds) can connect to a database at a time.
Ifyou executed a log-related procedure, drop the connection over which theprocedure was executed or execute sp_replflush over that connection beforestarting the Log Reader Agent or executing another log-related procedure.
3 新增加article的时候我们要注意给同步的表加上primary key，同样也要使得snapshot失效
exec sp_addarticle
       @publication = 'lenistest_snapshot'
       ,@article='dbo_bookshops'
       ,@source_object='bookshops'
       ,@source_owner='dbo'
       ,@type='logbased'
       ,@force_invalidate_snapshot= 1;
go
Msg14088, Level 16, State 1, Procedure sp_MSrepl_addarticle, Line 1622
Thetable '[dbo].[bookshops]' must have a primary key to be published using thetransaction-based method.
比如 :
alter table dbo.bookshops alter columnbookshopid int notnull ;
alter table dbo.bookshops add constraintpk_bookshopid primary key(bookshopid);