January 8, 2005 at 5:40 pm
I'm thinking the answer here is hire consultants again, but here goes. We have a two-node SQL Enterprise 2000 active/passive cluster set up at a remote network facility. All of the database files, error logs and backups are on a SAN device separate from the two sql boxes. ( see drive mappings to SAN below ) We want to use a Dell Powervault storage array instead, keeping the SAN just for "backups of backups"
I know, the backups should not now be on the SAN but writing them over that network to another server was too slow and errorprone. The sql server to SAN is a fibre connection.
We'll also need to plan for the possibility of this new NAS melting down and having to recover the cluster from the extra backups on the SAN. Now that I've said that, the backups should probably just continue to be written to the SAN so we don't have a single point of failure and don't have to copy backups.
As "acting" DBA I don't have the experience for this, and our network admin guys are the only ones who have run cluster manager.
f:\ClientDatabase.mdf
e:\ClientDatabase_log.ldf
g:\MSSQL\data\master.mdf
g:\MSSQL\data\mastlog.ldf g:\MSSQL\data\model.mdf
g:\MSSQL\data\modellog.ldf
g:\MSSQL\data\msdbdata.mdf g:\MSSQL\data\msdblog.ldf
g:\MSSQL\data\tempdb.mdf g:\MSSQL\data\templog.ldf
H:\sqlbackups
G:\mssql\log
January 10, 2005 at 3:51 am
From what I've read it's seems to be a very costly way of storing your backups. Is your SAN full to capacity and can't handle a growing database? If so I see where your coming from but if it's not now you've got your backups on expensive high performance hardware.
My plan would be to leave the databases on the SAN and make the best use of the high performance hardware --- making the SAN a cost effective solution and I would store my backups on the NAS. NAS disks are cheaper and since you should only need to use backups rarely I would keep them here.
I also remember reading a whitepaper somewhere saying that it is not a good idea to keep database files on a NAS because it does not have the same level of I/O reliabilibty as a SAN. NAS's were originally desgined to be file servers which are not as I/O intensive as database servers.
I know Oracle have released the lastest version of their database on a NAS but I don't thinik Micosoft have qualified this hardware setup ( I would suggest contacting Microsoft and get their cut on it.
Hope this helps.
January 10, 2005 at 4:43 am
I agree, i would definately leave the databases on the SAN and move the backup files to the NAS.
Unless it's a really old SAN then the performance decrease you'd get by moving the databases to NAS would be fairly bad.
January 10, 2005 at 5:55 am
I'd agree with the whole "not moving your db's to the san" thing. The SAN is a real high performer, the NAS is not, especially when you start to factor in network latency into your database performance. You could end up with your whole system being unreliable.
January 11, 2005 at 11:12 am
Thanks for the responses. The SAN is "leased" space and our company would like to get off of that entirely down the road for cost reasons. At that point we would have two NAS of our own. Our chief Network Admin belives a NAS connected directly to our sql cluster boxes would be as fast as a local drive ( as good or better than to a SAN ). Is he wrong???
The current amount of space available to us on the SAN will soon run out, thus the need to make a change. Either pay the high price to increase our leased SAN space ( and have backups on the NAS ), or leave backups on the SAN for now and move all database files to a NAS ( management's preference ).
We can't have backups go to other Win2k servers on that network because the network connections are too slow and we get backup errors.
Randy
January 11, 2005 at 3:38 pm
>>>Our chief Network Admin belives a NAS connected directly to our sql cluster boxes would be as fast as a local drive ( as good or better than to a SAN ). Is he wrong???<<<
YES, HE IS WRONG!
You can read it here (straight from the horses mouth).
Eventhough it May be possible to set it up, so far 100% of the people I know that used NAS, the minute performance was required, the system melted down with errors and then you pay the price. If your db is not very transactionally intensive you may get away with it. Personally, I wouldn't recommend it.
HTH
* Noel
January 11, 2005 at 6:03 pm
Thanks again. I got a little more info on this Dell Powervault. I shouldn't have referred to it as a NAS. I'm still trying to determine if it is included in Microsoft's Windows Hardware Quality Lab certified list for sql server. Another question will be how/if the cluster administrator will finesse the fact that our database ldf and mdf files are now on a SAN -- the sql cluster nodes are mapped E,F,G drives to the SAN for ldf, mdf and error logs. System DBs are also on the G drive.
Dell™ PowerVault™ 220S and 221S systems are reliable, flexible, external SCSI expansion enclosures designed to support multiple Dell storage environments and RAID configurations
Network Admin's clarification: "The device that we are installing is not a NAS in the literal sense (Network Area Storage). Because it’s not connected to the network, the devices are connected directly to the servers though a SCSI card. . The term would be Local Area Storage or Local Storage Device (LAS, LSD)"
January 12, 2005 at 2:38 am
Looks like what you've got is an external disk array attached directly to your server (SCSI connections should be perfect as they can handle I/O etc. very well + plus no other devices will be using these disks). Provided the drives look like local drives on the server you should be able to put all your error logs, databases and backups on this disk array. Although from a disaster recovery standpoint you should have your backups on some other physical device.
One other point:- Could you guys not just have bought bigger disks the internal drive bays for server?
You can move your database files around on the new disk array so your not tied to the drive letters you currently have. There are 2 very robust ways that I know of for doing this :-
1) Restore the database from a backup file and use the ' With Move' cluse in ther restore command to move your files to different locations.
2) use Sp_detach_db and sp_attach_db. These 2 stored procedures allow you disconnect the databases from sql server. Copy the mdf and ldf files to new locations and reconnect them to the server (works well --- used it many times).
Hope this helps
January 12, 2005 at 8:23 am
As Shane says, you've got an external RAID Array there, rather than a NAS. External RAIDs perform great, depending on the setup, and I've found are second in performance only to a SAN.
January 12, 2005 at 9:26 am
Great discussion and answers. Now let's focus on the fact that it's a two node active/passive cluster so none of the database files can reside on either of the sql server boxes. During a failover from one node to the other, the new active node needs to be able to see the data, which can't if the first node has melted down.
Also, moving the system databases ( master,model,msdb and tempdb ) is different. Can be done obviously, but not in the same way as client databases. Details on that for this scenario would be great. then comes recovering if the raid array we're talking about melts down. If the drive letters are different, ( e.g. backups are on the san and you have to restore the mdf/ldf files to the SAN ) then restoring from backups including dozens and dozens of trans log backups, gets more messy. Can't just use the Enterprise Manager GUI without lots of retyping paths.
How much the cluster administrator ( which I've never had access to ) handles all of this for you I have no idea.
Randy
January 12, 2005 at 9:37 am
Randy, cluster admin rules. Honestly. It handles everything in the failover for you, fully automated.
You'll set up the disk on the array so that it can be seen by both servers. Assign the drive letters so that they are the same on both machines. Install the cluster service on each node in turn, install SQL on one node (it will install on the other node automatically), apply your sql service packs (just to the one node). Test your failover, all is good, then add your db's and you are done.
I would, personally, move all objects that you need from master, ie logins, to the new server rather than restore the database from the other server, it just makes everything cleaner. I would then backup you old db's, transfer the backups and restore, using the with move option (you could shut down the old server and copy the mdf, ldf I just prefer backups and restores).
For the clustering check out the step-by-step guide to installing cluster service from Microsoft
http://www.microsoft.com/windows2000/techinfo/planning/server/clustersteps.asp
January 12, 2005 at 10:03 am
Can't Really help on the clustering stuff but moving system databases is not much of a job either.
All info is in the following webpage:- http://databasejournal.com/features/mssql/article.php/3379901
Later
shane
January 21, 2005 at 12:11 pm
I got some clarification on Cluster Administrator. Apparently it is just the software that runs in the background and handles failover from one server to the other node in a cluster. I would imagine it also includes configuration screens where you can change the cluster setup/arrangement.
But it is not an Enterprise Manager on steroids like I thought. So the movement of databases is still a task for the DBA. Now that I've looked at the details I'm surprised that SQL server techniques are so different for client databases and system databases. Four different approaches as I see it. One for master, one for client data, one for msdb/model and one for tempdb ( plus the error log) .
Any review comments of the following would be greatly appreciated!!!
-- MOVING DATABASES
-- FILE LOCATION CHECKS what of mismatches between file paths reported by these two commands following moves?
select * from clientdatabase.dbo.sysfiles
select NAME,DBID,FILENAME from master.dbo.sysdatabases
-- 1. DETACH USER DATABASES
use master
go
sp_detach_db 'CollateralManager'
go
sp_detach_db 'aspstate'
go
sp_detach_db 'uipstate'
-- EXEC master.dbo.xp_cmdshell "net use M:
\\server\mssql"-- 3. REATTACH USER DATABASES
use master
go
sp_attach_db 'collateralmanager',M:\DATA\COLLATERALMANAGER.MDF',M:\DATA\COLLATERALMANAGER_LOG.ldf'
go
sp_attach_db 'ASPSTATE',M:\DATA\ASPSTATE.MDF',M:\DATA\ASPSTATE_LOG.ldf'
go
sp_attach_db 'UIPSTATE',M:\DATA\UIPSTATE.MDF',M:\DATA\UIPSTATE_LOG.ldf'
go
-- To move the MODEL/MSDB databases on SQL Server 2000, follow these steps:
-- In SQL Server Enterprise Manager, right-click the server name and click Properties.
-- On the General tab, click Startup Parameters.
-- Add a new parameter as "-T3608" (without the quotation marks).
-- After you add trace flag 3608, follow these steps: 1.Stop, and then restart SQL Server.
-- Make sure that the SQL Server Agent service is not currently running.
-- Detach the model/msdb databases as follows:
use master
go
sp_detach_db 'msdb'
go
SP_DETACH_DB 'model'
-- Remove the -T3608 trace flag from the startup parameters box in Enterprise Manager.
-- Stop and then restart SQL Server.
-- Reattach the MODEL FIRST THEN msdb database as follows:
use master
go
sp_attach_db 'model','M:\DATA\MODEL.mdf','M:\LOG\MODELLOG.ldf'
GO
sp_attach_db 'msdb','M:\DATA\msdbdata.mdf','M:\LOG\msdblog.ldf'
select * from tempdb.dbo.sysfiles
use master
go
alter database tempdb modify file(name='tempdev',filename='M:\DATA\tempdb.mdf')
go
alter database tempdb modify file(name='templog',filename='M:\DATA\templog.ldf')
--Change the path for the master data and log files in SQL Server Enterprise Manager.
--.Right-click the SQL Server in Enterprise Manager and click Properties.
--Click the Startup Parameters button and you will see the following entries:
-dM:\data\master.mdf
-eM:\log\ErrorLog
-lM:\data\mastlog.ldf
-d is the fully qualified path for the master database data file.
--Copy the Master.mdf and Mastlog.ldf files to the new location
January 24, 2005 at 2:39 am
That's pretty much how to do it. Nothing different from the way that I have always moved files around. Go for it!!
Later
Shane
January 24, 2005 at 5:50 am
One note of warning, just be aware that you will have to have the disks that you are moving to managed within the SQL cluster otherwise SQL will not be able to see them and you will not be able to attach the databases.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply