笔记45 SQL2005数据库镜像的步骤

笔记45 SQL2005数据库镜像的步骤

  1 --SQL2005数据库镜像的步骤
  2 
  3 --1、实验中有两台服务器,不是集群,但是每台有两张网卡,其中一张插心跳线(局域网)
  4 --,另外一张暴露在公网
  5 
  6 --2、检查SQL Server 2005数据库
  7 --只有SQL Server 2005 标准版、企业版和开发版才可以建立数据镜像。
  8 --其他版本即Express只能作为见证服务器
  9 --要保证打上SP2补丁 SELECT SERVERPROPERTY('productlevel')
 10 
 11 
 12 
 13 --3、主备实例互通
 14 --实现互通可以使用域或证书来实现,考虑实现的简单,以下选取证书的方式实现。注意:
 15 --实现“主备数据库实例互通”的操作只需要做一次,例如为了将两个SQL Server 2005的实例中
 16 --的5个数据库建成镜像关系,则只需要做一次以下操作就可以了;或者这样理解:每一对主备
 17 --实例(不是数据库)做一次互通。
 18 
 19 -------------------------------------------------------------------------------------------
 20 
 21 --1、设置镜像数据库为完整恢复模式
 22 alter DATABASE S_C_SC set recovery FULL
 23 
 24 
 25 --2、创建证书(主备可并行执行)
 26 --主机执行
 27 USE master;
 28 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'joe';
 29 CREATE CERTIFICATE HOST_A_cert WITH SUBJECT = 'HOST_A certificate' ,
 30 START_DATE = '2012-09-25';
 31 
 32 --备机执行
 33 USE master;
 34 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'joe';
 35 CREATE CERTIFICATE HOST_B_cert WITH SUBJECT = 'HOST_B certificate' ,
 36 START_DATE = '2012-09-25';
 37 
 38 --见证执行
 39 USE master;
 40 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'joe';
 41 CREATE CERTIFICATE HOST_C_cert WITH SUBJECT = 'HOST_C certificate' ,
 42 START_DATE = '2012-09-25';
 43 
 44 --3、创建连接的端点(主备可并行执行)
 45 
 46 --主机执行:
 47 USE master
 48 CREATE ENDPOINT Endpoint_Mirroring
 49 STATE = STARTED
 50 AS
 51 TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL )
 52 FOR
 53 DATABASE_MIRRORING
 54 ( AUTHENTICATION = CERTIFICATE HOST_A_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );
 55 
 56 --备机执行:
 57 USE master
 58 CREATE ENDPOINT Endpoint_Mirroring
 59 STATE = STARTED
 60 AS
 61 TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL )
 62 FOR
 63 DATABASE_MIRRORING
 64 ( AUTHENTICATION = CERTIFICATE HOST_B_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );
 65 
 66 --见证执行:
 67 USE master
 68 CREATE ENDPOINT Endpoint_Mirroring
 69 STATE = STARTED
 70 AS
 71 TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL )
 72 FOR
 73 DATABASE_MIRRORING
 74 ( AUTHENTICATION = CERTIFICATE HOST_C_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = WITNESS );
 75 
 76 
 77 
 78 --4、备份证书以备建立互联(主备可并行执行)
 79 
 80 --主机执行:
 81 USE master
 82 --BACKUP CERTIFICATE HOST_A_cert TO FILE = 'D:\SQLBackup\HOST_A_cert.cer';
 83 BACKUP CERTIFICATE HOST_A_cert TO FILE = 'D:\HOST_A_cert.cer';
 84 --备机执行:
 85 USE master
 86 BACKUP CERTIFICATE HOST_B_cert TO FILE = 'D:\SQLBackup\HOST_B_cert.cer';
 87 --见证执行:
 88 USE master
 89 BACKUP CERTIFICATE HOST_C_cert TO FILE = 'D:\SQLBackup\HOST_C_cert.cer';
 90 
 91 
 92 --5、互换证书
 93 --
 94 --将备份到D:\SQLBackup\的证书进行互换,即HOST_A_cert.cer复制(是复制不是剪切)
 95 --到备机的D:\SQLBackup\。HOST_B_cert.cer复制(是复制不是剪切)到主机的D:\SQLBackup\
 96 --见证的证书HOST_C_cert.cer复制到主机和备机,主机和备机复制到见证
 97 
 98 
 99 
