1.
Basic concepts
a.
Availability % =Online Time/(Online time + down
time), where down time = offline time + repair time
b.
HA vs. Disaster Recovery. They are not the same.
HA focus on the SQL Server
service as a whole
D.R focus on the data
2.
SQL Server Failover Cluster
a.
Based on Windows Cluster Service
b.
SQL Server
i.
Primarily an HA
feature
ii.
Uses time tested
Windows Clustering technology. Advanced heartbeat, failover, quorum model, and
quorum voting options available in the latest versions of Windows.
iii.
Protects against
Machine failures (ex. CPU/motherboard causes machine to become unresponsive or
failed)
iv.
Protects against OS
failures (ex. blue screens)
v.
Protects against SQL
Instance failures (ex. SQL hangs/ AV’s)
vi.
Application connects
using one virtual server name, which is not tied to a specific machine name.
The current owning node is abstracted via the virtual server name.
vii.
Works well with most
other features such as log shipping, replication, and asynchronous
availability groups
viii.
Manages external
dependencies well upon failover. All system and user databases reside on a
shared drive – failover simply provides access to the same shared drives to the
new owning node. Registry keys are replicated via cluster checkpoints.
ix.
Supports rolling
upgrades scenarios
x.
Instance-level
protection without data redundancy (Instance [-]DATA RLO)
xi.
Considerations:
· There is no concept of a secondary database.
Does not maintain a redundant copy of the data and so does not protect
against an I/O subsystem failure
· No special requirements with respect to
database recovery models
· Must ensure nodes are maintained properly
(patching levels should match)
· A major outage like a datacenter power
failure, or failure of the network link to the primary data center is not
addressed because all nodes are within the same datacenter.
c.
When to failover – “looksalive check” and “is
alive check”
d.
SQL Server Cluster Topology
i. Two
nodes: active – passive: one instance is running the active node in the cluster
ii. Two
nodes: active – active: two instances are running on the two nodes, competing
the resources
iii. N+1
nodes: N active – 1 passive
e.
SQL Server 2012 Improvement - Support
multi-subnet if installed on Windows Server 2008R2
i. Multi-Site Clustering with NO SAN
Replication and with NO shared storage
a.
SQL 2012 supports multi-subnet
environments, eliminating the need for a VLAN
b. Will become even more relevant:
i.
SQL 2012 now supports databases on
SMB
ii.
Windows 2012 clusters support 64
nodes
iii.
Hyper-V Replicas feature on Windows
2012
iv.
Hyper-V support for SMB
c. Used often to support DR within a SQL 2012 Availability
Group topology
ii. Multi-Site Clustering with SAN
Replication
a. HA and DR solution using Failover Clustering in combination with
SAN replication
b. Protects against I/O subsystem failure on the primary datacenter
c. Data disks can be synchronously or asynchronously mirrored
d. External dependencies handled well as in typical single site
failover clusters
e. Instance-level protection WITH data redundancy (Instance
[+] DATA RLO)
f. SQL 2012 supports multi-subnet environments, eliminating the
need for a VLAN
g. Considerations:
i. Additional expense of SAN replication technology
ii. Additional complexity of managing and configuring SAN
replication technology
iii. Quorum model and node vote options should be configured to
ensure an outage at the DR site will not cause the cluster to lose quorum
f.
Troubleshooting
i. Resources
not available
a.
Can SQL Server start on the node?
1.
All resources needed by SQL server in the
Failover Cluster Manager are online? If not, check windows event log.
2.
Then start the sqlservr.exe file in the BINN
folder on the node. If it cannot be started, check the error log in the Log
folder on the shared disk. If sql server can start, then it is the connection
problem as below
b.
Can Windows connect to SQL Server and run
sp_server_diagostics?
1.
Use sqlcmd to start SQL Server, if there is an
error, it is probably an authentication problem
2.
If sqlcmd works, it likely related to
Kerbero/NTLM authentication problem.
ii. Failover
unexpectedly
a.
Check Windows Cluster Log
1.
Caused by required resources?
2.
Caused by resources that have assigned with
“affect the group”?
3.
Cannot do isalive check
i. Windows
cluster cannot find SQL Server
ii. SQL
Server cannot respond to Windows request of cannot execute sp_server_diagostics
4.
Run cluster log /g to create Windows Server 2008
Cluster log
b.
Check SQL Server Error Log
c.
Check Windows Event Log (mainly for on I/O and
network errors)
d.
Check SQLDiag log (Extended Events)
1.
In the same folder as the error log file
2.
SQL Server 2012 has three extended events
i. SQLDiag
ii. AlwaysOn
iii. System
Health
3.
All the health diagnostics
SQL Server Resource DLL receives from sp_server_diagnostics are automatically
saved in the default Log directory of the SQL Server instance
(%PROGRAMFILES%\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log). These logs
are known as SQLDIAG logs and are saved in the XEL (extended events) file
format. These files in the SQL Server Log directory have the following format:
<HOSTNAME>_<INSTANCENAME>_SQLDIAG_X_XXXXXXXXX.xel. By looking at
the SQLDIAG logs, you may be able to determine the root cause of availability
group resource failure or failover event.
To view a SQLDIAG log, drag the .xel file into SQL Server
Management Studio.
3.
Log Shipping
a.
Overview
i. Primarily a DR solution
ii. Can use compressed backups
iii. Very good way to validate the transaction log
backups you are already taking
iv. Can run in a delayed restore mode to protect
secondary from human error on primary site (ex. If TableX was deleted on
Primary by mistake, and there is a 2-hour delay, you may have enough time to be
able to recover TableX from the Secondary)
v. DB Level protection (database RLO)
vi. Can have multiple secondaries
vii. Very good option when other DR options are not
possible or supported by a vendor since it is essentially a constant
transaction log backup and restore sequence.
viii.
Considerations:
a. Database must be in Full or Bulk Logged
recovery model
b. No automatic failover
c. No automatic redirection
d. Can only failover at database granularity -
cannot group a set of databases to failover together
e. Database on secondary not readable during
restore
f.
Database on secondary
not writeable
g. Databases have dependencies on objects that
reside in other databases such as the master database. These objects often
include logins, jobs, certificates, and server audits but can include other
objects. A process (normally involving scripts) needs to be adopted to keep
objects that are external to the database synchronized across the servers.
Suitable for environments where these external dependencies are not changed
very often (which tends to be true in most environments).
b.
Four jobs
i. backup job
A SQL Server Agent job that performs the
backup operation, logs history to the local server and the monitor server, and
deletes old backup files and history information. When log shipping is enabled,
the job category "Log Shipping Backup" is created on the primary
server instance.
ii. copy job
A SQL Server Agent job that copies the backup
files from the primary server to a configurable destination on the secondary
server and logs history on the secondary server and the monitor server. When
log shipping is enabled on a database, the job category "Log Shipping
Copy" is created on each secondary server in a log shipping configuration.
iii. restore job
A SQL Server Agent job that restores the
copied backup files to the secondary databases. It logs history on the local
server and the monitor server, and deletes old files and old history
information. When log shipping is enabled on a database, the job category
"Log Shipping Restore" is created on the secondary server instance.
iv. alert job
A SQL Server Agent job that raises alerts for
primary and secondary databases when a backup or restore operation does not
complete successfully within a specified threshold. When log shipping is
enabled on a database, job category "Log Shipping Alert" is created
on the monitor server instance. If no monitor server, SQL Server will create an
alert job on the principal and secondary servers。 The
job name is LSAlert_<instance Name>, which the following the sp every 2
minutes by default: EXEC
sys.sp_check_log_shipping_monitor_alert.
The other three jobs run the backup, copy, or
restore as below:
sqllogship -server instance_name { -backup primary_id
|
-copy secondary_id |
-restore secondary_id }
[
–verboselevel level ]
[
–logintimeout timeout_value ]
[
-querytimeout timeout_value ]
Primary_ID, Secondary_ID can be found from
SELECT * FROM msdb.dbo.log_shipping_primary_databases
SELECT * FROM msdb.dbo.log_shipping_secondary_databases
c.
Major Steps
i. Back up the transaction log at the primary
server instance.
ii. Copy the transaction log file to the secondary
server instance.
iii. Restore the log backup on the secondary server
instance (the full backup of the database on the principal server and the
restore on the secondary server is decided in the initial setup of log
shipping)
For instance, in the above figure, the log
shipping configuration has a primary server instance, three secondary server
instances, and a monitor server instance. The figure illustrates the steps
performed by backup, copy, and restore jobs, as follows:
1. The primary server instance runs the backup
job to back up the transaction log on the primary database. This server
instance then places the log backup into a primary log-backup file, which it
sends to the backup folder. In this figure, the backup folder is on a shared
directory—the backup share.
2. Each of the three secondary server instances
runs its own copy job to copy the primary log-backup file to its own local
destination folder.
3. Each secondary server instance runs its own
restore job to restore the log backup from the local destination folder onto
the local secondary database.
The primary and secondary server instances
send their own history and status to the monitor server instance.
d.
Interval: default 15 minutes, minimum – 10s
e.
Manual Failover
i. Tail
log backup
ii. Restore
all open backups
iii. Restore
database…with recovery，
then delete the copy and restore Jobs on the secondary server
iv. Transfer
the logins and passwords to the secondary server (see http://support2.microsoft.com/kb/918992)
v. Modify
the connection string to use the new server
vi. After
repairing the original primary (now secondary), you can use re-establish the
log shipping.
f.
Log Shipping Monitoring
i. Primary
server: SSMS|Reports|Standard Report|xLog Shipping Status; Secondary server:
SSMS|Reports|Standard Report|xLog Shipping Status (for copy and restore jobs)
ii. Run
EXEC
sys.sp_help_log_shipping_monitor from the master database
iii. Check
Job History
SELECT * FROM
msdb.dbo.sysjobs
WHERE category_id=6
SELECT * FROM
msdb..sysjobhistory
SELECT * FROM
msdb.dbo.log_shipping_monitor_history_detail
SELECT * FROM
msdb.dbo.log_shipping_monitor_error_detail
g.
Trouble Shooting
i. Failed
copy job
a.
The SQL Server Agent account does not have
permission on the shared folder
b.
The shared fold/file does not exist or is moved
c.
The file name has been changed or the timestamp
has been messed up
ii. Failed
restore job
a.
The restore job cannot use the log file as it is
being used - use Process Explorer to check, antivirus program maybe is scanning
it.
b.
Permission issue for the SQL Agent account
c.
Log backup is damaged – re-establish the log
shipping
d.
LSN are out of the range, possibly interrupted
by the manual log backup. Need to recreate log shipping.
4.
Database Mirroring - HA (with a witness server) + DR
a.
Overview
i. 数据库镜像功能首次出现在SQL Server 2005 SP1中。它设计的目的是试图为SQL Server提供一个具有实时性数据同步的灾难恢复技术，即能提供数据冗余备份，切换起来也比较方便。相比较故障转移群集，数据库镜像同样也能够为客户端应用提供统一的连接方式，但同时它又具备故障转移群集所没有的抵御数据损失的能力。相对日志传送，它进行灾难切换要快很多，又基本对客户端应用透明。这些优点都使得数据库镜像成为了一项兼备高可用性和灾难恢复功能的技术。现在，数据库镜像技术在企业客户环境中，使用得越来越多了。
ii. Without
a witness
All database mirroring
sessions support only one principal server and one mirror server. This
configuration is shown in the following illustration.
iii. With
a witness
High-safety mode with
automatic failover requires a third server instance, known as a witness.
Unlike the two partners, the witness does not serve the database. The witness
supports automatic failover by verifying whether the principal server is up and
functioning. The mirror server initiates automatic failover only if the mirror
and the witness remain connected to each other after both have been
disconnected from the principal server.
The following illustration
shows a configuration that includes a witness.
iv. Summary
a. Primarily a DR solution.
Can be used as HA when used with synchronous/automatic failover options.
b. Protects against I/O
subsystem failure on the primary server or datacenter
c. Log stream compression
available for increased performance
d. Automatic page repair
e. Automatic failover is
possible, requires a 3rd witness
server
f.
Automatic redirection to secondary is possible
g. Can choose between
several mirroring and failover options: synchronous or asynchronous streaming,
automatic or manual failover
h. DB Level protection
(database RLO)
i.
Considerations:
1. Database must be using
the FULL recovery model
2. Database on secondary is
not writeable
3. Database on secondary is
not readable (can create database snapshots to work around this, but this can
quickly become burdensome)
4. Additional management
overhead of third witness server
5. Deprecated in SQL 2012
6. Only 1 secondary allowed
7. Can only failover at
database granularity - cannot group a set of databases to failover together
8. If automatic client
redirection is required, the client must change the connection string to
include the new FAILOVER_PARTNER option. Also, the client connectivity
components must be at a version which supports the new connection string – so
may not be suitable for legacy applications.
9. Database failover occurs
at the user database level, not at the server level. Note that the system
databases master, tempdb, and model cannot be mirrored and that only one
secondary database is supported with database mirroring in SQL Server 2008.
Databases have dependencies on objects that reside in other databases such as
the master database. These objects often include logins, jobs, certificates,
and server audits but can include other objects. A process (normally involving
scripts) needs to be adopted to keep objects that are external to the database
synchronized across the servers. Suitable for environments where these external
dependencies are not changed very often (which tends to be true in most
environments).
10. Some features are not
supported, such as cross database transactions.
11. Special configuration
considerations when using Replication (http://msdn.microsoft.com/en-us/library/ms151799.aspx).
b.
How does database mirroring work?
c.
Differences between Log Shipping and Database
Mirroring
i. Log
shipping uses backup and restore
ii. Database
mirroring uses the log records, more granular than log shipping thus, more
timely.
d.
Operation modes
操作模式
|
事务安全
|
传输模式
|
见证服务器
|
故障切换类型
|
高可用
|
Full
|
同步
|
有
|
自动或手动切换
|
高保护
|
Full
|
同步
|
没有
|
只能手动切换
|
高性能
|
Off
|
异步
|
无需配置
|
只能强制切换
|
e.
客户端连接重定向及超时控制
ADO.NET和SQL Native Client这两个数据库驱动程序添加一项特性：你可以在连接字符串中指定首选的服务器，同时也可以指定故障切换后的备选数据库。那些使用ADO.NET或者SQL Native Client来连接SQL Server的应用，通过这个特性可以获得自动重定向连接的能力。
"DataSource=SeverA;Failover
Partner=ServerB;InitialCatalog=AdventureWorks;Integrated Security=True;"
如果应用程序尝试连接Data Source指定的主体服务器并且成功了，那么连接字符串中的Failover
Partner属性指定的服务器就会被忽略掉。此时驱动程序会从通过主体服务器获得它的故障转移伙伴的名字，把这个名字缓存在应用程序的内存中。要注意的是，这个被缓存的名字会覆盖连接字符串中的failoverpartner属性值。也就是说，即使你在连接字符串中没有指定failover partner，甚至你有意把failover
partner设置为一个错误的、非镜像服务器的实例，只要应用程序第一次能成功连接到主体服务器，应用程序就能获得正确的镜像服务器名称。
无论是通过上面两个情况中哪一种，只要应用程序得到了两个伙伴的名称，驱动程序就会遵循“连接重试算法”。不论是初次与会话建立连接，还是在中断已建立连接后重新连接，当应用程序成功无法连接到当时主体服务器后，就会去尝试连接故障转移伙伴。这时又会有两种情况：
（1） 如果成功连接，那么就会读取新的主体服务器的镜像配置信息，得到新的镜像服务器名，并缓存在应用程序的内存里。
（2） 如果连接失败，那么驱动程序会继续尝试去访问Data
Source中指定的首选服务器。驱动程序就会这样不断的尝试这两个服务器，直到达到了连接超时的阈值。
这就是数据库镜像的“连接重试算法”。由于连接重试算法的存在，连接一个镜像数据库的时候，计算“超时”错误的方法也和连接普通的SQL
Server数据库不同。当数据库是镜像数据库的时候，你在连接字符串中指定的connectiontimeout值和非镜像数据库的connection timeout值尽管大小一样，但实际上效果是不一样的。
重试时间使用以下公式进行计算：
RetryTime =
PreviousRetryTime + ( 0.08 * TotalLoginTimeout )
（PreviousRetryTime
初始值为0）
例如，如果使用默认的登录超时期限
15 秒，则 LoginTimeout = 15。在这种情况下，前三轮中分配的重试时间如下：
轮次
|
RetryTime 计算
|
每次尝试的重试时间
|
1
|
0 + (0.08 * 15)
|
1.2 秒
|
2
|
1.2 + (0.08 * 15)
|
2.4 秒
|
3
|
2.4 + (0.08 * 15)
|
3.6 秒
|
4
|
3.6 + (0.08 * 15)
|
4.8 秒
|
也就是说，对于15秒的timeout值，实际连接服务器的最大timeout值是3.6秒！ 如果前面3轮连接（1.2秒，2.4秒，3.6秒）都不成功，在你还没有用尽第四轮的4.8秒时间之前（实际上此时第四轮只剩下0.4秒），15秒超时时间就到了，你的应用程序就会报超时错误。
要增加连接服务器的真实timeout值，你只能增加总的connectiontimeout值。比如，当我们将timeout设为120秒时，那么第一轮的重试时间是120*8%=9.6秒。即便如此，第一轮的尝试连接时间还是比非镜像数据库的缺省15秒timeout时间要短。假设你现有的应用本就需要较长的连接超时，那么如果你需要把数据库配置镜像的话，一定记住要把connectiontimeout增大，否则你的应用可能非常容易就得到超时错误。
f.
Troubleshooting
i. Major problems in DB Mirroring
a. 诊断发生故障转移的原因（对于非高可用模式的镜像，就是诊断镜像停止工作的原因）。
对于这类问题，你需要收集主体服务器，镜像服务器和见证服务器（如果有的话）上的ERRORLOG。如上面所讲的，ERRORLOG里会记录什么时候镜像发生了问题，发生了什么类型的问题，以及最终的结果如何。最重要的是，你不能孤立地去分析各个ERRORLOG，而是应该将它们彼此作为参考，互相印证。举例来说，如果你在主体服务器的ERRORLOG里发现了主体服务器失去了镜像服务器的连接，你就应当去查看镜像服务器和见证服务器ERRORLOG中相应时间点的日志，去了解当时镜像服务器和见证服务器是否能连接到主体服务器。只有了解了所有服务器彼此之间的连通状况，才能正确判断出当时是那台服务器的网络端出现了问题。
b.
性能问题。
由于只有主体服务器才能影响应用端的请求，性能问题都会体现在主题服务器端。例如主体服务器上事物提交有严重的延迟，或者是发送队列很长等等。但是这类问题的最终症结往往却不在主体服务器上，而在镜像服务器上或者是在网络上。要诊断这类问题，需要在主体服务器和镜像服务器上打开性能监视器来记录各项镜像相关的数据，以及磁盘、网络、内存、CPU相关的数据。此外数据库镜像监视器也能帮助你了解主体/镜像服务器的运行状态。通过这些数据，你可以了解镜像服务器的处理事务的效率，网络传送事务的速度，以及镜像端的磁盘负载和磁盘性能（往往是最大瓶颈）等情况。掌握了这些信息，你就可以判断出问题的源头：是否主体服务器的负载已经超出了镜像数据库的容纳范围，是否网络性能低于设计容量，等等。
ii. What to check for troubleshooting?
a. Check the ErrorLog on the principal, mirror, and witness
servers.
b. Performance Monitor – check the counters for SQL Server: Database Mirroring
1. On
the principal server, check:
·
Transaction Delay - 以确定数据库镜像是否影响主体服务器的性能。
·
Log Send Queue KB - 以确定发送队列的大小。如果send
queue很长的话，有可能是镜像数据库处理日志慢导致的，也可能是由于网络传输慢导致的。
·
Log Bytes Sent/sec - 显示了每秒发送的日志量。可以计算“(Log Send Queue)/( Log Bytes Received /sec)”来估算镜像数据库需要多长时间才能追赶上主体数据库。
2. On
the mirroring server, check:
·
Redo Queue KB - 计数器来确定重做队列的大小，就知道有多少日志没有被重做。Log
Bytes Received/sec - 显示了每秒接收到的日志量。你可以检查如果Redo queue很大的话，这往往意味着镜像数据库存在I/O瓶颈。
·
Redo Bytes/sec- 显示了执行重做操作的速率。发生故障转移的时候，原始的镜像数据库需要把重做队列里的日志都重做完才能真正上线运行。我们可以通过计算“(RedoQueue)/(Redo
Bytes/sec)”来估算一旦发生故障转移，需要多长时间才能完成全部重做操作。
这些计数器，配合一些系统自带的计数器（例如，物理磁盘，网络等对象下的计数器），可以很直观地定位到数据库镜像的性能瓶颈。
c.
Database Mirroring Monitor
1.
How to start it?
To launch the Database Mirroring
Monitor
After
connecting to the principal server instance, in Object Explorer, click the
server name to expand the server tree.
Expand Databases,
and select the database to be monitored.
Right-click
the database, select Tasks, and then click Launch Database
Mirroring Monitor.
In the Database
Mirroring Monitor dialog
box, click Register
Mirrored Database to
register one or more mirrored database.
2. What can you do with it?
来监视镜像镜像伙伴以及镜像会话的状态，利用这些信息你可以评估在当前状况下完成故障转移所需的时间，和潜在的数据丢失量。你也可以为一些您认为很重要的指标（如send queue，redo queue）配置警告阈值，一旦超过你设置的阈值就触发警告来通知数据库管理员。
d.
System stored procedures and system tables
1.
Run sp_dbmmonitorresults
- 查看当前数据库镜像的状态。
sp_dbmmonitorresults database_name
, rows_to_return
, update_status
database_name
Specifies the database for which to return
mirroring status.
rows_to_return
Specifies the quantity of rows returned:
0 = Last row
1 = Rows last two hours
2 = Rows last four hours
3 = Rows last eight hours
4 = Rows last day
5 = Rows last two days
6 = Last 100 rows
7 = Last 500 rows
8 = Last 1,000 rows
9 = Last 1,000,000 rows
update_status
Specifies that before returning results the
procedure:
0 = Does not update the status for the
database. The results are computed using just the last two rows, the age of
which depends on when the status table was refreshed.
1 = Updates the status for the database by
calling sp_dbmmonitorupdate before computing the results.
However, if the status table has been updated within the previous 15 seconds,
or the user is not a member of the sysadmin fixed server
role, sp_dbmmonitorresults runs without updating the status.
2. sp_dbmmonitorresultsçdbm_monitor_dataçsp_dbmmonitorupdateçsys.dm_os_performance_counters。而sys.dm_os_performance_counters中的数据和性能监视器中SQL
Server:Database Mirroring下的计数器是一一对应的。
5.
Replications
a.
Overview
Replication is a set of
technologies for copying and distributing data and database objects from one
database to another and then synchronizing between databases to maintain
consistency. Using replication, you can distribute data to different locations
and to remote or mobile users over local and wide area networks, dial-up
connections, wireless connections, and the Internet.
Transactional replication is
typically used in server-to-server scenarios that require high throughput,
including: improving scalability and availability; data warehousing and
reporting; integrating data from multiple sites; integrating heterogeneous
data; and offloading batch processing. Merge replication is primarily designed
for mobile applications or distributed server applications that have possible
data conflicts.
i. Not
designed for disaster recovery or HA
ii. Mainly
focus on data synchronization
iii. Powerful
in data synchronization and merge from many data sources
iv. It
uses the magazine metaphor.
Although the magazine
metaphor is useful for understanding replication, it is important to note that
SQL Server replication includes functionality that is not represented in this
metaphor, particularly the ability for a Subscriber to make updates and for a
Publisher to send out incremental changes to the articles in a publication.
A replication
topology defines the
relationship between servers and copies of data and clarifies the logic that
determines how data flows between servers. There are several replication processes (referred
to as agents) that are responsible
for copying and moving data between the Publisher and Subscribers. The
following illustration is an overview of the components and processes involved
in replication.
v. Replication
Agents
The
Snapshot Agent is typically used with all types of replication. It prepares
schema and initial data files of published tables and other objects, stores the
snapshot files, and records information about synchronization in the
distribution database. The Snapshot Agent runs at the Distributor. For more
information, see Replication Snapshot Agent.
The Log
Reader Agent is used with transactional replication. It moves transactions
marked for replication from the transaction log on the Publisher to the
distribution database. Each database published using transactional replication
has its own Log Reader Agent that runs on the Distributor and connects to the
Publisher (the Distributor can be on the same computer as the Publisher). For
more information, see Replication Log Reader Agent.
The
Distribution Agent is used with snapshot replication and transactional
replication. It applies the initial snapshot to the Subscriber and moves
transactions held in the distribution database to Subscribers. The Distribution
Agent runs at either the Distributor for push subscriptions or at the
Subscriber for pull subscriptions. For more information, see Replication Distribution Agent.
The Merge
Agent is used with merge replication. It applies the initial snapshot to the
Subscriber and moves and reconciles incremental data changes that occur. Each
merge subscription has its own Merge Agent that connects to both the Publisher
and the Subscriber and updates both. The Merge Agent runs at either the
Distributor for push subscriptions or the Subscriber for pull subscriptions. By
default, the Merge Agent uploads changes from the Subscriber to the Publisher
and then downloads changes from the Publisher to the Subscriber. For more
information, see Replication Merge Agent.
The Queue
Reader Agent is used with transactional replication with the queued updating
option. The agent runs at the Distributor and moves changes made at the
Subscriber back to the Publisher. Unlike the Distribution Agent and the Merge
Agent, only one instance of the Queue Reader Agent exists to service all
Publishers and publications for a given distribution database. For more
information about the Queue Reader Agent, see Replication Queue Reader Agent. For more
information about updatable subscriptions, see Updatable Subscriptions for Transactional Replication.
b.
Types of replications
i. Snapshot
replication - Snapshot replication is most appropriate when data changes are
substantial but infrequent.
By default, all three types
of replication use a snapshot to initialize Subscribers. The SQL Server Snapshot
Agent always generates the snapshot files, but the agent that delivers the
files differs depending on the type of replication being used. Snapshot
replication and transactional replication use the Distribution Agent to deliver
the files, whereas merge replication uses the SQL Server Merge Agent. The
Snapshot Agent runs at the Distributor. The Distribution Agent and Merge Agent
run at the Distributor for push subscriptions, or at Subscribers for pull
subscriptions (Note: All replication types allow push -推送订阅and
pull –请求订阅subscriptions).
Snapshots can be generated
and applied either immediately after the subscription is created or according
to a schedule set at the time the publication is created. The Snapshot Agent
prepares snapshot files containing the schema and data of published tables and
database objects, stores the files in the snapshot folder for the Publisher,
and records tracking information in the distribution database on the
Distributor.
ii. Transactional
replication
a.
When to use it?
Transactional replication is typically used in
server-to-server
environments and is appropriate in each of the following cases:
·
You want incremental
changes to be propagated to Subscribers as they occur.
·
The application
requires low latency between the time changes are made at the Publisher and the
changes arrive at the Subscriber.
·
The application
requires access to intermediate data states. For example, if a row changes five
times, transactional replication allows an application to respond to each
change (such as firing a trigger), not simply the net data change to the row.
·
The Publisher has a
very high volume of insert, update, and delete activity.
·
The Publisher or
Subscriber is a non-SQL Server database, such as Oracle.
b.
What if you want to update data from the
subscriber? – updatable subscription and peer-to-peer subscription
Transactional replication supports updates at
Subscribers through updatable subscriptions and peer-to-peer replication. The
following are the two types of updatable subscriptions:
·
Immediate updating.
The Publisher and Subscriber must be connected to update data at the
Subscriber.
·
Queued updating The
Publisher and Subscriber do not have to be connected to update data at the
Subscriber. Updates can be made while the Subscriber or Publisher is offline.
c.
Peer-to-Peer replications
1. Primarily a scalability
solution
2. With Peer-To-Peer
replication, we have the capability to keep multiple copies of the data on
multiple sites, providing a scale-out, HA (if bandwidth permits) and DR
solution. Because data is maintained across the nodes, peer-to-peer replication
provides data redundancy, which increases the availability of data.
3. Database can be in
Simple recovery model
4. Database on secondary is
both readable and writeable
5. No automatic redirection
6. No concept of a failover
since all “nodes” are always “active”
7. Can have multiple nodes
participate in the topology
8. Can choose to replicate
only a subset of tables within the database (table level or filtered rows from
table RLO)
9. Since all databases are
writeable, you can create different indexes optimized for reporting
environment, optimal solution if you are running very expensive reporting
queries which need custom indexes
10. Considerations:
i. Client will have to
change connection string to an available node if the node it is connected to
becomes unavailable
ii. Replication is mainly
intended for reporting offloading. Therefore the architecture builds an interim
queue in the distribution database, causing higher latencies than would be
observed with the Availability Groups architecture.
iii. Does not handle
conflicts gracefully, application owner will need to ensure data modified at
different sites does not conflict (app should logically partition the updates).
P2P replication will simply overwrite data (lost updates) unless configured to
fail when a conflict occurs, which requires manual intervention (http://technet.microsoft.com/en-us/library/bb934199.aspx).
iv. Does not support
initialization and re-initialization via a snapshot
v. Replicated tables must
have a primary key
iii. Merge
replication
a.
When to use it?
Merge replication is typically used in server-to-client environments.
Merge replication is appropriate in any of the following situations:
·
Multiple Subscribers
might update the same data at various times and propagate those changes to the
Publisher and to other Subscribers.
·
Subscribers need to
receive data, make changes offline, and later synchronize changes with the
Publisher and other Subscribers.
·
Each Subscriber
requires a different partition of data.
·
Conflicts might occur
and, when they do, you need the ability to detect and resolve them.
·
The application
requires net data change rather than access to intermediate data states. For
example, if a row changes five times at a Subscriber before it synchronizes
with a Publisher, the row will change only once at the Publisher to reflect the
net data change (that is, the fifth value).
b.
How does it work?
1. Merge
replication allows various sites to work autonomously and later merge updates
into a single, uniform result. Because updates are made at more than one node,
the same data may have been updated by the Publisher and by more than one
Subscriber. Therefore, conflicts can occur when updates are merged and merge
replication provides a number of ways to handle conflicts.
2. Merge
replication is implemented by the SQL Server Snapshot Agent and Merge Agent. If
the publication is unfiltered or uses static filters, the Snapshot Agent
creates a single snapshot. If the publication uses parameterized filters, the
Snapshot Agent creates a snapshot for each partition of data. The Merge Agent
applies the initial snapshots to the Subscribers. It also merges incremental
data changes that occurred at the Publisher or Subscribers after the initial
snapshot was created, and detects and resolves any conflicts according to rules
you configure.
3. To
track changes, merge replication (and transactional replication with queued
updating subscriptions) must be able to uniquely identify every row in every
published table. To accomplish this merge replication adds the column rowguid to every table, unless the table
already has a column of data type uniqueidentifier with
the ROWGUIDCOL property set (in which case this
column is used). If the table is dropped from the publication, the rowguid column is removed; if an existing
column was used for tracking, the column is not removed. A filter must not
include the rowguidcol used by replication to identify rows.
The newid() function is provided as a default for
the rowguid column, however customers can provide
a guid for each row if needed. However, do not provide value
00000000-0000-0000-0000-000000000000.
iv. Summary
不管是那种类型，它们最终的目的都是把数据从发布服务器同步到订阅服务器。但是每种类型的复制跟踪数据更改的方式都不同。快照复制不会跟踪快照生成后任何的数据更改，因此要同步数据变更就需要每次都把新的快照应用订阅服务器上，完全覆盖现有数据。事务复制通过SQL Server的 事务日志跟踪更改，复制以事务为单位将更改发送到订阅服务器。而合并复制则通过触发器和系统表来跟踪数据和架构更改。一般情况下，数据更改都发生在发布服务器上，订阅服务器上不应有数据更改；但是合并复制、对等事务复制以及可更新订阅的事物复制允许在订阅服务器上进行更改，并使这些更改流向发布服务器。
v. Replication
and Disaster Recovery
一个灾难恢复的方案，必定需要一个和主数据库保持同步的备用数据库。因为复制可以创建并维护一份数据副本，所以也可以用来创建备用数据库。在作备份数据库时，一般用户都希望同步的间隔能越短越好，最好是完全同步；并且希望同步给主服务器带来的负担越少越好。基于这个要求，如果要使用复制来作为灾难恢复的方案，事务复制是最佳的选择。如果数据库是用于灾难恢复的目的，你应该人为地禁止任何人或应用更新订阅服务器上的数据。
事实上，事务复制最适合用于数据库对象级别的复制，例如仅复制一个或几个表，而不是复制整个数据库。事务复制的强大之处在于它可以只同步几个表，甚至是表中的部分数据，这是数据库镜像和日志传送都无法达到的功能。微软也建议只在同步比较小量数据的情况下使用事务复制作为灾难恢复方案。
另外，复制只同步表格里的数据，而不会同步其他数据库对象，例如，数据库用户、存储过程、索引、约束、外键、触发器等。仅仅使用复制，是无法得到一个完整的数据库的。应用程序一般无法直接使用订阅服务器上的数据库。用户常常是在主服务器出现问题的时候，先恢复数据库结构，然后将订阅服务器上的表导回到主服务器（发布服务器）。在服务器上恢复数据库的某几个表往往比恢复整个数据库更困难，你除了需要确保表中的数据恢复回来之外，还要确保和表相关的对象（索引，约束，外键，触发器等）也被全部恢复回来。
所以，复制是一个用来实现数据同步的技术，而不是设计用来实现灾难恢复的。
vi. Disaster
Recovery Options
a.
Failover Clustering
Advantage
You have high server availability. Failover clustering
automatically occurs if the primary server fails.
Disadvantages
·
You incur a greater expense. The maintenance of two servers is
two times the cost of maintaining a single server. Because you have to maintain
two servers at the same time, it is more expensive to install and maintain
clustered nodes.
·
Servers should be in the same location. If the branches of the
organization are across the globe and the Active/Active clusters must be
implemented in the branches, the networking and the storage infrastructure that
you have to use is very different from a standard quorum device server cluster.
Therefore, although it is possible, it is best not to use geographically
distant servers.
·
You have no protection against a disk array failure.
·
Failover clustering does not allow you to create failover
clusters at the database level or at the database object level, such as the
table level.
b.
Database Mirroring
Advantages
·
Database mirroring increases data protection.
·
Database mirroring increases availability of a database.
·
Database mirroring improves the availability of the production
database during upgrades.
Disadvantages
·
The mirror database should be identical to the principal
database. For example, all objects, logins, and permissions should be
identical.
·
Database mirroring involves the transfer of information from one
computer to another computer over a network. Therefore, the security of the
information that SQL Server transfers is very important.
c.
Peer-to Peer Replications - designed for applications that might read or
might modify the data in any database that participates in replication.
Advantages
·
Read performance is improved because you can spread activity
across all nodes.
·
Aggregate update performance, insert performance, and delete
performance for the topology resembles the performance of a single node because
all changes are propagated to all nodes.
Disadvantages
·
Peer-to-peer replication is available only in SQL Server 2005
Enterprise Edition.
·
All participating databases must contain identical schemas and data.
·
We recommend that each node use its own distribution database.
This configuration eliminates the potential for SQL Server 2005 to have a
single point of failure.
·
You cannot include tables and other objects in multiple
peer-to-peer publications within a single publication database.
·
You must have a publication enabled for peer-to-peer replication
before you create any subscriptions.
·
You must initialize subscriptions by using a backup or by
setting the value of the subscription synchronization type toreplication
support only.
·
Peer-to-peer transactional replication does not provide conflict
detection or conflict resolution.
·
We recommend that you do not use identity columns.
d.
Maintenance of a warm standby server
You can create and maintain a warm standby server by using either of the
following methods:
·
Log shipping
·
Transactional replication
More information about each of these two
methods follows.
1.
Log Shipping
Advantages
·
You can recover all database activities. The recovery includes
any objects that were created such as tables and views. It also includes
security changes such as the new users who were created and any permission
changes.
·
You can restore the database faster. The restoration of the
database and the transaction log is based on low-level page formats. Therefore,
log shipping speeds up the restoration process and results in the fast recovery
of data.
Disadvantages
·
The database is unusable during the restoration process because
the database is in exclusive mode on the standby server.
·
There is a lack of granularity. During the restoration process,
all the changes in the primary server are applied at the standby server. You
cannot use log shipping to apply changes to a few tables and to reject the
remaining changes.
·
There is no automatic failover of applications. When the primary
server fails because of a disaster, the standby server does not failover
automatically. Therefore, you must explicitly redirect the applications that
connect to the primary server to the standby (failover) server.
Note If your main purpose is to maintain a warm
standby server, Microsoft recommends that you use log shipping. The warm
standby server reflects all the transactions that occur on the primary server.
However, you cannot use the standby server when the primary server is
available.
2.
Transactional replications
You can also use transactional replication to maintain a warm standby
server. You should use transactional replication to maintain a warm standby
server only when you do not implement schema changes or you do not implement
other changes to the database such as security changes that replication does
not support.
Note Replication is not designed for the maintenance of warm standby servers. With replication, you can use replicated data at the subscriber to generate reports. You can also use replication for other general uses without having to perform processing on the relatively busy publisher.
Note Replication is not designed for the maintenance of warm standby servers. With replication, you can use replicated data at the subscriber to generate reports. You can also use replication for other general uses without having to perform processing on the relatively busy publisher.
Advantages and disadvantages of using transactional replication
Advantages
·
You can read data on a subscriber while you apply changes.
·
Changes are applied with less latency.
Note This advantage may not be applicable if either of the following is true:
Note This advantage may not be applicable if either of the following is true:
o
Replication agents are not set to Continuous.
o
Replication agents are stopped because of errors that may occur
during replication.
Transactional replication may take more time to apply changes because
large batch updates must be performed during the replication.
Disadvantages
·
Schema changes or security changes that are performed at the
publisher after establishing replication will not be available at the
subscriber.
·
The distributor in transactional replication uses an Open
Database Connectivity (ODBC) connection or an OLE Database (OLEDB) connection
to distribute data. However, log shipping uses the RESTORE TRANSACTION
low-level Transact-SQL statement to distribute the transaction logs. A RESTORE
TRANSACTION statement is much faster than an ODBC connection or an OLEDB
connection.
·
Typically, switching servers erases replication configurations.
Therefore, you have to configure replication two times:
When you switch to the subscriber.
When you switch back to the publisher.
·
If a disaster occurs, you must manually switch servers by
redirecting all the applications to the subscriber.
3.
Backup and restore
Advantages
·
You can back the database up to removable media to help protect
against disk failures.
·
You do not have to depend on the network as you do when you use
failover clustering or log shipping.
Disadvantages
·
When you back up the database, you cannot perform operations
such as table creation, index creation, database shrinking, or non-logged
operations.
·
If a failure occurs, you may lose your most recent data.
·
If a disaster occurs, you must manually restore the database.
4.
RAID
Advantage
·
You do not lose data if any
one disk fails.
Disadvantages
·
It may take a long time to recover the data.
·
If multiple disks fail, you may not be able to recover valuable
data.
vii. High
Availability Options
a.
Failover clustering
Failover clustering and database mirroring
both provide the following:
·
Automatic detection
and failover
·
Manual failover
·
Transparent client
redirect
Failover clustering has the following
constraints:
·
Operates at the server
instance scope
·
Requires signed
hardware
·
Has no reporting on
standby
·
Utilizes a single copy
of the database
·
Does not protect
against disk failure
b.
Database mirroring
· Operates at the database scope.
· Uses a single, duplicate copy of the database
· Uses standard servers
· Provides limited reporting on the mirror
server by using database snapshots.
· When it operates synchronously, provides for
zero work loss through delayed commit on the principal database.
· Database mirroring offers a substantive
increase in availability over the level previously possible with SQL Server and
offers an easy-to-manage alternative to failover clustering.
c.
Log shipping
Log shipping can be a supplement or an
alternative to database mirroring. Although similar in concept, asynchronous
database mirroring and log shipping have key differences. Log shipping offers
the following distinct capabilities:
·
Supports multiple
secondary databases on multiple server instances for a single primary database.
·
Allows a
user-specified delay between when the primary server backs up the log of the
primary database and when the secondary servers must restore the log backup. A
longer delay can be useful, for example, if data is accidentally changed on the
primary database. If the accidental change is noticed quickly, a delay can let
you retrieve still unchanged data from a secondary database before the change
is reflected there.
Asynchronous database mirroring has the
potential advantage over log shipping of a shorter time between when a given
change is made in the primary database and when that change is reflected to the
mirror database.
An advantage of database mirroring over log
shipping is that high-safety mode is a no data loss configuration that is
supported as a simple failover strategy.
d.
Replication
Replication offers the following benefits:
·
Allows filtering in
the database to provide a subset of data at the secondary databases because it
operates at the database scope
·
Allows more than one
redundant copy of the database
·
Allows real-time
availability and scalability across multiple databases, supporting partitioned
updates
·
Allows complete
availability of the secondary databases for reporting or other functions,
without query recovery.
c.
Summary
功能
|
故障转移群集
|
日志传送
|
数据库镜像
|
事务复制
|
|
保护级别
|
实例级
|
数据库级
|
数据库级
|
数据库对象级
|
|
是否有数据损失
|
/
|
可能有少量数据损失
|
无数据损失（同步模式）
|
可能有少量数据损失
|
|
自动故障转移
|
是
|
否
|
是（高可用操作模式）
|
否
|
|
故障转移后是否可逆
|
是
|
否
|
是
|
否
|
|
对客户端是否透明
|
是，自动重连接到相同IP的另一个节点
|
否
|
是，自动重定向（需要驱动程序支持）
|
否
|
|
停机时间
|
约等于SQL Server服务重启的时间+数据库恢复时间
|
较长
|
约等于数据库恢复时间
|
较长
|
|
多个备用数据副本
|
否
|
是
|
否
|
是
|
|
备用数据副本可读
|
/
|
是
|
否
|
是
|
|
能抵御用户误操作
|
否
|
是
|
否
|
否
|
|
能抵御磁盘故障
|
否
|
是
|
是
|
是
|
|
是否有特定硬件要求
|
windows群集
|
无
|
要求有较好的磁盘和网络
|
无
|
|
对性能的影响
|
低
|
中
|
中
|
高
|
|
版本支持
|
SQL Server 2000及以后
|
SQL Server 2000及以后
|
SQL Server 2005及以后
|
SQL Server 2000及以后
|
6. Combinations of HA and DR
a.
Failover Clustering +
Log Shipping
i. 这个组合是在数据库镜像出现之前最常见的方案。
ii. 整个方案需要至少3台服务器。两台服务器用于实现故障转移群集。故障转移群集实例需要被配置为日志传送的主服务器，而第三台服务器作为日志传送的辅助服务器维护一个冗余的副本数据库。一旦故障转移群集的数据库出了问题，你有两个选择：
a.
将第三台服务器（日志传送的辅助服务器）上的数据库恢复使其上线。将应用程序重定向到这台服务器上。如果故障转移群集上数据库的问题是由于磁盘问题导致的，短期内很难解决，这个选择会比较合适。这个选择需要你在发生故障转移后将应用程序重定向到新的主服务器上。
b.
将第三台服务器上的数据库复制到故障转移群集实例上，替代损坏的数据库上线。这个方案适用于仅是数据库文件损坏而磁盘系统正常的情况。虽然复制数据库可能需要花费一定的时间，但是好处是不用重定向应用程序，而且问题解决后你的SQL
Server依旧拥有群集提供的高可用性。
iii. 另外要注意的是，一旦做过日志传送的故障转移，日志传送就被破坏了。因此无论你的选择上面哪种办法，都要在完成灾难恢复后重新配置日志传送。
b.
Failover Clustering + Database
Mirroring
当镜像与群集一起使用时，你有三种部署的方案:
1. 两台服务器的方案。两个服务器配置成一个群集，在该群集上安装两个SQL Server实例，分别运行在两个节点，即所谓的活跃/活跃模式。而镜像的主体服务器在其中一个实例上运行，镜像服务器在另一个实例中运行。这个方案虽然很省硬件但是风险也很大。一旦某一个群集节点失败，就会造成镜像的主体服务器和镜像服务器运行在同一个节点上，该节点的硬件资源很可能捉襟见肘，严重影响主体服务器的性能。另外，主体服务器和镜像服务器作为一个Windows群集上的两个实例，意味它们其实是共用一个硬件存储器的。一旦这个存储器出了问题，那么主体服务器和镜像服务器上的两个数据副本就都不可用了，灾难恢复和高可用也无从谈起。
2. 三个服务器的方案。这个方案中，故障转移群集作为主体服务器，而镜像服务器驻留在一个单独的非群集的服务器上。
3. 四台服务器的方案。四台服务器分别组成两个故障转移群集，一个群集作为镜像的主体服务器实例，另一个群集作为镜像的镜像服务器实例。最佳情况下，这两个群集使用两个独立的存储器，避免存储区损坏导致整个方案失败的可能性。
数据库镜像的故障转移是可逆的，因此在解决故障后可以比较简单地将一切都恢复到出问题前的样子。另外，对于使用ADO.NET和SQL Native Client的应用，无论是使用哪种方案，无论是镜像还是群集发生了故障转移，都不需要重定向连接。
当决定如何在群集环境中配置数据库镜像时，所使用的镜像操作模式至关重要。
·
高可用操作模式
如果镜像使用这种操作模式，你可以选择上述第三种方案，因为该方案提供了最高的可用性。见证服务器可以安装在一个第三个群集上或一个非群集的服务器上。
假设现在有两个群集：ClusterA和ClusterB，还有另一台非群集的服务器ServerC作为见证服务器 (6 Servers!!! See below)。ClusterA上的SQL Server实例作为主体服务器，而ClusterB上的SQL Server实例作为镜像服务器。如果ClusterA的活跃节点失败，将在几秒钟内先开始进行数据库镜像自动故障转移，同时群集也会开始启动故障转移。数据库镜像的故障转移完成后，
ClusterB就从镜像服务器变为了主体服务器。在ClusterA群集故障转移完成（通常需要几分钟）之后，ClusterA的SQL Server就被切换到另一个节点，并且该实例变为了镜像服务器。
The following
illustration shows an automatic failover between clusters in a mirroring
session running in high-safety mode with a witness (which supports automatic
failover).
这样配置的优点在于：
(1) 镜像的故障转移速度比群集更快，因此停止时间更短。
(2) 一旦在短时间内ClusterB也出了问题，而原来ClusterA的活跃节点上的故障还没来得及解决，SQL Server还能切换到ClusterA上新的活跃节点继续工作。如果是单机环境的数据库镜像就无法实现这点。
·
高保护操作模式
这种操作模式下，第二种方案和第三种方案都适用。你也不需要额外的服务器做见证服务器。
假设现在的环境是一个群集ClusterA和一个非群集的服务器ServerB。ClusterA作为主体服务器而ServerB作为镜像服务器。一旦ClusterA的活跃节点失败，并不会发生数据库镜像的故障转移，而是等到ClusterA的群集故障转移完成后，Cluster新的活跃节点继续维持主体服务器的角色并和ServerB维护镜像对话。
这种方案和单纯的故障转移群集相比，通过数据库镜像获得了额外的灾难恢复的能力（镜像数据库提供数据库副本）。但是就高可用性而言，该方案和单纯的故障转移群集差别不大。另外，如果群集的两个节点都失败了，整个数据库就不可用了，你需要手动的执行镜像的故障转移才能解决问题。
·
高性能操作模式
此操作模式的部署方案基本和高保护模式相同。区别是你可以考虑将主体服务器放置在群集的故障转移群集实例中，而将镜像服务器放置在远程位置的非群集服务器上，这样实现了异地灾备。
c.
Failover Clustering + Database
Mirroring +
Log Shipping
故障转移群集+数据库镜像已经能提供非常高的可用性和不错的灾难恢复能力了。不过如果你觉得还是不放心，对于你的关键应用来说每一分钟的意外停机时间都会带来的巨大的损失，那你需要一个更加强大的方案。下面这个方案可能对你有帮助。
1. 你的主机房设在城市A，在主机房内，你选择使用故障转移群集+数据库镜像的第三套方案，即双群集和数据库镜像的方案，这样可以提供最好的高可用性。
2. 由于双群集都在一个机房里，当面临一些不可抗的因素，比如全市范围的停电，整个SQLServer系统还是会受到影响。为了进一步提高可用性和抵御灾难的能力，你可以为数据库镜像的主体数据库配置日志传送。主体服务器作为日志传送的主服务器，然后为其配置一个或多个远程的辅助服务器。你可以考虑配置两个远程的辅助服务器，一个设立在离城市A比较近的城市B，另一个设立在离城市A很远的城市C。这样你的数据在面对一些自然灾害时也有了一定的抵御能力。
3. 若要使日志传送在数据库镜像故障转移后仍能继续进行，还必须使用相同的配置将镜像服务器也配置为日志传送的主服务器（使用同样的备份共享目录，同样辅助服务器等）。由于镜像数据库平时处于还原状态，这样可以防止备份作业备份镜像数据库中的日志。这确保了镜像数据库配置的日志传送不会影响主体数据库的日志传送。
4. 如果数据库镜像进行故障转移，以前的镜像数据库将作为主体数据库并联机。此时，该数据库也将作为日志传送主数据库进入活动状态。以前无法在该数据库中完成的备份作业也将开始执行备份并传送日志。相反，故障转移将使以前的主体数据库进入还原状态，同时该数据库上日志传送的备份作业也将停止备份日志。
这种方案可靠性最高，代价是硬件成本和维护复杂度也很高。
