Cluster Upgrade SP2 2005

Have recently performed an in-place upgrade of a 2 instance sql cluster from 2000 to 2005. The upgrade process went relatively smoothly. However, I am running into problems installed sp2. The first problem that I run into is that I have resrcmon.exe locked. I can click next, which is fine, but once I get past that point I get stuck during the installation process. I am updating DatabaseServices and Integration Services. It gets stuck on 'Awaiting first complete passive cluster node SQL-Test'. Does anyone have any ideas?
Thanks,
TimFor what it's worth I'm experiencing the same issue as described above. I'm using VMWare Server and have a Windows 2003 Enterprise Edition cluster with SQL Server 2005 Standard Edition clustered on top. This works well but in trying to apply SP2 it is stuck "Awaiting first complete passive cluster node". Slight difference from that described above, I had no locked files.

Any info that anyone could provide would be greatly appreciated.

Cheers,

Ian
|||Unbelievable. Typically just as I finish writing the above, the process moved on. However it failed at the "Database Services" product. Everything else seemed to go okay but I'll have to check and will come back with any info I find.

Cheers,

Ian
|||I got my issue resolved...after talking to MS Support for half of a day. There are a lot of things that you have to make sure is just right. For example, when you install the services, it is easier to have the first node control both instances. From there, make sure you install the client tools on the default instance first. You also cannot have remote connections to the 2nd node in the cluster (will cause it to fail). Mine failed on installed the database services as well. Another thing we had to change was that when the network guys setup the cluster, the distributed transaction coordinator wasn't created as a clustered resource...it needs to be. let me know if this helps any.
Tim|||Tim,

Did you manage to continue with resrcmon.exe locked or did you find a way to stop it (it's part of the cluster so, short of stopping the cluster services I wasn't sure how else to stop it)?

We've got the DTC in place in our production cluster but I forgot to add it with my virtual one. I've done this now and will give it another go shortly.

Cheers,

Ian
|||At first I seen that resrcmon.exe was locked, and it did concern me. However, I don't think that it really matters if it is locked or not. If it is locked, you can still go through with the install, but it will prompt you to reboot later in the process. Try to kill all remote connections to either cluster node, place both instances on one node, and try to install that way. That will likely unlock resrcmon.|||I actually only have one instance, it is on the node from which I'm running the SP. WRT remote connections, I can't see anything apart from the clustering services that would be connecting remotely to the second node.

That said, I've only got resrcmon.exe locked now so am gonna proceed with the SP and see where I get - beauty of VMs is that I can just roll 'em back any time I like :-)
|||

I had the exact same issue. Installing the client tools on both nodes, and then moving the inst1 over to the passive node and running the SP2 from there seemed to do the trick.

|||I've finally had some level of success with this on my virtual cluster. But it was kinda forceful. I basically downed node 2, installed the SP onto node 1 (twice as it had "reboot required" on the SQL Server engine the first time). Then brought up node 2, failed over and downed node 1. Did the same to node 2 (twice) and brought node 1 back up. Testing seems to confirm this has gone okay. However, as we're in the process of trying to get a test cluster (for exactly this kind of thing - good as VMWare is, it's not the real thing) I'm not going to apply this to production until that's happened.

Cheers,

Ian
|||

Hi Ian,

Please help - when you downed the one node - was it shutdown or did you stop cluster service on the node or did you pause the node?

Thanks in advance :-)

|||

Hi there,

Basically I did the following.

Node 1 was up, node 2 was shutdown completely. I upgraded node 1 to SP2, then brought node 2 back online and failed over by shutting down node 1. I then upgraded node 2 to SP2, brought node 1 back online, failed over manually to confirm all as expected and left it at that. Bear in mind this was on virtual machines so is not a perfect test. I've yet to do this on our production cluster and am currently trying to get a proper physical test cluster in place to run through this for real.

Hope this helps!

Cheers,

Ian

Cluster Upgrade SP2 2005

