Database Mirroring Question

I've read several instances that when implementing Database Mirroring, all
client connections are lost. A retry from client/s, applications, etc... will
reconnect to the Failover.
In order to compensate for the disconnect you should make use of TRY CATCH
logic within your code.
When going over that statement, TRY CATCH is not valid for disconnects so
how would you accomplish this feat?
What I mean is how could I allow my application to essentially reestablish
the coonection or look for the Failover from within my Connection String?
Many Thx,
Anthony E. Castro - MCDBA
Need to utilize FAILOVER PARTNER=<Mirror SQL server> in your connection string.
"acmcdba68" wrote:

> I've read several instances that when implementing Database Mirroring, all
> client connections are lost. A retry from client/s, applications, etc... will
> reconnect to the Failover.
> In order to compensate for the disconnect you should make use of TRY CATCH
> logic within your code.
> When going over that statement, TRY CATCH is not valid for disconnects so
> how would you accomplish this feat?
> What I mean is how could I allow my application to essentially reestablish
> the coonection or look for the Failover from within my Connection String?
> Many Thx,
> --
> Anthony E. Castro - MCDBA

Database Mirroring hangs on ALTER DATABASE SET PARTNER

Hi,

Here is the scenario. I currently have a single XP Professional machine with three SQL Server instances installed. I'm trying to set up a database mirroring session.

The mirror instance was created with an endpoint listening on port 5151. The principal instance endpoint listens on port 5150. The witness listens on port 5152.

After creating the endpoints, I back up the database I wish to mirror on the principal instance, and then restore with NORECOVERY on the mirror instance.

I then execute the following code on the mirror instance:

ALTER DATABASE BookStore

SET PARTNER = 'TCP://192.168.0.2:5151'

GO

This works fine. I then go to the principal to add it as a partner too:

ALTER DATABASE BookStore

SET PARTNER = 'TCP://192.168.0.2:5150'

GO

The query runs indefinitely. I finally stop it after several minutes, and I see in the object browser that the database has the following in parenthesis "(Principal, Synchronizing)". I then cannot drop, modify, or do anything with the principal database.

Ideas?

hmmmm....

the first ALTER DATABASE should always work (as long as the db has been restored with NORECOVERY) because it puts the database in a "waiting to be contacted" mode.

you should be using the FQDN instead of the IP. i think you are getting away with that because you are on the same server.

your port mappings are not right. if the mirror is listening on 5151, then the principal should be issuing its ALTER DATABASE with the port 5151 on it, not 5150 as you have indicated.

thanks,
mark|||Thanks for the response, Mark.

I went ahead and tried switching the ALTER DATABASE to point to the mirror port, and get the following message:

Msg 1418, Level 16, State 1, Line 3

The server instance "TCP://192.168.0.2:5022" is not running or does not exist. Check the name and reissue the command.

