High Availability to High Protection without reconfiguring Mirroring

Hi,

Is there a way to configure mirroring to go from High Availability to High Protection without having to reconfigure Database Mirroring? Using the interface in Management Studio, I can change the configuration option to High Performance, but not High Protection despite both of them being Synchronous.

If not, what are the recommended steps to configure the mirror once it already has been configured? Is just like initially setting up the mirror or would there be any shortcuts I could take? If I stop the mirroring and remove the witness, will the High Protection option be available?

Thanks,
J.

Hi Jchohio,

When you are in high availability mode you the witness server is configured SAFETY FULL. To switch to High-Prottection mode, just turn the witness server by using the following command.

Code Snippet

ALTER DATABASE <database_name> SET WITNESS OFF

where <database_name> is the name of the mirrored database.

regards,

Jag

|||Thanks for the Reply!
-J.

High Availability solutions

Hi

We are using in our company an ERP system called iScala.

This software is using SQL server as DBMS.

We would like to use the SQL mirroring feature from SQL 2005.

The software is using the SQL server instance name to connect to databases and when the Main SQL server fails the application can't connect to the Mirror instance.

Unfortunatelly we can't change the connection string (the software is provided by another company).

Do you have a solution?

Thanks

I believe you have to contact the vendor to provide such changes, as this is application issue to talk to the mirror server in the event of failover. If the application is not db mirror aware then you have to find a route from connectivity to talk to other server in such cases.|||

Thanks

You are right.

I'm checking now some solutions for High Availability from Symantec (VERITAS Storage Foundation HA 4.3 for Windows) and Double-Take.

Do you have any information about them?

|||

You could solve this issue using a manual change to DNS.

i.e. MYLIVESQL -> 10.0.0.1 - connection string uses MYLIVESQL as the server name, in the event of a failure you update the DNS record to MYLIVESQL -> 10.0.0.2 (which is the IP address of the mirror server).

Andrew

|||

If the (former) principal instance is still up and running but the database failover occured, then SNAC will automatically learn the location of the mirror and redirect the connection to the mirror, as described here: http://msdn2.microsoft.com/en-us/library/ms366348.aspx. But this works only if the connection string specifies the database and if the principal instance is still running.

But if the vendor is not providing support for mirroring, is highly unlikely that the application will actually work with mirrored database, it may have dependencies outside the database that are not gonna failover (e.g. logins resulting in orphaned users, jobs in msdb etc)

HTH,
~ Remus

|||

Remus,

MOSS 2007 and WSS V3 fall into the category of not supporting mirroring in the connection strings, hence the need to use the cname approach. Microsoft recommend using stsadm to manually change the settings which I think is less elegant.

Andrew

High availability for Database Mirroring

Hello,

I would like implementing a database mirroring architecture with SQL server 2005 but i have questions.

If i don't use a cluster architecture, i would like knowing if there's any solution to move the alias SQL server from the primary to the secondary by script.

Anyone got a pb like me? or any solution?...

Thanks for your help...

You mean you'd like to have the two servers have the same name?

|||

Well...

On SQL 2005 the alias SQL server name stay on the server and don't go up in the WINS. With the graphic administration, we can directly add or modify this alias.

The best way for me should be to get an alias being moved by script in order to be used on the primary server.

As we can change this alias by a graphical MMC, so i guess we can change it by script no?

|||I don't see how switching the alias will help you without some other background work being set up first. As luck would have it, I'm contemplating this same issue, so perhaps my reply will provide enough provocation for someone else to come along and answer this to both our satisfactions...

First, as far as the alias goes... Suppose we have a production server, 10.1.2.1 and a 'hot-standby' server, 10.1.2.2. Now further, suppose our alias is 'SQLSRVR' = 10.1.2.1.
Now, assume something bad happens. If one simply goes into the alias and changes the mapping to 10.1.2.2, what does that get you? Answer, nothing you want - unless in the background you have been shipping the logs or in some other way, trickling over the transactions that have been going on for 10.1.2.1.

What I'm contemplating is this - 10.1.2.1 is up and running and attached to a SAN. 10.1.2.2 is up and running and attached to the same SAN. Both servers have their 'own' logical volumes - say - one for mdf/trn; one for ldg/bak; and one for tempdb. Assume the SQL Server software is installed on the servers internal drives. They can both see the other's volumes, but since they are in effect running their own instances, the id's for objects will be unique to each system (I'm not clustering).

Now, through some mechanism, we need to on some time interval, trickle over the transactions that have occurred on 10.1.2.1 to 10.1.2.2. My question is this - what is the best way on 2005 to do this 'trickle'? Log shipping. mirroring, what?

Can someone provide input here? Thanks in advance.

