Optimize RAID configuration

I am setting up a server that will be a dedicated database server. I don't have much flexibility in choosing a server, and the servers we use are limited to six disk drives. There will be only one user database on the server, aside from the system databases. Everything I have read indicates it is better to place the transaction log file on a separate disk array from the data files. I could put the transaction log file on a mirrored disk array (RAID 1) and then put the data file on a RAID 10 array of four disks. But where would I put the OS and SQL binaries? The application in question is fairly write-intensive, and does not perform well in a RAID 5 array. In the past, we placed everything (OS, data, logs, etc.) on the same RAID 5 array. It is apparent that this configuration is not going to work, or at least it won't scale very well. My questions are as follows:

1) How much write activity is generated by the transaction log? Will I see a tangible performance benefit by separating the log file onto its own physical array?

2) What would be the optimal RAID configuration for the above scenario, while limited to six drives?

3) What would be the optimal RAID configuration if I had more that one user database on the same server, with the six drive limit?

Any help would be very much appreciated.As a GENERAL rule, you want to seperate your log files, databases and indexs from each other. In your case I doubt if you will have much oppertunity to optimize your hardware.

IMnsHO, I would put the os, binaries and logs on the RAID 1 drives and db et all on the RAID 10 drives. I suspect you have a single channel controler so watch disk I/O as I would bet you will have a bottleneck here. If you have a dual channel controler put the RAID 1 on one channel and the RAID 10 on the other.

Your question...

1) Every write to a table will generate a write to the transaction log even in Simple mode! Putting your logs on a RAID one drive will increase performance on a write intensive db.

2) It is more a question of RAID configuration and controler configuration. Unless you have a dual channel controler you aren't going to see much of a performance increase. Switching from RAID 5 to RAID 1 & 10 will help. Make sure the data cache on the controler is optimized for more writes than reads.

3) Get a three channel controler, channel one for the RAID 1 drives, channel two and three for the RAID 10 drives, mirror across the two channels so the strip stays on one channel.

Optimal Placement of Data/Log Files

What is the best way to setup a server for hosting SQL Sever 2005? If I could get input of type of RAID to use and where to place the files that would be very appreciated.

Thanks,

Todd Sparks

Hey Todd. Well, ideally we'd always recommend RAID 10 if possible for both data and log file LUNs for performance and availability reasons. If that's not possible due to cost, I'd recommend a RAID 1 configuration for your log file and a RAID 5 for data files...ideally, if cost is not prohibitive again RAID 1 for data files would probaby be better than 5.

For a more in depth look at the internals of SQL Server IO see the following paper:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx

In addition, check out the following paper on disk subsystem performance:
http://www.microsoft.com/whdc/device/storage/subsys_perf.mspx

HTH

operating systems

Hello,
We aim to use the current server (w2k,sql2000ent, 2x1ghz, 1gb ram, mirrored
pair & raid 5 for data) as a failover machine and upgrade to a new server
for production.
The 2 machines will be in different countries. Users in both countries will
use the production server. If the connection between the countries is
broken, the remote users will use the failover.
We propose to have merge replication between the 2 machines (mainly one
way).
How can we estimate the physical physical size of data that would need to be
transferred to the failover m/c for a given time period?
Presumably it would make sense to use w2003 on both machines?
Do the operating systems on 2 merge replicating machines have to be the
same?
Any advice would be appreciated.
Thanks SOC.
I would not use merge for this - I would use bi-directional transactional
replication. This is ideal because your data is highly partitioned.
Transactions occur on only one side at a time.
Transactional replication is in general much faster than merge replication.
Transactional replication is designed for server to server replication -
whereas merge is designed for clients who often go offline. Merge
replication adds a GUID column to each table you are replicating.
Transactional requires a primary key on each table.
Merge replication can be more difficult to trouble shoot than transactional.
Merge also requires (in general) more processing than transactional.
This is not to say of course that merge is always less performant than
transactional or slower than transactional. If you have a solution where
there are heavy updates, the servers are not connected for significant
lengths of time, and transactions originate on one server - merge is faster
and performs better. Consider a stock market application where 1) the
majority of the transactions are updates 2) the servers connect once a day
3) the transactions all occur on the publisher - in this case merge performs
way better than tranny.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"soc" <zxc0@.yahoo.com> wrote in message
news:eWfg9uQ4EHA.2404@.TK2MSFTNGP14.phx.gbl...
> Hello,
> We aim to use the current server (w2k,sql2000ent, 2x1ghz, 1gb ram,
> mirrored pair & raid 5 for data) as a failover machine and upgrade to a
> new server for production.
> The 2 machines will be in different countries. Users in both countries
> will use the production server. If the connection between the countries is
> broken, the remote users will use the failover.
> We propose to have merge replication between the 2 machines (mainly one
> way).
> How can we estimate the physical physical size of data that would need to
> be transferred to the failover m/c for a given time period?
> Presumably it would make sense to use w2003 on both machines?
> Do the operating systems on 2 merge replicating machines have to be the
> same?
> Any advice would be appreciated.
> Thanks SOC.
>
|||Thanks Hilary,
I will look into bi-directional tranasactional replication.
-Presumably it would make sense to use w2003 on both machines?
-Do the operating systems on 2 replicating machines have to be the
same?
Any advice would be appreciated.
Thanks SOC.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:O8z42MR4EHA.4092@.TK2MSFTNGP14.phx.gbl...
>I would not use merge for this - I would use bi-directional transactional
>replication. This is ideal because your data is highly partitioned.
>Transactions occur on only one side at a time.
> Transactional replication is in general much faster than merge
> replication. Transactional replication is designed for server to server
> replication - whereas merge is designed for clients who often go offline.
> Merge replication adds a GUID column to each table you are replicating.
> Transactional requires a primary key on each table.
> Merge replication can be more difficult to trouble shoot than
> transactional. Merge also requires (in general) more processing than
> transactional.
> This is not to say of course that merge is always less performant than
> transactional or slower than transactional. If you have a solution where
> there are heavy updates, the servers are not connected for significant
> lengths of time, and transactions originate on one server - merge is
> faster and performs better. Consider a stock market application where 1)
> the majority of the transactions are updates 2) the servers connect once a
> day 3) the transactions all occur on the publisher - in this case merge
> performs way better than tranny.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
> "soc" <zxc0@.yahoo.com> wrote in message
> news:eWfg9uQ4EHA.2404@.TK2MSFTNGP14.phx.gbl...
>
|||you do not have to have the same operating system. It is adviseable to use
NT server operating systems, ie NT 4., Win2k, and Win2003.
I think you can get away with using NT workstation, or XP professional but
there can be a connection limit you can bang into.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"soc" <zxc0@.yahoo.com> wrote in message
news:egaFkBT4EHA.2540@.TK2MSFTNGP09.phx.gbl...
> Thanks Hilary,
> I will look into bi-directional tranasactional replication.
> -Presumably it would make sense to use w2003 on both machines?
> -Do the operating systems on 2 replicating machines have to be the
> same?
> Any advice would be appreciated.
> Thanks SOC.
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:O8z42MR4EHA.4092@.TK2MSFTNGP14.phx.gbl...
>

Operating system error 38(Reached the end of the file.) on file "C:\Data\myfile_log.LDF&quo

Hi,

I am facing a problem on a server which has raid 5 solution (3 disks), the raid controller went down 2 of the disks were off in the Bios.

We added the 3 disks to a different server identical in brand and architecture, the raid controller was able to reconfigure the virtual drive H:.

All files were there, we installed sql server 2005 on the new server, but when we tried to attach the database we got the error below:

TITLE: Microsoft SQL Server Management Studio

Attach database failed for Server 'myserver'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The operating system returned error 38(Reached the end of the file.) to SQL Server during a read at offset 0x00000000af0000 in file 'C:\Data\mylog_log.LDF'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Operating system error 38(Reached the end of the file.) on file "C:\Data\mylog_log.LDF" during ReadFileHdr.
Could not open new database 'mydb'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 823)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=823&LinkId=20476

BUTTONS:

OK

I tried the following steps but it always failed:

- create a new db with the same name of the lost db;

- put the db in emergency mode;

- stop sql service and replace the mdf file;

- start sql service;

- Run Dbcc checkdb('mydb')

we got the error below:

Msg 945, Level 14, State 2, Line 1

Database 'ism0506' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

Any HELP please ?

Thanks,

Tarek Ghazali

Sql Server MVP

Well, the obvious question is where is your most recent backup of the database?

This situation doesn't look good. Losing 2/3 of a RAID 5 set means that you have lost a significant amount of data. Just because the file metada is there doesn't mean that the contents are there or intact. At a minimum, your log file is corrupt.

You can try CREATE DATABASE FOR ATTACH_REBUILD_LOG and see if you can get it going that way.

You WILL lose data, and most probably have inconsistencies due not being able to read the log file to do recovery.

Seriously restore from backup is your best option at this point.

|||

Hi,

The issue is that the client did not backup since 3 weeks (his mistake) and the only solution is to restore from the corrupted files that they have. I tried your solution but it did not work.

This is the error that i got when i ran (CREATE DATABASE FOR ATTACH_REBUILD_LOG ):

The log cannot be rebuilt because the database was not cleanly shut down.

Thanks for your reply,

Tarek Ghazali

SQL Server MVP

|||Try this undocumented stuff provided by Kevin [MS].

==========
1. Back up the .mdf/.ndf files at first!!!

2. Change the database context to Master and allow updates to system tables:

Use Master
Go
sp_configure 'allow updates', 1
reconfigure with override
Go

3. Set the database in Emergency (bypass recovery) mode:

select * from sysdatabases where name = '<db_name>'
-- note the value of the status column for later use in # 6
begin tran
update sysdatabases set status = 32768 where name = '<db_name>'
-- Verify one row is updated before committing
commit tran

4. Stop and restart SQL server.

5. Call DBCC REBUILD_LOG command to rebuild a "blank" log file based on the
suspected db.
The syntax for DBCC REBUILD_LOG is as follows:

DBCC rebuild_log('<db_name>','<log_filename>')

where <db_name> is the name of the database and <log_filename> is
the physical path to the new log file, not a logical file name. If you
do not
specify the full path, the new log is created in the Windows NT system
root
directory (by default, this is the Winnt\System32 directory).

6. Set the database in single-user mode and run DBCC CHECKDB to validate
physical consistency:

sp_dboption '<db_name>', 'single user', 'true'
DBCC checkdb('<db_name>')
Go
begin tran
update sysdatabases set status = <prior value> where name = '<db_name>'
-- verify one row is updated before committing
commit tran
Go

7. Turn off the updates to system tables by using:

sp_configure 'allow updates', 0
reconfigure with override
Go
============|||

Satya - none of that works in SQL Server 2005 (e.g. I removed the DBCC REBUILD_LOG command).

Tarek - you should call Product Support to help with recovering this.

Thanks

Operating system error 38(Reached the end of the file.) on file "C:\Data\myfile_log.LDF

Hi,

I am facing a problem on a server which has raid 5 solution (3 disks), the raid controller went down 2 of the disks were off in the Bios.

We added the 3 disks to a different server identical in brand and architecture, the raid controller was able to reconfigure the virtual drive H:.

All files were there, we installed sql server 2005 on the new server, but when we tried to attach the database we got the error below:

TITLE: Microsoft SQL Server Management Studio

Attach database failed for Server 'myserver'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The operating system returned error 38(Reached the end of the file.) to SQL Server during a read at offset 0x00000000af0000 in file 'C:\Data\mylog_log.LDF'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Operating system error 38(Reached the end of the file.) on file "C:\Data\mylog_log.LDF" during ReadFileHdr.
Could not open new database 'mydb'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 823)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=823&LinkId=20476

BUTTONS:

OK

I tried the following steps but it always failed:

- create a new db with the same name of the lost db;

- put the db in emergency mode;

- stop sql service and replace the mdf file;

- start sql service;

- Run Dbcc checkdb('mydb')

we got the error below:

Msg 945, Level 14, State 2, Line 1

Database 'ism0506' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

Any HELP please ?

Thanks,

Tarek Ghazali

Sql Server MVP

Well, the obvious question is where is your most recent backup of the database?

This situation doesn't look good. Losing 2/3 of a RAID 5 set means that you have lost a significant amount of data. Just because the file metada is there doesn't mean that the contents are there or intact. At a minimum, your log file is corrupt.

You can try CREATE DATABASE FOR ATTACH_REBUILD_LOG and see if you can get it going that way.

You WILL lose data, and most probably have inconsistencies due not being able to read the log file to do recovery.

Seriously restore from backup is your best option at this point.

|||

Hi,

The issue is that the client did not backup since 3 weeks (his mistake) and the only solution is to restore from the corrupted files that they have. I tried your solution but it did not work.

This is the error that i got when i ran (CREATE DATABASE FOR ATTACH_REBUILD_LOG ):

The log cannot be rebuilt because the database was not cleanly shut down.

Thanks for your reply,

Tarek Ghazali

SQL Server MVP

|||Try this undocumented stuff provided by Kevin [MS].

==========
1. Back up the .mdf/.ndf files at first!!!

2. Change the database context to Master and allow updates to system tables:

Use Master
Go
sp_configure 'allow updates', 1
reconfigure with override
Go

3. Set the database in Emergency (bypass recovery) mode:

select * from sysdatabases where name = '<db_name>'
-- note the value of the status column for later use in # 6
begin tran
update sysdatabases set status = 32768 where name = '<db_name>'
-- Verify one row is updated before committing
commit tran

4. Stop and restart SQL server.

5. Call DBCC REBUILD_LOG command to rebuild a "blank" log file based on the
suspected db.
The syntax for DBCC REBUILD_LOG is as follows:

DBCC rebuild_log('<db_name>','<log_filename>')

