環境:SQL Server 2005 Enterprise SP3

 

//查詢Tcp_tcp_endpoints
SELECT type_desc, port FROM sys.tcp_endpoints;

//查詢database_mirroring_endpoints
SELECT name,role,state_desc FROM sys.database_mirroring_endpoints;

//刪掉某個ENDPOINT
DROP ENDPOINT [NAME];

//創造ENDPOINT
CREATE ENDPOINT [Mirroring]
AS TCP (LISTENER_PORT = 5022)
FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION=DISABLED)

//修改ENDPOINT
ALTER ENDPOINT [Mirroring]
STATE = STARTED
AS TCP (LISTENER_PORT = 5022)
FOR database_mirroring (ROLE = PARTNER);

//設定Partner的IP位置與Port
ALTER DATABASE [NAME] SET PARTNER ='TCP://IP:5022';

//主要資料庫壞掉時,在鏡像資料庫上執行這段命令即可使用鏡像的資料庫
ALTER DATABASE [NAME] SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;

//去除鏡像
ALTER DATABASE [NAME] SET PARTNER OFF;

//將資料庫改成普通狀態
RESTORE DATABASE [NAME] WITH RECOVERY;

//從主要資料庫上容錯移轉到鏡像

ALTER DATABASE SET PARTNER FAILOVER

====================================

如果有問題請參考下面這篇文章

http://social.msdn.microsoft.com/Forums/en/sqldatabasemirroring/thread/73fb15c0-9270-4cbf-a74e-544639e792da

 

以下為轉載網址裡面的文章

 

1)Machine A, Machine B with Sqlserver installed. NO XP or other kind of firewalls ON.

2)MRRUSR is local admin in both machines. Also is a sysadmin user in both sqlservers.

3)MRRUSR is the MSSQLSERVER service account in both machines

4)Machine A is gonna be the PRINCIPAL, B the MIRROR.

5)Log on in my machine as MRRUSR

6)Open Sql server Studio. Connect to both machines using windows authenticacion.

7)Create a New Database in A (you may need to have sysadmin role to MRRUSR using sa) create a table, add some data

8)Backup the new DB (Full backup) with .bak extension

9)Backup the new DB (Transaction log) with .trn extension

10)Copy the both files to a location in B machine.

11)Restore .bak into a new DB using NORECOVERY option

12)Restore .trn into the previous DB using NORECOVERY option

13)Go to Machine A, open a new query

14)Take a look to the endpoints

15)Delete existing ones (DROP ENDPOINT [NAME])

16)Create a new endopoint

17)Start it

18)Do steps 13 to 17 in machine B.

19)Return to machine A. Query.

20)Lets link to our partner in machine B. First ensure you can see it with ping and telnet to the port.

21)Repeat step 20 from MACHINE B pointing to MACHINE A

22)Refresh the both databases in the UI, you should see the role and status of the mirror beside the database UI object.

23)Lets test them: Add some new data in Machine A database.

24)Then, right click over database, then mirror, then in the form select FAILOVER, then say yes.

25)Refresh the 2 databases in the UI you should see the new status and roles changed.

26)Open Machine B databse, the new data should be there.

welkingunther 發表在 痞客邦 PIXNET 留言(0) 人氣()