Mirroring Client

Hello,
When a failover occurs can clients be automatically redirected to the active
instance of a db or does this have to be done with code?
Thanks in advance!
Mark,
When the database failover occurs your sessions will be disconnected. You
need to update your application to automatically fail over when this database
failover occurs.
Here is how you can do it.
Implementing Application Failover with Database Mirroring
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/implappfailover.mspx
Hope this helps,
Ben Nevarez
"Mark" wrote:

> Hello,
> When a failover occurs can clients be automatically redirected to the active
> instance of a db or does this have to be done with code?
> Thanks in advance!
>
|||When you have a session connected to a database (principal) and the database
fails over to the mirror, your session will be disconnected. You can open a
new session and it will now be connected to the new principal (that was the
mirror before). This requires no application changes and perhaps only will
need the failover partner clause on the connection string.
But if you need application automatic failover you can implement the retry
logic described in the best practices article I mentioned here.
Hope this helps,
Ben Nevarez
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Mark,
> When the database failover occurs your sessions will be disconnected. You
> need to update your application to automatically fail over when this database
> failover occurs.
> Here is how you can do it.
> Implementing Application Failover with Database Mirroring
> http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/implappfailover.mspx
> Hope this helps,
> Ben Nevarez
>
>
> "Mark" wrote:

Mirroring Client

Hello,
When a failover occurs can clients be automatically redirected to the active
instance of a db or does this have to be done with code?
Thanks in advance!Mark,
When the database failover occurs your sessions will be disconnected. You
need to update your application to automatically fail over when this database
failover occurs.
Here is how you can do it.
Implementing Application Failover with Database Mirroring
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/implappfailover.mspx
Hope this helps,
Ben Nevarez
"Mark" wrote:
> Hello,
> When a failover occurs can clients be automatically redirected to the active
> instance of a db or does this have to be done with code?
> Thanks in advance!
>|||When you have a session connected to a database (principal) and the database
fails over to the mirror, your session will be disconnected. You can open a
new session and it will now be connected to the new principal (that was the
mirror before). This requires no application changes and perhaps only will
need the failover partner clause on the connection string.
But if you need application automatic failover you can implement the retry
logic described in the best practices article I mentioned here.
Hope this helps,
Ben Nevarez
"Ben Nevarez" wrote:
> Mark,
> When the database failover occurs your sessions will be disconnected. You
> need to update your application to automatically fail over when this database
> failover occurs.
> Here is how you can do it.
> Implementing Application Failover with Database Mirroring
> http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/implappfailover.mspx
> Hope this helps,
> Ben Nevarez
>
>
> "Mark" wrote:
> > Hello,
> >
> > When a failover occurs can clients be automatically redirected to the active
> > instance of a db or does this have to be done with code?
> >
> > Thanks in advance!
> >

mirroring between two database

Hi,
i'm a novice of sql server and I have a problem.
I have to reply a server in which there are database that are managed with sql server 2000 sp3; what I must make in order to set up the mirroring between the database of the two server so that the data are always aligned ?

thanks for the attention
best regards

