November 24, 2008 at 5:50 am
Hi all
At beginning of this year i see a presentation for sql server 2008 and was tell the database mirroring in sql 2008 does not need to change the connection string in applications to automatically failover(using witness..etc..etc). In 2005 we need to add a partner parameter in applications connections strings.
is this correct ?..i dont see nothing about this...
Thanks all
November 29, 2008 at 12:45 am
Yes this is true. A client machine connects to the principal server and the principal server returns the details of the mirrored server which I believe is then stored in memory. If subsequent calls to the principal fail because it is down the client will contact the mirrored server.
However if the principal is down on the first connection it will not retrieve the details of the mirrored site and no connection will be made.
November 30, 2008 at 5:35 am
Thanks DNA..
So i dont need to change anything in cliente connection string if i using mirroring in sql 2008 ?....do you have any formal doccument or KB about this ?.. I need to do a solution based in this feature and our principal problem are the "third part" or other suppliers because they have to change the connections string of their aplicattions and to do this is very complicated for them.
Thanks DNA.....
November 30, 2008 at 7:25 am
You don't have to specify a mirror, but you can. It was the same in SQL 2005. No changes in that area.
Books Online SQL 2005:
The data-access provider, whether SQL Native Client or ADO.NET Data Provider, stores both an initial partner name and a failover partner name (when it is available). These names are stored in a cache in the client's volatile memory (for managed code, the cache is scoped to the the application domain). Minimally, a connection string supplies a server name for the initial connection attempt. For a mirroring connection, this name should identify the current principal server. This name is known as the initial partner name. The data-access provider caches the initial partner name supplied in a connection string.
The failover partner name is obtained in one of two ways: from the connection string or by downloading it from the principal server at the time of the initial connection. On a first connection request by a client for a server, the data-access provider caches the server name as the initial partner name. Once cached, the initial partner name is not updated by the data-access provider.
Optionally, the client might also supply a failover partner name that identifies the mirror server. The data-access provider keeps this client-supplied name only until it connects to the principal server. If the connection succeeds, the data-access provider downloads the name of the current mirror server, which overwrites the client-supplied name.
Books Online SQL 2008:
For the initial connection to a mirrored database, a client must supply a connection string that minimally supplies the name of a server instance. This required server name should identify the current principal server instance and is known as the initial partner name.
Optionally, the connection string can also supply the name of another server instance, which should identify the current mirror server instance, for use if the initial partner is unavailable during the first connection attempt. The second name is known as the failover partner name.
The thing is, the client must be using either SNAC or an up-to-date ADO.NET Data Provider. If they're using somethign else, the client won't know about the failover.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 30, 2008 at 4:12 pm
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply