November 10, 2010 at 2:56 pm
Forgive me but this was the closest area i saw to 2005 HA
i am using 2 windows 2k3 r2 sp2 machines with sql2k5 sp2 installed on them.
i am trying to setup a local DB mirror for redundancy purposes. i have my db backed up and set over to my endpoint i setup the mirror and when i go to start the mirror i get these errors
===================================
An error occurred while starting mirroring.
===================================
Alter failed for Database 'VT7'. (Microsoft.SqlServer.Smo)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdN...e&LinkId=20476
------------------------------
Program Location:
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.Al terImpl()
at Microsoft.SqlServer.Management.Smo.Database.Alter( )
at Microsoft.SqlServer.Management.SqlManagerUI.DBProp Mirroring.StartMirroring(Object sender, EventArgs e)
===================================
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Program Location:
at Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Smo.ExecutionManage r.ExecuteNonQuery(StringCollection queries)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.Ex ecuteNonQuery(StringCollection queries, Boolean includeDbContext)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.Al terImplFinish(StringCollection alterQuery, ScriptingOptions so)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.Al terImplWorker()
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.Al terImpl()
===================================
The server network address "TCP://tstsqk2k5.milrose-ny.com: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. (.Net SqlClient Data Provider)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdN...8&LinkId=20476
------------------------------
Server Name: MRCSQLNEW
Error Number: 1418
Severity: 16
State: 1
Line Number: 1
------------------------------
Program Location:
at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQuer yTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNo nQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
then the mirror fails and wont connect i set up the mirrored DB with the NORECOVERY option but still wont connect
tested telnet and i get black screen .. there is not much out there on this so any direction:w00t: will help
please help as our DR hinges on this working.
Reply With Quote
November 10, 2010 at 5:31 pm
Is port 5022 open on BOTH servers (principal and mirror)?
Make sure your endpoints are STARTED also. Check
http://msdn.microsoft.com/en-us/library/ms181591(v=SQL.90).aspx
Minor, but you should probably upgrade to SQL Server service pack 3, also.
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
November 11, 2010 at 6:37 am
maybe not the best for security but here is how I always set up my database mirrors;
I use a single domain user account to run all the SQL server services on all servers participating in a db mirror. so the principal, mirror and witness (if used) will all be running SQL Server service and SQL Server Agent service using a single domain account.
November 11, 2010 at 11:49 am
how do i start the replication on both sides? i though once you did it on the primary is automatically started on the mirror? also.. how do i break the replication
5022 is the mirror port on both servers
November 11, 2010 at 11:54 am
also i have "0" db experience. Do i have to run a query to make this work? I don't even know exactly how to enter or even where to put this.
November 11, 2010 at 12:00 pm
oh my.... with "0" dba experience, telling you how to initiate a DB mirror may be a tall order.....
also, are we talking about replication OR database mirroring OR both?
November 11, 2010 at 12:07 pm
mlabara (11/11/2010)
how do i start the replication on both sides? i though once you did it on the primary is automatically started on the mirror? also.. how do i break the replication5022 is the mirror port on both servers
to initiate a mirror, you do this;
on the principal, you take a full backup and a tran log backup.
on the mirror, you restore from those backups the full and the tran log, but leave the database in recovery.
then use the GUI to start the mirror.
but as i said before, using the same domain account to run the services on all 3 servers will make this process easier. (only 2 servers if no witness is used)
November 11, 2010 at 12:18 pm
all my servers run as local system... not an admin account. i did what ms documents states
copy db with backup on principal
restore db and tran logs on mirror
then i go to server and try to start mirror from wizard
but then it fails with at weird message.
dont know what i am doing wrong
noticed my mirror server shows restoring next to it..
November 15, 2010 at 5:21 am
and under what account are you attempting to connect the mirrors with?
that is why i talked earlier about domain accounts that run the SQL server services being the same on all the servers..
since you are running your SQL Server instances as local system accounts, you must assign accounts to manage the mirror connections (and probably set up certificates).
the error messages you are seeing is because the account that you are using to connect the mirrors with lacks the permission needed to initiate the mirror.
November 15, 2010 at 10:05 am
Here is a quick cheat sheet I used when I set up my first mirroring session. Hopefully it helps.
Database mirroring:
-------------------
Overview and discussion guides:
===============================
http://msdn.microsoft.com/en-us/library/ms190941.aspx
How to setup database mirroring:
================================
1. Create endpoints
http://msdn.microsoft.com/en-us/library/ms190456.aspx
2. Setup logins to connect to the endpoints
http://msdn.microsoft.com/en-us/library/ms178029.aspx
3. Prepare mirror database
http://msdn.microsoft.com/en-us/library/ms189047.aspx
4. Establish mirroring session
http://msdn.microsoft.com/en-us/library/ms175883.aspx
5. Establish witness server (for high availability mode)
http://msdn.microsoft.com/en-us/library/ms190430.aspx
How to remove database mirroring:
===============================
http://msdn.microsoft.com/en-us/library/ms189112.aspx
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply