May 2, 2007 at 9:49 am
Hi,
I am quite new to SQL Server 2005 and very new to database mirroring. I am testing database mirroring solution for a new project. There are 3 questions I wish to ask:
(1) After I configured database mirroring for one database in an instance (using default port 5022 for mirroring endpoint), is it possible to change the endpoint port without re-setting up database mirroring? I tried to run the following alter endpoint command in both principal and mirror, but after that mirroring does not work saying the mirror could not be connected:
alter endpoint Mirroring
as TCP
(Listener_port = 15051)
(2) Suppose I clicked "Remove mirroring" for an existing log shippimirroring pair, and re-setup mirroring again. When I configured everything, and prepared to click "Start Mirroring", I followed what I did when I set it up the first time, i.e., to make full backup and log backup at principal and attempted to restore them with norecovery at the mirror. But I am getting this message during restore: "Msg 3104, Level 16, State 1, Line 1 RESTORE cannot operate on database 'testdb' because it is configured for database mirroring. Use ALTER DATABASE to remove mirroring if you intend to restore the database."
But I checked database property and it shows mirroring is not configured. I even checked sys.database_mirroring table and there is no valid entry for that particular database ID.
How can I completely remove log shipping and re-set it up again?
(3) This question may not really relate to database mirroring. I changed the TCP listening port of SQL Server ServerB from the default 1433, to 50001. Then I tried to connect to ServerB from SQL Server ServerA, and did not succeed. Finally I need to type "ServerB, 50001" as server name in order to connect successfully. I started SQL Server Browser service in both servers. Is it possible to let SQL Server automatically detect the target listening port when connecting from another server?
Thanks a lot,
Del Piero
May 2, 2007 at 9:55 am
Sorry in (2) it should be "clicked "Remove mirroring" for an existing mirroring pair" instead of existing log shipping pair. Everything I asked is about mirroring and nothing about log shipping.
Thanks!
Del Piero
May 3, 2007 at 1:24 am
Answer for ur third is add a entry in the client configuration to point to servername,portnumber with alais as ur servername then u can use ur servername itself to connect to the sql server instead of using servername,portnumber.
Answer for second:
Are you sure that database mirroring is fully removed from both the instances. check there might be few tables that hold entries for database mirroring check them and remove those entries.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
May 3, 2007 at 7:12 am
1) I would be leery of attempting to build a production system using mirroring if you are a sql server newbie. WAY too many opportunities to fail there, and you might well not know you did something wrong until the sh-t hits the fan and that is a REALLY bad time to find out something wasn't set up correctly! 🙂
2) Try explicitly doing the ALTER DATABASE statement to 'remove' mirroring before doing the restore. If that doesn't work, completely (also using alter database) remove mirrroring from the primary too and then restart the sequence. You may also consider simply dropping the testdb from the secondary server too.
3) Try the SQL Server Configuration Manager and create an Alias (under SQL Native Client Configuration) that specifies the correct TCP port.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 3, 2007 at 8:41 am
Regarding TheSQLGuru's reply, there is also one thing i am worrying about... In SQL Server 2005 Books Online, if you click any topic about Database Mirroring, there is a notice at the top saying:
"Microsoft support policies do not apply to the database mirroring feature in SQL Server 2005 ........ Database mirroring should not be used in production environments, and Microsoftsupport services will not support databases or aplications that use database mirroring ..."
Is this still true? Or it's just an outdated statement??? I am really worried about this statement. Please let me know ....
Thanks,
Del Piero
May 3, 2007 at 9:03 am
>>"Microsoft support policies do not apply to the database mirroring feature in SQL Server 2005 ........ Database mirroring should not be used in production environments, and Microsoftsupport services will not support databases or aplications that use database mirroring ..."
This is an outdated statement. The RTM version of SQL 2005 shipped without database mirroring 'being functional' because of issues found during CTP stage. It just wasn't ready for prime time - but SQL2K5 was WAY behind schedule so they release it without a number of features ready.
Check the MS website to download the latest copy of SQL2K5 BOL (Feb07 is the date of them I believe).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 10, 2007 at 10:54 pm
Hi all,
I have got another problem in setting up the witness in database mirroring. I am trying to use a SQL Server Express Edition as a witness. The instance name is DESKTOP1\SQLEXPRESS. I tried using GUI and T-SQL to set up, but each time I am getting this error at the final stage at this command:
ALTER DATABASE mirrortest
SET WITNESS =
'TCP://desktop1.mydomain.com:5052'
GO
The error is:
Msg 1456, Level 16, State 3, Line 1
The ALTER DATABASE command could not be sent to the remote server instance 'TCP://desktop1.mydomain.com:5052'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.
I already set the mirroring endpoint and its port properly. The necessary logins and permissions are also created/granted. The port is not blocked.
Can anyone help? Not sure if it is a problem in the server name. I am not sure from where the TCP address can identify that it is connecting to a named instance instead of a default instance...
Thanks,
Del Piero
May 18, 2007 at 10:34 am
Del,
Did you ever find a solution for this. I have been on the phone with Microsoft for 4 hours and they still do not know how to do it. They noticed that this is not documented.
When I get a final answer on this I will post it. I just want to see if you already knew.
Thanks
Rick
May 18, 2007 at 9:22 pm
No I still haven't got an answer ...I tried using some methods discussed in other forums/user groups, but it's still failing ........
Del Piero
May 21, 2007 at 3:50 am
You may wish to bring this to the attention of "PaulRandal"....a member over at SQLTeam.com. Paul's a Microsoft head who wrote some of the internals of the SQL Engine. He participates on a few forums to head off problems before they get a momentum of their own.
May 21, 2007 at 8:02 am
Paul's posted here as well and his blog is here:http://blogs.msdn.com/sqlserverstorageengine/
I pinged him with this thread, so you may see a response here.
May 21, 2007 at 2:41 pm
Sounds like your Express instance is not configured to accept remote connections (default setting for an Express instance). To change it, follow the instructions at http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277
Hope this helps
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
June 3, 2008 at 11:06 am
breezed over this as i'm trying to troubleshoot a similar issue...
Here are things to try:
-ensure principal/witness/mirror all have same DNS settings
-ensure principal/witness/mirror can all resolve each others FQDN (if not, add something to the C:\windows\system32\drivers\etc\hosts file)
-ensure principal/witness/mirror all have the same user running the SQL Server service
July 17, 2008 at 8:39 am
I am getting the same error while adding the witness server:
The Database mirroring works fine with Principal and Mirror (both are on Standard edition). The witness is on Enterprise edition (earlier i tired with Satndard but failed).
Msg 1456, Level 16, State 3, Line 1
The ALTER DATABASE command could not be sent to the remote server instance 'TCP://witnessserver.domain.COM:5022'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.
Let me know if you find anything
July 18, 2008 at 1:51 am
It seems strange that an ALTER DATABASE command should be sent to the Witness server. hmm, unless changes are required to the MSDB database...? Or maybe the ALTER is required to create the Endpoint in the first place. But then we have a curious Catch 22 situation, whereby there is no Endpoint listening.
To help fault-find, I recommend using SSMS, opening the Properties dialogue for the database on either the Primary or the Mirror, and running through the "Configure Database Mirroring Security Wizard". This will confirm if all 3 Endpoints are available.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply