Good Day SQL Guru's

I have a rather lenghty question and I will try and be specific as possible.
I currently have a single server with Windows 2003 Enterprise running a
single instance of SQL Server Standard 2005. The Master and other "default"
databases are in the Default location on the C drive. The "Production"
databases are on "shared" resources on a CX-300 which is attached to my
Server via EMULEX HBA's. Now a Caviat to this configuration is that it used
to be part of a cluster.
When I first started working here I found that it had been configured with
an 8 gig C drive partion and I needed to reconfigure them one at a time, So
when I went to remove SQL off of the second node to be able to "evict" it
from the cluster, I did it through "Add/Remove Programs" and the set up came
up as normal, but after I selected the node and hit 'uninstall' It never
asked me the usual SQL cluster questions and Just uninstalled SQL, removing
all the shared "SQL Group" resources in the cluster" Not a tragedy, because I
still have a node, But very disconcerting and curious
So I rebuilt it and added it back to the cluster and manually configured
everything. It worked pretty good, Everything failed over (SQL services,
MSDTC, Drives and the databases attached okay, Howvere they since the Master
did not match the other, none of the "objects" were there. so what I did was
"stopped the services" on the good node, copied the databases into the
default folder on the new node and brought it up, it seemed to work. But when
I tried to attach my "production databases" it came back and said "recovery
in progress" and would not attach them, it didn't go for very long before My
SQL DB started whining about wanting to revert back because he had to go home.
SO, I am assuming that there was a problem doing it that way or maybe
eventually they would have recovered, We did try a "restore" first, But when
we did a full backup of the Good Node "master" and tried to restore it to the
new node, the versions didin't match.
Now, I have a few questions I was hoping I could get a few pointers on. I
have built two servers that I am going to build a new cluster on, so I want
to "migrate" these SQL "default" database to the new cluster, Since it is
also SAN attached, I am simply going to pull the single node out of the
"Storage Group" that has the LUNS for the SQL Resources and add the two SQL
Cluster NODES, giving the drives the exact same letters (of course attaching
them one node at a time)
So assuming I want to migrate the SQL default database to the Cluster is
there a migration tool that will do this relatively risk free and can I put
this database on a shared drive and have both nodes point to it and when
failover occurs, the node being failed from detaches and the node being
failed to attaches to these databases. The reason I ask is I have built
several SQL Clusters, But they have all been new ones that were going to be
used, I have not built one to replace a single node system.
I haven't done a lot of research myself yet, I am looking for any microsoft
tools that will assist me, But if anyone has done this or has some sagely
advise, it would be greatly appreciated
--
Scott A Cummins
Sr. Systems Engineer
Equity Analytics
( A division of Merrill Lynch)
14614 N. Keirland Blvd
Scottsdale, AZ 85254
480-998-3515I would treat this as a side-by-side migration. I would build a new cluster
and copy the data files to the new system. You can try a detach-move
LUNS-Reattach from the stand-alone machine to the cluster, but there is no
way I would try and migrate the system databases. Treat this as a normal
SQL host migration and you might come out of it OK.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Scott Cummins" <scummins@.equitymethods.com> wrote in message
news:EF1A1CB2-8192-41C7-8088-4C2B1EC78835@.microsoft.com...
>I have a rather lenghty question and I will try and be specific as
>possible.
> I currently have a single server with Windows 2003 Enterprise running a
> single instance of SQL Server Standard 2005. The Master and other
> "default"
> databases are in the Default location on the C drive. The "Production"
> databases are on "shared" resources on a CX-300 which is attached to my
> Server via EMULEX HBA's. Now a Caviat to this configuration is that it
> used
> to be part of a cluster.
> When I first started working here I found that it had been configured with
> an 8 gig C drive partion and I needed to reconfigure them one at a time,
> So
> when I went to remove SQL off of the second node to be able to "evict" it
> from the cluster, I did it through "Add/Remove Programs" and the set up
> came
> up as normal, but after I selected the node and hit 'uninstall' It never
> asked me the usual SQL cluster questions and Just uninstalled SQL,
> removing
> all the shared "SQL Group" resources in the cluster" Not a tragedy,
> because I
> still have a node, But very disconcerting and curious
> So I rebuilt it and added it back to the cluster and manually configured
> everything. It worked pretty good, Everything failed over (SQL services,
> MSDTC, Drives and the databases attached okay, Howvere they since the
> Master
> did not match the other, none of the "objects" were there. so what I did
> was
> "stopped the services" on the good node, copied the databases into the
> default folder on the new node and brought it up, it seemed to work. But
> when
> I tried to attach my "production databases" it came back and said
> "recovery
> in progress" and would not attach them, it didn't go for very long before
> My
> SQL DB started whining about wanting to revert back because he had to go
> home.
> SO, I am assuming that there was a problem doing it that way or maybe
> eventually they would have recovered, We did try a "restore" first, But
> when
> we did a full backup of the Good Node "master" and tried to restore it to
> the
> new node, the versions didin't match.
> Now, I have a few questions I was hoping I could get a few pointers on. I
> have built two servers that I am going to build a new cluster on, so I
> want
> to "migrate" these SQL "default" database to the new cluster, Since it is
> also SAN attached, I am simply going to pull the single node out of the
> "Storage Group" that has the LUNS for the SQL Resources and add the two
> SQL
> Cluster NODES, giving the drives the exact same letters (of course
> attaching
> them one node at a time)
> So assuming I want to migrate the SQL default database to the Cluster is
> there a migration tool that will do this relatively risk free and can I
> put
> this database on a shared drive and have both nodes point to it and when
> failover occurs, the node being failed from detaches and the node being
> failed to attaches to these databases. The reason I ask is I have built
> several SQL Clusters, But they have all been new ones that were going to
> be
> used, I have not built one to replace a single node system.
> I haven't done a lot of research myself yet, I am looking for any
> microsoft
> tools that will assist me, But if anyone has done this or has some sagely
> advise, it would be greatly appreciated
> --
> Scott A Cummins
> Sr. Systems Engineer
> Equity Analytics
> ( A division of Merrill Lynch)
> 14614 N. Keirland Blvd
> Scottsdale, AZ 85254
> 480-998-3515|||Hey Geoff
Okay, I will..Now What is a "Normal Host Migration" and is it possible to
create a single set of system databases(Master, Model, MSDB and Tempdb) on a
shared drive and have both nodes connect to it when they fail over
Just so you know, I am by no stretch of the imagination a SQL guy, My DB is
even having issues
--
Scott A Cummins
Sr. Systems Engineer
Equity Analytics
( A division of Merrill Lynch)
14614 N. Keirland Blvd
Scottsdale, AZ 85254
480-998-3515
"Scott Cummins" wrote:
> I have a rather lenghty question and I will try and be specific as possible.
> I currently have a single server with Windows 2003 Enterprise running a
> single instance of SQL Server Standard 2005. The Master and other "default"
> databases are in the Default location on the C drive. The "Production"
> databases are on "shared" resources on a CX-300 which is attached to my
> Server via EMULEX HBA's. Now a Caviat to this configuration is that it used
> to be part of a cluster.
> When I first started working here I found that it had been configured with
> an 8 gig C drive partion and I needed to reconfigure them one at a time, So
> when I went to remove SQL off of the second node to be able to "evict" it
> from the cluster, I did it through "Add/Remove Programs" and the set up came
> up as normal, but after I selected the node and hit 'uninstall' It never
> asked me the usual SQL cluster questions and Just uninstalled SQL, removing
> all the shared "SQL Group" resources in the cluster" Not a tragedy, because I
> still have a node, But very disconcerting and curious
> So I rebuilt it and added it back to the cluster and manually configured
> everything. It worked pretty good, Everything failed over (SQL services,
> MSDTC, Drives and the databases attached okay, Howvere they since the Master
> did not match the other, none of the "objects" were there. so what I did was
> "stopped the services" on the good node, copied the databases into the
> default folder on the new node and brought it up, it seemed to work. But when
> I tried to attach my "production databases" it came back and said "recovery
> in progress" and would not attach them, it didn't go for very long before My
> SQL DB started whining about wanting to revert back because he had to go home.
> SO, I am assuming that there was a problem doing it that way or maybe
> eventually they would have recovered, We did try a "restore" first, But when
> we did a full backup of the Good Node "master" and tried to restore it to the
> new node, the versions didin't match.
> Now, I have a few questions I was hoping I could get a few pointers on. I
> have built two servers that I am going to build a new cluster on, so I want
> to "migrate" these SQL "default" database to the new cluster, Since it is
> also SAN attached, I am simply going to pull the single node out of the
> "Storage Group" that has the LUNS for the SQL Resources and add the two SQL
> Cluster NODES, giving the drives the exact same letters (of course attaching
> them one node at a time)
> So assuming I want to migrate the SQL default database to the Cluster is
> there a migration tool that will do this relatively risk free and can I put
> this database on a shared drive and have both nodes point to it and when
> failover occurs, the node being failed from detaches and the node being
> failed to attaches to these databases. The reason I ask is I have built
> several SQL Clusters, But they have all been new ones that were going to be
> used, I have not built one to replace a single node system.
> I haven't done a lot of research myself yet, I am looking for any microsoft
> tools that will assist me, But if anyone has done this or has some sagely
> advise, it would be greatly appreciated
> --
> Scott A Cummins
> Sr. Systems Engineer
> Equity Analytics
> ( A division of Merrill Lynch)
> 14614 N. Keirland Blvd
> Scottsdale, AZ 85254
> 480-998-3515|||What you describe is exactly how clustering is supposed to work. SQL has
binaries on each node, but all databases, including system databases are on
a "shared" storage system. The Cluster service arbitrates which node owns
the resources associated with a particular SQL server instance. The
service, network name, IP address, and all storage disks go together to a
new node when the group fails over or is moved.
So, set up the cluster (single node if necessary) script your logins, jobs,
etc. and get them moved to the new system. You can then move the database
files to the new cluster and attach them, or use a backup-restore technique
to sync the systems. That is the very short version of a normal migration.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Scott Cummins" <scummins@.equitymethods.com> wrote in message
news:A0FC13A4-857B-4B08-9DDC-BD93F32504A7@.microsoft.com...
> Hey Geoff
> Okay, I will..Now What is a "Normal Host Migration" and is it possible to
> create a single set of system databases(Master, Model, MSDB and Tempdb) on
> a
> shared drive and have both nodes connect to it when they fail over
> Just so you know, I am by no stretch of the imagination a SQL guy, My DB
> is
> even having issues
> --
> Scott A Cummins
> Sr. Systems Engineer
> Equity Analytics
> ( A division of Merrill Lynch)
> 14614 N. Keirland Blvd
> Scottsdale, AZ 85254
> 480-998-3515
>
> "Scott Cummins" wrote:
>> I have a rather lenghty question and I will try and be specific as
>> possible.
>> I currently have a single server with Windows 2003 Enterprise running a
>> single instance of SQL Server Standard 2005. The Master and other
>> "default"
>> databases are in the Default location on the C drive. The "Production"
>> databases are on "shared" resources on a CX-300 which is attached to my
>> Server via EMULEX HBA's. Now a Caviat to this configuration is that it
>> used
>> to be part of a cluster.
>> When I first started working here I found that it had been configured
>> with
>> an 8 gig C drive partion and I needed to reconfigure them one at a time,
>> So
>> when I went to remove SQL off of the second node to be able to "evict" it
>> from the cluster, I did it through "Add/Remove Programs" and the set up
>> came
>> up as normal, but after I selected the node and hit 'uninstall' It never
>> asked me the usual SQL cluster questions and Just uninstalled SQL,
>> removing
>> all the shared "SQL Group" resources in the cluster" Not a tragedy,
>> because I
>> still have a node, But very disconcerting and curious
>> So I rebuilt it and added it back to the cluster and manually configured
>> everything. It worked pretty good, Everything failed over (SQL services,
>> MSDTC, Drives and the databases attached okay, Howvere they since the
>> Master
>> did not match the other, none of the "objects" were there. so what I did
>> was
>> "stopped the services" on the good node, copied the databases into the
>> default folder on the new node and brought it up, it seemed to work. But
>> when
>> I tried to attach my "production databases" it came back and said
>> "recovery
>> in progress" and would not attach them, it didn't go for very long before
>> My
>> SQL DB started whining about wanting to revert back because he had to go
>> home.
>> SO, I am assuming that there was a problem doing it that way or maybe
>> eventually they would have recovered, We did try a "restore" first, But
>> when
>> we did a full backup of the Good Node "master" and tried to restore it to
>> the
>> new node, the versions didin't match.
>> Now, I have a few questions I was hoping I could get a few pointers on. I
>> have built two servers that I am going to build a new cluster on, so I
>> want
>> to "migrate" these SQL "default" database to the new cluster, Since it is
>> also SAN attached, I am simply going to pull the single node out of the
>> "Storage Group" that has the LUNS for the SQL Resources and add the two
>> SQL
>> Cluster NODES, giving the drives the exact same letters (of course
>> attaching
>> them one node at a time)
>> So assuming I want to migrate the SQL default database to the Cluster is
>> there a migration tool that will do this relatively risk free and can I
>> put
>> this database on a shared drive and have both nodes point to it and when
>> failover occurs, the node being failed from detaches and the node being
>> failed to attaches to these databases. The reason I ask is I have built
>> several SQL Clusters, But they have all been new ones that were going to
>> be
>> used, I have not built one to replace a single node system.
>> I haven't done a lot of research myself yet, I am looking for any
>> microsoft
>> tools that will assist me, But if anyone has done this or has some sagely
>> advise, it would be greatly appreciated
>> --
>> Scott A Cummins
>> Sr. Systems Engineer
>> Equity Analytics
>> ( A division of Merrill Lynch)
>> 14614 N. Keirland Blvd
>> Scottsdale, AZ 85254
>> 480-998-3515|||Geoff
Thanks again, that is a very good piece of Knowledge to have, For some
reason I had assumed that the system databases were loaded separately on each
node and because it was cluster "aware" it replictaed any changes on one
node to the other node so that the "system databases" were exactly the same.
It seems my DBA has so much info in the system databases, he will probably
have to script everything over. He was hoping just to be bale to "migrate" or
"copy" the current System databases over to the new system.
Again, you are of tremendous help and I really appreciate it
--
Scott A Cummins
Sr. Systems Engineer
Equity Analytics
( A division of Merrill Lynch)
14614 N. Keirland Blvd
Scottsdale, AZ 85254
480-998-3515
"Geoff N. Hiten" wrote:
> What you describe is exactly how clustering is supposed to work. SQL has
> binaries on each node, but all databases, including system databases are on
> a "shared" storage system. The Cluster service arbitrates which node owns
> the resources associated with a particular SQL server instance. The
> service, network name, IP address, and all storage disks go together to a
> new node when the group fails over or is moved.
> So, set up the cluster (single node if necessary) script your logins, jobs,
> etc. and get them moved to the new system. You can then move the database
> files to the new cluster and attach them, or use a backup-restore technique
> to sync the systems. That is the very short version of a normal migration.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "Scott Cummins" <scummins@.equitymethods.com> wrote in message
> news:A0FC13A4-857B-4B08-9DDC-BD93F32504A7@.microsoft.com...
> > Hey Geoff
> >
> > Okay, I will..Now What is a "Normal Host Migration" and is it possible to
> > create a single set of system databases(Master, Model, MSDB and Tempdb) on
> > a
> > shared drive and have both nodes connect to it when they fail over
> >
> > Just so you know, I am by no stretch of the imagination a SQL guy, My DB
> > is
> > even having issues
> > --
> > Scott A Cummins
> > Sr. Systems Engineer
> > Equity Analytics
> > ( A division of Merrill Lynch)
> > 14614 N. Keirland Blvd
> > Scottsdale, AZ 85254
> > 480-998-3515
> >
> >
> > "Scott Cummins" wrote:
> >
> >> I have a rather lenghty question and I will try and be specific as
> >> possible.
> >>
> >> I currently have a single server with Windows 2003 Enterprise running a
> >> single instance of SQL Server Standard 2005. The Master and other
> >> "default"
> >> databases are in the Default location on the C drive. The "Production"
> >> databases are on "shared" resources on a CX-300 which is attached to my
> >> Server via EMULEX HBA's. Now a Caviat to this configuration is that it
> >> used
> >> to be part of a cluster.
> >>
> >> When I first started working here I found that it had been configured
> >> with
> >> an 8 gig C drive partion and I needed to reconfigure them one at a time,
> >> So
> >> when I went to remove SQL off of the second node to be able to "evict" it
> >> from the cluster, I did it through "Add/Remove Programs" and the set up
> >> came
> >> up as normal, but after I selected the node and hit 'uninstall' It never
> >> asked me the usual SQL cluster questions and Just uninstalled SQL,
> >> removing
> >> all the shared "SQL Group" resources in the cluster" Not a tragedy,
> >> because I
> >> still have a node, But very disconcerting and curious
> >>
> >> So I rebuilt it and added it back to the cluster and manually configured
> >> everything. It worked pretty good, Everything failed over (SQL services,
> >> MSDTC, Drives and the databases attached okay, Howvere they since the
> >> Master
> >> did not match the other, none of the "objects" were there. so what I did
> >> was
> >> "stopped the services" on the good node, copied the databases into the
> >> default folder on the new node and brought it up, it seemed to work. But
> >> when
> >> I tried to attach my "production databases" it came back and said
> >> "recovery
> >> in progress" and would not attach them, it didn't go for very long before
> >> My
> >> SQL DB started whining about wanting to revert back because he had to go
> >> home.
> >>
> >> SO, I am assuming that there was a problem doing it that way or maybe
> >> eventually they would have recovered, We did try a "restore" first, But
> >> when
> >> we did a full backup of the Good Node "master" and tried to restore it to
> >> the
> >> new node, the versions didin't match.
> >>
> >> Now, I have a few questions I was hoping I could get a few pointers on. I
> >> have built two servers that I am going to build a new cluster on, so I
> >> want
> >> to "migrate" these SQL "default" database to the new cluster, Since it is
> >> also SAN attached, I am simply going to pull the single node out of the
> >> "Storage Group" that has the LUNS for the SQL Resources and add the two
> >> SQL
> >> Cluster NODES, giving the drives the exact same letters (of course
> >> attaching
> >> them one node at a time)
> >>
> >> So assuming I want to migrate the SQL default database to the Cluster is
> >> there a migration tool that will do this relatively risk free and can I
> >> put
> >> this database on a shared drive and have both nodes point to it and when
> >> failover occurs, the node being failed from detaches and the node being
> >> failed to attaches to these databases. The reason I ask is I have built
> >> several SQL Clusters, But they have all been new ones that were going to
> >> be
> >> used, I have not built one to replace a single node system.
> >>
> >> I haven't done a lot of research myself yet, I am looking for any
> >> microsoft
> >> tools that will assist me, But if anyone has done this or has some sagely
> >> advise, it would be greatly appreciated
> >> --
> >> Scott A Cummins
> >> Sr. Systems Engineer
> >> Equity Analytics
> >> ( A division of Merrill Lynch)
> >> 14614 N. Keirland Blvd
> >> Scottsdale, AZ 85254
> >> 480-998-3515
>