pb fail over automatic with one instance alias with data mirroring

Hello,

I've got a question about failover with the database mirroring.

I have 3 servers, 2 with SQL 2005 and 1 with SQL Express as witness.

After getting some problems to install database mirroring with Windows authentification, i had to create an authentification by certificate.

All goes well but my problem is about my primary instance SQL alias.

The alias on the primary doesn't follow on the secondary when the primary is down.

I've searched on forums and in the internet SQL web sites, but i didn't find anything.

I create another alias on the secondary but i don't know how to modify it in T-SQL...

I would like to know if there's any solution to modify an SQL instance alias by transact-SQL? or if anyone has already install on its servers, a fail over automatic with one instance alias with data mirroring in T-SQL?

Thank you all for your help...

Aliases are written to the registry, so you must edit the registry. There are a number of undocumented extended stored procedures that can be used for editing the registry. However, they are undocumented which means that they are unsupported.

The procedures are in the master database and begin with xp_reg. You can read a description of some of them for SQL 2000 here: http://www.sql-server-performance.com/ac_extended_stored_procedures.asp

I do not know what, if any, changes have been made to these procedures in 2005, so be very careful if you use them.

|||

Ok thanks for this issue.

But if i modify the registry i had to reboot my server and like i want an high avaibility access to my database without clustering them, it's not the way i think i'll take.

In SQL 2000, the SQL alias server up in the WINS, but with 2005, is there anyone who know where this information go? and how to modify it? I tried to find informations in my database, but i don't find anything yet..

In forums SQL, there's nothing too...

Anyone have experiences about database mirroring with moving alias when the SQL role change?

Thanks for your help...

|||

At my work, we don't use .Net so we can't use the new support for ADO connection failover

we decided to use DNS Alias to mimic the feature so that it won't be instant failover for the entire application, but should be shorten to 30-60 mins downtime (just change DNS Alias from Server1 to Server2, use Alias name in all applications)

|||

We are using a federated model where each client has it's own version of the database. The application logs in to a central core database for authentication and then redirects to the client database. Some of the older modules of our web application is using .Net 1.0 which is not mirroring aware. But since the application first checks the central database before connecting to the client database, it seemed like handling failover for these older modules would be as simple as changing the ServerName in the Central database to point to the other server. Unfortunately, this only works for new users, not existing ones. The application is caching the connection information, and even if I log off and back on, it tries to send me to the previous server.

Until these portions of the application have been recoded, I have set up a job (runs every minute) on the mirror server that checks for principal instances running on itself. If it finds one and it is in the synched state, it fails it back over to the original principal server.

|||

Hello,

I've found my answer on this site

http://blogs.developpeur.org/christian/archive/2006/11/04/SQL-Server-_3A00_-Alias-de-serveurs.aspx

We can change the alias SQL Server 2005 directly in the registry.

Thanks for your answers..