LSX-blog

Feed Rss

【转载】SQL Server 2005 镜像构建手册

06.23.2011, MSSQL, by .

一、 镜像简介

1、 简介

数据库镜像是将数据库事务处理从一个SQL Server数据库移动到不同SQL Server环境中的另一个SQL Server数据库中。镜像不能直接访问;它只用在错误恢复的情况下才可以被访问。

要进行数据库镜像所需的最小需求包括了两个不同的SQL Server运行环境。主服务器被称为“主机”,第二个服务器被称作“备机”。主机数据库就是你实际用着的数据库,镜像数据库就是你的数据库的备用拷贝。当事务写入你的基本服务器的时候,他们也同样被传送到并写入你的镜像数据库中。

除了基本和镜像之外,你还可以引入另一个可选的组件,名为“见证”。见证服务器是第三个SQL Server 2005运行实例,它是在判断什么时候进行错误恢复的时候,用于基本和镜像之间内部交流。只有当你想实现自动错误恢复的时候用到这个选项。它实现了2比1投票的能力,当我的一个组件不可达,并因此需要进行错误恢复的时候。见证服务器只有在你想实现自动错误恢复的时候才需要用到。

2、 优点

下表是SQL Server可用性官方解决方案的一个对照表,现时我中心使用的恢复模式是“冷备份”中的“备份/恢复”,通常来说“热备份”比“冷备份”的可用性更高,恢复更快,更适合我中心现时的实际情况。如果不从成本考虑的话,“热备份”中的“故障转移群集”的可用性是最高的,但是故障转移群集需要借助磁盘阵列而且建设本身复杂性较高。数据库镜像的建立并没有太多的硬件要求,最起码没有像“故障转移群集”需要共享存储这么高的要求。

2、 缺点

(1)由于SQL Server是一个实例多个数据库的产品,数据库镜像技术是基于数据库级别的,因此每次主数据库新增数据库都必须为备机增加数据库并且为新增的数据库建立镜像关系。

(2)数据库的登录名和用户是存储在master数据库,master数据库是不能做镜像的,所以每次操作数据库的登录名和用户也是需要多维护一份,

(3)数据库作业不能得到相应的维护。

(4)微软号称镜像可以让客户端对故障透明,但是实际测试中发现只有满足特定的条件才能实现透明化,而且透明化得客户端支持才可行(.net Framework 2.0以上,Microsoft jdbc驱动 1.1以上)。

(5)跨数据库事务和分布式事务均不支持数据库镜像。

纵观其他几种方式,仅有“热备份”的“故障转移群集”没有这些问题。

一、配置主备机 1、 物理连接

将主备数据库按照如图所示连接:

2、 检查SQL Server 2005数据库

只有SQL Server 2005 标准版、企业版和开发版才可以建立数据镜像。其他版本即Express只能作为见证服务器。如果实在不清楚什么版本,执行如下语句查看:

1select @@version;

若要对此数据库进行数据库镜像,必须将它更改为使用完整恢复模式。若要用 Transact-SQL 实现此目的,请使用 ALTER DATABASE 语句:

1 USE master;
2 ALTER DATABASE <DatabaeName>
3 SET RECOVERY FULL;
4

二、主备实例互通

实现互通可以使用域或证书来实现,考虑实现的简单,以下选取证书的方式实现。注意:实现“主备数据库实例互通”的操作只需要做一次,例如为了将两个SQL Server 2005的实例中的5个数据库建成镜像关系,则只需要做一次以下操作就可以了;或者这样理解:每一对主备实例(不是数据库)做一次互通。

1、创建证书(主备可并行执行)

–主机执行:

1USE master;
2CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘killkill’;
3CREATE CERTIFICATE HOST_A_cert WITH SUBJECT = ‘HOST_A certificate’ ,
4START_DATE = ’01/01/2008′;
5

–备机执行:

1USE master;
2CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘killkill’;
3CREATE CERTIFICATE HOST_B_cert WITH SUBJECT = ‘HOST_B certificate’,
4START_DATE = ’01/01/2008′;
5