100 
101 
102 --6、添加登陆名、用户(主备见证可并行执行)
103 
104 --以下操作只能通过命令行运行,通过图形界面无法完成。(截至文档编写结束,SQL Server2005的版本号为SP2)
105 
106 --主机执行:
107 USE master
108 CREATE LOGIN HOST_B_login WITH PASSWORD = 'test';
109 CREATE USER HOST_B_user FOR LOGIN HOST_B_login;
110 --CREATE CERTIFICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = 'D:\SQLBackup\HOST_B_cert.cer';
111 CREATE CERTIFICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = 'D:\HOST_B_cert.cer';
112 GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login]
113 USE master
114 CREATE LOGIN HOST_C_login WITH PASSWORD = 'test';
115 CREATE USER HOST_C_user FOR LOGIN HOST_C_login;
116 CREATE CERTIFICATE HOST_C_cert AUTHORIZATION HOST_C_user FROM FILE = 'D:\HOST_C_cert.cer';
117 GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_C_login];
118 GO
119 --HOST_B_cert是第一步创建的证书
120 
121 
122 
123 --备机执行:
124 USE master
125 CREATE LOGIN HOST_A_login WITH PASSWORD = 'test';
126 CREATE USER HOST_A_user FOR LOGIN HOST_A_login;
127 --CREATE CERTIFICATE HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE = 'D:\SQLBackup\HOST_A_cert.cer';
128 CREATE CERTIFICATE HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE = 'D:\HOST_A_cert.cer';
129 GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login];
130 USE master
131 CREATE LOGIN HOST_C_login WITH PASSWORD = 'test';
132 CREATE USER HOST_C_user FOR LOGIN HOST_C_login;
133 CREATE CERTIFICATE HOST_C_cert AUTHORIZATION HOST_C_user FROM FILE = 'D:\HOST_C_cert.cer';
134 GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_C_login];
135 GO
136 
137 --见证执行:
138 USE master
139 CREATE LOGIN HOST_A_login WITH PASSWORD = 'test';
140 CREATE USER HOST_A_user FOR LOGIN HOST_A_login;
141 --CREATE CERTIFICATE HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE = 'D:\SQLBackup\HOST_A_cert.cer';
142 CREATE CERTIFICATE HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE = 'D:\HOST_A_cert.cer';
143 GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login];
144 USE master
145 CREATE LOGIN HOST_B_login WITH PASSWORD = 'test';
146 CREATE USER HOST_B_user FOR LOGIN HOST_B_login;
147 --CREATE CERTIFICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = 'D:\SQLBackup\HOST_B_cert.cer';
148 CREATE CERTIFICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = 'D:\HOST_B_cert.cer';
149 GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];
150 
151 
152 --注意: 这里添加的登录名和用户是添加在master数据库里的不是要做镜像的那个数据库
153 --这里添加的登录名和用户是用来做连接的,下面建立镜像关系才是修复孤立用户才是
154 --修复要做镜像的那个数据库的孤立用户!!!!!!!!!!!!!!!
155 
156 
157 
158 -------------------------------------------------------------------------------------------------------
159 --建立镜像关系---------------------------------------------------------------------------------------
160 
161 --以下步骤是针对每个数据库进行的,例如:现有主机中有5个数据库以下过程就要执行5次。
162 
163 --7、 手工同步登录名和密码
164 
165 --在前面提到数据库镜像的缺点之一是无法维护登录名,所以需要我们手工维护登录解决孤立用户。
166 
167 
168 --在主数据库中执行如下语句:
169 USE master;
170 select sid,name,dbname from syslogins WHERE dbname='要做镜像的数据库名'
171 USE master;
172 select sid,name,dbname from syslogins WHERE dbname='pratice'
173 
174 --查找出要做镜像的那个数据库里面有哪些用户名和sid,例如:上述的’myuser’
175 
176 --在备库中执行如下语句:
177 
178 USE master;
179 exec sp_addlogin
180 @loginame = '<LoginName>',
181 @passwd = '<Password>',
182 @sid = <sid> ;
183 
184 
185 --8、准备备机数据库
186 --主库要备份两个bak文件
187 --第一个:完整备份
188 --第二个:事务日志备份  ,截断事务日志
189 
190 --(1)先在备机还原完整备份,“restore with norecovery”和 覆盖现有数据库
191 --做了第一步之后,gposdb-》任务-》还原-》事务日志 按钮才可用
192 --(2)再还原事务日志,“restore with norecovery” 和时间点:最近状态
193 
194 --如果执行成功备机数据库将会变成 "正在还原"
195 
196 
197 
198 --由于是实验,没有为服务器配置双网卡,IP地址与图有点不一样,但是原理一样。
199 
200 
201 --9、必须要在镜像数据库中先设置好伙伴后,才能在主体服务器执行
202 --再在主体服务器实例上,将 备机上的服务器实例设置为伙伴
203 -- \\.\pipe\sql\query
204 
205 --备机执行
206 USE master
207 ALTER DATABASE S_C_SC SET PARTNER = 'TCP://192.168.1.100:5022'
208 
209 --主机执行
210 USE master
211 ALTER DATABASE S_C_SC SET PARTNER = 'TCP://192.168.1.103:5022'
212 
213 
214 --10、在主机执行设置见证服务器
215 ALTER DATABASE S_C_SC SET WITNESS = 'TCP://192.168.1.101:5022';
216 GO
217 
218 
219 --此时主:gposdb(主体,已同步)  备: gposdb(镜像,已同步/正在还原) 
220 --TCP://192.168.1.100:主
221 --TCP://192.168.1.103:备
222 
223 
224 
225 --如果删除不了正在还原的备库,那么可以重启SQL服务,就可以删除了!!!!!!!!!!
226 
227 
228 --故障解决: ping ip   telnet 5022端口
229 
230 --博客园里面是主:dbmirror(主体,正在同步)  备: dbmirror(镜像,已同步/正在还原) 
231 
232 
233 -------------------------测试-----------------------------------------------------------
234 --默认情况下,事务安全级别的设置为 FULL,即同步运行模式,而且SQL Server 2005 标准版只支持同步模式。
235 --关闭事务安全可将会话切换到异步运行模式,该模式可使性能达到最佳。
236 USE master;
237 ALTER DATABASE S_C_SC SET PARTNER SAFETY FULL --(默认)事务安全,同步模式  镜像的更改和主体的更改都同步
238 ALTER DATABASE S_C_SC SET PARTNER SAFETY OFF --事务不安全,异步模式   只有主体的更改同步
239 
240 
241 --1、主备互换
242 
243 --主机停掉SQL服务
244 
245 
246 --2、主服务器Down掉,备机紧急启动并且开始服务
247 --备机执行:
248 USE master;
249 ALTER DATABASE S_C_SC SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;
250 ALTER DATABASE S_C_SC SET ONLINE
251 
252 
253 --3、开启主机的SQL服务,原来的主服务器恢复,可以继续工作,需要重新设定镜像
254 --备机执行:
255 USE master;
256 ALTER DATABASE S_C_SC SET PARTNER RESUME; --恢复镜像
257 ALTER DATABASE S_C_SC SET PARTNER FAILOVER; --切换到主机
258 
259 
260 --4、原来的主服务器恢复,可以继续工作
261 
262 
263 --5、用C#测试一下镜像的切换
264 
265 
266 --查看当前服务器做了镜像partner的那个服务器
267 SELECT * FROM sys.database_mirroring
268 
269 -------------------------------删除镜像-----------------------------------------------------------
270 --查看终端点
271 select * from sys.endpoints
272 
273 --删除某终端点(终端点不带引号)  
274 drop ENDPOINT endpoint_Mirroring
275 
276 --删除证书  在master | Security | Certificates
277 --删除用户  在master | User
278 
279 --然后可以删除登录名  drop login <login_name>
280 
281 --修改master key  : 
282 alter master key drop encryption by service master key
283 -- 删除master key  :
284 drop master key
285 
286 --删除镜像的命令:
287 ALTER DATABASE GPOSDB set partner OFF
288 
289 RESTORE DATABASE GPOSDB WITH recovery  --放弃事务,立刻还原
290 
291 -------------解除数据库镜像--------------------------------------------------------------
292 ALTER DATABASE S_C_SC SET PARTNER OFF

 

posted @ 2013-08-02 22:20 桦仔 阅读(...) 评论(...)  编辑 收藏