Cluster Set up Question

Hello,
We are implementing clustering (Active/Passive) in our Production environment.
So we will have two windows server 2003 for SQL SERVER. (ProdDbServer1 and
ProdDbServer2)
I will install SQL Server on both the machine and i will create my database
(FinDB)
on both the machine's SQL SERVER with all the tables,store procs, functions
etc.
So lets say if my Active Server is in action and accepting all the OLTP
transaction
and for some reason if it goes fail...How can passive server can take over,
how
passive server can be at the same point of the fail of active server ?
Bacically how both the Database will be in sink with each other ?
Pls help me
A cluster consists of two machines and a storage system that is physically
connected to both systems. The Cluster software arbitrates ownership of the
disk resource so that only one server can access it at a time. The cluster
software also monitor the sql instance on the active host node for failure.
If the first node fails, ownership of the SQL instance and the corresponding
disk resource is transferred to the other node. From the outside, it looks
like the SQL Server stopped and restarted.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:3F5342B1-3AB6-4CD7-B763-33587B1BED0E@.microsoft.com...
> Hello,
> We are implementing clustering (Active/Passive) in our Production
> environment.
> So we will have two windows server 2003 for SQL SERVER. (ProdDbServer1 and
> ProdDbServer2)
> I will install SQL Server on both the machine and i will create my
> database
> (FinDB)
> on both the machine's SQL SERVER with all the tables,store procs,
> functions
> etc.
> So lets say if my Active Server is in action and accepting all the OLTP
> transaction
> and for some reason if it goes fail...How can passive server can take
> over,
> how
> passive server can be at the same point of the fail of active server ?
> Bacically how both the Database will be in sink with each other ?
> Pls help me
|||Thanks Geoff.
So From your reply, I understand that i will have to install SQL SERVER 2000
on both the windows 2003 server.
I have following questions.
1) After installing SQL SERVER on both the windows m/c, When i create
database on both the machine, Would i have to mentioned same Data files and
Transaction Log Path ( Because we will have storage will be in SAN), when i
create DB ?
2) For example if i create one table in the FinDB of on ServerA and then if
i go to FinDB of ServerB, I will see that table there too, if i will have
Datafiles and Transaction Log Path are same for both the servers ?
"Geoff N. Hiten" wrote:

> A cluster consists of two machines and a storage system that is physically
> connected to both systems. The Cluster software arbitrates ownership of the
> disk resource so that only one server can access it at a time. The cluster
> software also monitor the sql instance on the active host node for failure.
> If the first node fails, ownership of the SQL instance and the corresponding
> disk resource is transferred to the other node. From the outside, it looks
> like the SQL Server stopped and restarted.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "mvp" <mvp@.discussions.microsoft.com> wrote in message
> news:3F5342B1-3AB6-4CD7-B763-33587B1BED0E@.microsoft.com...
>
>
|||Thanks Geoff.
So From your reply, I understand that i will have to install SQL SERVER 2000
on both the windows 2003 server.
I have following questions.
1) After installing SQL SERVER on both the windows m/c, When i create
database on both the machine, Would i have to mentioned same Data files and
Transaction Log Path ( Because we will have storage will be in SAN), when i
create DB ?
2) For example if i create one table in the FinDB of on ServerA and then if
i go to FinDB of ServerB, I will see that table there too, if i will have
Datafiles and Transaction Log Path are same for both the servers ?
"Geoff N. Hiten" wrote:

