SQLServer 2005: DB Design 1

[7280]

Hi,
I need to create a database on a sqlserver 2005 which will be the mail archiver repository of exchange.
This database will be mainly read; it is written only during archiving operation (once a day).
This database will become big (estimated size is 60 gb).
I was asked to create this database with many files, each should be 2gb in order to balance io.
Do you see any problems with this solution?
Do you see real benefits?

Thanks,
Tarek

 

[ptheriault]

It sounds like the person who requested this is an old Oracle\unix person. The 2gb file limit was a limitation of Unix. Not any more though. There is no need to do it that way. having 30 data files of 2 gb each will make things difficult to manage.
I would not want to have to detach and move that db to another server.
Best practices states that "You should store all data and objects in secondary files and leave the database catalog in the primary file. This configuration helps reduce disk access contention" That means create one primary db file. (.mdf) and then create a secondary file for your db objects. (.ndf). You can create more than one .ndf and separate tables based on access. You can then place these files in filegroups. the advatage of using filegroups is that you can restore one filegroup while leaving the database on-line.
This link might help explain somethings to you.

http://msdn2.microsoft.com/en-us/library/ms189317.aspx

- Paul
- Database performance looks fine, it must be the Network!

 

[7280]

Hi,
thanks for response.
I read from the link you posted few info about primary/other files and filegroups.
But I didn't understand how to tell system to use for user objects the secondary file. Is this done by default?
Let's say I create a new database, called archive.
I will create two filegroups.
filegroup 1: within this I create system *.mdf file
filegroup 2: within this I create user *.ndf file, let's say arch1.ndf, arch2.ndf
When new tables/indexes are created in this database are they automatically assigned to filegroup 2?


Thanks

 

[ptheriault]

no, when you create a table you specify which filegroup to to use.
Like this
CREATE TABLE mytable ( Table Def...)
ON [arch2.ndf]
Same with an index.
CREATE INDEX .... ON table
ON [filegroup]

So you would never create a table on the PRIMARY filegroup.
If you don't specify the filegroup it is created on the primary by default.

- Paul
- Database performance looks fine, it must be the Network!

 

[7280]

I expected this behaviour, but it's a problem.
When I create new tables I can specify it, but usually objects are created during application installation and I can't modify all the scripts!!
However I think there's a way to put the new filegroups as the default ones for creating users data.
I was also thinking to create a dedicated filegroup for indexes. Is this a good idea?
If so is there a way to move indexes between filegroups online?

Thanks again.

 

[ptheriault]

by default the clustered index will be in the same filegroup as the table. You can create a non-clusterd index on a different filegroup. You can also create a partition clustered index across multiple filegroups. You would use the CREATE INDEX with DROP EXISTING clause to do that.
Check out Books On-Line (BOL). Search for filegroups [SQL Server] index placement. There is some really good stuff there.

- Paul
- Database performance looks fine, it must be the Network!

 

[7280]

Isn't there something like oracle's rebuild online index:
alter index indexname rebuild tablespace tabelspacename online;
of course tablespace will we replaced by filegroup in the statement.
has it been implemented in 2005??

thanks.

 

[ptheriault]

yes there is
ALTER INDEX ...
WITH (ONLINE = ON)

http://msdn2.microsoft.com/en-US/library/ms188388.aspx

- Paul
- Database performance looks fine, it must be the Network!

 

[7280]

Thanks, unfortunately I can't specify to which filegroup I want to rebuild.

 

[7280]

Going back to the design, where can I look for guidelines to follow in order to create a healthy environment?
I need something like good practices to follow.
I'm looking here:

http://msdn2.microsoft.com/en-us/library/ms189317.aspx

but I'm finding general information, not specific.

I'd like something more detailed about how to create the database in order to avoid common performance issues.

Thanks again.

 

[ptheriault]

Try this site. There is lots of good information there.

http://www.databasejournal.com/features/mssql/article.php/3640431

- Paul
- Database performance looks fine, it must be the Network!

 

[7280]

Thanks Paul, that's article is more related to design.
In my post I haven't been very clear
I need something for dba. Unfortunately I haven't control on the database design because the application is third party, not developed internally.

I need some info as you already post about filegroups. I want a mix of logical/physical design, security and so on.
A practice guide about installation and securing the system.

Thanks

 

[ptheriault]

Here are few links that could offer some help in that area.

http://www.sql-server-performance.com/filegroups.asp

http://www.sql-server-performance.com/ac_filegroup_performance.asp

http://www.databasejournal.com/features/mssql/article.php/1439801

http://www.sql-server-performance.com/q&a_performance.asp

I

- Paul
- Database performance looks fine, it must be the Network!

 

[7280]

That's very good and usefull, thanks!