Possible to Connect Clients to Mirrored DB's (10.0)

0 views
Skip to first unread message

tuned

unread,
Dec 3, 2008, 8:35:01 AM12/3/08
to
We had our Dynamics db's on a clustered sql instance with clients connecting
to the instance name without an issue but were forced to go to new hardware
and a new implementation. We elected to use db mirroring with SQL 2005 and
the mirroring is set up fine and users are able to connect if we create an
ODBC connection to the primary db server.

This past week the roles were reversed (mirror became primary) so when
everyone attempted to connect they got error messages since they were then
trying to connect to the mirror db. I thought about creating 2 dns entries
(say greatplainserver) with each of the servers ip address's, but thinking
about it I don't think it would work since technically the server will be
responding. Is there a way to get Dynamics to connect properly to a mirrored
db implementation?

Mariano Gomez

unread,
Dec 3, 2008, 10:11:01 AM12/3/08
to
Tuned,

Help me understand a bit: when you say "the roles were reversed", do you
mean that you had an automatic failover to the mirror server? Do you have a
witness server in place? Let me rephrase the question, what operating mode is
your mirroring setup to support? High Availability? High Protection? High
Performance? If in High Availability, how does the failover occur (automatic,
manual)?

Another place to look is your connection. In SQL Server 2005, if you connect
to a database that is being mirrored with the SQL Native Client, GP can take
advantage of the drivers' ability to automatically redirect connections when
a database mirroring failover occurs. You must specify the initial principal
server and database in the connection string, and optionally the failover
partner server.

Best regards,
--
MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com
The Dynamics GP Blogster at http://dynamicsgpblogster.blogspot.com

tuned

unread,
Dec 3, 2008, 5:51:02 PM12/3/08
to
Mariano,

Thanks for replying. To answer your questions: when I said roles were
reversed I was indeed talking about an automatic failover to the mirror
server. We do have a witness server in place, though the roles didn't go
back to their initial roles even when the original primary server came back
online.

The databases are configured for high safety with automatic failover. The
SQL connection from the client is using the default SQL Server connection.
If we use the SQL Native Client would we need to modify code on the clients
themselves or would it be a setting to change when setting up the ODBC
connection? I notice if I choose SQL Native Client there is a provision for
a mirror server. Is that where I'd specify the mirror server? I've never
had to use connection strings for the application prior to this so I'm a
little knew to it.

Thanks,
Dave

Mariano Gomez

unread,
Dec 3, 2008, 8:50:00 PM12/3/08
to
There is no support for mirroring on the old SQL Server driver, hence you
will need to setup a new ODBC connection using the SQL Native Client. With
that said, I need to ask a few more questions.

When the failover occurred, what database became unavailable? The Dynamics
database? The company database? You cannot group databases and have them fail
over together. A database failover may occur automatically (with a witness
instance acting as a tie-breaking vote) or manually, but it only occurs one
database at a time. If the principal server remains intact, and only a single
mirrored database becomes unavailable on the principal server, then just the
one database will fail over in the automatic failover scenario.

The bottom line is that failover occurs at the single database level. There
is no built-in support for grouping mirrored databases so that they will fail
over together, either automatically or manually.

Now, suppose you are mirroring multiple databases from one server to another
and you manually fail over only one of the databases? Then as long as the
initial principal server and its other databases are still available, the
mirroring will now occur in the opposite direction.

The question is: which is the principal server, and which is the mirror
server? The answer is both are both! Both servers have both roles, whereas
the databases involved never have more than one role. This now explains why
both servers had the same role when the principal became available.

For database mirroring, the database roles are fixed, but the server or SQL
Server instance roles may be dual. A database is either a principal or
mirrored, but not both. However, a SQL Server instance can be a principal
server for one set of databases, and a mirror for another set.

tuned

unread,
Dec 4, 2008, 8:34:01 AM12/4/08
to
Mariano,

Thanks again for the input. To make sure we're on the same page lets call
the initial primary server A, the initial mirror B, and the witness C.

When the failover occured all the db's failed over from A to B. If I
modified the SQL driver to using B I was able to log into GP. I thought when
server A came back online the role of primary would be transferred back to it
but instead the db's on A remained as mirrors until I manually failed them
back over to it from B. Is that by design?

With regards to client connections do I simply have to remove the ODBC
connection using the SQL driver and recreate it with the SQL Native Client
and specify server B as the mirror server?

I'm curious as to how that would work with Dynamics since we only create a
link to the Dynamics db for users. Let say I correctly set the connection
for the user to either connect to A or B depending on the primary status.
Now if the Dynamics db fails over to B but the remaining db's stay on A and
they log into GP will they still be able to open other companies?

I'm new to mirroring so I apologize if these are silly questions.

Mariano Gomez

unread,
Dec 4, 2008, 9:16:02 AM12/4/08
to
>When the failover occured all the db's failed over from A to B<

Are you sure of this? Are you sure that the entire SQL Server (A) became
unavailable? For the server to become unavailable, the SQL Service would have
had to become unavailable (as in stopped). In this case, all the databases
selected for mirroring would have become available on server B once promoted
to principal.

>With regards to client connections do I simply have to remove the ODBC
connection using the SQL driver and recreate it with the SQL Native Client
and specify server B as the mirror server?<

Yes.



>I'm curious as to how that would work with Dynamics since we only create a
link to the Dynamics db for users.<

Dynamics connects to it's databases via a SQL Server ODBC connection, in
this case the one created with the SQL Server Native Client driver. Whatever
rules are specified in the driver are passed in to the GP runtime engine.

>I'm new to mirroring so I apologize if these are silly questions<

I am still learning myself, hence no question is silly. Silly would be not
to ask.

tuned

unread,
Dec 4, 2008, 3:37:00 PM12/4/08
to
All the db's failed over. The primary server was reboot to allow for a
backup agent to be installed on it.

Makes sense about the native client, will give it a try.

Thanks for understanding about the questions. Just trying to make this as
seamless a process as possible for my end users.

Reply all
Reply to author
Forward
0 new messages