> A cluster consists of two machines and a storage system that is physically
> connected to both systems. The Cluster software arbitrates ownership of the
> disk resource so that only one server can access it at a time. The cluster
> software also monitor the sql instance on the active host node for failure.
> If the first node fails, ownership of the SQL instance and the corresponding
> disk resource is transferred to the other node. From the outside, it looks
> like the SQL Server stopped and restarted.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "mvp" <mvp@.discussions.microsoft.com> wrote in message
> news:3F5342B1-3AB6-4CD7-B763-33587B1BED0E@.microsoft.com...
>
>
|||Does anyone want to rethink their perspective on why active/active and
active/passive should be used as terminology?
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:3F5342B1-3AB6-4CD7-B763-33587B1BED0E@.microsoft.com...
> Hello,
> We are implementing clustering (Active/Passive) in our Production
> environment.
> So we will have two windows server 2003 for SQL SERVER. (ProdDbServer1 and
> ProdDbServer2)
> I will install SQL Server on both the machine and i will create my
> database
> (FinDB)
> on both the machine's SQL SERVER with all the tables,store procs,
> functions
> etc.
> So lets say if my Active Server is in action and accepting all the OLTP
> transaction
> and for some reason if it goes fail...How can passive server can take
> over,
> how
> passive server can be at the same point of the fail of active server ?
> Bacically how both the Database will be in sink with each other ?
> Pls help me
|||If you install SQL Server twice, you will have 2 physically separate
instances of SQL Server running within a single cluster. If you then create
your database in each of these instances, you will have 2 physically
separate copies of the database. In order to synchronize data between the
two databases, you would have to implement replication, log shipping, or
database mirroring (2005 only). If one of the servers failed, you would
have to change your application connections to point at the other SQL Server
instance.
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:3F5342B1-3AB6-4CD7-B763-33587B1BED0E@.microsoft.com...
> Hello,
> We are implementing clustering (Active/Passive) in our Production
> environment.
> So we will have two windows server 2003 for SQL SERVER. (ProdDbServer1 and
> ProdDbServer2)
> I will install SQL Server on both the machine and i will create my
> database
> (FinDB)
> on both the machine's SQL SERVER with all the tables,store procs,
> functions
> etc.
> So lets say if my Active Server is in action and accepting all the OLTP
> transaction
> and for some reason if it goes fail...How can passive server can take
> over,
> how
> passive server can be at the same point of the fail of active server ?
> Bacically how both the Database will be in sink with each other ?
> Pls help me
|||No. You create the cluster. You then install a single copy of SQL Server
into the cluster. You then create your database inside that instance. You
do not install SQL Server directly to a machine. It is installed to the
cluster.
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:95A55E02-1BCB-47E1-9458-E01080486B3D@.microsoft.com...[vbcol=seagreen]
> Thanks Geoff.
> So From your reply, I understand that i will have to install SQL SERVER
> 2000
> on both the windows 2003 server.
> I have following questions.
> 1) After installing SQL SERVER on both the windows m/c, When i create
> database on both the machine, Would i have to mentioned same Data files
> and
> Transaction Log Path ( Because we will have storage will be in SAN), when
> i
> create DB ?
> 2) For example if i create one table in the FinDB of on ServerA and then
> if
> i go to FinDB of ServerB, I will see that table there too, if i will have
> Datafiles and Transaction Log Path are same for both the servers ?
> "Geoff N. Hiten" wrote:
|||The SQL Installer is cluster-aware. That is, it handles all the details of
cluster-wide installation. It creates the virtual server and installs the
local binaries on all nodes. It also groups all the cluster resources and
sets the dependencies correctly. Once SQL is running and you are connected,
there is almost no difference in SQL operations between clustered and
non-clustered SQL Servers. You end up connecting to a virtual SQL Server
instance that looks identical regardless of which node is actually hosting
it at the moment.
Here is an excellent overview of SQL failover clustering that you may find
helpful:
http://www.microsoft.com/technet/pro.../failclus.mspx
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:95A55E02-1BCB-47E1-9458-E01080486B3D@.microsoft.com...[vbcol=seagreen]
> Thanks Geoff.
> So From your reply, I understand that i will have to install SQL SERVER
> 2000
> on both the windows 2003 server.
> I have following questions.
> 1) After installing SQL SERVER on both the windows m/c, When i create
> database on both the machine, Would i have to mentioned same Data files
> and
> Transaction Log Path ( Because we will have storage will be in SAN), when
> i
> create DB ?
> 2) For example if i create one table in the FinDB of on ServerA and then
> if
> i go to FinDB of ServerB, I will see that table there too, if i will have
> Datafiles and Transaction Log Path are same for both the servers ?
> "Geoff N. Hiten" wrote:
|||Thanks for the reply.
No i do not want two sql server instance in single cluster, I just want one
instance in my cluster. so what should i do, install SQL SERVER 2000 Software
and create my database in just one machine (ProdSqlServerA)?
If i do like this and if i go to another sever (ProdSqlServerB), would i see
SQL SERVER and my created Db on that maching too ?
"Z" wrote:

> If you install SQL Server twice, you will have 2 physically separate
> instances of SQL Server running within a single cluster. If you then create
> your database in each of these instances, you will have 2 physically
> separate copies of the database. In order to synchronize data between the
> two databases, you would have to implement replication, log shipping, or
> database mirroring (2005 only). If one of the servers failed, you would
> have to change your application connections to point at the other SQL Server
> instance.
>
> "mvp" <mvp@.discussions.microsoft.com> wrote in message
> news:3F5342B1-3AB6-4CD7-B763-33587B1BED0E@.microsoft.com...
>
>
|||No thanks! I don't want Mike and Geoff mad at me anymore on this subject.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
"Z" <z@.z.com> wrote in message
news:%23MIgYMvFGHA.984@.tk2msftngp13.phx.gbl...
> Does anyone want to rethink their perspective on why active/active and
> active/passive should be used as terminology?
>
> "mvp" <mvp@.discussions.microsoft.com> wrote in message
> news:3F5342B1-3AB6-4CD7-B763-33587B1BED0E@.microsoft.com...
>
sqlsql

Cluster failing connections with SSL error message

We recently migrated our production environment from a win2k3/SQL 2000 EE cluster to a new 64 bit win2k3/ SQL 2005 SP1 Cluster.

Cluster works fine for a while (3-6 hours), then our logins start failing. We recieve the following error in the event log:

"The server was unable to load the SSL provider library needed to log in; the connection has been closed. SSL is used to encrypt either the login sequence or all communications, depending on how the administrator has configured the server. See Books Online for information on this error message"

The only way to resume connectivity is to offline and online the sql service in the cluster.

The only reference to this error that I can find has something to do with a hotfix that might be available?

It's sad to say but we are experiencing the same issue.
Only we get this after a good 30 minutes...

Someone from Microsoft Spain is flying over here.

Very painful

|||

In working with microsoft PSS, we found the issue to be a permissions issue of all things.

Our service account did not have rights to lock pages in memory on either machines in the cluster. (even though is a domain administrator).

Once we gave it this right and restarted the SQL Server object in the cluster we have not had a similar failure since.

|||

This was unfortunately enabled already so it did not cause the problem here :-(

|||Hi Wesley,

Any joy with this? I'm currently going throught the same thing, and also have locked pages enabled for my SQL service account. I'm currently going through MS Support, but seem to be going around the houses :o(

Thanks

Stu|||Any luck, we have a similar problem.
We noticed that memory usage grows slowly beyond physical memory (32G) then connection failiurs start and server becomes inoperable.
Workaround solution is to reduce max mem in the server, down to 18G from (25).
This causes the server and OS to release memory. After the system stabilized we increased the maxmem in the server back to its original settng. Lock pages in memory is set.

Hope this helps.

This is a workaround, so we don't have to bounce the server, we are still looking for the cause.
esk.

Setup:2 node Cluster 4*dual 64bit AMD, 32Gb, SQL 2005 Enterprise SP0.|||

Hi,

Apparently we have hit a bug where the usercache size becomes too large and for every execution of a statement SQL Server has to check this store.
This store is not cleaned and keep growing too the point where lookups become blocked with spinlocks. If you want I can give you the commands to check this.

This will be fixed in SP2 because of the major impact a fix would have. The workaround is adding the users to the sysadmin group or clearing the cache manually with DBCC FREESYSTEMCACHE.

HTH

Kind regards,
Wesley

|||Thanks for the replies !

Wes, if you could give the command to check the spinlocks that would be great.

many thanks

Stu|||

Stu,

It's actually the query for checking the cache size.

SELECT * FROM sys.dm_os_memory_clerks
WHERE type = 'USERSTORE_TOKENPERM'
We have seen problems as of 150MB in our environment but this probably all depends on the load and types of queries. Add up the single pages and multipages to determine the size.

HTH
Wesley

|||Thanks Wesley!

After much investigation it turned out the problem was the iLO Management Channel Interface Driver (Cpqcidrv.sys) from Hewlett-Packard. Apparently this

is known to cause this issue on x64 editions of SQL Server 2005. The driver

causes the SQL Server 2005 64-bit working set to be trimmed!


We do not use ILO on our HP Servers, so I disable it in device manager, and so far so good (fingers crossed)!!!

Thanks

Stu|||

Great news!

There seems to be a hotfix for Windows 2003 concerning working set trims too.
Apparently working sets get trimmed when you use Terminal Services on the server... so beware ;-)

|||

If possible could you share the hotfix number? Thanks in advance,

Drew

|||

Hey Wesley. Can you please elaborate more on this 'working sets get trimmed' and beware. and the hotfix available? Thank you very much. I've had problems with this also.

|||

I am also have a problem with this issue. I am going to try disabling the ILO. The terminal service hotfix seems to be this one:

http://support.microsoft.com/kb/905865/en-us

sqlsql

Cluster failing connections with SSL error message

We recently migrated our production environment from a win2k3/SQL 2000 EE cluster to a new 64 bit win2k3/ SQL 2005 SP1 Cluster.

Cluster works fine for a while (3-6 hours), then our logins start failing. We recieve the following error in the event log:

"The server was unable to load the SSL provider library needed to log in; the connection has been closed. SSL is used to encrypt either the login sequence or all communications, depending on how the administrator has configured the server. See Books Online for information on this error message"

The only way to resume connectivity is to offline and online the sql service in the cluster.

The only reference to this error that I can find has something to do with a hotfix that might be available?

It's sad to say but we are experiencing the same issue.
Only we get this after a good 30 minutes...

Someone from Microsoft Spain is flying over here.

Very painful

|||

In working with microsoft PSS, we found the issue to be a permissions issue of all things.

Our service account did not have rights to lock pages in memory on either machines in the cluster. (even though is a domain administrator).

Once we gave it this right and restarted the SQL Server object in the cluster we have not had a similar failure since.

|||

This was unfortunately enabled already so it did not cause the problem here :-(

|||Hi Wesley,

Any joy with this? I'm currently going throught the same thing, and also have locked pages enabled for my SQL service account. I'm currently going through MS Support, but seem to be going around the houses :o(

Thanks

Stu|||Any luck, we have a similar problem.
We noticed that memory usage grows slowly beyond physical memory (32G) then connection failiurs start and server becomes inoperable.
Workaround solution is to reduce max mem in the server, down to 18G from (25).
This causes the server and OS to release memory. After the system stabilized we increased the maxmem in the server back to its original settng. Lock pages in memory is set.

Hope this helps.

This is a workaround, so we don't have to bounce the server, we are still looking for the cause.
esk.

Setup:2 node Cluster 4*dual 64bit AMD, 32Gb, SQL 2005 Enterprise SP0.|||

Hi,

Apparently we have hit a bug where the usercache size becomes too large and for every execution of a statement SQL Server has to check this store.
This store is not cleaned and keep growing too the point where lookups become blocked with spinlocks. If you want I can give you the commands to check this.

This will be fixed in SP2 because of the major impact a fix would have. The workaround is adding the users to the sysadmin group or clearing the cache manually with DBCC FREESYSTEMCACHE.

HTH

Kind regards,
Wesley

|||Thanks for the replies !

Wes, if you could give the command to check the spinlocks that would be great.

many thanks

Stu|||

Stu,

It's actually the query for checking the cache size.

SELECT * FROM sys.dm_os_memory_clerks
WHERE type = 'USERSTORE_TOKENPERM'
We have seen problems as of 150MB in our environment but this probably all depends on the load and types of queries. Add up the single pages and multipages to determine the size.

HTH
Wesley

|||Thanks Wesley!

After much investigation it turned out the problem was the iLO Management Channel Interface Driver (Cpqcidrv.sys) from Hewlett-Packard. Apparently this

is known to cause this issue on x64 editions of SQL Server 2005. The driver

causes the SQL Server 2005 64-bit working set to be trimmed!


We do not use ILO on our HP Servers, so I disable it in device manager, and so far so good (fingers crossed)!!!

Thanks

Stu|||

Great news!

There seems to be a hotfix for Windows 2003 concerning working set trims too.
Apparently working sets get trimmed when you use Terminal Services on the server... so beware ;-)

|||

If possible could you share the hotfix number? Thanks in advance,

Drew

|||

Hey Wesley. Can you please elaborate more on this 'working sets get trimmed' and beware. and the hotfix available? Thank you very much. I've had problems with this also.

|||

I am also have a problem with this issue. I am going to try disabling the ILO. The terminal service hotfix seems to be this one:

http://support.microsoft.com/kb/905865/en-us

Cluster failing connections with SSL error message

We recently migrated our production environment from a win2k3/SQL 2000 EE cluster to a new 64 bit win2k3/ SQL 2005 SP1 Cluster.

Cluster works fine for a while (3-6 hours), then our logins start failing. We recieve the following error in the event log:

"The server was unable to load the SSL provider library needed to log in; the connection has been closed. SSL is used to encrypt either the login sequence or all communications, depending on how the administrator has configured the server. See Books Online for information on this error message"

The only way to resume connectivity is to offline and online the sql service in the cluster.

The only reference to this error that I can find has something to do with a hotfix that might be available?

It's sad to say but we are experiencing the same issue.
Only we get this after a good 30 minutes...

Someone from Microsoft Spain is flying over here.

Very painful

|||

In working with microsoft PSS, we found the issue to be a permissions issue of all things.

Our service account did not have rights to lock pages in memory on either machines in the cluster. (even though is a domain administrator).

Once we gave it this right and restarted the SQL Server object in the cluster we have not had a similar failure since.

|||

This was unfortunately enabled already so it did not cause the problem here :-(

|||Hi Wesley,

Any joy with this? I'm currently going throught the same thing, and also have locked pages enabled for my SQL service account. I'm currently going through MS Support, but seem to be going around the houses :o(

Thanks

Stu|||Any luck, we have a similar problem.
We noticed that memory usage grows slowly beyond physical memory (32G) then connection failiurs start and server becomes inoperable.
Workaround solution is to reduce max mem in the server, down to 18G from (25).
This causes the server and OS to release memory. After the system stabilized we increased the maxmem in the server back to its original settng. Lock pages in memory is set.

Hope this helps.

This is a workaround, so we don't have to bounce the server, we are still looking for the cause.
esk.

Setup:2 node Cluster 4*dual 64bit AMD, 32Gb, SQL 2005 Enterprise SP0.|||

Hi,

Apparently we have hit a bug where the usercache size becomes too large and for every execution of a statement SQL Server has to check this store.
This store is not cleaned and keep growing too the point where lookups become blocked with spinlocks. If you want I can give you the commands to check this.

This will be fixed in SP2 because of the major impact a fix would have. The workaround is adding the users to the sysadmin group or clearing the cache manually with DBCC FREESYSTEMCACHE.

HTH

Kind regards,
Wesley

|||Thanks for the replies !

Wes, if you could give the command to check the spinlocks that would be great.

many thanks

Stu|||

Stu,

It's actually the query for checking the cache size.

SELECT * FROM sys.dm_os_memory_clerks
WHERE type = 'USERSTORE_TOKENPERM'
We have seen problems as of 150MB in our environment but this probably all depends on the load and types of queries. Add up the single pages and multipages to determine the size.

HTH
Wesley

|||Thanks Wesley!

After much investigation it turned out the problem was the iLO Management Channel Interface Driver (Cpqcidrv.sys) from Hewlett-Packard. Apparently this

is known to cause this issue on x64 editions of SQL Server 2005. The driver

causes the SQL Server 2005 64-bit working set to be trimmed!


We do not use ILO on our HP Servers, so I disable it in device manager, and so far so good (fingers crossed)!!!

Thanks

Stu|||

Great news!

There seems to be a hotfix for Windows 2003 concerning working set trims too.
Apparently working sets get trimmed when you use Terminal Services on the server... so beware ;-)

|||

If possible could you share the hotfix number? Thanks in advance,

Drew

|||

Hey Wesley. Can you please elaborate more on this 'working sets get trimmed' and beware. and the hotfix available? Thank you very much. I've had problems with this also.

|||

I am also have a problem with this issue. I am going to try disabling the ILO. The terminal service hotfix seems to be this one:

http://support.microsoft.com/kb/905865/en-us

Cluster failing connections with SSL error message

We recently migrated our production environment from a win2k3/SQL 2000 EE cluster to a new 64 bit win2k3/ SQL 2005 SP1 Cluster.

Cluster works fine for a while (3-6 hours), then our logins start failing. We recieve the following error in the event log:

"The server was unable to load the SSL provider library needed to log in; the connection has been closed. SSL is used to encrypt either the login sequence or all communications, depending on how the administrator has configured the server. See Books Online for information on this error message"

The only way to resume connectivity is to offline and online the sql service in the cluster.

The only reference to this error that I can find has something to do with a hotfix that might be available?

It's sad to say but we are experiencing the same issue.
Only we get this after a good 30 minutes...

Someone from Microsoft Spain is flying over here.

Very painful

|||

In working with microsoft PSS, we found the issue to be a permissions issue of all things.

Our service account did not have rights to lock pages in memory on either machines in the cluster. (even though is a domain administrator).

Once we gave it this right and restarted the SQL Server object in the cluster we have not had a similar failure since.

|||

This was unfortunately enabled already so it did not cause the problem here :-(

|||Hi Wesley,

Any joy with this? I'm currently going throught the same thing, and also have locked pages enabled for my SQL service account. I'm currently going through MS Support, but seem to be going around the houses :o(

Thanks

Stu|||Any luck, we have a similar problem.
We noticed that memory usage grows slowly beyond physical memory (32G) then connection failiurs start and server becomes inoperable.
Workaround solution is to reduce max mem in the server, down to 18G from (25).
This causes the server and OS to release memory. After the system stabilized we increased the maxmem in the server back to its original settng. Lock pages in memory is set.

Hope this helps.

This is a workaround, so we don't have to bounce the server, we are still looking for the cause.
esk.

Setup:2 node Cluster 4*dual 64bit AMD, 32Gb, SQL 2005 Enterprise SP0.|||

Hi,

Apparently we have hit a bug where the usercache size becomes too large and for every execution of a statement SQL Server has to check this store.
This store is not cleaned and keep growing too the point where lookups become blocked with spinlocks. If you want I can give you the commands to check this.

This will be fixed in SP2 because of the major impact a fix would have. The workaround is adding the users to the sysadmin group or clearing the cache manually with DBCC FREESYSTEMCACHE.

HTH

Kind regards,
Wesley

|||Thanks for the replies !

Wes, if you could give the command to check the spinlocks that would be great.

many thanks

Stu|||

Stu,

It's actually the query for checking the cache size.

SELECT * FROM sys.dm_os_memory_clerks
WHERE type = 'USERSTORE_TOKENPERM'
We have seen problems as of 150MB in our environment but this probably all depends on the load and types of queries. Add up the single pages and multipages to determine the size.

HTH
Wesley

|||Thanks Wesley!

After much investigation it turned out the problem was the iLO Management Channel Interface Driver (Cpqcidrv.sys) from Hewlett-Packard. Apparently this

is known to cause this issue on x64 editions of SQL Server 2005. The driver

causes the SQL Server 2005 64-bit working set to be trimmed!


We do not use ILO on our HP Servers, so I disable it in device manager, and so far so good (fingers crossed)!!!

Thanks

Stu|||

Great news!

There seems to be a hotfix for Windows 2003 concerning working set trims too.
Apparently working sets get trimmed when you use Terminal Services on the server... so beware ;-)

|||

If possible could you share the hotfix number? Thanks in advance,

Drew

|||

Hey Wesley. Can you please elaborate more on this 'working sets get trimmed' and beware. and the hotfix available? Thank you very much. I've had problems with this also.

|||

I am also have a problem with this issue. I am going to try disabling the ILO. The terminal service hotfix seems to be this one:

http://support.microsoft.com/kb/905865/en-us

Cluster DR

My production cluster disks are corruptd - i would like to find out how to
restore from backups
I have all system backups and mdf and ldf files of the databases - I would
like to keep the logins, jobs etc
Thanks
Restoring a cluster is almost exactly the same as restoring a stand-alone
SQL Server. The only difference is that when you restire the master
database, the disks have to be online using the cluster tool, but leave the
SQL Server service offline. You then start and stop it in single-user mode
using the command-line. See BOL for details on how to restore a SQL Server.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"John Smith" <bukusu@.gmail.com> wrote in message
news:el5gBC7GGHA.532@.TK2MSFTNGP15.phx.gbl...
> My production cluster disks are corruptd - i would like to find out how to
> restore from backups
> I have all system backups and mdf and ldf files of the databases - I would
> like to keep the logins, jobs etc
>
> Thanks
>

cluster

Hello ,
I have couple of questions on clustering as well as on hardware.
1) Is the license for SQL Server 2005 Enterprise edition ( Production) on
active/active and active/passive is same?
2) Is the license for sql server 2005 is socket based or processor based, is
there any differnce between them.
3) Do we have any figures for SQL Server 2005 performance on single socket
as well as dual socket servers ?
4) If we have a active/active cluster on single socket on quad core
processor , what will be the license as well as performance when compared to
the active/active on dual socket on quad processor?
Any input is appreciated?
Comments Inline
"IT" <IT@.discussions.microsoft.com> wrote in message
news:87B5B6CB-413D-4A12-A4D3-59CDAC0C569A@.microsoft.com...
> Hello ,
> I have couple of questions on clustering as well as on hardware.
> 1) Is the license for SQL Server 2005 Enterprise edition ( Production) on
> active/active and active/passive is same?
Sort of. There is a single-instance or N+1 exception to normal licensing
requirements. If you have a node that is always a standby system, Then it
doesn't have to be licensed for SQL. Two nodes, one instance, one license.
Four nodes, three instances, three licenses.
Also, on two-node clusters, you might look into Standard Edition for
clustering. It's lots cheaper and it does support two-node clusters.

