SAN question.

Last Post 27 Nov 2002 06:56 AM by mimadon. 5 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
mimadon
New Member
New Member

--
26 Nov 2002 08:45 AM
Here's the scenario:

SQL Server 2000 / Win 2000

We're moving the SQL Server databases supporting a medium-sized (total about 2.5 TB in a number of 200GB databases) data warehouse to a SAN to improve our ability to manage space, etc.

Currently (without the SAN) we have two SQL Servers which contain identical databases. One server is production, the other is a 'warm backup' server which would go into use in the event that the primary server failed.

In the SAN implementation, we are considering connecting both of these servers to the SAN, but only maintaining one copy of the database(s) which of course would be used by the production server.

It has been my hope that, in the event of a failure of the production server, we could simply associate the database disk files (through the SAN) to the 'backup' SQL Server and attach those existing production database files (previously associated with the failed production server) so that the 'backup' SQL Server could be put into the production role while the failed production server is repaired.

Will it be possible to attach (using sp_attach_db) the production database files to the 'backup' SQL Server even if they were not actually detached (using sp_detach_db) from the production server due to it's failure. The data is effectively read-only, but the databases are not configured as read-only.

Any experience, insight would be greatly appreciated...


mimadon
New Member
New Member

--
26 Nov 2002 11:56 AM
Thanks RM and RichS for your suggestions to use clustering.

However, clustering would add considerable complication, most of which is not directly related to SQL Server, but more about the processes that deliver the data to the warehouses and various other interface issues.

I guess my question still stands. Perhaps I can state it more generically:

If a server running SQL Server should fail, but the database files that make up the databases in use by that server are still intact, can those database files be attached (using sp_attach_db) to a different SQL Server, assuming that the database files can be made available to that second SQL Server?

Thanks in advance!




BobBarker
New Member
New Member

--
26 Nov 2002 03:29 PM
I suppose you could try it. You could just stop one of your SQL servers and swap out a database file with the same name. You might have to swap out the master too.

If you can't tell I have I no idea. Please post your results!!!

BB
mimadon
New Member
New Member

--
27 Nov 2002 06:56 AM
Very helpful comments, all! I suspect (as some of you have) that I may be beyond the limits of what's 'officially' supported.

I'm going to attempt a test of the scenario:

1. Server up and running
2. Take server down with the off switch (no graceful shutdown)
3. Copy the target database files to another server
4. Attempt a sp_attach_db of the files on the other server

I'll post results in the next few days when I have 'em...
mimadon
New Member
New Member

--
27 Nov 2002 08:52 AM
I've run the test as described in my previous posting.

Here's how it went:

1. Set SQL Server service to manual start.

2. Stop the server ungracefully (we actually just pulled the plug to make sure SQL Server would be unable to perform any shutdown operations)

3. Restart server and move northwind db files from normal location

4. Start SQL Server service (SQL reports Northwind database as suspect due to 'missing' files)

5. Drop suspect Northwind database

6. Copy Northwind DB files back to normal location

7. Execute sp_attach_db to recreate Northwind database.

The attach operation issued no complaint, and the database is once more available and passes all DBCC integrity checks.

Conclusion? It sure looks like the approach will work.

I suspect that MS does not explicitly support the sp_attach_db without a previous sp_detach_db because it may result in loss of transactional integrity if any transactions are in progress when the service goes down ungracefully.

It would appear that for a database that is being used for querying only (no data modifications underway), it is possible to reliably attach the database files to another server after the ungraceful failure of the SQL Server service.

Thanks to all!!!

Any opinions/additional experience is always welcome...



mimadon
New Member
New Member

--
27 Nov 2002 09:54 AM
I reran the same test with SELECT query statements running against the database at the time of the 'failure'.

This time the sp_attach_db did return a '8 transactions rolled forward' message, but the re-attach worked just fine anyway.

I assume, however, that if there had been data modifications underway, this operation likely would not have succeeded.

You are not authorized to post a reply.

Acceptable Use Policy