You said I should be on FQDN (but unfortunately this the only machine where I can test this - and it isn't on a domain). What's more, I used the GUI to set up mirroring, and it worked.

Right now my goal is to see if I can get it to work using just Transact-SQL . (I thought to capture what SQL Server was doing via the Wizard with Profiler, but I'm getting an "Cannot retrieve trace definition for SQL Server version
9.0.1187." error on that).

Thanks for any thoughts you may have on this...

|||

we aren't testing using the direct TCP-IP address for setting up database mirroring, so you need to use the FQDN.

there is a section on troubleshooting database mirroring in BOL specifically designed for this error message. please look at that.

specifically,
1. for the FQDN use the output described in the troubleshooting giude to use the output from ipconfig to set the name.
2. you should be able to run sqlservr.exe as LocalSystem as long as the instances are on the same machine.

mark

|||Thank you Mark.|||

I use this script to setup test mirrored db

-- On Principal
IF NOT EXISTS(SELECT * FROM sys.endpoints WHERE type = 4)
CREATE ENDPOINT DBMirroring STATE=STARTED AS TCP (LISTENER_PORT = 5022) FOR DATABASE_MIRRORING (ROLE = ALL)

create database testMirror
--sp_helpdb testmirror

alter database testmirror
modify file (name=testmirror_log, size=1,maxsize=2,filegrowth=1)

alter database testmirror
modify file (name=testmirror, Maxsize=3,filegrowth=2)

alter database testMirror set recovery full

-- We use SQL lite speed if you don't have then you are missing out, however you can use SQL backup

exec master..xp_backup_database @.database='testMirror', @.filename='\\SERVERA\sqlbak\testMirror.lsb',@.init=1
exec master..xp_backup_log @.database='testMirror', @.filename='\\SERVERA\\sqlbak\testMirror_log.lsb',@.init=1

-- On mirror
IF NOT EXISTS(SELECT * FROM sys.endpoints WHERE type = 4)
CREATE ENDPOINT DBMirroring STATE=STARTED AS TCP (LISTENER_PORT = 5022) FOR DATABASE_MIRRORING (ROLE = ALL)

exec master..xp_restore_database @.database='testMirror', @.filename='\\SERVERA\sqlbak\testMirror.lsb',@.With='NoRecovery'

exec master..xp_restore_log @.database='testMirror', @.filename='\\SERVERA\sqlbak\testMirror_log.lsb',@.With='NoRecovery'
ALTER DATABASE testMirror SET PARTNER = 'TCP://SERVERA:5022'

-- On principal
ALTER DATABASE testMirror SET PARTNER = 'TCP://SERVERB:5022'

-- testing failing over, run on principal
ALTER DATABASE testMirror SET PARTNER FAILOVER

-- once testing is completed, break the mirror and drop the database
ALTER DATABASE testMirror SET PARTNER off
drop database testMirror

--NOTE: I have set this up not using FQDN and it works OK, also this script is for between servers

-- I have tried setting this up on a VISTA machine running 2 instances of SQL (dev edition) however I can get mirroring to work YET, I think I'm hitting a firewall issue (could be wrong), my ports are open correctly and I can see then via netstat - a

-- I'm getting errors like The Network address XYZ can't be reached or does not exist

-- Oh The joys of firewalls and ports....

Database Mirroring hangs on ALTER DATABASE SET PARTNER

Hi,

Here is the scenario. I currently have a single XP Professional machine with three SQL Server instances installed. I'm trying to set up a database mirroring session.

The mirror instance was created with an endpoint listening on port 5151. The principal instance endpoint listens on port 5150. The witness listens on port 5152.

After creating the endpoints, I back up the database I wish to mirror on the principal instance, and then restore with NORECOVERY on the mirror instance.

I then execute the following code on the mirror instance:

ALTER DATABASE BookStore

SET PARTNER = 'TCP://192.168.0.2:5151'

GO

This works fine. I then go to the principal to add it as a partner too:

ALTER DATABASE BookStore

SET PARTNER = 'TCP://192.168.0.2:5150'

GO

The query runs indefinitely. I finally stop it after several minutes, and I see in the object browser that the database has the following in parenthesis "(Principal, Synchronizing)". I then cannot drop, modify, or do anything with the principal database.

Ideas?

hmmmm....

the first ALTER DATABASE should always work (as long as the db has been restored with NORECOVERY) because it puts the database in a "waiting to be contacted" mode.

you should be using the FQDN instead of the IP. i think you are getting away with that because you are on the same server.

your port mappings are not right. if the mirror is listening on 5151, then the principal should be issuing its ALTER DATABASE with the port 5151 on it, not 5150 as you have indicated.

thanks,
mark|||Thanks for the response, Mark.

I went ahead and tried switching the ALTER DATABASE to point to the mirror port, and get the following message:

Msg 1418, Level 16, State 1, Line 3

The server instance "TCP://192.168.0.2:5022" is not running or does not exist. Check the name and reissue the command.

You said I should be on FQDN (but unfortunately this the only machine where I can test this - and it isn't on a domain). What's more, I used the GUI to set up mirroring, and it worked.

Right now my goal is to see if I can get it to work using just Transact-SQL . (I thought to capture what SQL Server was doing via the Wizard with Profiler, but I'm getting an "Cannot retrieve trace definition for SQL Server version
9.0.1187." error on that).

Thanks for any thoughts you may have on this...

|||

we aren't testing using the direct TCP-IP address for setting up database mirroring, so you need to use the FQDN.

there is a section on troubleshooting database mirroring in BOL specifically designed for this error message. please look at that.

specifically,
1. for the FQDN use the output described in the troubleshooting giude to use the output from ipconfig to set the name.
2. you should be able to run sqlservr.exe as LocalSystem as long as the instances are on the same machine.

mark

|||Thank you Mark.|||

I use this script to setup test mirrored db

-- On Principal
IF NOT EXISTS(SELECT * FROM sys.endpoints WHERE type = 4)
CREATE ENDPOINT DBMirroring STATE=STARTED AS TCP (LISTENER_PORT = 5022) FOR DATABASE_MIRRORING (ROLE = ALL)

create database testMirror
--sp_helpdb testmirror

alter database testmirror
modify file (name=testmirror_log, size=1,maxsize=2,filegrowth=1)

alter database testmirror
modify file (name=testmirror, Maxsize=3,filegrowth=2)

alter database testMirror set recovery full

-- We use SQL lite speed if you don't have then you are missing out, however you can use SQL backup

exec master..xp_backup_database @.database='testMirror', @.filename='\\SERVERA\sqlbak\testMirror.lsb',@.init=1
exec master..xp_backup_log @.database='testMirror', @.filename='\\SERVERA\\sqlbak\testMirror_log.lsb',@.init=1

-- On mirror
IF NOT EXISTS(SELECT * FROM sys.endpoints WHERE type = 4)
CREATE ENDPOINT DBMirroring STATE=STARTED AS TCP (LISTENER_PORT = 5022) FOR DATABASE_MIRRORING (ROLE = ALL)

exec master..xp_restore_database @.database='testMirror', @.filename='\\SERVERA\sqlbak\testMirror.lsb',@.With='NoRecovery'

exec master..xp_restore_log @.database='testMirror', @.filename='\\SERVERA\sqlbak\testMirror_log.lsb',@.With='NoRecovery'
ALTER DATABASE testMirror SET PARTNER = 'TCP://SERVERA:5022'

-- On principal
ALTER DATABASE testMirror SET PARTNER = 'TCP://SERVERB:5022'

-- testing failing over, run on principal
ALTER DATABASE testMirror SET PARTNER FAILOVER

-- once testing is completed, break the mirror and drop the database
ALTER DATABASE testMirror SET PARTNER off
drop database testMirror

--NOTE: I have set this up not using FQDN and it works OK, also this script is for between servers

-- I have tried setting this up on a VISTA machine running 2 instances of SQL (dev edition) however I can get mirroring to work YET, I think I'm hitting a firewall issue (could be wrong), my ports are open correctly and I can see then via netstat - a

-- I'm getting errors like The Network address XYZ can't be reached or does not exist

-- Oh The joys of firewalls and ports....

Database Mirroring Connection Question

I've read several instances that when implementing Database Mirroring and a
fail-over
occurs, all client connections are lost. A retry from client/s,
applications, etc... will
reconnect to the Failover.
In order to compensate for the disconnect you should make use of TRY CATCH
logic within your code.
When going over that statement, TRY CATCH is not valid for disconnects so
how would you accomplish this feat?
What I mean is how could I allow my application to essentially reestablish
the coonection or look for the Failover from within my Connection String?
--
Anthony E. Castro - MCDBADoesn't the disconnect raise a SqlException that could be caught and you
resubmit the failed request? Have you tried this already?
In case you are using ADO.Net 2.0 or later, you have a 'failover
partner' parameter in the connection string that SqlClient will use to
connect to if there was loss of connectivity to the primary server.
So, all your application needs to do is resubmit the request - whichever
database - principal or the new principal - that SqlClient is able to
connect to will be used.
acmcdba68 wrote:
> I've read several instances that when implementing Database Mirroring and a
> fail-over
> occurs, all client connections are lost. A retry from client/s,
> applications, etc... will
> reconnect to the Failover.
> In order to compensate for the disconnect you should make use of TRY CATCH
> logic within your code.
> When going over that statement, TRY CATCH is not valid for disconnects so
> how would you accomplish this feat?
> What I mean is how could I allow my application to essentially reestablish
> the coonection or look for the Failover from within my Connection String?
>

Database Maintenance Plan Issue with non-default instances

I have a SQL Server (2000 sp3) with one default instance and many named
instances. I have configured, tested, and monitored a database maintenance
plan for the default instance - works fine. I can't seem to get any of the
default instances to perform the maintenance plan. I have examined the logs,
but I don't see any any relevant entries (I just tried to kick off the job
manaully by executing the associated SQL job). I do see one message in the
log across all of the named instances I tried to run the maintenance plan
against, "Using 'xpsqlbot.dll' version '2000.80.194' to execute extended
stored procedure 'xp_qv'". I have googled this message, but I came up empty.
I should also note that the maintenance plan isn't even writing the history
report.
The only difference I'm aware of is that I performed a 'minimum install' on
the named instances. My transaction logs are growing and growing. The only
way I know to keep the size of these in check is to back them up. Please help
if you can.
Thank you,
- Johnny
Hi
It is not clear how you are running this maintenance plan, and if you
created a different plan in the other instances (which if you did will
probably have a different Ids).
You may want to use create a job that calls xp_slqmaint and specifies the
database and options separately rather than in a plan.
John
"JohnnyMagz" wrote:

> I have a SQL Server (2000 sp3) with one default instance and many named
> instances. I have configured, tested, and monitored a database maintenance
> plan for the default instance - works fine. I can't seem to get any of the
> default instances to perform the maintenance plan. I have examined the logs,
> but I don't see any any relevant entries (I just tried to kick off the job
> manaully by executing the associated SQL job). I do see one message in the
> log across all of the named instances I tried to run the maintenance plan
> against, "Using 'xpsqlbot.dll' version '2000.80.194' to execute extended
> stored procedure 'xp_qv'". I have googled this message, but I came up empty.
> I should also note that the maintenance plan isn't even writing the history
> report.
> The only difference I'm aware of is that I performed a 'minimum install' on
> the named instances. My transaction logs are growing and growing. The only
> way I know to keep the size of these in check is to back them up. Please help
> if you can.
> Thank you,
> - Johnny

Database Maintenance Plan Issue with non-default instances

I have a SQL Server (2000 sp3) with one default instance and many named
instances. I have configured, tested, and monitored a database maintenance
plan for the default instance - works fine. I can't seem to get any of the
default instances to perform the maintenance plan. I have examined the logs,
but I don't see any any relevant entries (I just tried to kick off the job
manaully by executing the associated SQL job). I do see one message in the
log across all of the named instances I tried to run the maintenance plan
against, "Using 'xpsqlbot.dll' version '2000.80.194' to execute extended
stored procedure 'xp_qv'". I have googled this message, but I came up empty.
I should also note that the maintenance plan isn't even writing the history
report.
The only difference I'm aware of is that I performed a 'minimum install' on
the named instances. My transaction logs are growing and growing. The only
way I know to keep the size of these in check is to back them up. Please help
if you can.
Thank you,
- JohnnyHi
It is not clear how you are running this maintenance plan, and if you
created a different plan in the other instances (which if you did will
probably have a different Ids).
You may want to use create a job that calls xp_slqmaint and specifies the
database and options separately rather than in a plan.
John
"JohnnyMagz" wrote:
> I have a SQL Server (2000 sp3) with one default instance and many named
> instances. I have configured, tested, and monitored a database maintenance
> plan for the default instance - works fine. I can't seem to get any of the
> default instances to perform the maintenance plan. I have examined the logs,
> but I don't see any any relevant entries (I just tried to kick off the job
> manaully by executing the associated SQL job). I do see one message in the
> log across all of the named instances I tried to run the maintenance plan
> against, "Using 'xpsqlbot.dll' version '2000.80.194' to execute extended
> stored procedure 'xp_qv'". I have googled this message, but I came up empty.
> I should also note that the maintenance plan isn't even writing the history
> report.
> The only difference I'm aware of is that I performed a 'minimum install' on
> the named instances. My transaction logs are growing and growing. The only
> way I know to keep the size of these in check is to back them up. Please help
> if you can.
> Thank you,
> - Johnny

Database Maintenance Plan Issue with non-default instances

I have a SQL Server (2000 sp3) with one default instance and many named
instances. I have configured, tested, and monitored a database maintenance
plan for the default instance - works fine. I can't seem to get any of the
default instances to perform the maintenance plan. I have examined the logs,
but I don't see any any relevant entries (I just tried to kick off the job
manaully by executing the associated SQL job). I do see one message in the
log across all of the named instances I tried to run the maintenance plan
against, "Using 'xpsqlbot.dll' version '2000.80.194' to execute extended
stored procedure 'xp_qv'". I have googled this message, but I came up empty.
I should also note that the maintenance plan isn't even writing the history
report.
The only difference I'm aware of is that I performed a 'minimum install' on
the named instances. My transaction logs are growing and growing. The only
way I know to keep the size of these in check is to back them up. Please hel
p
if you can.
Thank you,
- JohnnyHi
It is not clear how you are running this maintenance plan, and if you
created a different plan in the other instances (which if you did will
probably have a different Ids).
You may want to use create a job that calls xp_slqmaint and specifies the
database and options separately rather than in a plan.
John
"JohnnyMagz" wrote:

> I have a SQL Server (2000 sp3) with one default instance and many named
> instances. I have configured, tested, and monitored a database maintenance
> plan for the default instance - works fine. I can't seem to get any of the
> default instances to perform the maintenance plan. I have examined the log
s,
> but I don't see any any relevant entries (I just tried to kick off the jo
b
> manaully by executing the associated SQL job). I do see one message in the
> log across all of the named instances I tried to run the maintenance plan
> against, "Using 'xpsqlbot.dll' version '2000.80.194' to execute extended
> stored procedure 'xp_qv'". I have googled this message, but I came up empt
y.
> I should also note that the maintenance plan isn't even writing the histor
y
> report.
> The only difference I'm aware of is that I performed a 'minimum install' o
n
> the named instances. My transaction logs are growing and growing. The only
> way I know to keep the size of these in check is to back them up. Please h
elp
> if you can.
> Thank you,
> - Johnny