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

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 ?

arnold,

They are under the same domain,

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

DBA100.

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.

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.

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.

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 ?

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.

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?

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

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.

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.

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.

arnold,

What is ALTER DATABASE database_name SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS do ?

is it safe ?

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.

arnold,

Thanks and your explaination is exactly what I am thinking, really to do when no choice.

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.

Right, this one is the change of principal when the principal is up and accessible.

and that one will gurantee the data safe ?

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.