SQLServer数据库复制.docx
《SQLServer数据库复制.docx》由会员分享,可在线阅读,更多相关《SQLServer数据库复制.docx(19页珍藏版)》请在冰豆网上搜索。
SQLServer数据库复制
通过SQLServer2008数据库复制实现数据库同步备份
SQLServer2008数据库复制是通过发布/订阅的机制进行多台服务器之间的数据同步,我们把它用于数据库的同步备份。
这里的同步备份指的是备份服务器与主服务器进行实时数据
同步,正常情况下只使用主数据库服务器,备份服务器只在主服务器出现故障时投入使用。
它是一种优于文件备份的数据库备份解决方案。
在选择数据库同步备份解决方案时,我们评估了两种方式:
SQLServer2008的数据库镜像
和SQLServer2008数据库复制。
数据库镜像的优点是系统能自动发现主服务器故障,并且自动切换至镜像服务器。
但缺点是配置复杂,镜像数据库中的数据不可见(在SQLServer
ManagementStudio中,只能看到镜像数据库处于镜像状态,无法进行任何数据库操作,
最简单的查询也不行。
想眼见为实,看看镜像数据库中的数据是否正确都不行。
只有将镜像
数据库切换主数据库才可见)。
如果你要使用数据库镜像,强烈推荐killkill写的SQLServer2005镜像构建手册,我们就是按照这篇文章完成了数据库镜像部署测试。
最终,我们选择了SQLServer2008数据库复制。
下面通过一个示例和大家一起学习一下如何部署SQLServer2008数据库复制。
测试环境:
WindowsServer2008R2+SQLServer2008R2(英文版),两台服务器,一
台主数据库服务器CNBIogsDB1,—台备份数据库服务器CNBIogsDB2。
复制原理:
我们采用的是基于快照的事务复制。
主数据库服务器生成快照,备份库服务器读
取并加载该快照,然后不停地从主数据库服务器复制事务日志。
见下图:
图片来自SQLServer联机丛书
安装与配置步骤:
一、在两台服务器上安装好SQLServer2008R2,主要安装的组件:
DatabaseEngine(含
SQLServerReplication),ManagementTools。
二、主数据库服务器(发布服务器)的配置:
1.在主数据库服务器CNBIogsDBI新建示例数据库CNBIogsDemo(注意Recoverymode
要使用默认值Full,只有这个模式才能进行事务复制),然后建立一张测试表,比如:
CNBIogsTest。
-.J匚MEjIagsDema
'+_1DatabaseLJiagrams
戸丄Tables
+_jSystemTables
dbo.CNBlo^sTest
El□
日二(ColLmne圍ID(intjnotnull)
Msg们曲忙亦「(5(臨notnull)
IE丄Ke/s
[±]Constraints
十一1Triggers
[+1Indexes
Ti二1Statistics
2.设置存放快照的文件夹:
创建发布之前,先设置一下存放快照的文件夹,创建发布后会在该文件夹生成快照文件,
阅服务器需要在初始化时加载该快照文件。
选择Replication(复制)》LocalPublications(本地发布)》属性,在出现的窗口中选择
Publishers(发布),如下图:
点击红框处的按钮,出现设置窗口:
3.在主数据库服务器创建发布:
在Replication(复制)》LocalPublications(本地发布)中选择NewPublication(新建发
布),出现一个向导。
先选择要发布的数据库CNBIogsDemo,然后选择发布类型Transational
publication(事物发布),如下图:
点击Next,出现错误:
原来所有要复制的表都需要有主键,刚才建CNBIogsTest表时,没有建主键。
建一下主键,
并重新启动向导就可以了。
接着选择要复制的对象:
f'NewPubfic^ionWizard
Articles
$el6cttdbloiandotherobjectstopublishb&arttl^.Selectcolumnstofiltertables.
help
Next>
Einish»\\
Cancel
点Next,Next,进入SnapshotAgent窗口,选择Createasnapshotimmediatelyandkeepthesnapshotavailabletoinitializesubscriptions,见下图:
Next,进入AgentSecurity
选择SecuritySettings,进行相应的帐户设置:
SnapshotAgentSecurity
SpeiifvtheJonnairiormachineatcocinfurder^hichtheSnapshotAgentprocess蝌ill口此
RununderthefollciAflngWindowsjccouril:
Password:
ConfirmPassword'
&RunuridertheSQLServerAgentserviceaccount(Thi?
isnoJarecomrriendedsecuritybe址practice.]
ConnecttothePubhshet
厂创iftipsrcor-itinglheprocestaccount
阳UsingthsfollowingSOLServerlogin:
Login:
Fa理口毗
ConfirimFasswarid:
OK1C^ncd旦裾
一个是设置运行SnapshotAgent的Windows帐户,我们这里选择与SQLServerAgent同
样的帐户。
一个是设置连接发布服务器的SQL帐户,我们这里就用主数据库服务器的sa帐户。
继续:
OK,Next,Next,为这个发布起个名字:
点击Finish,就开始正式创建发布,创建成功就会出现如下窗口:
■NewPublicationWizard
CreatingPublicotian
ClickStoptointerruptbeoper^tionL
2Total
3Cuccess:
0Enoi
0Waming
Details:
Action
IStatus
|Message
空CreatingPublic时加'CNEIogs:
DerriC_Pn...
Su亡e«s
£Addins胡心心1cf1
Jewess
田EtartinglheSnapshoJAgent
Sucres
览叩|Report▼
这时查看快照文件夹,就会看到unc文件夹,快照文件就在这个文件夹中。
这里要考虑这样一个问题,如何让订阅服务器通过网络访问这个快照文件夹。
我们在这个问题上折腾了一些时间,本来想通过共享文件夹的方式,但又不想打开匿名共享,折腾了半天,没搞定订阅服务器访问共享文件夹用户验证的问题。
于是采用了FTP的方式,所以,下面介绍一下如何让订阅服务器通过FTP访问快照文件。
4.设置快照的FTP访问
首先在主数据库服务器上开通FTP服务,建立一个指向快照文件夹的FTP站点,设置好可
以远程连接的FTP帐户。
然后在这台发布服务器设置一下FTP客户端配置。
配置方法如下:
在Replication》LocalPublications中选择刚才创建的发布
[CNBIogsDemo]:
CNBIogsDemo_Publication,选择属性》FTPSnapshot,如下图:
驷GeneraJ
JArhcies
5FillerRows
i盘Srtapshcit
Accesssi^apshotfilesIhiaughFTP-
肓Allow5ubscriberstodawnloadsnapshotfilesusingFTP[File?
ransJerProtocoQ
也1£ub泊讪ionOptions*TjPublicaAionAccessList
2Ag&rrtSecurity
FTPseirername
i9SLl6&ik1S
Snumber
21
PathfromtheFTProotfolder
^ftp
Logjn
P日滋囱ord
Subscriber冋illusethefollowinginformationtoaccessthesnapshotfiles:
NotetheSnapshotAgentcanautomaticallputfilesinthisfolderifyouspecifyitasnapshotfiblocationontheSnapshotpageofthifdialogboM.
Connection
选中AllowSubscriberstodownloadsnapshotfilesusingFTP,并设置一下FTP客户端连
接参数,订阅服务器就是通过这里的设置连接FTP服务器的(注:
PathfromtheFTProot
folder的设置要和上图一样,设置为:
/ftp)。
点击OK,这时会在快照文件夹中创建一个ftp文件夹,并在该文件夹中生成快照文件。
这样,发布服务器就配置好了,下面配置订阅服务器。
三、备份数据库服务器(订阅服务器)的配置:
进入订阅服务器CNBIogsDB2,创建与发布服务器同名的数据库CNBIogsDemo,使用完全
恢复模式。
在Replication》LocalSubscriptions中选择NewSubscriptions,进入向导。
Next,进入选择发布服务器的窗口,选择FindSQLServerPublisher,出现服务器连接窗口:
这里要注意的是ServerName中一定要填写发布服务器的计算机名,如果计算机名连接不上,要在hosts文件中加一个IP地址解析。
成功连接发布服务器之后,就可以看到刚才在主数据库服务器上创建的发布:
智NewSubscriptionWizard
Publication
ChoatethepublicdtiorifaifvhichyouwanthoerectsoneornnoresubscxipNons
PubHsher:
|YcServerOOB
Databasesandpidjlicatons!
iCNQIagsDemo
ii园
S-jjITPioBlog
Help
Eirrwh〉》]
Cancel
Next,进入分发代理工作位置”的选择窗口:
NewSubscriptionWizard
□TH
DistributionAgentLocation
Choosywher?
tpiuntheDi^triLutisn^gQnt(s],
ForfriesubscriptionsIcr&^teinthiswizard
「Run制agentsa\lh&Dstributor,VCSERWEROOG(puslisubscriphons]
Thboptionmakes:
iteasierteidmiri^teithesynchronizationofsubscriptionscentrally.
f*Runeachagen^嬴it$Subsetiber[p训汕bscri^tions]
ThisopHtinthep-oressinrjnveiheaddthePistrilTufaraidIH5fccli
5ubscuberadministerthesynchronizdtioriofit*s:
ubscripHon.
HelpI
RunthewizardmoretKsricriesifyousemeag&ntston;rmtth曰DrshibutarandsometorunatSubscritiers.
Caned
我们这里选择pullsubscriptons,把数据给拉过来,这样主数据库服务器的负担会轻些。
(实际测试选择次选项不成功,选择第一选项)
Next,选择订阅服务器上的数据库,之前我们已经建好同名的数据库,所以系统自己会找到。
Next,进入分发代理安全设置窗口:
Dist和buttonAgentSccurity
Specifthedomainormachineaccountunderwh:
chtheDistTibUic-nAgen!
rprecessMilmbwhensvnchrani:
ir*g(hi5subscription.
Rununder(befollowiiig'Y/inclo^s^Gcount:
rroce^sacc&urrt:
P^ESWtlid.
ConfifinPais^ojcl.
乍RununderIhtSQLServerAgentsei^iceaccounl(Thisi$ncit白reccKnmertdtdsecuritybe:
.>tpractice.]
Connect怕HieDistribiior
「Rylimpersonatingtheprocessaccount
UsngthefollovJ粤SQLServeilogin:
XXXMXXXKXXKXX
Login
Password;
Confirmpassword
Th巴loginusedtoconriecttoHiePublisherrriu->tbeamemberdthePublicationAccessList.
ConnecttotheSubscriber
ByrmpersoDatingtheprocessaccount
CUsi'igaSer.-cilojii
Theconnectiontothecarveronwhich(tieagentrunsmuslirnpecscnatAthepieces^,account.Theproces?
^ccoynlmviwlbea日宇eowneioluibsciiptiand占Imb日
CKCanedHelp
点击红框内的按钮,进入设置窗口:
设置如上图,ConnecttotheDistributor处设置的是发布服务器的sa帐户。
OK,Next,Next,Next:
Next,Finish.Success:
备份数据库的订阅就建好了!
现在来瞧一瞧订阅服务器CNBIogsDB2上的用于复制的数据库CNBIogsDemo
3匚N6l(xisDemD
l-t'_jDatabaseDiagrams
Tables
ear
H_jSystemTables
l+J_jdbo.CMHogsTest
1+'Views
l+ji_jSynonyms
:
+_jPrograrrirY>abilitv
i土_|ServiceBroker
Ft_j
|+j^eciMity
看!
我们在发布服务器上建立的表CNBIogsTest复制过来了。
现在我们去发布服务器CNBIogsDBI上添加一条记录:
¥CSEltVER006r...ba.CNB!
og5Test
10
Msg
1
HelloV/orid!
卜*
再去订阅服务器CNBIogsDB2瞧一瞧:
5QLQuory5*sql.^dmindudu(55))
曰
/rfitjetfrScriptforSelect-TopMRowscommandfromSSMStirrir^r/SELECTTOPlogo[ID]
f[T
FROM[CUSlojsDemo].[dbo],[CMBlogsTest]
databaseCTJBlo^sDemDl
斗|
]Re皿孚|由閘带泪時|
IDTMsg
1
I1|HelloWorld!
|