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 镜像构建手册 有 2,670 条回应

  1. I am sure this paragraph has touched all the internet
    people, its really really good article on building up new blog.

    回复
  2. Having read this I believed it was extremely enlightening.

    I appreciate you spending some time and energy to put this short article together.
    I once again find myself spending way too much
    time both reading and posting comments. But so what, it was still worthwhile!

    回复
  3. Nice post. I learn something totally new and challenging on blogs
    I stumbleupon everyday. It will always be interesting to read through
    content from other authors and practice something from their web
    sites.

    回复
  4. Hello I am so delighted I found your web site,
    I really found you by error, while I was looking on Digg for something else, Anyways I
    am here now and would just like to say thanks for a remarkable post and a all round thrilling blog (I also love the theme/design),
    I don’t have time to browse it all at the moment but I have saved it
    and also included your RSS feeds, so when I have time I will be back to read more, Please do keep up the fantastic work.

    回复
  5. Today, I went to the beach front with my kids.
    I found a sea shell and gave it to my 4 year old daughter and said “You can hear the ocean if you put this to your ear.” She put the shell to her ear and screamed.
    There was a hermit crab inside and it pinched her ear. She never wants to go back!
    LoL I know this is completely off topic but I had to tell someone!

    回复
  6. Thank you for the good writeup. It in fact was a amusement account
    it. Look advanced to far added agreeable from you!

    By the way, how could we communicate?

    回复
  7. I love it when folks come together and share opinions.
    Great site, continue the good work!

    回复
  8. each time i used to read smaller articles that
    as well clear their motive, and that is also happening with this paragraph which
    I am reading here.

    回复
  9. I am regular reader, how are you everybody? This piece of
    writing posted at this web site is genuinely fastidious.

    回复
  10. 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.

    回复
  11. I’m amazed, I have to admit. Seldom do I
    encounter a blog that’s both equally educative and interesting,
    and without a doubt, you’ve hit the nail
    on the head. The issue is something too few people are speaking intelligently about.

    Now i’m very happy I stumbled across this in my search for something
    concerning this.

    回复
  12. It is appropriate time to make some plans for the future and it
    is time to be happy. I have read this post and if I could
    I wish to suggest you some interesting things or advice.
    Perhaps you could write next articles referring to this article.
    I desire to read even more things about it!

    回复
  13. I am actually thankful to the owner of this website
    who has shared this great piece of writing at here.

    回复
  14. Great blog here! Also your web site lots up fast! What web
    host are you the usage of? Can I am getting your affiliate hyperlink on your host?
    I desire my web site loaded up as fast as yours lol

    回复
  15. Howdy would you mind letting me know which webhost you’re utilizing?
    I’ve loaded your blog in 3 different web browsers and I must
    say this blog loads a lot faster then most. Can you recommend a good hosting provider at a
    fair price? Cheers, I appreciate it!

    回复
  16. Wonderful work! That is the kind of info that are meant to
    be shared around the net. Shame on the seek engines for now not positioning
    this publish upper! Come on over and discuss with my site .
    Thank you =)

    回复
  17. I was recommended this blog by my cousin. I’m not sure
    whether this post is written by him as no one else know such detailed about my difficulty.
    You’re amazing! Thanks!

    回复
  18. Having read this I believed it was rather enlightening.
    I appreciate you spending some time and effort to put this informative
    article together. I once again find myself personally
    spending a lot of time both reading and leaving comments.
    But so what, it was still worthwhile!

    回复
  19. This is really interesting, You’re a very skilled
    blogger. I have joined your rss feed and look forward to seeking more of your magnificent post.
    Also, I’ve shared your web site in my social networks!

    回复
  20. Great delivery. Outstanding arguments. Keep up the great
    work.

    回复
  21. Oh my goodness! Incredible article dude! Thanks, However I am having problems with your RSS.
    I don’t know why I am unable to subscribe to it.
    Is there anybody having identical RSS problems? Anyone that knows the solution can you kindly respond?

    Thanks!!

    回复
  22. Thanks for sharing your info. I truly appreciate your efforts
    and I am waiting for your further write ups thank you once
    again.

    回复
  23. I am truly thankful to the holder of this web site who has
    shared this enormous piece of writing at here.

    回复
  24. My spouse and I stumbled over here by a different page and thought I might check things out.
    I like what I see so now i’m following you. Look forward to looking into
    your web page for a second time.

    回复
  25. Marvelous, what a weblog it is! This web site presents valuable facts to us, keep it
    up.

    回复
  26. I’m not sure where you’re getting your information, but great topic.
    I needs to spend some time learning more or understanding more.

    Thanks for wonderful information I was looking for this info for my mission.

    回复
  27. Hi there to all, the contents present at this web page are actually remarkable for people experience, well, keep up the nice work
    fellows.

    回复
  28. I could not resist commenting. Exceptionally well written!

    回复
  29. Hey there, You have done an excellent job. I’ll definitely digg it and personally suggest
    to my friends. I’m confident they’ll be benefited from this web site.

    回复
  30. What’s up to every single one, it’s genuinely a nice for me to go to see this
    web site, it includes valuable Information.

    回复
  31. After going over a few of the blog posts on your site, I seriously
    like your way of writing a blog. I bookmarked it to my bookmark webpage list and will be checking back soon.
    Please check out my website too and tell me your opinion.

    回复
  32. Howdy! This article could not be written any better!
    Reading through this article reminds me of my previous roommate!

    He always kept preaching about this. I will forward this
    post to him. Fairly certain he’ll have a great read. I appreciate you for sharing!

    回复
  33. I am really loving the theme/design of your blog.
    Do you ever run into any browser compatibility issues?
    A handful of my blog audience have complained about my site not operating correctly in Explorer but looks
    great in Opera. Do you have any suggestions to help fix this issue?

    回复
  34. I am in fact delighted to read this website posts which consists
    of tons of useful information, thanks for providing these data.

    回复
  35. I’m very pleased to discover this page. I need to to thank you for your time for this wonderful read!!
    I definitely really liked every little bit of it and I have
    you saved to fav to look at new information in your web site.

    回复
  36. I enjoy, lead to I found exactly what I used to be having a look for.
    You’ve ended my 4 day lengthy hunt! God Bless you man. Have a nice day.
    Bye

    回复
  37. Asking questions are actually pleasant thing if you are not understanding something totally, except this piece of writing gives pleasant
    understanding even.

    回复
  38. Hey there! I’m at work browsing your blog from my new iphone!
    Just wanted to say I love reading your blog and look forward to all your posts!
    Carry on the superb work!

    回复
  39. Have you ever thought about adding a little bit more than just
    your articles? I mean, what you say is important and all.
    However just imagine if you added some great images or video clips to
    give your posts more, “pop”! Your content is excellent but with images and video clips, this blog
    could certainly be one of the best in its niche. Fantastic blog!

    回复
  40. Hi to every , for the reason that I am truly keen of reading this blog’s post to be updated regularly.

    It includes fastidious stuff.

    回复
  41. Heya i’m for the primary time here. I found this
    board and I in finding It truly useful & it helped me out much.
    I hope to present one thing again and aid others such as you helped
    me.

    回复
  42. This blog was… how do I say it? Relevant!! Finally
    I’ve found something which helped me. Many thanks!

    回复
  43. Wow, that’s what I was exploring for, what a material!
    present here at this webpage, thanks admin of this site.

    回复
  44. There is certainly a great deal to know about this issue.
    I love all the points you’ve made.

    回复
  45. Great article! That is the type of info that
    are supposed to be shared across the web. Shame on Google for
    not positioning this put up higher! Come on over
    and talk over with my website . Thank you =)

    回复
  46. Someone necessarily lend a hand to make significantly posts I’d state.
    That is the very first time I frequented your website page and to
    this point? I surprised with the research you made to make this actual submit
    incredible. Wonderful job!

    回复
  47. I know this if off topic but I’m looking into starting my own weblog and was
    curious what all is needed to get setup? I’m assuming having a blog like yours would cost a pretty penny?
    I’m not very internet smart so I’m not 100% positive.
    Any suggestions or advice would be greatly appreciated. Kudos

    回复
  48. My partner and I stumbled over here coming from a different page and thought I
    may as well check things out. I like what I see so now i’m following you.
    Look forward to going over your web page yet again.

    回复
  49. This post is genuinely a good one it helps new net visitors, who are wishing for blogging.

    回复
  50. I think everything said made a ton of sense. However, think
    on this, suppose you typed a catchier post title?
    I am not saying your content isn’t good, however what if you added a title that makes people
    desire more? I mean 【转载】SQL Server 2005 镜像构建手册 – LSX-blog is kinda vanilla.
    You might look at Yahoo’s home page and watch how they create news headlines to get viewers to click.
    You might add a related video or a picture or
    two to grab readers interested about everything’ve written. Just my opinion, it might make
    your website a little livelier.

    回复

coconut oil for进行回复 取消回复

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