> 2) Is the license for sql server 2005 is socket based or processor based,
> is
> there any differnce between them.
Socket-based. This makes dual and quad core systems very price competitive
from a licensing standpoint.

> 3) Do we have any figures for SQL Server 2005 performance on single socket
> as well as dual socket servers ?
I generally don't trust benchmarks all that much. With SQL, the nature of
the load can vary so much between systems that finding a matching benchmark
is difficult if not impossible. Dual and quad socket systems are commodity
priced so I wouldn't go with a single-socket system except for the very
lowest application. Certainly not for a cluster.

> 4) If we have a active/active cluster on single socket on quad core
> processor , what will be the license as well as performance when compared
> to
> the active/active on dual socket on quad processor?
>
Assuming per-socket licensing, you would need one socket license for the
first and four licenses for the second. CAL licensing (assuming the usage
meets the requirements) would be one server license and N CALS for the first
or two server licenses and N CALs for the second. N being the number of
client users or devices.

> Any input is appreciated?
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
|||Thanks that was helpful....
Is there any KB or white paper describing the same for some documentation...
"Geoff N. Hiten" wrote:

> Comments Inline
> "IT" <IT@.discussions.microsoft.com> wrote in message
> news:87B5B6CB-413D-4A12-A4D3-59CDAC0C569A@.microsoft.com...
> Sort of. There is a single-instance or N+1 exception to normal licensing
> requirements. If you have a node that is always a standby system, Then it
> doesn't have to be licensed for SQL. Two nodes, one instance, one license.
> Four nodes, three instances, three licenses.
> Also, on two-node clusters, you might look into Standard Edition for
> clustering. It's lots cheaper and it does support two-node clusters.
> Socket-based. This makes dual and quad core systems very price competitive
> from a licensing standpoint.
> I generally don't trust benchmarks all that much. With SQL, the nature of
> the load can vary so much between systems that finding a matching benchmark
> is difficult if not impossible. Dual and quad socket systems are commodity
> priced so I wouldn't go with a single-socket system except for the very
> lowest application. Certainly not for a cluster.
> Assuming per-socket licensing, you would need one socket license for the
> first and four licenses for the second. CAL licensing (assuming the usage
> meets the requirements) would be one server license and N CALS for the first
> or two server licenses and N CALs for the second. N being the number of
> client users or devices.
>
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
|||Microsoft SQL Server 2005 Licensing
http://www.microsoft.com/sql/howtobuy/default.mspx#EXE
The links to the white paper and FAQ are very useful
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"IT" <IT@.discussions.microsoft.com> wrote in message
news:8848E76E-646D-4AA9-B7FD-7FCCA6D5DAFE@.microsoft.com...[vbcol=seagreen]
> Thanks that was helpful....
> Is there any KB or white paper describing the same for some
> documentation...
> "Geoff N. Hiten" wrote:

