We are in a middle of a SQL Server 2012 migration project and one of the main tasks is to completely mirror all of the databases. Having said that it does not mean we did not mirrored it before, some of the legacy databases are already mirroring and we have one dedicated server to do the job and that’s what it only does. With this project we want to install a new instance of SQL Server 2012 to be as witness for other SQL Server 2012 databases on top of SQL Server 2008 already installed in the server, so after installation we then setup the mirroring and then we had this error:
TITLE: Database Properties ------------------------------ An error occurred while starting mirroring. ------------------------------ ADDITIONAL INFORMATION: Alter failed for Database 'YourDatabase'. (Microsoft.SqlServer.Smo) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.2100.60+((SQL11_RTM).120210-1917+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476 ------------------------------ An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ The server network address TCP://servername:5022 can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.2100&EvtSrc=MSSQLServer&EvtID=1418&LinkId=20476 ------------------------------ BUTTONS: OK ------------------------------
Now that is giving you an exception because port 5022 is used by the old SQL Server Witness, which means you need to change this port so that it would work.
Run this TSQL command in your Witness server instance to check if indeed 5022 is being used and at the same time what other ports you can use
SELECT * FROM sys.tcp_endpoints
To change that all you have to do is select a port number not on the list below as well as any port number that is not used by your server and applications, so for this example lets use 5023, you have to issue this command on your SQL Server Witness
ALTER ENDPOINT [Mirroring] AS TCP (listener_port = 5023)
Once done restart you will see “command(s) completed successfully“, you need to restart the SQL Server for it to take effect. Now go back and configure your mirror again and it will now connect to the 5023 port
Thank you so much!