will Mirroring work in local account and workgroup | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

will Mirroring work in local account and workgroup

I like to know if db mirroring will work for two SQL server 2K5 in workgroup env and each has a local account with the same name and password to start the SQL services. The local account has admin right and also has a SQL login setup. The database is restored as norecovery in the secondary server. Here is the command I used to set up the mirroring. — on the primary ServerA
CREATE ENDPOINT endpoint_mirroring
STATE = STARTED
AS TCP ( LISTENER_PORT = 7777 )
FOR DATABASE_MIRRORING (ROLE=PARTNER); ALTER DATABASE db1
SET PARTNER = ‘TCP://172.16.100.1:7777’ grant connect on endpoint::endpoint_mirroring to [ServerAAccountA] — on the secondary ServerB CREATE ENDPOINT endpoint_mirroring
STATE = STARTED
AS TCP ( LISTENER_PORT = 7777 )
FOR DATABASE_MIRRORING (ROLE=PARTNER); ALTER DATABASE db1
SET PARTNER = ‘TCP://172.16.100.2:7777’ grant connect on endpoint::endpoint_mirroring to [ServerBAccountA]
I issue the following two commands to make sure the endpoint is created and started and they are. SELECT name, port FROM sys.tcp_endpoints SELECT state_desc FROM sys.database_mirroring_endpoints ———-
I have couple questions. 1. If I do the above setup using Studio management tool instead of transact-SQL, I will get ‘not able to connect to ServerB. I have checked the BOL to make sure the network address name is constructed as suggested. If I run the above commands in query analyzer, I don’t get any error. However, when I right click the prinicple database and select mirroring, it said the DB mirroring is not configured yet and the Server Network address for mirror is empty. Is it a normal behavior if I set up mirroring using transact-sql? 2. Since the command above didn’t give me any error, so I went ahead and see if I can switch over to the mirror database, when I issue the below command ALTER DATABASE db1 SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
It will give me the following error: Msg 1404, Level 16, State 3, Line 1
The command failed because the database mirror is busy. Reissue the command later. Any idea of why this happens? Wingman
You have to ensure the SQL server services have the same name and password as they are in workgroup; also check the log on mirror server to ensure the REDO queue is completed. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
]]>