Have recently performed an in-place upgrade of a 2 instance sql cluster from 2000 to 2005. The upgrade process went relatively smoothly. However, I am running into problems installed sp2. The first problem that I run into is that I have resrcmon.exe locked. I can click next, which is fine, but once I get past that point I get stuck during the installation process. I am updating DatabaseServices and Integration Services. It gets stuck on 'Awaiting first complete passive cluster node SQL-Test'. Does anyone have any ideas?
Thanks,
TimFor what it's worth I'm experiencing the same issue as described above. I'm using VMWare Server and have a Windows 2003 Enterprise Edition cluster with SQL Server 2005 Standard Edition clustered on top. This works well but in trying to apply SP2 it is stuck "Awaiting first complete passive cluster node". Slight difference from that described above, I had no locked files.

Any info that anyone could provide would be greatly appreciated.

Cheers,

Ian
|||Unbelievable. Typically just as I finish writing the above, the process moved on. However it failed at the "Database Services" product. Everything else seemed to go okay but I'll have to check and will come back with any info I find.

Cheers,

Ian
|||I got my issue resolved...after talking to MS Support for half of a day. There are a lot of things that you have to make sure is just right. For example, when you install the services, it is easier to have the first node control both instances. From there, make sure you install the client tools on the default instance first. You also cannot have remote connections to the 2nd node in the cluster (will cause it to fail). Mine failed on installed the database services as well. Another thing we had to change was that when the network guys setup the cluster, the distributed transaction coordinator wasn't created as a clustered resource...it needs to be. let me know if this helps any.
Tim|||Tim,

Did you manage to continue with resrcmon.exe locked or did you find a way to stop it (it's part of the cluster so, short of stopping the cluster services I wasn't sure how else to stop it)?

We've got the DTC in place in our production cluster but I forgot to add it with my virtual one. I've done this now and will give it another go shortly.

Cheers,

Ian
|||At first I seen that resrcmon.exe was locked, and it did concern me. However, I don't think that it really matters if it is locked or not. If it is locked, you can still go through with the install, but it will prompt you to reboot later in the process. Try to kill all remote connections to either cluster node, place both instances on one node, and try to install that way. That will likely unlock resrcmon.|||I actually only have one instance, it is on the node from which I'm running the SP. WRT remote connections, I can't see anything apart from the clustering services that would be connecting remotely to the second node.

That said, I've only got resrcmon.exe locked now so am gonna proceed with the SP and see where I get - beauty of VMs is that I can just roll 'em back any time I like :-)
|||

I had the exact same issue. Installing the client tools on both nodes, and then moving the inst1 over to the passive node and running the SP2 from there seemed to do the trick.

|||I've finally had some level of success with this on my virtual cluster. But it was kinda forceful. I basically downed node 2, installed the SP onto node 1 (twice as it had "reboot required" on the SQL Server engine the first time). Then brought up node 2, failed over and downed node 1. Did the same to node 2 (twice) and brought node 1 back up. Testing seems to confirm this has gone okay. However, as we're in the process of trying to get a test cluster (for exactly this kind of thing - good as VMWare is, it's not the real thing) I'm not going to apply this to production until that's happened.

Cheers,

Ian
|||

Hi Ian,

Please help - when you downed the one node - was it shutdown or did you stop cluster service on the node or did you pause the node?

Thanks in advance :-)

|||

Hi there,

Basically I did the following.

Node 1 was up, node 2 was shutdown completely. I upgraded node 1 to SP2, then brought node 2 back online and failed over by shutting down node 1. I then upgraded node 2 to SP2, brought node 1 back online, failed over manually to confirm all as expected and left it at that. Bear in mind this was on virtual machines so is not a perfect test. I've yet to do this on our production cluster and am currently trying to get a proper physical test cluster in place to run through this for real.

Hope this helps!

Cheers,

Ian

sqlsql

Cluster Upgrade SP2 2005

Have recently performed an in-place upgrade of a 2 instance sql cluster from 2000 to 2005. The upgrade process went relatively smoothly. However, I am running into problems installed sp2. The first problem that I run into is that I have resrcmon.exe locked. I can click next, which is fine, but once I get past that point I get stuck during the installation process. I am updating DatabaseServices and Integration Services. It gets stuck on 'Awaiting first complete passive cluster node SQL-Test'. Does anyone have any ideas?
Thanks,
TimFor what it's worth I'm experiencing the same issue as described above. I'm using VMWare Server and have a Windows 2003 Enterprise Edition cluster with SQL Server 2005 Standard Edition clustered on top. This works well but in trying to apply SP2 it is stuck "Awaiting first complete passive cluster node". Slight difference from that described above, I had no locked files.