2、创建连接的端点(主备可并行执行)

–主机执行:

1CREATE ENDPOINT Endpoint_Mirroring
2STATE = STARTED
3AS
4TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL )
5FOR
6DATABASE_MIRRORING
7( AUTHENTICATION = CERTIFICATE HOST_A_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );
8

–备机执行:

1REATE ENDPOINT Endpoint_Mirroring
2STATE = STARTED
3AS
4TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL )
5FOR
6DATABASE_MIRRORING
7( AUTHENTICATION = CERTIFICATE HOST_B_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );
8

3、备份证书以备建立互联(主备可并行执行)

–主机执行:

1BACKUP CERTIFICATE HOST_A_cert TO FILE = ‘D:\SQLBackup\HOST_A_cert.cer’;

–备机执行:

1BACKUP CERTIFICATE HOST_B_cert TO FILE = ‘D:\SQLBackup\HOST_B_cert.cer’;

4、互换证书

将备份到D:\SQLBackup\的证书进行互换,即HOST_A_cert.cer复制到备机的D:\SQLBackup\。HOST_B_cert.cer复制到主机的D:\SQLBackup\

5、添加登陆名、用户(主备可并行执行)

以下操作只能通过命令行运行,通过图形界面无法完成。(截至文档编写结束,SQL Server2005的不定号为SP2)

–主机执行:

1CREATE LOGIN HOST_B_login WITH PASSWORD = ‘killkill’;
2CREATE USER HOST_B_user FOR LOGIN HOST_B_login;
3CREATE CERTIFICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = ‘D:\SQLBackup\HOST_B_cert.cer’;
4GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];
5

–备机执行:

1CREATE LOGIN HOST_A_login WITH PASSWORD = ‘killkill’;
2CREATE USER HOST_A_user FOR LOGIN HOST_A_login;
3CREATE CERTIFICATE HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE = ‘D:\SQLBackup\HOST_A_cert.cer’;
4GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login];
5

 

三、建立镜像关系

以下步骤是针对每个数据库进行的,例如:现有主机中有5个数据库以下过程就要执行5次。

1、 手工同步登录名和密码

在第一章中提到数据库镜像的缺点之一是无法维护登录名,所以需要我们手工维护登录。

通常来说数据库都将会有若干个用户作为访问数据库的用户,并且数据库会有相应的登录名,但是在备机中缺少与之相对应的登录名,例如某业务系统使用’myuser’作为登录名访问数据库,但是在备机中没有’myuser’这个登录名,因此一旦主备切换,业务系统就无法登录数据库了,这种情况称为”孤立用户”

在主数据库中执行如下语句:

1USE master;
2select sid,name from syslogins;
3

查找出相应的用户名和sid,例如:上述的’myuser’

在备数据库中执行如下语句:

1USE master;
2exec sp_addlogin
3@loginame = ‘<LoginName>’,
4@passwd = ‘<Password>’,
5@sid = <sid> ;
6

这里的’LoginName’即主数据库中的登录名,sid即是上述通过SQL语句查找出的sid。

例如,查询得到的sid和name如下所示。

1sid name
2
3———————————- —————–
4
50x074477739DCA0E499C29394FFFC4ADE4 cz_account
6
7

则建立登录名的SQL语句:

1USE master;
2exec sp_addlogin
3@loginame = ‘cz_account’,
4@passwd = ‘password’,
5@sid = 0x074477739DCA0E499C29394FFFC4ADE4;
6

到此为止可以认为备机数据库的环境已经与主机同步了,还差数据库内的数据未同步。

2、 准备备机数据库

承接上文,该节是描述如何同步主备数据库内的数据。

可以尝试从刚刚使用的全备文件进行还原,在还原数据的时候需要使用选上“with non recover”。如图所示:

 

如果执行成功数据库将会变成这个样子:

3、 建立镜像

由于是实验,没有为服务器配置双网卡,IP地址与图有点不一样,但是原理一样。

–主机执行:

1ALTER DATABASE shishan SET PARTNER = ‘TCP://10.168.6.45:5022’;

–如果主体执行不成功,尝试在备机中执行如下语句:

1ALTER DATABASE shishan SET PARTNER = ‘TCP://10.168.6.49:5022’;

 

如果执行成功,则主备数据库将会呈现如上图所示的图标。

如果建立失败,提示类似数据库事务日志未同步,则说主备数据库的数据(日志)未同步,为保证主备数据库内的数据一致,应在主数据库中实施一次“事务日志”备份,并还原到备数据库上。备份“事务日志”如图所示:

 

还原事务日志时需在选项中选择“restore with norecovery”,如图所示:

 

 

成功还原以后再执行建立镜像的SQL语句。

四、测试操作

1、主备互换

–主机执行:

1USE master;
2ALTER DATABASE <DatabaseName> SET PARTNER FAILOVER;
3

2、主服务器Down掉,备机紧急启动并且开始服务

–备机执行:

1USE master;
2ALTER DATABASE <DatabaseName> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;
3

3、原来的主服务器恢复,可以继续工作,需要重新设定镜像

1–备机执行:
2USE master;
3ALTER DATABASE <DatabaseName> SET PARTNER RESUME; –恢复镜像
4ALTER DATABASE <DatabaseName> SET PARTNER FAILOVER; –切换主备
5

4、原来的主服务器恢复,可以继续工作

–默认情况下,事务安全级别的设置为 FULL,即同步运行模式,而且SQL Server 2005 标准版只支持同步模式。

–关闭事务安全可将会话切换到异步运行模式,该模式可使性能达到最佳。

1USE master;
2ALTER DATABASE <DatabaseName> SET PARTNER SAFETY FULL; –事务安全,同步模式
3ALTER DATABASE <DatabaseName> SET PARTNER SAFETY OFF; –事务不安全,异步模式
4

 

【转载】SQL Server 2005 镜像构建手册 有 6,089 条回应

  1. We’re a group of volunteers and starting a new scheme in our community.

    Your site offered us with valuable info to work on. You’ve done an impressive job and our whole community will be
    grateful to you.

    回复
  2. I for all time emailed this website post page to all my associates, since if
    like to read it then my friends will too.

    回复
  3. What’s up mates, its enormous paragraph on the topic of educationand fully explained, keep
    it up all the time.

    回复
  4. Hello, I would like to subscribe for this website to take newest updates,
    thus where can i do it please help out.

    回复
  5. For latest news you have to pay a visit world-wide-web and on internet I found this
    web site as a best site for most recent updates.

    回复
  6. I like the helpful info you provide in your articles.
    I’ll bookmark your weblog and check again here regularly.
    I’m quite certain I’ll learn plenty of new stuff right here!
    Best of luck for the next!

    回复
  7. Excellent goods from you, man. I have understand
    your stuff previous to and you are just extremely great. I actually
    like what you’ve acquired here, really like what you’re
    stating and the way in which you say it.
    You make it enjoyable and you still care for to
    keep it smart. I can’t wait to read far more from you.
    This is actually a great web site.

    回复
  8. It’s remarkable to visit this web site and reading
    the views of all friends about this article, while I am also keen of getting knowledge.

    回复
  9. With havin so much written content do you ever run into any problems of plagorism or copyright violation?
    My site has a lot of completely unique content I’ve either created myself or outsourced but it appears a lot of it is popping it up all over the web
    without my agreement. Do you know any methods to help protect against content from being ripped off?
    I’d truly appreciate it.

    回复
  10. At this time it sounds like Drupal is the best blogging platform available right now.
    (from what I’ve read) Is that what you’re using on your blog?

    回复
  11. Spot on with this write-up, I honestly feel this site needs a lot more attention. I’ll probably be returning to read more, thanks for the information!

    回复
  12. Hey there! I know this is kinda off topic but I’d figured I’d ask.
    Would you be interested in exchanging links or maybe guest authoring a blog article or vice-versa?
    My website discusses a lot of the same topics as yours
    and I think we could greatly benefit from each other.
    If you might be interested feel free to shoot me an email.

    I look forward to hearing from you! Excellent blog by the
    way!

    回复
  13. This is my first time visit at here and i am actually happy to
    read everthing at alone place.

    回复
  14. Oh my goodness! Amazing article dude! Thank you, However I am having troubles with your RSS. I don’t understand the reason why I am unable to join it. Is there anyone else getting similar RSS issues? Anyone that knows the answer will you kindly respond? Thanx!!

    回复
  15. Have you ever thought about writing an ebook or guest authoring
    on other sites? I have a blog based upon on the same ideas you discuss and would really like to have you share some stories/information. I know my viewers would enjoy your work.
    If you are even remotely interested, feel free to shoot
    me an email.

    回复
  16. I conceive this website has got very excellent composed content posts.

    回复
  17. hello there and thank you for your info –
    I’ve certainly picked up something new from right here. I did however expertise some technical points using this web site,
    as I experienced to reload the site a lot of times previous to
    I could get it to load correctly. I had been wondering if your hosting is OK?
    Not that I’m complaining, but sluggish loading instances times will very frequently affect your placement in google and could damage your high-quality score if ads and marketing with Adwords.

    Anyway I am adding this RSS to my email and can look out for
    much more of your respective intriguing content. Make sure you update this again soon.

    回复
  18. This article presents clear idea in support of the new people of blogging, that in fact how to do blogging and site-building.

    回复
  19. Do you mind if I quote a couple of your posts as long as I provide
    credit and sources back to your webpage? My blog site is in the very same area of interest as yours and my users would genuinely benefit from some of the
    information you present here. Please let me know if this
    ok with you. Many thanks!

    回复
  20. Wonderful blog! I found it while searching on Yahoo News.
    Do you have any suggestions on how to get listed in Yahoo
    News? I’ve been trying for a while but I never seem to get there!

    Appreciate it

    回复
  21. Very energetic post, I enjoyed that bit. Will
    there be a part 2?

    回复
  22. I’ve been browsing online more than 4 hours today, yet I never found any interesting article like yours.
    It’s pretty worth enough for me. Personally, if all site owners and bloggers made good content
    as you did, the net will be a lot more useful than ever before.

    回复
  23. I’ve been browsing online more than three hours today,
    yet I never found any interesting article like yours. It is pretty worth enough for me.
    In my opinion, if all web owners and bloggers made good content as you did, the net will be a lot more useful than ever before.

    回复
  24. Pretty nice post. I just stumbled upon your blog and wished to say that
    I have really loved surfing around your weblog posts.
    In any case I will be subscribing to your feed and I hope you write once more soon!

    回复
  25. Have you ever thought about writing an ebook or guest authoring on other sites?
    I have a blog centered on the same subjects you
    discuss and would love to have you share some stories/information. I
    know my audience would appreciate your work. If you are even remotely interested, feel
    free to send me an email.

    回复
  26. What i do not realize is in fact how you are now not actually a lot more
    well-preferred than you may be right now. You are very intelligent.
    You already know thus considerably relating to this matter,
    produced me in my view imagine it from numerous various angles.
    Its like men and women are not fascinated except it’s one thing
    to do with Lady gaga! Your own stuffs great. All the time deal with it up!

    回复
  27. Right here is the perfect webpage for anyone who wishes
    to understand this topic. You realize so much its almost hard to argue
    with you (not that I personally will need to…HaHa).
    You certainly put a new spin on a topic that has been discussed for ages.
    Wonderful stuff, just wonderful!

    回复
  28. Hey very nice blog!

    回复
  29. Hi! Do you know if they make any plugins to assist with Search Engine Optimization? I’m trying to get my blog to rank for some targeted keywords but I’m not seeing very good success.
    If you know of any please share. Cheers!

    回复
  30. Hey I know this is off topic but I was wondering if you
    knew of any widgets I could add to my blog that
    automatically tweet my newest twitter updates. I’ve been looking for a plug-in like this for
    quite some time and was hoping maybe you would have some experience with something like this.
    Please let me know if you run into anything. I truly enjoy reading your blog and I look forward
    to your new updates.

    回复
  31. I think this is among the most significant information for me.
    And i am glad reading your article. But wanna remark on few general things, The web site style is wonderful,
    the articles is really excellent : D. Good job,
    cheers

    回复
  32. Simply desire to say your article is as astonishing.
    The clarity in your post is just great and i can assume you are an expert on this subject.
    Fine with your permission allow me to grab your RSS feed to
    keep updated with forthcoming post. Thanks a million and please continue the enjoyable work.

    回复
  33. Hey just wanted to give you a brief heads up and let you know a
    few of the images aren’t loading properly. I’m not sure why but I think its a linking issue.
    I’ve tried it in two different web browsers and
    both show the same results.

    回复
  34. Fabulous, what a webpage it is! This webpage provides helpful facts to us, keep it
    up.

    回复
  35. Amazing! This blog looks just like my old one!

    It’s on a entirely different topic but it has pretty much the same layout and design. Superb choice of colors!

    回复
  36. I’m amazed, I have to admit. Seldom do I come across
    a blog that’s equally educative and amusing, and let me tell you, you’ve hit the nail on the head.
    The issue is something too few folks are speaking intelligently about.
    I am very happy that I came across this in my search for something
    concerning this.

    回复
  37. I think the admin of this web site is actually working hard in favor of his website, for the
    reason that here every stuff is quality based data.

    回复
  38. My brother recommended I might like this blog.
    He was entirely right. This post actually made my day. You cann’t imagine just how much time
    I had spent for this information! Thanks!

    回复
  39. When I originally commented I clicked the
    “Notify me when new comments are added” checkbox and now each time a
    comment is added I get four e-mails with the same comment.
    Is there any way you can remove people from that service?
    Cheers!

    回复
  40. Good day! Do you know if they make any plugins to assist with SEO?
    I’m trying to get my blog to rank for some targeted keywords but I’m not seeing very good gains.
    If you know of any please share. Many thanks!

    回复
  41. Hi there i am kavin, its my first occasion to commenting anyplace, when i read
    this paragraph i thought i could also create comment due to this brilliant
    piece of writing.

    回复
  42. Hello to every body, it’s my first pay a quick visit of this blog; this weblog consists of amazing
    and in fact good stuff in support of visitors.

    回复
  43. Hi there, i read your blog occasionally and i own a
    similar one and i was just curious if you get
    a lot of spam feedback? If so how do you protect against it, any plugin or anything you can advise?
    I get so much lately it’s driving me crazy so any support
    is very much appreciated.

    回复
  44. Hi there, I found your website via Google whilst searching
    for a similar subject, your web site got here up, it appears good.
    I’ve bookmarked it in my google bookmarks.
    Hello there, simply become alert to your blog thru Google,
    and located that it is truly informative. I am gonna be
    careful for brussels. I’ll be grateful when you continue this in future.
    Numerous people can be benefited from your writing.

    Cheers!

    回复
  45. Way cool! Some very valid points! I appreciate you penning this article and also the rest of the
    website is really good.

    回复
  46. Unquestionably believe that which you said. Your favorite reason seemed to be on the internet the easiest thing to be aware of.
    I say to you, I definitely get irked while people think about worries that they just do not know about.
    You managed to hit the nail upon the top and defined out the whole thing without having side-effects , people can take a signal.
    Will probably be back to get more. Thanks

    回复
  47. Admiring the hard work you put into your blog and detailed information you
    offer. It’s great to come across a blog every once
    in a while that isn’t the same unwanted rehashed information. Wonderful read!
    I’ve bookmarked your site and I’m including your RSS feeds to
    my Google account.

    回复
  48. Thanks for sharing your thoughts on SQL2005. Regards

    回复
  49. you’re really a just right webmaster. The
    site loading speed is amazing. It seems that you are doing any distinctive trick.
    Furthermore, The contents are masterpiece. you have done a great process on this matter!

    回复
  50. I really like reading through an article that can make men and women think.
    Also, thanks for allowing me to comment!

    回复

发表评论

电子邮件地址不会被公开。 必填项已用*标注