• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 371
  • Last Modified:

Use the SQL cert. to setup the SQL 2005 mirror.

Under what situtation shoud we use the SQL cert. to setup the mirror?

I can't see why we cant' just use the SQL mirror wizard to create the mirror session but using certification. this is what we are doing currently.
0
marrowyung
Asked:
marrowyung
  • 10
  • 7
  • 4
2 Solutions
 
marrowyungAuthor Commented:
will it be the case that if both SQL server (principle and mirror parnter) use untrust account like the localsystem as the SQL start up account then we need to do it ?
0
 
arnoldCommented:
It might be that the two servers are on separate domains/environments.
http://msdn.microsoft.com/en-us/library/ms191140.aspx
Discusses how to set it up.
Have not considered it, so not sure.
0
 
marrowyungAuthor Commented:
arnold,

They are under the same domain,

using the localsystem account to start the SQL server will need this ?

DBA100.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
dave_tillerCommented:
You need to start the SQL Server service using an AD account.  Then, don't use the wizard to create the endpoints.  The wizards are great for simple operations.  However, using TSQL, you have full power to use all options.
0
 
marrowyungAuthor Commented:
dave_tiller,

Why don't use the wizard to create the end point ? I just used it to finish my job.

any URL that show the REAL T-SQL to create the mirror session once the end point is created? I find some which doesn't work.

Also what is the T-SQL to do the mirror failover in case DR drill ?

DBA100.
0
 
dave_tillerCommented:
A good MSDN blog to look over would be http://blogs.msdn.com/b/sqlblog/archive/2011/08/24/setting-up-database-mirroring-with-certificates.aspx.

This has a good example when to use it, and the TSQL used to set it up.
0
 
marrowyungAuthor Commented:
dave_tiller,

Thanks but as I said, I do the mirror without using cert. now.

But from DR drill point of view, we need to have a quick failover from primary to DR server, what shoudl be the script ?
0
 
arnoldCommented:
When the primary is down and your setup does not include a witness for automatic failover,
You would issue a alter database
http://technet.microsoft.com/en-us/library/ms189270.aspx
Note that once this stp is taken, the database/ mirroring would need to be reestablished.
Restoring the database from dr backup on the former principal instance and then reestablished the mirroring setup at which point it can be transitioned back from DR instance.
0
 
marrowyungAuthor Commented:
arnold,

NO what I mean is that right now we setup the mirror ALREADY, not need to setup anymore but we concern how we do the DR drill by failover to the DR partner without using UI but script! what is the script to failover and fail back?
0
 
arnoldCommented:
If the principal is up, you would run an alter script on the principal only (must be on the principal)
ALTER DATABASE database_name SET PARTNER FAILOVER
http://msdn.microsoft.com/en-us/library/ms179481.aspx
0
 
dave_tillerCommented:
You can also use Management Studio to do a manual failover.  To do this, on the principle server, go to the properties of the database.  Then go to the mirroring tab.  There is a button for a manual failover.

Additionally, you can also monitor the mirroring using the mirroring monitor.
0
 
marrowyungAuthor Commented:
dave_tiller,

Yesh, I knew that as I use this to test my failover of each DB mirroed but I don't want to do it in this way as script in this case, is faster, agree?

DBA100.
0
 
arnoldCommented:
If faster is the goal, configuring the witness will provide for an automated failover of the mirror without the need for manual intervention as well as minimize data loss i.e. the principal experiences a hardware failure to perform a graceful transition.
0
 
dave_tillerCommented:
And a witness server can be SQL express, which is free.
0
 
marrowyungAuthor Commented:
arnold,

What is ALTER DATABASE database_name SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS do ?

is it safe ?
0
 
arnoldCommented:
Safe? when the principal is permanently down and can not be brought back, you have little to no other choice.
This is done only when the principal is offline and can not be brought back online.  At this stage you would have to force the Mirror online with the data AS IS (allow_data_loss).
Provided you did not configure the mirroring to run asynchronously and provided you did not limit  the bandwith for mirroring and provided that the timing of the principal failure did not shortly follow a massive transaction that has not made it to the mirror, it is "safe"

If timing is such that the principal server crashed (HW Failure, will not boot) just after a massive transaction, the forced failover with allow data loss, will likely get the mirrored database up as principal without the most recent transaction data.
0
 
marrowyungAuthor Commented:
arnold,

Thanks and your explaination is exactly what I am thinking, really to do when no choice.
0
 
marrowyungAuthor Commented:
So this one:

ALTER DATABASE database_name SET PARTNER FAILOVER, are making sure that NO data lose ?

"Manually fails over the principal server to the mirror server. You can specify FAILOVER only on the principal server. This option is valid only when the SAFETY setting is FULL (the default).

The FAILOVER option requires master as the database context.
"

from http://msdn.microsoft.com/en-us/library/bb522476.aspx.
0
 
arnoldCommented:
Right, this one is the change of principal when the principal is up and accessible.
0
 
marrowyungAuthor Commented:
and that one will gurantee the data safe ?
0
 
arnoldCommented:
It is supposed to be.
Mirroring is not a replacement for a backup/disaster recovery plan.
With mirroring, you should have a backup/disaster recovery plan in place.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 10
  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now