Friday, May 8, 2009

hints about mirroring sql server

SQL 2005 Database Mirroring Tutorial (Without Domain)
http://alan328.com/SQL2005_Database_Mirroring_Tutorial.aspx
------------
http://msdn.microsoft.com/en-us/ms189127.aspx
----------
http://wiki.cdyne.com/wiki/index.php?title=Mirroring_SQL_Server_2005
----------
mirror steps
0) In this example we are inside a DOMAIN. We have a domain user called MRRUSR
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
SELECT type_desc, port FROM sys.tcp_endpoints;SELECT name,role,state_desc FROM sys.database_mirroring_endpoints;

15) Delete existing ones (DROP ENDPOINT [NAME])
16) Create a new endopoint
CREATE ENDPOINT [Mirroring] AS TCP (LISTENER_PORT = 5023)FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION=DISABLED)
17) Start it
ALTER ENDPOINT [Mirroring]STATE = STARTED AS TCP (LISTENER_PORT = 5023)FOR database_mirroring (ROLE = PARTNER);GO

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.
ALTER DATABASE NAMESET PARTNER ='TCP://MACHINENAME:5023'
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.
----------------
http://social.msdn.microsoft.com/forums/en-US/sqldatabasemirroring/thread/73fb15c0-9270-4cbf-a74e-544639e792da/
-----------------

No comments: