Opinions on this box, please.

[Blake Kercovich]

Hi all.
We are currently running SQL7 on an NT4 server (dual 800Mhz, 1GB RAM) and it is being pounded mercilessly 24/7!

We are currently in the market to upgrade, and I would like to get your opinions on this setup. Maybe some has experience with this box, or other issues in upgrading to a new OS and new version of SQL...

Box:
Compaq Proliant ML530

Processors:
2 Xeon 2.8GHz/512KB with 400Mhz System Bus

Memory:
2GB (4x512)

Drives:
2 18.2GB U3 SCSI in Raid 1 (for Operating System)
3 72.8GB U3 SCSI in Raid 5 (for Backups/TLogs/OS Swap file)
4 36.4GB U3 SCSI in Raid 5 (for SQL data)

Operating System:
Windows 2000 Server

SQL Server:
MS SQL 2000 Standard Edition

Any thoughts/advice are appreciated.

[rmiao]

It really depends on number of concurrent connections and load of transactions. If stays same as on sql7 and you don't have performance issue on old box, new box is more than good enough. By the way, why put logs on raid 0 array? I'll use raid 1 since they are very important for db integrity.

[Blake Kercovich]

Originally posted by rmiao
By the way, why put logs on raid 0 array? I'll use raid 1 since they are very important for db integrity. [/B]

That was the suggestion of the network admin. He suggested Raid 0 because it's faster for the drives that will only be for disk backups, since those will be written to during the day, and the backed up to tape at night.

If it makes more since to make that array Raid 1 instead of Raid 0, I can make arguements toward that end.

Does Raid 1 result in a loss of space?
I know Raid 5 means that one of the drives is "lost" to allow for the structure.
I am not real familiar with Raid 0 and 1.

[MAK]

Config of the new box looks good.

SQL Server Issues/tips/suggestions

0. Do a complete inventory of the 7.0 server including batch jobs, user DLL registration,data and log locations, index location, ODBC connections, 3rd party client installations like Sybase,oracle,db2 client, shared folder names, NT logins and permissions etc.

1. Try the upgrade with the backup of production in sanbox and then try to connect from frontend. there may me issues from your client side like MDAC version. Make sure your Frontend likes MDAC 2.6 or 2.7

2. SQL 2000 syntax is only little different from SQL 7.0. u can always run the database in SQL 70 compatible mode.

3. Make sure u do a maintenance plan (reindex) on all the databases after upgrade or else your queries will be much slower.

4. Since it is 24/7 make sure you have good contingency plan.

5. Avoid doing an upgrade from 7.0 to 2000 which has fulltext. Usually it takes hell lot of time to upgrade Fulltext indexes. I suggest u to script all the fulltextindex and manually upgrade.

6. if you have log shipping in 7.0, script it and document it.

7. Usually DBA's forget all the User defined stored procedure in master database like USP_Kill_process etc. Script all the user procs and tables from master database.

[mist]

Originally posted by Blake Kercovich


That was the suggestion of the network admin. He suggested Raid 0 because it's faster for the drives that will only be for disk backups, since those will be written to during the day, and the backed up to tape at night.

If it makes more since to make that array Raid 1 instead of Raid 0, I can make arguements toward that end.

Does Raid 1 result in a loss of space?
I know Raid 5 means that one of the drives is "lost" to allow for the structure.
I am not real familiar with Raid 0 and 1.

Using raid 1 means you will lose 50% capacity, but gain fault tolerancy and availability.

If I were you, I would analyse the risk of loosing up to a day's worth of database activity, and balance this against the loss of x GB of disk space. Bear in mind that with all your backups on that same drive if it failed in raid 0 you would probably lose that day, plus the extra day to restore from tape. If this is acceptable, you can go with raid 0 and have more space. (If you really need it.)

Also, and I'm not sure on this, you may want to check into the efficiency of having the OS swap and the transaction logs on the same drive array.

[Chigrik]

See this FAQ question for more details:

"Can you give me the links to upgrade from SQL 7.0 to SQL 2000?"
http://www.MSSQLCity.com/FAQ/InstUpg/sql70upg2000.htm

[rmiao]

You can put backup on raid 0 if necessary, but never put transaction log on raid 0.

[Blake Kercovich]

After posing your concerns and recommendations to our network guy, he said we could change the second array from 2 72GB in Raid 0 to 3 72GB in Raid 5.
Sound like a better solution?

Someone mentioned no having the OS swap on the same array as the backups and TLs. Where would it best be placed then? I know it's poor performance to have it on the same disk as the OS itself. Would it go on the data array then? That doesn't seem like a good idea.

[mist]

Originally posted by Blake Kercovich

Someone mentioned no having the OS swap on the same array as the backups and TLs. Where would it best be placed then? I know it's poor performance to have it on the same disk as the OS itself. Would it go on the data array then? That doesn't seem like a good idea.

Like I said, I wasn't sure if any other place would give better performance. It just seems like that + the transactions are likely to get the most continuous / concurrent use.