Clone new database from existing

How do I go about creating a cloned copy of an existing database on the same server? I basically want to set up a test copy of a production database, but let it reside on the same server with a slightly different name, prefixed with 'Testing_".

I typically just use Studio Express Manager and not scripts to do admin tasks. Here's what I tried that didn't work so well:

* Had full backups of production db
* Detached production database I wanted to clone
* From within Win Explorer, copy the .mdf and .ldf files within same data folder and gave them a new name, prefixing with "Testing_"
* Re-attached prodution db
* Attached "Testing_..." database

Sql Express seems confused at this point with new database I attached. It references the full path of the database I copied from (c:\program files\microsoft sql server\mssql.1\data\...". I rename the datbase, removing full path and prefixing with "Testing_".

* Using Sql authentication, changed the default database for the Login account to the new cloned copy of database.
* Did some query level data sanitizing to the cloned copy of the db. I am positive I performed this on the correct db.
* Was able to connect/authenticate to the cloned copy through a custom VB app on my dev machine, and all looked well.
* Tried to connect to the cloned db from another machine and couldn't. Went back to my dev machine and noticed the original production db looked hosed -- there were no tree nodes available to it! Was still able to connect to the cloned "Testing_" db copy, but not able to connect/authenticate anymore to the production copy.
* Detached cloned copy of "Testing_" db and moved it out of the data folder.
* Detached production db. Got an error msg indicating operation failure of some kind, but the database was gone from the tree.
* Reattached the production db but couldn't hit it from my custom vb app. Re-did the login and user accounts used by the app and was able to hit it. However, the data sanitizing I had done earlier in the cloned copy clearly was visibile in the production db!
* Had to restore production db from backup. Had problems authenticating from app, and recreated User account before the app was to connect.

Q1: What I am doing wrong? Sql Express was obviously as confused as me somewhere along the line.

Q2: Is specifying a default database used by a Login account necessary for a VB app like mine. I think it was something I discovered in the past, however, this would seemingly present a problem whenever I want my app to hit a different database, established through a connection string.

TIA...

Rick

hi Rick,

when doing this kind of "sorcery", please be carefull when "re-registering" the database..

just to cross check, usually you should:
-detach the original db;

-copy it elsewhere or rename destination files (mdf, ldf and eventual ndf) say, with your prefix.. (orig.mdf becomes clone_of_orig.mdf and the like);

-reattach the original db via

USE [master]

GO

CREATE DATABASE [myDB] ON

( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data\myDB.mdf' ),

( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data\myDB_log.ldf' )

FOR ATTACH

GO

-the original logical db name is correct, as the original physical file(s) pointers..

-attach the cloned db via

USE [master]

GO

CREATE DATABASE [CLONE_of_myDB] ON

( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data\clone_of_myDB.mdf' ),

( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data\clone_of_myDB_log.ldf' )

FOR ATTACH

GO

as you can see, the logical name of the db is different, as are the referenced phisical file names..

-the internal logical names of each physical file are not considered to collide with other database's logical names, so you are not in trouble here..

regards

|||Hmmh... what you described is exactly what I did, with pecuilar results, except use Sql Express Manager.

- I detached original db
- Copied to new set and renamed (cloned)
- Reattached original db
- Attached cloned db

Is Sql Express Manager not up to this task?

Rick|||

Do you get an error message or what are you experiencing ? If you can′t get attach / detach to run for you, you can also use the backup / restore functionality for this.

HTH, jens K. Suessmeyer.

http://www.sqlserver2005.de

|||All I got was some meaningless generic "operation failed" type of error, plus, the odd behavior I described above when sanitizing (running update queries against) the cloned database.

I did try restoring the source db and specifying clone db as target, but was prevented from doing so with a message to the effect of "target database not in dataset so not allowable."

If somebody wouldn't mind trying to clone a db from the same server through Sql Express Manager, and let me know the results, I'd feel better about trying it again. When I tried it, I discovered I was simultaneously sanitizing both databases.

Rick|||The 'safest' (and easiest) method to do this is to do a backup, and then restore to a different name and/or location. Read up on RESTORE in Books Online.|||Alright, I was able to do that through Express Manager this time. Much easier and safer. Thanks, both of you.

Rick