Database Mirroring Transport is disabled when setting up Database Mirroring
-
Hi,
I am trying to setup database mirroring on the RTM version of SQL Server 2005 but I hit an error message when trying to run the following scripts on the principal server:
ALTER DATABASE AdventureWorks
SET PARTNER = 'TCP://swissboy64w2k3:5023'
the error message is as follows:
Msg 1486, Level 14, State 2, Line 1
Database Mirroring Transport is disabled in the endpoint configuration.
I have enabled Database Mirroring feature by running the DBCC TRACEON (1400,-1) in the query window and follow by the rest of the scripts to setup database mirroring :
-----------------------------------------------------------------
USE master
GO
CREATE ENDPOINT AdvWorksEndpoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 5022 )
FOR DATABASE_MIRRORING (ROLE=PARTNER)
GO
-- Specify mirror server from principal server
-- Run on swissboy64w2k3
USE master
GO
ALTER DATABASE AdventureWorks
SET PARTNER = 'TCP://swissboy64w2k3:5023'
-----------------------------------------------------------------
How do I enable the Database Mirroring Transport in the endpoint configuration?
Thanks.
regards,
Boon-Tiong Lim
-
database mirroring was turned off in the RTM. There were some issues they didn't have time to resolve before their ship date. I understand there is a trace flag that can be turned on for that, but i forget its number. If you turn the trace flag on, Microsoft won't support that database server.
-
http://msdn2.microsoft.com/en-us/library/ms188396.aspx
1400
Enables database mirroring. This trace flag is allowed only when using the -Ttrace# startup option during start up of the server instance. Therefore, enabling mirroring requires a restart of the server instance. For more information about using Setup options, see Using the SQL Server Service Startup Options.
Important: Microsoft support policies do not apply to the database mirroring feature in SQL Server 2005. Database mirroring is currently disabled by default, but may be enabled for evaluation purposes only by using trace flag 1400 as a startup parameter. Database mirroring should not be used in production environments, and Microsoft support services will not support databases or applications that use database mirroring. Database mirroring documentation is included in SQL Server 2005 for evaluation purposes only, and the Documentation Policy for SQL Server 2005 Support and Upgrade does not apply to the database mirroring documentation. After starting up a server instance using this trace flag, you can set up and evaluate database mirroring. For more information, see Setting Up Database Mirroring.
Trace flag 1400 is a temporary flag. This flag will be removed when database mirroring is made available for general production use.
-
I am getting the same error after turning on trace flag 1400 and exactly following what microsoft has on its site setting up database mirroring. Can someone help fix it ?
-
i have fixed the problem.
when turning on the trace flags T1400, make sure u include the semicolon and no spaces between the parameters.
e.g...
...<some other parameters>;-T1400
take note.... no spaces... and include semicolon.
restart the SQL service.... it should work.
-
hi,
do we have do this in the registry because the way I was turning on the flag was using DBCC TRACEON (1400,-1).
I tried adding the parameters T1400 in the registry and it did not turn on the flag when I restarted SQL. I cannot find any other place where I can add variables to turn the flag on. Can you give me the exact synatax please and where I need to do it. Thank you very much for your help. I cannot deploy my production servers till I get this working.
Also another question, can we can alter tables and add new tables when mirroring is on. If not what is the best way to do it.
-
I tried adding -T1400 in the startup parameters using registry but it gave me an error when I starting SQL with semicolon between paramters.
"C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe" -sMSSQLSERVER;-T1400
Am I doing something wrong?
-
hi,
dont use the registry editing method.
go to the SQL Server COnfiguration manager, select the MSSQLSERVER service --> PROPERTIES --> ADVANCED, look for the startup parameters.
append the ";-T1400" parameter to the back of the existing parameters. take note.. no spaces.
-
Thanks that part worked but then I got a new error saying that the database was using bulk-logged. I changed it to full and backed it up again and restored with norecovery; then I restored one log back up with norecovery.
Now I am getting this error "Neither the partner nor the witness server instance for database "shop" is available. Reissue the command when at least one of the instances becomes available. (Microsoft SQL Server, Error: 1431)"
I did the backup and recovery based on this link http://msdn2.microsoft.com/en-us/library/ms189053.aspx.
I have a fully qualified path for the servers also. I am not sure where I am going wrong. Please help.
-
Hi Lim,
Can you please help me fix this error "Neither the partner nor the witness server instance for database "shop" is available. Reissue the command when at least one of the instances becomes available. (Microsoft SQL Server, Error: 1431)"
-
did u successfully start all the 3 instances (principal, mirror, witness) ?
u need to set the T1400 trace flags for all 3 instances.
goto the principal instance and select database properties, configure database mirroring from there.
u need to setup the end points first.
-
Hi,
I am not using a witness server. It is only principal and mirror. I have set the trace flasgs on both instances and checked their status and they are on. I added ;-T1400 to the startup parameters without space.
I created endpoints on both servers. I did try to configure the principal server. I know I can cannot to the mirror from princiapl because I use management studio to connect to mirror from principal. I was also able to telnet to port 5022 on mirror. So I am not sure why I am getting this error at the last stage. I am using a admin account to start the SQL service and loggin in as sa. SHould I change any of these.
-
I had trouble getting this to work as well. A couple of things that helped me get things going:
1. Ensure that all SQL Server instances in your mirroring quorum are using service accounts and that all service accounts have had logins created on each of the servers. Use GRANT CONNECT ON ENDPOINT statements to grant access to each of the endpoints that you create.
2. Believe it or not, using the database mirroring wizard worked great for me the second time I set it up. Saved me a ton of time. Delete all the endpoints, then right click the database on the principal server, select Properties, then mirroring and then click the Configure Security button. It worked great for me.
-
Thanks Aaron, I did the same and also tried the wizard but I still have the same problem.
-
Hi I tried this:
-dC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf;-T1400
But still recieved the same error. please help!
thanks
Important: Viewing 15 posts - 1 through 15 (of 38 total)
You must be logged in to reply to this topic. Login to reply