Any info that anyone could provide would be greatly appreciated.

Cheers,

Ian
|||Unbelievable. Typically just as I finish writing the above, the process moved on. However it failed at the "Database Services" product. Everything else seemed to go okay but I'll have to check and will come back with any info I find.

Cheers,

Ian
|||I got my issue resolved...after talking to MS Support for half of a day. There are a lot of things that you have to make sure is just right. For example, when you install the services, it is easier to have the first node control both instances. From there, make sure you install the client tools on the default instance first. You also cannot have remote connections to the 2nd node in the cluster (will cause it to fail). Mine failed on installed the database services as well. Another thing we had to change was that when the network guys setup the cluster, the distributed transaction coordinator wasn't created as a clustered resource...it needs to be. let me know if this helps any.
Tim|||Tim,

Did you manage to continue with resrcmon.exe locked or did you find a way to stop it (it's part of the cluster so, short of stopping the cluster services I wasn't sure how else to stop it)?

We've got the DTC in place in our production cluster but I forgot to add it with my virtual one. I've done this now and will give it another go shortly.

Cheers,

Ian
|||At first I seen that resrcmon.exe was locked, and it did concern me. However, I don't think that it really matters if it is locked or not. If it is locked, you can still go through with the install, but it will prompt you to reboot later in the process. Try to kill all remote connections to either cluster node, place both instances on one node, and try to install that way. That will likely unlock resrcmon.|||I actually only have one instance, it is on the node from which I'm running the SP. WRT remote connections, I can't see anything apart from the clustering services that would be connecting remotely to the second node.

That said, I've only got resrcmon.exe locked now so am gonna proceed with the SP and see where I get - beauty of VMs is that I can just roll 'em back any time I like :-)
|||

I had the exact same issue. Installing the client tools on both nodes, and then moving the inst1 over to the passive node and running the SP2 from there seemed to do the trick.

|||I've finally had some level of success with this on my virtual cluster. But it was kinda forceful. I basically downed node 2, installed the SP onto node 1 (twice as it had "reboot required" on the SQL Server engine the first time). Then brought up node 2, failed over and downed node 1. Did the same to node 2 (twice) and brought node 1 back up. Testing seems to confirm this has gone okay. However, as we're in the process of trying to get a test cluster (for exactly this kind of thing - good as VMWare is, it's not the real thing) I'm not going to apply this to production until that's happened.

Cheers,

Ian
|||

Hi Ian,

Please help - when you downed the one node - was it shutdown or did you stop cluster service on the node or did you pause the node?

Thanks in advance :-)

|||

Hi there,

Basically I did the following.

Node 1 was up, node 2 was shutdown completely. I upgraded node 1 to SP2, then brought node 2 back online and failed over by shutting down node 1. I then upgraded node 2 to SP2, brought node 1 back online, failed over manually to confirm all as expected and left it at that. Bear in mind this was on virtual machines so is not a perfect test. I've yet to do this on our production cluster and am currently trying to get a proper physical test cluster in place to run through this for real.

Hope this helps!

Cheers,

Ian

Cluster SQL Active-Active

My friends:
I need document information about the installation process of SQL in Cluster
with two instances, both active, one in each Server. In summary I want to
install a Cluster of SQL Active-Active, for take greater benefit of both
Cluster nodes.
Even though I have looked for in BOL (follow the recommendations of Maxi), I
had investigate and knock the same trouble, there are only information about
the Active-Pasive installation, and I'll wish a procedure or document about
Active-Active setup and installation.
If someone knows where I can find the information, or somebody understand
about Active-Active installation, please give it me know. Thanks a lot.
Regards,
Carlo Sorrel
Hello Carlo,
Once you have installed the first instance of virtual SQL Server 2000 then the steps for installing the second instance of virtual SQL Server 2000 is exactly the same. The second virtual instance of SQL Server 2000
will need a seperate shared drive (you cannot use the shared drive that you used for the first instance), unique SQL IP Address (again you cannot use the one you used for your windows cluster ip or the first sql
server ip) and unique SQL network name. Also, if the first virtual instance is a default instance then the second instance can ONLY be a named instance. You CANNOT have two default virtual SQL Server 2000
instance on the same cluster. To have both the instances similar, many customers install both instances of virtual SQL Server 2000 as named instances.
Since you have multiple instances (2 in your case), you will need to ensure that all server nodes have the same resources (processor, memory) and it is enough to handle the instances that could potentially fail to
that node. Another important consideration is to cap memory usage of the instance of SQL Server 2000 with max server memory. Especially if AWE memory is enabled, max server memory must be set in a
multiple-instance cluster to prevent starving the server node.
If you have a Windows Server 2003 EE cluster then you may find the following webcast useful
TechNet Support WebCast: How to install a Microsoft SQL Server 2000 virtual server on a Microsoft Windows Server 2003 cluster
Discusses how to install Microsoft SQL Server 2000 clustering on Microsoft Windows Server 2003 and differences between Microsoft Windows 2000 Server and Windows Server 2003.
http://support.microsoft.com/default...b;en-us;888121
For Win2K Adv Server cluster, you may find the following whitepaper useful
SQL Server 2000 Failover Clustering
http://www.microsoft.com/technet/pro.../failclus.mspx
Review the above and feel free to post any other qs that you may have.
Additional Information
=======================
Here is some other related links that you may find useful
INF: Clustered SQL Server Do's, Don'ts, and Basic Warnings
http://support.microsoft.com/?kbid=254321
Introduction to Microsoft SQL Server 2000 Clustering
http://support.microsoft.com/default...lurb051001.asp
Microsoft SQL Server 2000 Virtual Server: Things You Should Know
http://support.microsoft.com/default...lurb032602.asp
Microsoft SQL Server 2000 Virtual Server Basic Setup, Maintenance, and Service Pack http://support.microsoft.com/default...lurb061002.asp
Microsoft SQL Server 2000 Failover Clustering Disaster Recovery Procedures
http://support.microsoft.com/default...lurb101802.asp
Troubleshooting SQL 2000 Virtual Server and Service Pack Setups for Failover Clustering
http://support.microsoft.com/default...lurb020703.asp
HTH,
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
|||Hi
Just be aware, Active-Active does not mean load balancing. It means 2
instances of different databases, each running by default on one of the
nodes of a cluster.
The references Uttam has supplied are great and will help you along.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Carlo Sorrel" <csorrel@.hotmail.com> wrote in message
news:#nPFJo7AFHA.3576@.TK2MSFTNGP11.phx.gbl...
> My friends:
>
> I need document information about the installation process of SQL in
Cluster
> with two instances, both active, one in each Server. In summary I want to
> install a Cluster of SQL Active-Active, for take greater benefit of both
> Cluster nodes.
>
> Even though I have looked for in BOL (follow the recommendations of Maxi),
I
> had investigate and knock the same trouble, there are only information
about
> the Active-Pasive installation, and I'll wish a procedure or document
about
> Active-Active setup and installation.
>
> If someone knows where I can find the information, or somebody understand
> about Active-Active installation, please give it me know. Thanks a lot.
>
> Regards,
>
> Carlo Sorrel
>
|||first, sorry my english..., apears this error during instalattion the second instance on Cluster Windows 2003.
The description for Event ID ( 17052 ) in Source ( MSSQL$LASCAR ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: [sqsrvres] checkODBCConnectError: sqlstate = 01000; native error = 35; message = [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
Tahnk's.
Carlo Sorrel.
"Uttam Parui[MS]" <uttamkp@.online.microsoft.com> escribi en el mensaje news:uX597q8AFHA.1680@.cpmsftngxa10.phx.gbl...
> Hello Carlo,
> Once you have installed the first instance of virtual SQL Server 2000 then the steps for installing the second instance of virtual SQL Server 2000 is exactly the same. The second virtual instance of SQL Server 2000
> will need a seperate shared drive (you cannot use the shared drive that you used for the first instance), unique SQL IP Address (again you cannot use the one you used for your windows cluster ip or the first sql
> server ip) and unique SQL network name. Also, if the first virtual instance is a default instance then the second instance can ONLY be a named instance. You CANNOT have two default virtual SQL Server 2000
> instance on the same cluster. To have both the instances similar, many customers install both instances of virtual SQL Server 2000 as named instances.
> Since you have multiple instances (2 in your case), you will need to ensure that all server nodes have the same resources (processor, memory) and it is enough to handle the instances that could potentially fail to
> that node. Another important consideration is to cap memory usage of the instance of SQL Server 2000 with max server memory. Especially if AWE memory is enabled, max server memory must be set in a
> multiple-instance cluster to prevent starving the server node.
> If you have a Windows Server 2003 EE cluster then you may find the following webcast useful
> TechNet Support WebCast: How to install a Microsoft SQL Server 2000 virtual server on a Microsoft Windows Server 2003 cluster
> Discusses how to install Microsoft SQL Server 2000 clustering on Microsoft Windows Server 2003 and differences between Microsoft Windows 2000 Server and Windows Server 2003.
> http://support.microsoft.com/default...b;en-us;888121
>
> For Win2K Adv Server cluster, you may find the following whitepaper useful
> SQL Server 2000 Failover Clustering
> http://www.microsoft.com/technet/pro.../failclus.mspx
> Review the above and feel free to post any other qs that you may have.
> Additional Information
> =======================
> Here is some other related links that you may find useful
> INF: Clustered SQL Server Do's, Don'ts, and Basic Warnings
> http://support.microsoft.com/?kbid=254321
> Introduction to Microsoft SQL Server 2000 Clustering
> http://support.microsoft.com/default...lurb051001.asp
> Microsoft SQL Server 2000 Virtual Server: Things You Should Know
> http://support.microsoft.com/default...lurb032602.asp
>
> Microsoft SQL Server 2000 Virtual Server Basic Setup, Maintenance, and Service Pack http://support.microsoft.com/default...lurb061002.asp
> Microsoft SQL Server 2000 Failover Clustering Disaster Recovery Procedures
> http://support.microsoft.com/default...lurb101802.asp
> Troubleshooting SQL 2000 Virtual Server and Service Pack Setups for Failover Clustering
> http://support.microsoft.com/default...lurb020703.asp
>
> HTH,
> Best Regards,
> Uttam Parui
> Microsoft Corporation
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
> Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
> http://www.microsoft.com/protect
> http://www.microsoft.com/security/guidance/default.mspx
>

cloning a database

What is the process that would allow us to create a copy of our production
database that we could use for testing puproses.
Don
Hi
Backup the database and restore it on your test server.
Regards
Mike
"don" wrote:

> What is the process that would allow us to create a copy of our production
> database that we could use for testing puproses.
> --
> Don
|||This is also a good test of the backup procedures that you should have in
place. Knowing how to restore from a backup is important!
Keith
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:6C2998EA-E002-4919-954E-335060B62641@.microsoft.com...[vbcol=seagreen]
> Hi
> Backup the database and restore it on your test server.
> Regards
> Mike
> "don" wrote:
production[vbcol=seagreen]
|||Thanks, found the info in the sqlserver online books 'How to restore a
database with a new name' .
"Keith Kratochvil" wrote:

> This is also a good test of the backup procedures that you should have in
> place. Knowing how to restore from a backup is important!
> --
> Keith
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:6C2998EA-E002-4919-954E-335060B62641@.microsoft.com...
> production
>

Clone a SQL Server

We are in the process of upgrading from SQL Server 7 to SQL Server 2000.

Before we do that we want to run our system on our new server in a test environment to iron out any issues which may arrive from the upgrade, and to use for future testing. Is there a way to 'clone' a SQL 7 server onto a 2nd physical machine.

ThanksYou could restore sql7 database on sql2k box and do whatever tests needed.|||Originally posted by oj_n
You could restore sql7 database on sql2k box and do whatever tests needed.

I need to make sure all of the DTS pacjkages go as well.|||Here is a good kb that should help you...

http://support.microsoft.com/?id=314546|||There is a utility called DTSBackup2000 that's available free from SQLDTS.com. It works quite well, though I don't know what impact migrating from 7.0 to 2000 might have.

Regards,

Hugh Scott

Originally posted by GregCrossan
I need to make sure all of the DTS pacjkages go as well.

Client-Side Database Mirroring Details

Hi,
I've been tasked with investigating different methods of achieving database
redundancy.
In doing so, I'm currently in the process of writing a test application to
prove that Database Mirroring in SQL Server 2005 is a suitable solution.
Of the established requirements I'm finding difficulties with the following:
1. Identifying the name/instance of SQL Server my application is currently
connected to.
2. Identifying the name/instance of SQL Server cached as the failover
partner, and confirming it state at any time.
3. Identifying the name/instance of SQL Server cached as the witness, and
confirming it state at any time.
4. Forcing a failover via my test application.
I've already got some of this functionality via sp_dbmmonitorresults, but
this relies on the Server Agent running and also adds to the network traffic
.
I was hoping for a solution using calls to the SQL Native Client.
I’m using SQL Server 2005 Enterprise Edition SP1.
My application is written in Visual C++ 6 as is the product this is intended
for.
Also, if anyone knows of any good links to web pages or forum topics with
regards to methods of implementing database retry/reconnect client-side when
SQL Server fails over, I would be grateful. I’ve implemented this
functionality already by deriving my own version of CDatabase, but I’m ope
n
to new suggestions.
Thanks!I haven't really worked on it, But this link seemed interesting..
http://blogs.msdn.com/sqltips/archi..._Mirroring.aspx
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||There is some useful information and one or two scripts, but it doesn’t
really answer my question.
"Omnibuzz" wrote:

> I haven't really worked on it, But this link seemed interesting..
> http://blogs.msdn.com/sqltips/archi..._Mirroring.aspx
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>|||As I had said, I hadn't really worked on it.. I guess we will have to wait
for reply from someone else
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/

Client-Side Database Mirroring Details

Hi,
I've been tasked with investigating different methods of achieving database
redundancy.
In doing so, I'm currently in the process of writing a test application to
prove that Database Mirroring in SQL Server 2005 is a suitable solution.
Of the established requirements I'm finding difficulties with the following:
1. Identifying the name/instance of SQL Server my application is
currently connected to.
2. Identifying the name/instance of SQL Server cached as the
failover partner, and confirming it state at any time.
3. Identifying the name/instance of SQL Server cached as the
witness, and confirming it state at any time.
4. Forcing a failover via my test application.
I've already got some of this functionality via sp_dbmmonitorresults, but
this relies on the Server Agent running and also adds to the network traffic
.
I was hoping for a solution using calls to the SQL Native Client.
I’m using SQL Server 2005 Enterprise Edition SP1.
My application is written in Visual C++ 6 as is the product this is intended
for.
Also, if anyone knows of any good links to web pages or forum topics with
regards to methods of implementing database retry/reconnect client-side when
SQL Server fails over, I would be grateful. I’ve implemented this
functionality already by deriving my own version of CDatabase, but I’m ope
n
to new suggestions.
Thanks!On Thu, 27 Jul 2006 05:43:03 -0700, GMouzourou wrote:

>Hi,
>I've been tasked with investigating different methods of achieving database
>redundancy.
>In doing so, I'm currently in the process of writing a test application to
>prove that Database Mirroring in SQL Server 2005 is a suitable solution.
>Of the established requirements I'm finding difficulties with the following
:
> 1. Identifying the name/instance of SQL Server my application is
> currently connected to.
> 2. Identifying the name/instance of SQL Server cached as the
> failover partner, and confirming it state at any time.
> 3. Identifying the name/instance of SQL Server cached as the
> witness, and confirming it state at any time.
> 4. Forcing a failover via my test application.
>I've already got some of this functionality via sp_dbmmonitorresults, but
>this relies on the Server Agent running and also adds to the network traffi
c.
>I was hoping for a solution using calls to the SQL Native Client.
Hi GMouzourou,
For the first three questions, have you looked at the system management
views? More precisely, the column mirroring_role_desc in sys.databases,
and all of the columns in sys.database_mirroring and
sys.database_mirroring_witness.
For the last question, you can manually force a failover with the
command
ALTER DATABASE dbname SET PARTNER FAILOVER;
If you want to test automatic failover, try shutting down the server:
SHUTDOWN WITH NOWAIT;
Hugo Kornelis, SQL Server MVP