Cluster Resource replacing physical server

We are replacing an existing SQL 2000 EE server with a clustered
resource by the same name. We have done this before, but are running
into an issue on this install. Here are the steps we have performed:
* Migrate databases as files
* Shut off original server
* Delete original computer account and replicate to all domain
controllers.
* Create cluster resource (third resource in this cluster)
We are having problems though with cluster recognizing the old server
account. We are getting Event ID 1210 on Clussvc on the install.
"Cluster resource 'SQL Network Name(ITSQLEE)' failed to go online
because its associated computer account (ITSQLEE) exists in Active
Directory and the Kerberos authentication is not enabled for the
resource. Client applications that are using Kerberos based
authentication (either directly or through the Negotiate security
package) will fail to authenticate when contacting a service by this
network name.
To bring the resource online, either enable Kerberos authentication or
delete the computer account."
In a prior event message, we get 1119 - Clussvc - Bad DNS Key. I have
tried this multiple times after waiting for Active directory to
synchronize and verifying with replmon. Any ideas? Thank you.
Hi Bowulf
The reason for this could be that the changes in Active Directory isn't
replicated around to all Domain Controllers. You could initiate Force
Replication but if you are in an enterprise environment this could cost in
terms of bandwith. Hence when you delete the computer account and it's not
replicated you would get this message.
Any help?
"Bowulf" wrote:

> We are replacing an existing SQL 2000 EE server with a clustered
> resource by the same name. We have done this before, but are running
> into an issue on this install. Here are the steps we have performed:
> * Migrate databases as files
> * Shut off original server
> * Delete original computer account and replicate to all domain
> controllers.
> * Create cluster resource (third resource in this cluster)
> We are having problems though with cluster recognizing the old server
> account. We are getting Event ID 1210 on Clussvc on the install.
> "Cluster resource 'SQL Network Name(ITSQLEE)' failed to go online
> because its associated computer account (ITSQLEE) exists in Active
> Directory and the Kerberos authentication is not enabled for the
> resource. Client applications that are using Kerberos based
> authentication (either directly or through the Negotiate security
> package) will fail to authenticate when contacting a service by this
> network name.
> To bring the resource online, either enable Kerberos authentication or
> delete the computer account."
> In a prior event message, we get 1119 - Clussvc - Bad DNS Key. I have
> tried this multiple times after waiting for Active directory to
> synchronize and verifying with replmon. Any ideas? Thank you.
>
|||Thanks for the reply. I had ensured with replmon that the 11 Domain
Controllers had all been synchronized with the each other, and that the
ADUC on each domain controller showed the computer as removed. The
entry was of course still present but tombstoned. I would hate to
think I would have to wait 90 days though.
ipconfig2 wrote:[vbcol=seagreen]
> Hi Bowulf
> The reason for this could be that the changes in Active Directory isn't
> replicated around to all Domain Controllers. You could initiate Force
> Replication but if you are in an enterprise environment this could cost in
> terms of bandwith. Hence when you delete the computer account and it's not
> replicated you would get this message.
> Any help?
>
> "Bowulf" wrote:
|||It is my understanding that this would be the behavior if DDNS did not
properly delete the entry, regardless of the status of the AD replication.
A simple ping of the name for the A record and reverse for the PTR would
indicate if it were still present.
Delete the DNS records manually.
Install the installation.
Create the virtual computer account manually if the Cluster service account
does not have sufficient rights.
Manually apply the SPN registrations for the SQL Server service account.
Then connect to cluster administrator to Enable Kerberos on the network name
resources.
Sincerely,
Anthony Thomas

"Bowulf" <bowulf@.gmail.com> wrote in message
news:1165709800.241227.11990@.79g2000cws.googlegrou ps.com...[vbcol=seagreen]
> Thanks for the reply. I had ensured with replmon that the 11 Domain
> Controllers had all been synchronized with the each other, and that the
> ADUC on each domain controller showed the computer as removed. The
> entry was of course still present but tombstoned. I would hate to
> think I would have to wait 90 days though.
> ipconfig2 wrote:
in[vbcol=seagreen]
not
>

Cluster problems

I was wondering if any of you could offer some help with my cluster. I'm
troubleshooting an existing problem within the company where the website
crashes because the clustered SQL servers' 8x CPUs reach 100%.
I've looked in the event logs of the SQL Servers and the only error is on
one inthe System Log :
User: N/A
Comptuer: SQLCLA
Source: ClusSvc
Categroy: Failover Mgr
Event ID: 1069
Description: Cluster resource "SQL Server (PRODUCTION)' in Resource Group
'PRODUCTION Disk Group' failed.
Any help is gratefully received!
The simple answer is the SQL instance didn't respond to the Cluster Service
LooksAlive and IsAlive tests within the allowed timeout period. The Cluster
interpreted that as a failure and acted appropriately.
The complex answer is to find out why your server is running at CPU
saturation. Until you find the answer to that question, you will still
experience cluster failures. You need to do a performance impacy analysis
of your server to determine the cause of the load. One common cause of this
is complex math or string manipulation code running in the server. Usually
that is better done at the application layer rather than at the data layer.
Unfortunately fixing that generally requires an application change. If that
is off the table, start shopping for a 16-way or higher server.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
"savvy95" <savvy95@.discussions.microsoft.com> wrote in message
news:554168C0-0879-45A4-9D43-1079F4E8A6AC@.microsoft.com...
>I was wondering if any of you could offer some help with my cluster. I'm
> troubleshooting an existing problem within the company where the website
> crashes because the clustered SQL servers' 8x CPUs reach 100%.
> I've looked in the event logs of the SQL Servers and the only error is on
> one inthe System Log :
> User: N/A
> Comptuer: SQLCLA
> Source: ClusSvc
> Categroy: Failover Mgr
> Event ID: 1069
> Description: Cluster resource "SQL Server (PRODUCTION)' in Resource Group
> 'PRODUCTION Disk Group' failed.
> Any help is gratefully received!
|||Followup to my previous post.
If this is a Hyper-Thread enabled host, restrict MAXDOP (Maximum Degree of
Parallelism) to the actual physical CPU count or lower. This won't help
query processing but may give the server a chance to respond to the CLuster
checks and keep it from failing over. The really good news is you cna
change this without restarting your server.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Admininistrator
"savvy95" <savvy95@.discussions.microsoft.com> wrote in message
news:554168C0-0879-45A4-9D43-1079F4E8A6AC@.microsoft.com...
>I was wondering if any of you could offer some help with my cluster. I'm
> troubleshooting an existing problem within the company where the website
> crashes because the clustered SQL servers' 8x CPUs reach 100%.
> I've looked in the event logs of the SQL Servers and the only error is on
> one inthe System Log :
> User: N/A
> Comptuer: SQLCLA
> Source: ClusSvc
> Categroy: Failover Mgr
> Event ID: 1069
> Description: Cluster resource "SQL Server (PRODUCTION)' in Resource Group
> 'PRODUCTION Disk Group' failed.
> Any help is gratefully received!

Close existing connection before deleting/restore database

I want to be able to force a restore or a delete on a database even if there still have some active connections. In SQL Server management studio there is a checkbox when you're trying to delete a database to "Close existing connection" i want to do the same thing but using SMO. How can i do this ?

ThankHi,

see my blog entry here:

http://sqlserver2005.de/SQLServer2005/MyBlog/tabid/56/EntryID/9/Default.aspx

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

close all existing connections and processes to a database

Dear all

I created this trigger on a table that i think failed while execution. I tried to modify it and run it again but it seems that i cant do that. If i try and delete the database i also cant - saying that it is still in use. But i am not using it and ther are no other users connected to it. I think the trigger has probably hit a loop and that is holding the link.

To close that i know that a solution would be to restart the SQL server instance but that would be a bit hard since the SQL server where my test database resides is a production server and has few other databases that are important and few users use them.

Is there any way through a SQL statement that there can be forced a delete? Or force close all the connections? Or force close all the processes without actually restarting the SQL server instance.

I have tried all options that were offered on some other forums like forcing it to a single user but even that operation can not be performed saying that the database is still in use.

Thank you so much for all your help and time.

Sincerely

Dan

You could cycle through the SPID's in master.dbo.sysprocess and KILL off any SPID's using the test database_id.

And then you have learned why you 'should' not 'play' on a production server. Create yourself a local server for experiementation and testing. Get an 'old', decommissioned desktop and convert it to a test server. There is no excuse for mucking up a production server.

Cloning a table

In my .NET code, with SQL Server 2000 as the backend, I need to create an
exact copy, without data, of an existing table in the same database, that
the user selects from a combo box. Any ideas on how to do this?You can create a copy without constraints or indexes using SELECT ... INTO.
Specify a WHERE clause to exclude data too:

SELECT * INTO MyNewTable FROM MyTable WHERE 1 = 0

Note that the user will need CREATE TABLE permissions. This isn't the kind
of thing one usually does in database applications though.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Michael Jackson" <michaeldjackson@.cox.net> wrote in message
news:%MsXd.6532$Wy.1516@.okepread02...
> In my .NET code, with SQL Server 2000 as the backend, I need to create an
> exact copy, without data, of an existing table in the same database, that
> the user selects from a combo box. Any ideas on how to do this?

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