randyvol|||

Actually, I believe that what you are asking for is built into mirroring if you run in synchronous mode.

You can build your application with a connection string that names both mirror instances, and will transparently fail over to the secondary if the primary cannot be reached. In synchronous mode, the failover can be accomplished entirely automatically, with no scripting required, or on command.

Is there something additional that you need which I'm missing?

|||

I can't change connection string because i'm using a software not developped by me.

I'll send a request to the support of this program.

I found on this site the solution to my problem by changing alias in the registry

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

Thanks you all for your answers...

High availability for Database Mirroring

Hello,

I would like implementing a database mirroring architecture with SQL server 2005 but i have questions.

If i don't use a cluster architecture, i would like knowing if there's any solution to move the alias SQL server from the primary to the secondary by script.

Anyone got a pb like me? or any solution?...

Thanks for your help...

You mean you'd like to have the two servers have the same name?

|||

Well...

On SQL 2005 the alias SQL server name stay on the server and don't go up in the WINS. With the graphic administration, we can directly add or modify this alias.

The best way for me should be to get an alias being moved by script in order to be used on the primary server.

As we can change this alias by a graphical MMC, so i guess we can change it by script no?

|||I don't see how switching the alias will help you without some other background work being set up first. As luck would have it, I'm contemplating this same issue, so perhaps my reply will provide enough provocation for someone else to come along and answer this to both our satisfactions...

First, as far as the alias goes... Suppose we have a production server, 10.1.2.1 and a 'hot-standby' server, 10.1.2.2. Now further, suppose our alias is 'SQLSRVR' = 10.1.2.1.
Now, assume something bad happens. If one simply goes into the alias and changes the mapping to 10.1.2.2, what does that get you? Answer, nothing you want - unless in the background you have been shipping the logs or in some other way, trickling over the transactions that have been going on for 10.1.2.1.

What I'm contemplating is this - 10.1.2.1 is up and running and attached to a SAN. 10.1.2.2 is up and running and attached to the same SAN. Both servers have their 'own' logical volumes - say - one for mdf/trn; one for ldg/bak; and one for tempdb. Assume the SQL Server software is installed on the servers internal drives. They can both see the other's volumes, but since they are in effect running their own instances, the id's for objects will be unique to each system (I'm not clustering).

Now, through some mechanism, we need to on some time interval, trickle over the transactions that have occurred on 10.1.2.1 to 10.1.2.2. My question is this - what is the best way on 2005 to do this 'trickle'? Log shipping. mirroring, what?

Can someone provide input here? Thanks in advance.

randyvol|||

Actually, I believe that what you are asking for is built into mirroring if you run in synchronous mode.

You can build your application with a connection string that names both mirror instances, and will transparently fail over to the secondary if the primary cannot be reached. In synchronous mode, the failover can be accomplished entirely automatically, with no scripting required, or on command.

Is there something additional that you need which I'm missing?

|||

I can't change connection string because i'm using a software not developped by me.

I'll send a request to the support of this program.

I found on this site the solution to my problem by changing alias in the registry

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

Thanks you all for your answers...

High availability at subscribers using database mirroring

I have a mirrored database with transactional replication publishing
to 2 databases on different servers.
The amount of data written to the principal is fairly low.
The requirement is that at least one of the subscribers must be
available at all times with a latency of no greater than five minutes.
I thus have redundancy at the publisher via mirroring and at the
subscribers via multiple databases containing the same data, but I
can't see how to achieve redundancy at the distributor, since the
distribution database cannot be mirrored.
What is the recommended method of making the distributor highly
available? At this site there is reluctance to pursue SQL clustering
and to rename servers as described in BOL. At the moment it looks like
a need a set of scripts to completely set up replication from scratch
if the machine where the distributor DB lives goes offline.
Or, will Katmai offer new features to make all of the replication
components as highly available as the publisher can now be via
mirroring?
Hi Garry,
I understand that you would like to implement a high availability SQL
Server replication. You implemented Database mirroring for your principal
server and multiple databases with same data at the subscribers, but you
would like to know how to implement redundancy at the distributor since the
distribution database cannot be mirrored.
If I have misunderstood, please let me know.
I recommend that you can create a failover cluster for your distributer.
Deploy the distribution database to the virtual server instance. Also, for
your scenario, I think that it is better to use failover cluster for your
publisher since it just need two servers, but to implement a high
availability database mirroring, it needs three servers, principal server,
witness server and mirror server.
For how to setup a SQL Server 2005 failover cluster, you may refer to:
How to: Create a New SQL Server 2005 Failover Cluster (Setup)
http://msdn2.microsoft.com/en-us/library/ms179530.aspx
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====