where <db_name> is the name of the database and <log_filename> is
the physical path to the new log file, not a logical file name. If you
do not
specify the full path, the new log is created in the Windows NT system
root
directory (by default, this is the Winnt\System32 directory).

6. Set the database in single-user mode and run DBCC CHECKDB to validate
physical consistency:

sp_dboption '<db_name>', 'single user', 'true'
DBCC checkdb('<db_name>')
Go
begin tran
update sysdatabases set status = <prior value> where name = '<db_name>'
-- verify one row is updated before committing
commit tran
Go

7. Turn off the updates to system tables by using:

sp_configure 'allow updates', 0
reconfigure with override
Go
============|||

Satya - none of that works in SQL Server 2005 (e.g. I removed the DBCC REBUILD_LOG command).

Tarek - you should call Product Support to help with recovering this.

Thanks

Operating system error 38(Reached the end of the file.) on file "C:\Data\myfile_log.LDF

Hi,

I am facing a problem on a server which has raid 5 solution (3 disks), the raid controller went down 2 of the disks were off in the Bios.

We added the 3 disks to a different server identical in brand and architecture, the raid controller was able to reconfigure the virtual drive H:.

All files were there, we installed sql server 2005 on the new server, but when we tried to attach the database we got the error below:

TITLE: Microsoft SQL Server Management Studio

Attach database failed for Server 'myserver'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The operating system returned error 38(Reached the end of the file.) to SQL Server during a read at offset 0x00000000af0000 in file 'C:\Data\mylog_log.LDF'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Operating system error 38(Reached the end of the file.) on file "C:\Data\mylog_log.LDF" during ReadFileHdr.
Could not open new database 'mydb'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 823)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=823&LinkId=20476

BUTTONS:

OK

I tried the following steps but it always failed:

- create a new db with the same name of the lost db;

- put the db in emergency mode;

- stop sql service and replace the mdf file;

- start sql service;

- Run Dbcc checkdb('mydb')

we got the error below:

Msg 945, Level 14, State 2, Line 1

Database 'ism0506' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

Any HELP please ?

Thanks,

Tarek Ghazali

Sql Server MVP

Well, the obvious question is where is your most recent backup of the database?

This situation doesn't look good. Losing 2/3 of a RAID 5 set means that you have lost a significant amount of data. Just because the file metada is there doesn't mean that the contents are there or intact. At a minimum, your log file is corrupt.

You can try CREATE DATABASE FOR ATTACH_REBUILD_LOG and see if you can get it going that way.

You WILL lose data, and most probably have inconsistencies due not being able to read the log file to do recovery.

Seriously restore from backup is your best option at this point.

|||

Hi,

The issue is that the client did not backup since 3 weeks (his mistake) and the only solution is to restore from the corrupted files that they have. I tried your solution but it did not work.

This is the error that i got when i ran (CREATE DATABASE FOR ATTACH_REBUILD_LOG ):

The log cannot be rebuilt because the database was not cleanly shut down.

Thanks for your reply,

Tarek Ghazali

SQL Server MVP

|||Try this undocumented stuff provided by Kevin [MS].

==========
1. Back up the .mdf/.ndf files at first!!!

2. Change the database context to Master and allow updates to system tables:

Use Master
Go
sp_configure 'allow updates', 1
reconfigure with override
Go

3. Set the database in Emergency (bypass recovery) mode:

select * from sysdatabases where name = '<db_name>'
-- note the value of the status column for later use in # 6
begin tran
update sysdatabases set status = 32768 where name = '<db_name>'
-- Verify one row is updated before committing
commit tran

4. Stop and restart SQL server.

5. Call DBCC REBUILD_LOG command to rebuild a "blank" log file based on the
suspected db.
The syntax for DBCC REBUILD_LOG is as follows:

DBCC rebuild_log('<db_name>','<log_filename>')

where <db_name> is the name of the database and <log_filename> is
the physical path to the new log file, not a logical file name. If you
do not
specify the full path, the new log is created in the Windows NT system
root
directory (by default, this is the Winnt\System32 directory).

6. Set the database in single-user mode and run DBCC CHECKDB to validate
physical consistency:

sp_dboption '<db_name>', 'single user', 'true'
DBCC checkdb('<db_name>')
Go
begin tran
update sysdatabases set status = <prior value> where name = '<db_name>'
-- verify one row is updated before committing
commit tran
Go

7. Turn off the updates to system tables by using:

sp_configure 'allow updates', 0
reconfigure with override
Go
============|||

Satya - none of that works in SQL Server 2005 (e.g. I removed the DBCC REBUILD_LOG command).

Tarek - you should call Product Support to help with recovering this.

Thanks