We have a server which we needed to upgrade the OS on it from NT 4 Server to Windows 2000 Server.
As the system disks are seperate from the data disks. We did a fresh install of Windows, then installed SQL Server, applied service pack version 3, and then restored the master database.
From Query Analyser everything appears fine. I can query all the databases and tables, see them all, the web applications talk the database fine so all the users have recreated OK.
However ... if we try and look at the database through management console we get the following error.
A connection could not be established to ...
Reason: Cannot resolve collation conflict for like operation..
This happends logged in locally to the machine as administrator as well as connecting remotely. Does anybody have any idea what is happening?A little more info - I have run a trace to see what happens when enterprise manager connects, and the last thing it tries to run (which generates the collation error) is:
exec sp_MSdbuserpriv N'ver'|||You either have to support all collations or force your clients to reinstall SQL Server with the collation you chose.|||I am sorry but I don't understand what you mean by this?
Originally posted by Satya
You either have to support all collations or force your clients to reinstall SQL Server with the collation you chose.|||Even more info:
Looking at the query and playing a bit the following query if I run the following query against the master database, I get the "Cannot resolve collation conflict for like operation"
declare @.mode nvarchar(10)
set @.mode = N'ver'
if lower(@.mode) like N'serv%'
select 1 as result
NB if this run against another database (on the same server) it runs fine.
HELP!|||What is the default collation choose when you installed SQL Server?|||Unfortunately I didn't run the install - I asked the person running it to take the default collation which was offered.
Originally posted by Satya
What is the default collation choose when you installed SQL Server?|||You can run SERVERPROPERTY (Collation) to get it.|||Thanks for that:
The server collation is set to: Lation1_General_CI_AS
The master database collation is set to: SQL_Latin1_General_CP1_CI_AS
I guess this is the root of the problem. Is there anyway I can change the server default collation without re-installing SQL Server?sqlsql
Showing posts with label upgrade. Show all posts
Showing posts with label upgrade. Show all posts
Sunday, March 25, 2012
Thursday, March 22, 2012
collation case sensitive
I have a vendor who is trying to apply an upgrade to our database, he is running into errors because the database collation is set to case sensitive. Is there a way to by pass the setting so he can procedd with the update scripts.
Robert,
A case sensitive TSQL script can often be made to function in case
insensitive manner by using UPPER or COLLATE. You could also change the
collation of the database if there are new tables which are set to inherit
the default collation. However I'd need to know more details to see if these
methods can help - please post back to explain further what the upgrade is
doing?.
Paul Ibison
Robert,
A case sensitive TSQL script can often be made to function in case
insensitive manner by using UPPER or COLLATE. You could also change the
collation of the database if there are new tables which are set to inherit
the default collation. However I'd need to know more details to see if these
methods can help - please post back to explain further what the upgrade is
doing?.
Paul Ibison
collation case sensitive
I have a vendor who is trying to apply an upgrade to our database, he is run
ning into errors because the database collation is set to case sensitive. Is
there a way to by pass the setting so he can procedd with the update script
s.Robert,
A case sensitive TSQL script can often be made to function in case
insensitive manner by using UPPER or COLLATE. You could also change the
collation of the database if there are new tables which are set to inherit
the default collation. However I'd need to know more details to see if these
methods can help - please post back to explain further what the upgrade is
doing?.
Paul Ibisonsqlsql
ning into errors because the database collation is set to case sensitive. Is
there a way to by pass the setting so he can procedd with the update script
s.Robert,
A case sensitive TSQL script can often be made to function in case
insensitive manner by using UPPER or COLLATE. You could also change the
collation of the database if there are new tables which are set to inherit
the default collation. However I'd need to know more details to see if these
methods can help - please post back to explain further what the upgrade is
doing?.
Paul Ibisonsqlsql
Friday, February 24, 2012
Clustering VS Replication
We have a SQL 2000 server that we are about to upgrade to SQL 2005. This
server is running on Server 2003 standard. We would like to set up a second
SQL 2005 server and either do clustering or replication so that we would be
able to access our data in the event of a single server failure.
Which is the "better' solution and what are the minimum OS requirements for
both? In either case, each server will be running its own local disk array
that would contain the OS and the databaeses.
Thanx...Jon
Windows Clustering requires a shared disk array and all the equipment must
be on the Clustering Hardware Compatibility List. Replication does not
require any special hardware but is not usually used for DR although some
do. You might want to look at using Log shipping or even the new Database
Mirroring features. Both are documented in BooksOnLine.
Andrew J. Kelly SQL MVP
"Jon Yiesla" <JonYiesla@.discussions.microsoft.com> wrote in message
news:505EE799-B615-4220-96C5-F5A7EDE71F74@.microsoft.com...
> We have a SQL 2000 server that we are about to upgrade to SQL 2005. This
> server is running on Server 2003 standard. We would like to set up a
> second
> SQL 2005 server and either do clustering or replication so that we would
> be
> able to access our data in the event of a single server failure.
> Which is the "better' solution and what are the minimum OS requirements
> for
> both? In either case, each server will be running its own local disk array
> that would contain the OS and the databaeses.
> Thanx...Jon
|||"Jon Yiesla" <JonYiesla@.discussions.microsoft.com> wrote in message
news:505EE799-B615-4220-96C5-F5A7EDE71F74@.microsoft.com...
> We have a SQL 2000 server that we are about to upgrade to SQL 2005. This
> server is running on Server 2003 standard. We would like to set up a
> second
> SQL 2005 server and either do clustering or replication so that we would
> be
> able to access our data in the event of a single server failure.
> Which is the "better' solution and what are the minimum OS requirements
> for
> both? In either case, each server will be running its own local disk array
> that would contain the OS and the databaeses.
>
Well clustering is a shared-disk solution. Replication, Log Shipping and
Database Mirroring are all options, though.
David
|||Actually DB mirroring sounds like what we might need. We have a recent SQL
server crash and it's too critical a piece to not have some kind of DR
process in place. The more I read about clustering the more I am unsure that
this is the way I'd like to go. What we would like is for the data to be in
two places both running SQL Server and if one server goes down, the other
would automatically or with minimal effort pick up the slack...Which if the
processes you mentioned would be the best?
Thanx...Jon
"Andrew J. Kelly" wrote:
> Windows Clustering requires a shared disk array and all the equipment must
> be on the Clustering Hardware Compatibility List. Replication does not
> require any special hardware but is not usually used for DR although some
> do. You might want to look at using Log shipping or even the new Database
> Mirroring features. Both are documented in BooksOnLine.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Jon Yiesla" <JonYiesla@.discussions.microsoft.com> wrote in message
> news:505EE799-B615-4220-96C5-F5A7EDE71F74@.microsoft.com...
>
>
|||Hi
That is DB Mirroring, available for SQL Server 2005 early 2006.
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/
"Jon Yiesla" <JonYiesla@.discussions.microsoft.com> wrote in message
news:9C3C5AE8-3A63-4DD3-AD76-A79E4C13969F@.microsoft.com...[vbcol=seagreen]
> Actually DB mirroring sounds like what we might need. We have a recent SQL
> server crash and it's too critical a piece to not have some kind of DR
> process in place. The more I read about clustering the more I am unsure
> that
> this is the way I'd like to go. What we would like is for the data to be
> in
> two places both running SQL Server and if one server goes down, the other
> would automatically or with minimal effort pick up the slack...Which if
> the
> processes you mentioned would be the best?
> Thanx...Jon
> "Andrew J. Kelly" wrote:
|||As Mike stated that is what Mirroring is made for. But Log Shipping is also
a viable alternative and is available now. It has a longer time to be up
and running after a failover but that is configurable with how often you log
ship.
Andrew J. Kelly SQL MVP
"Jon Yiesla" <JonYiesla@.discussions.microsoft.com> wrote in message
news:9C3C5AE8-3A63-4DD3-AD76-A79E4C13969F@.microsoft.com...[vbcol=seagreen]
> Actually DB mirroring sounds like what we might need. We have a recent SQL
> server crash and it's too critical a piece to not have some kind of DR
> process in place. The more I read about clustering the more I am unsure
> that
> this is the way I'd like to go. What we would like is for the data to be
> in
> two places both running SQL Server and if one server goes down, the other
> would automatically or with minimal effort pick up the slack...Which if
> the
> processes you mentioned would be the best?
> Thanx...Jon
> "Andrew J. Kelly" wrote:
|||Hi,
I am just curious on the difference between replication & log shipping. Is
the underlaying just triggers of SQL statements that done the
insert/delete/update ?
if i set up replication to rep every 2 minutes or continuously , is it any
difference with having log shipping (which i personally find it more diffcult
to setup then replication)
tks & rdgs
Andrew J. Kelly wrote:[vbcol=seagreen]
>As Mike stated that is what Mirroring is made for. But Log Shipping is also
>a viable alternative and is available now. It has a longer time to be up
>and running after a failover but that is configurable with how often you log
>ship.
>[quoted text clipped - 32 lines]
Message posted via http://www.droptable.com
|||There is quite a bit of difference between the two. Too much to go into here
but if you read up in BooksOnLine on each you will begin to see all the
features of both. But in a nut shell Log shipping utilizes a backup of the
log and applies all of the changes in the log on the log shipped server
exactly as they occurred on the original. Replication has many configurable
options to determine what gets replicated and how and uses a Log reader and
distributor database to forward the changes.
Andrew J. Kelly SQL MVP
"maxzsim via droptable.com" <u14644@.uwe> wrote in message
news:58b3593a1e828@.uwe...
> Hi,
> I am just curious on the difference between replication & log shipping.
> Is
> the underlaying just triggers of SQL statements that done the
> insert/delete/update ?
> if i set up replication to rep every 2 minutes or continuously , is it
> any
> difference with having log shipping (which i personally find it more
> diffcult
> to setup then replication)
> tks & rdgs
>
> Andrew J. Kelly wrote:
> --
> Message posted via http://www.droptable.com
server is running on Server 2003 standard. We would like to set up a second
SQL 2005 server and either do clustering or replication so that we would be
able to access our data in the event of a single server failure.
Which is the "better' solution and what are the minimum OS requirements for
both? In either case, each server will be running its own local disk array
that would contain the OS and the databaeses.
Thanx...Jon
Windows Clustering requires a shared disk array and all the equipment must
be on the Clustering Hardware Compatibility List. Replication does not
require any special hardware but is not usually used for DR although some
do. You might want to look at using Log shipping or even the new Database
Mirroring features. Both are documented in BooksOnLine.
Andrew J. Kelly SQL MVP
"Jon Yiesla" <JonYiesla@.discussions.microsoft.com> wrote in message
news:505EE799-B615-4220-96C5-F5A7EDE71F74@.microsoft.com...
> We have a SQL 2000 server that we are about to upgrade to SQL 2005. This
> server is running on Server 2003 standard. We would like to set up a
> second
> SQL 2005 server and either do clustering or replication so that we would
> be
> able to access our data in the event of a single server failure.
> Which is the "better' solution and what are the minimum OS requirements
> for
> both? In either case, each server will be running its own local disk array
> that would contain the OS and the databaeses.
> Thanx...Jon
|||"Jon Yiesla" <JonYiesla@.discussions.microsoft.com> wrote in message
news:505EE799-B615-4220-96C5-F5A7EDE71F74@.microsoft.com...
> We have a SQL 2000 server that we are about to upgrade to SQL 2005. This
> server is running on Server 2003 standard. We would like to set up a
> second
> SQL 2005 server and either do clustering or replication so that we would
> be
> able to access our data in the event of a single server failure.
> Which is the "better' solution and what are the minimum OS requirements
> for
> both? In either case, each server will be running its own local disk array
> that would contain the OS and the databaeses.
>
Well clustering is a shared-disk solution. Replication, Log Shipping and
Database Mirroring are all options, though.
David
|||Actually DB mirroring sounds like what we might need. We have a recent SQL
server crash and it's too critical a piece to not have some kind of DR
process in place. The more I read about clustering the more I am unsure that
this is the way I'd like to go. What we would like is for the data to be in
two places both running SQL Server and if one server goes down, the other
would automatically or with minimal effort pick up the slack...Which if the
processes you mentioned would be the best?
Thanx...Jon
"Andrew J. Kelly" wrote:
> Windows Clustering requires a shared disk array and all the equipment must
> be on the Clustering Hardware Compatibility List. Replication does not
> require any special hardware but is not usually used for DR although some
> do. You might want to look at using Log shipping or even the new Database
> Mirroring features. Both are documented in BooksOnLine.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Jon Yiesla" <JonYiesla@.discussions.microsoft.com> wrote in message
> news:505EE799-B615-4220-96C5-F5A7EDE71F74@.microsoft.com...
>
>
|||Hi
That is DB Mirroring, available for SQL Server 2005 early 2006.
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/
"Jon Yiesla" <JonYiesla@.discussions.microsoft.com> wrote in message
news:9C3C5AE8-3A63-4DD3-AD76-A79E4C13969F@.microsoft.com...[vbcol=seagreen]
> Actually DB mirroring sounds like what we might need. We have a recent SQL
> server crash and it's too critical a piece to not have some kind of DR
> process in place. The more I read about clustering the more I am unsure
> that
> this is the way I'd like to go. What we would like is for the data to be
> in
> two places both running SQL Server and if one server goes down, the other
> would automatically or with minimal effort pick up the slack...Which if
> the
> processes you mentioned would be the best?
> Thanx...Jon
> "Andrew J. Kelly" wrote:
|||As Mike stated that is what Mirroring is made for. But Log Shipping is also
a viable alternative and is available now. It has a longer time to be up
and running after a failover but that is configurable with how often you log
ship.
Andrew J. Kelly SQL MVP
"Jon Yiesla" <JonYiesla@.discussions.microsoft.com> wrote in message
news:9C3C5AE8-3A63-4DD3-AD76-A79E4C13969F@.microsoft.com...[vbcol=seagreen]
> Actually DB mirroring sounds like what we might need. We have a recent SQL
> server crash and it's too critical a piece to not have some kind of DR
> process in place. The more I read about clustering the more I am unsure
> that
> this is the way I'd like to go. What we would like is for the data to be
> in
> two places both running SQL Server and if one server goes down, the other
> would automatically or with minimal effort pick up the slack...Which if
> the
> processes you mentioned would be the best?
> Thanx...Jon
> "Andrew J. Kelly" wrote:
|||Hi,
I am just curious on the difference between replication & log shipping. Is
the underlaying just triggers of SQL statements that done the
insert/delete/update ?
if i set up replication to rep every 2 minutes or continuously , is it any
difference with having log shipping (which i personally find it more diffcult
to setup then replication)
tks & rdgs
Andrew J. Kelly wrote:[vbcol=seagreen]
>As Mike stated that is what Mirroring is made for. But Log Shipping is also
>a viable alternative and is available now. It has a longer time to be up
>and running after a failover but that is configurable with how often you log
>ship.
>[quoted text clipped - 32 lines]
Message posted via http://www.droptable.com
|||There is quite a bit of difference between the two. Too much to go into here
but if you read up in BooksOnLine on each you will begin to see all the
features of both. But in a nut shell Log shipping utilizes a backup of the
log and applies all of the changes in the log on the log shipped server
exactly as they occurred on the original. Replication has many configurable
options to determine what gets replicated and how and uses a Log reader and
distributor database to forward the changes.
Andrew J. Kelly SQL MVP
"maxzsim via droptable.com" <u14644@.uwe> wrote in message
news:58b3593a1e828@.uwe...
> Hi,
> I am just curious on the difference between replication & log shipping.
> Is
> the underlaying just triggers of SQL statements that done the
> insert/delete/update ?
> if i set up replication to rep every 2 minutes or continuously , is it
> any
> difference with having log shipping (which i personally find it more
> diffcult
> to setup then replication)
> tks & rdgs
>
> Andrew J. Kelly wrote:
> --
> Message posted via http://www.droptable.com
Thursday, February 16, 2012
Clustered SQL7 upgrade to SQL2000 fails to find default server
We are trying to upgrade to SQL2000 in a failover cluster
environment but the upgrade process is failing to identify
the default server and is instead creating a new named
instance of SQL2000 alongside SQL7 (the 'default' checkbox
on the upgrade wizard is greyed out and unchecked). The
production database is 125Gb so I don't want to use the
copy database wizard if possible. Can anyone suggest how
to get round this? Is it a registry setting?
TIA
JohnI am not sure what process you are taking to do this but here is the proper
sequence:
1. Uncluster SQL Server 7.0 cluster
2. Upgrade SQL Server 7.0 to a SQL Server 2000 default instance (putting
binaries on a local drive)
3. Upgrade the default instance of SQL Server 2000 ti a clustered intance
of SQL Server.
This is documetned in Books on Line:
Upgrading to a SQL SErver 2000 Failover Cluster
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Rand,
this is exactly the process we are trying to follow, but
the 2nd step fails because the upgrade process can't find
the default SQL7 server to upgrade in place and instead
creates a new instance of SQL2000. We have tried this over
and over again, even to the point of completely removing
and reinstalling SQL7. I've had no trouble before with
upgrading a standalone server so it's probably something
to do with the clustering. Any suggestions would be
greatly appreciated...
>--Original Message--
>I am not sure what process you are taking to do this but
here is the proper
>sequence:
>1. Uncluster SQL Server 7.0 cluster
>2. Upgrade SQL Server 7.0 to a SQL Server 2000 default
instance (putting
>binaries on a local drive)
>3. Upgrade the default instance of SQL Server 2000 ti a
clustered intance
>of SQL Server.
>This is documetned in Books on Line:
>Upgrading to a SQL SErver 2000 Failover Cluster
>Rand
>This posting is provided "as is" with no warranties and
confers no rights.
>.
>
environment but the upgrade process is failing to identify
the default server and is instead creating a new named
instance of SQL2000 alongside SQL7 (the 'default' checkbox
on the upgrade wizard is greyed out and unchecked). The
production database is 125Gb so I don't want to use the
copy database wizard if possible. Can anyone suggest how
to get round this? Is it a registry setting?
TIA
JohnI am not sure what process you are taking to do this but here is the proper
sequence:
1. Uncluster SQL Server 7.0 cluster
2. Upgrade SQL Server 7.0 to a SQL Server 2000 default instance (putting
binaries on a local drive)
3. Upgrade the default instance of SQL Server 2000 ti a clustered intance
of SQL Server.
This is documetned in Books on Line:
Upgrading to a SQL SErver 2000 Failover Cluster
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Rand,
this is exactly the process we are trying to follow, but
the 2nd step fails because the upgrade process can't find
the default SQL7 server to upgrade in place and instead
creates a new instance of SQL2000. We have tried this over
and over again, even to the point of completely removing
and reinstalling SQL7. I've had no trouble before with
upgrading a standalone server so it's probably something
to do with the clustering. Any suggestions would be
greatly appreciated...
>--Original Message--
>I am not sure what process you are taking to do this but
here is the proper
>sequence:
>1. Uncluster SQL Server 7.0 cluster
>2. Upgrade SQL Server 7.0 to a SQL Server 2000 default
instance (putting
>binaries on a local drive)
>3. Upgrade the default instance of SQL Server 2000 ti a
clustered intance
>of SQL Server.
>This is documetned in Books on Line:
>Upgrading to a SQL SErver 2000 Failover Cluster
>Rand
>This posting is provided "as is" with no warranties and
confers no rights.
>.
>
Tuesday, February 14, 2012
Clustered SQL Server 2000 to 2005 Upgrade
I would like to upgrade a production active/passive SQL Server 2000 cluster to SQL Server 2005.
I've read all of the documentation I could find, and doing an in-place upgrade seems to be the way to go. (Despite the fact it scares the hell out of me.
But throughout all the documentation, I've yet to see some simple and fairly important questions answered.
How does the process of upgrading SQL Server work when you're dealing with a cluster?
Do you upgrade one machine then the other? If so, do you upgrade the active node first, or the passive node?
What happens if you're forced to failover from one instance to the other before you've had a chance to upgrade both to 2005? In other words, you failover from 2005 to 2000. Would that even work?
What happens if your upgrades fails for some reason? Is it easy to rollback the installation? If not, will reinstalling SQL Server 2000 cause any issues? Will it remember the previous configuration and simply fix the broken install?
Any help/insight would be greatly appreciated.
Can anybody offer any advice on this?|||Moved to ...Availability forum.|||Maybe I can help. I, along with our network admin, spent a solid 2-3 weeks working on upgrading a active/active cluster to SQL 2005 from 2000. From my experience, it was incredibly simple, yet ridiculously hard. Unless you have every single setting correct on the cluster you will likely run into problems....we did. Here is how we eventually were successful:
1. Fail all active nodes over to one node (I'll call the primary node).
2. Make sure MSDTC is a clustered resource on the cluster. ( we can a test cluster setup and forgot to do this).
3. Install the upgrade to the primary node. There was a little bit of ambiguity when running the install that makes you think that it is going to install it on the 2nd node as well. In my situation, it didnt.
4. After installing on the first node, reboot.
5. Fail over all nodes to the 2ndary node and install the upgrade.
6. Reboot.
7. Failover to primary and install the service pack.
8. Reboot.
9. Failover to the 2ndard and install the service pack.
I am pretty sure these are the steps we followed that worked for us. Most likely, this isn't the recommended course of action of making the upgrade, but it worked. Make sure that when you do install the upgrade, that you install the SQL client tools on the first node. If you don't, you will run into problems later.
If someone else has had a better experience and would like to share their steps to success, please, please do.
Hope this helped,
Tim|||Thanks for your insight. It is incredibly frustrating that there is almost no documentation on this process.
I'm a little confused by your process. You say in step 4 that after you install you should reboot the first node. Does this mean you have downtime or does this cause a failover to other nodes? (I assume it doesn't since you forced the failover to the primary node before starting the process.)
You say in step 5 that you fail over to the secondary node. So this means you're failing over from SQL Server 2005 (on the primary) to SQL Server 2000 (on the secondary). If that works, can we simply fail over to the secondary before we reboot during step 4, thereby avoiding downtime?
Your situation is slightly different than mine since my cluster is active/passive with just two machines. Using your process as a template, mine would look something like:
1.) Install the upgrade on node 1.
2.) Force a failover to node 2 (which is still running SQL Server 2000).
3.) Reboot node 1. Wait for node 1 to restart.
4.) Install the upgrade to node 2.
5.) Force a failover to node 1.
6.) Reboot node 2.
Does this seem correct?
|||
RMD: Did you complete this successfully following your steps? We have the same configuration you do and any previous experience advice would be helpful.
thanks
Clustered SQL Server 2000 to 2005 Upgrade
I would like to upgrade a production active/passive SQL Server 2000 cluster to SQL Server 2005.
I've read all of the documentation I could find, and doing an in-place upgrade seems to be the way to go. (Despite the fact it scares the hell out of me.
But throughout all the documentation, I've yet to see some simple and fairly important questions answered.
How does the process of upgrading SQL Server work when you're dealing with a cluster?
Do you upgrade one machine then the other? If so, do you upgrade the active node first, or the passive node?
What happens if you're forced to failover from one instance to the other before you've had a chance to upgrade both to 2005? In other words, you failover from 2005 to 2000. Would that even work?
What happens if your upgrades fails for some reason? Is it easy to rollback the installation? If not, will reinstalling SQL Server 2000 cause any issues? Will it remember the previous configuration and simply fix the broken install?
Any help/insight would be greatly appreciated.
Can anybody offer any advice on this?|||Moved to ...Availability forum.|||Maybe I can help. I, along with our network admin, spent a solid 2-3 weeks working on upgrading a active/active cluster to SQL 2005 from 2000. From my experience, it was incredibly simple, yet ridiculously hard. Unless you have every single setting correct on the cluster you will likely run into problems....we did. Here is how we eventually were successful:
1. Fail all active nodes over to one node (I'll call the primary node).
2. Make sure MSDTC is a clustered resource on the cluster. ( we can a test cluster setup and forgot to do this).
3. Install the upgrade to the primary node. There was a little bit of ambiguity when running the install that makes you think that it is going to install it on the 2nd node as well. In my situation, it didnt.
4. After installing on the first node, reboot.
5. Fail over all nodes to the 2ndary node and install the upgrade.
6. Reboot.
7. Failover to primary and install the service pack.
8. Reboot.
9. Failover to the 2ndard and install the service pack.
I am pretty sure these are the steps we followed that worked for us. Most likely, this isn't the recommended course of action of making the upgrade, but it worked. Make sure that when you do install the upgrade, that you install the SQL client tools on the first node. If you don't, you will run into problems later.
If someone else has had a better experience and would like to share their steps to success, please, please do.
Hope this helped,
Tim|||Thanks for your insight. It is incredibly frustrating that there is almost no documentation on this process.
I'm a little confused by your process. You say in step 4 that after you install you should reboot the first node. Does this mean you have downtime or does this cause a failover to other nodes? (I assume it doesn't since you forced the failover to the primary node before starting the process.)
You say in step 5 that you fail over to the secondary node. So this means you're failing over from SQL Server 2005 (on the primary) to SQL Server 2000 (on the secondary). If that works, can we simply fail over to the secondary before we reboot during step 4, thereby avoiding downtime?
Your situation is slightly different than mine since my cluster is active/passive with just two machines. Using your process as a template, mine would look something like:
1.) Install the upgrade on node 1.
2.) Force a failover to node 2 (which is still running SQL Server 2000).
3.) Reboot node 1. Wait for node 1 to restart.
4.) Install the upgrade to node 2.
5.) Force a failover to node 1.
6.) Reboot node 2.
Does this seem correct?
|||
RMD: Did you complete this successfully following your steps? We have the same configuration you do and any previous experience advice would be helpful.
thanks
Clustered SQL Server 2000 to 2005 Upgrade
I would like to upgrade a production active/passive SQL Server 2000 cluster to SQL Server 2005.
I've read all of the documentation I could find, and doing an in-place upgrade seems to be the way to go. (Despite the fact it scares the hell out of me.
But throughout all the documentation, I've yet to see some simple and fairly important questions answered.
How does the process of upgrading SQL Server work when you're dealing with a cluster?
Do you upgrade one machine then the other? If so, do you upgrade the active node first, or the passive node?
What happens if you're forced to failover from one instance to the other before you've had a chance to upgrade both to 2005? In other words, you failover from 2005 to 2000. Would that even work?
What happens if your upgrades fails for some reason? Is it easy to rollback the installation? If not, will reinstalling SQL Server 2000 cause any issues? Will it remember the previous configuration and simply fix the broken install?
Any help/insight would be greatly appreciated.
Can anybody offer any advice on this?|||Moved to ...Availability forum.|||Maybe I can help. I, along with our network admin, spent a solid 2-3 weeks working on upgrading a active/active cluster to SQL 2005 from 2000. From my experience, it was incredibly simple, yet ridiculously hard. Unless you have every single setting correct on the cluster you will likely run into problems....we did. Here is how we eventually were successful:
1. Fail all active nodes over to one node (I'll call the primary node).
2. Make sure MSDTC is a clustered resource on the cluster. ( we can a test cluster setup and forgot to do this).
3. Install the upgrade to the primary node. There was a little bit of ambiguity when running the install that makes you think that it is going to install it on the 2nd node as well. In my situation, it didnt.
4. After installing on the first node, reboot.
5. Fail over all nodes to the 2ndary node and install the upgrade.
6. Reboot.
7. Failover to primary and install the service pack.
8. Reboot.
9. Failover to the 2ndard and install the service pack.
I am pretty sure these are the steps we followed that worked for us. Most likely, this isn't the recommended course of action of making the upgrade, but it worked. Make sure that when you do install the upgrade, that you install the SQL client tools on the first node. If you don't, you will run into problems later.
If someone else has had a better experience and would like to share their steps to success, please, please do.
Hope this helped,
Tim|||Thanks for your insight. It is incredibly frustrating that there is almost no documentation on this process.
I'm a little confused by your process. You say in step 4 that after you install you should reboot the first node. Does this mean you have downtime or does this cause a failover to other nodes? (I assume it doesn't since you forced the failover to the primary node before starting the process.)
You say in step 5 that you fail over to the secondary node. So this means you're failing over from SQL Server 2005 (on the primary) to SQL Server 2000 (on the secondary). If that works, can we simply fail over to the secondary before we reboot during step 4, thereby avoiding downtime?
Your situation is slightly different than mine since my cluster is active/passive with just two machines. Using your process as a template, mine would look something like:
1.) Install the upgrade on node 1.
2.) Force a failover to node 2 (which is still running SQL Server 2000).
3.) Reboot node 1. Wait for node 1 to restart.
4.) Install the upgrade to node 2.
5.) Force a failover to node 1.
6.) Reboot node 2.
Does this seem correct?
|||
RMD: Did you complete this successfully following your steps? We have the same configuration you do and any previous experience advice would be helpful.
thanks
Subscribe to:
Posts (Atom)