alessandroI am afraid I didn't understand your question. Are you referring to the Database Mirroring feature? This is a new feature in SQL Server 2005 (fully supported in SP1). Depending upon what you are trying to achieve (I don't understand that from your post), you have several technologies available in SQL Server 2000, but I don't want to point you in a direction without knowing what is your goal. Please let's know your requirement in more detail.|||Thanks for your answer.....
As soon as possible i give you an explanation of my problem (now i'm out of office)!

Regards
Alessandrosql

Mirroring between Standard and Enterprise editions...

Hi All,

We are going to use database mirroring and database snapshots for reporting. The principal server runs SQL Server 2005 Standard Edition and mirror (reporting) runs SQL Server 2005 Enterprise Edition (64-bit). I failed to setup database mirroring trough GUI using SQL Server Management Studio and got this error:

This mirroring configuration is not supported. Because the principal server instance, <server_name>, is Standard Edition, the mirror server instance must also be Standard Edition.

But when I use T-SQL commands I was able to setup database mirroring and it works OK so far.

SQL BOL says that one of requirements for database mirroring is to use the same SQL Server editions but I am wondering if it works trough T-SQL why MS mention it in the requirements list, also is totally unsupported by MS?

Thanks

Oleg

Oleg,

While this will probably work fine, we don't test this combination internally. If you mirror from a principal that is STD edition to a mirror that is EE edition, it would probably work without issues. Mirroring the other way (from EE to STD) could be problematic because of the EE features that are not present in STD. Since mirroring supports failover and failback, in theory it is possible for you to be in the situation where you are mirroring from EE->STD edition even if that is not the initial configuarion.

Regards,

Matt Hollingsworth

Program Manager, SQL Server High Availability

|||

Matt,

Thanks for your answer.

Our plan is to use database mirroring only for reporting and not for high availability. (We use replication and remote DC for HA). So mirroring from Standard to Enterprise Edition will be the only one scenario that we going to use option in our reporting environment.
The concern that we have is if we will be able to use mirroring between Standard and Enterprise in the future releases of SP2, SP3.

Thanks Oleg

|||

I am curious to why you want EE on the mirror server?

Only because the database snapshot feature?

Actually this is very similiar to what we need (use mirroring mostly for reports, not for HA) but since we don't have EE, we actually setup 2 STD instances on the mirror server

Server1 <- live server

Server2\Mirror <- mirrored, can be automatic fail over

Server2\Log <- log shipping, used for reports

Server3 <- witness and log shipping monitor server

Although I do recall seeing someone mention that we do not need a license if only using the mirror server in standby mode

Does that mean we can use EE with snapshot on Server2 without having to pay a license?

Thanks

|||

Yes we are going to use EE edition only for reporting since it doesn't require extra support efforts and also we can have a fair data refresh latency.
For the stand-by server you don't have to pay for licensing and it's valid for cluster or mirroring unless you also going to use it also for reporting.

tnx.

I'm closing this topic, thanks to all for answers and inputs............OH

|||

>> Although I do recall seeing someone mention that we do not need a license if only using the mirror server in standby mode

No, it doesn't. This only applies if you are using the 2nd server for failover only. By creating snapshots and running reporting off of it, it is no longer a failover only server.

Also, in order to cover both servers under 1 license, the license has to be valid for each server. So, you would have to run both under an Enterprise license, not a standard license.

Mirroring between Standard and Enterprise editions...

Hi All,

We are going to use database mirroring and database snapshots for reporting. The principal server runs SQL Server 2005 Standard Edition and mirror (reporting) runs SQL Server 2005 Enterprise Edition (64-bit). I failed to setup database mirroring trough GUI using SQL Server Management Studio and got this error:

This mirroring configuration is not supported. Because the principal server instance, <server_name>, is Standard Edition, the mirror server instance must also be Standard Edition.

But when I use T-SQL commands I was able to setup database mirroring and it works OK so far.

SQL BOL says that one of requirements for database mirroring is to use the same SQL Server editions but I am wondering if it works trough T-SQL why MS mention it in the requirements list, also is totally unsupported by MS?

Thanks

Oleg

Oleg,

While this will probably work fine, we don't test this combination internally. If you mirror from a principal that is STD edition to a mirror that is EE edition, it would probably work without issues. Mirroring the other way (from EE to STD) could be problematic because of the EE features that are not present in STD. Since mirroring supports failover and failback, in theory it is possible for you to be in the situation where you are mirroring from EE->STD edition even if that is not the initial configuarion.

Regards,

Matt Hollingsworth

Program Manager, SQL Server High Availability

|||

Matt,

Thanks for your answer.

Our plan is to use database mirroring only for reporting and not for high availability. (We use replication and remote DC for HA). So mirroring from Standard to Enterprise Edition will be the only one scenario that we going to use option in our reporting environment.
The concern that we have is if we will be able to use mirroring between Standard and Enterprise in the future releases of SP2, SP3.

Thanks Oleg

|||

I am curious to why you want EE on the mirror server?

Only because the database snapshot feature?

Actually this is very similiar to what we need (use mirroring mostly for reports, not for HA) but since we don't have EE, we actually setup 2 STD instances on the mirror server

Server1 <- live server

Server2\Mirror <- mirrored, can be automatic fail over

Server2\Log <- log shipping, used for reports

Server3 <- witness and log shipping monitor server

Although I do recall seeing someone mention that we do not need a license if only using the mirror server in standby mode

Does that mean we can use EE with snapshot on Server2 without having to pay a license?

Thanks

|||

Yes we are going to use EE edition only for reporting since it doesn't require extra support efforts and also we can have a fair data refresh latency.
For the stand-by server you don't have to pay for licensing and it's valid for cluster or mirroring unless you also going to use it also for reporting.

tnx.

I'm closing this topic, thanks to all for answers and inputs............OH

|||

>> Although I do recall seeing someone mention that we do not need a license if only using the mirror server in standby mode

No, it doesn't. This only applies if you are using the 2nd server for failover only. By creating snapshots and running reporting off of it, it is no longer a failover only server.

Also, in order to cover both servers under 1 license, the license has to be valid for each server. So, you would have to run both under an Enterprise license, not a standard license.

Mirroring between SP1 and SP2 for rolling upgrade

Is database mirroring compatible between SQL Server 2005 (Standard Edition) SP1 and SP2? I currently run SP1, and will shortly be introducing a failover partner. I'd like to have the failover partner installed as SP2, start the mirroring, manually failover, then upgrade the existing server to SP2 as well.

Is this a supported upgrade method, or will I need to upgrade the existing server to SP2 before setting up database mirroring? The SP2 installation instructions and readme file don't appear to mention mirroring at all (at least not that I can find).

Many thanks for any assistance.

Yes it will work, but for better practice can you mention what is reason for not applying SP2 on principal server?|||The intention is to upgrade the principal server to SP2 once the database has failed over to the mirror. This particular database server is constrained by SLAs for downtime (including scheduled downtime), so I would far prefer to failover to the new mirror running SP2, and then upgrade the existing server to SP2.|||

How to install service packs and hotfixes on an instance of SQL Server 2005 that is configured to use database mirroring

http://support.microsoft.com/kb/926824

Hope this helps
Vishal

Mirroring ASPState

Hello Does any one have any information on how to mirror the ASP.NET 2 ASPState database we have set the connection string to allow custom DB and failover partner but this is still not working.

Regards Matt.

Hi Matt,

Are you having problems with automatic failover not happening at all, or the ASP.NET connection does not get re-routed to the failover partner after auto-failover occurs?

Thanks,

Kaloian.

|||

Hi Kaloian,

The problem that we are having is that the ASP.NET connection does not get re-routed to the failover partner after auto-failover. This is the connection string that i am using.

<sessionState mode="SQLServer"

sqlConnectionString="data source=Test; Failover Partner=Test2; initial catalog=Credit; user id=State;password=password"

cookieless="AutoDetect"

timeout="61"

allowCustomSqlDatabase="true" />

The databses all failover fine but it seems that the ASP.NET connection is never looking for the failover partner it just errors giving a named pipes error. But they are enabled on both the primary and the failover partner.

Thanks Matt.

|||

I believe you are missing the Network parameter -->

Network=dbmssocn; for TCP/IP

so sqlConnectionString="data source=Test; Failover Partner=Test2; Network=dbmssocn; (etc)

Let us know if it works!

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/fd000cb1-9426-4ed9-8108-1907ddfd1c5f.htm