August 10, 2009 at 2:58 am
I have been assigned a task to partition one database into 3 to 5 databases. Each database will be doing some specific task related to the business logic. Like error and exceptions will be in separate db, lookup tables will be in another db. Users related information and its Tracking will be in another db. Currently these all functionality is in one db. Overall the taget is to achieve optimal Performane and Reliability. Before i start, I gathered some information and started distributing all tables among different databases. Right now i am thinking if i use a Hardware level database partitioning like rather i distribue tables in different databases, there is an option to put table in specific file group and use RAID10. From my own study i think RAID10 provide Performance and Reliability both. Is it fine? I need to know if i'm on right path (either use partitioning db (Option 1) in different dbs or use filegroup with RAID10(Option 2)). How much these two will be feasible and which one approach is best? what else you can suggest to achieve performance and Reliability both.
P.S. DB Partitioning (Option 1) will be implemented on Cluster env. Each db will be on active/active node.
Please help what others are doing for handling large number of users activity and large databases?
Shamshad Ali.
August 11, 2009 at 8:28 am
Option 2 (Filegroups and RAID1+0)
You also will be clustering this instance?
How large is this database? Terabytes?
What are the servers that you're moving to?
1. You're trying to achieve maximum availability. What happens if the "lookup tables" database is down, then the application stops functioning? One reason to rule out option 1.
2. I don't see what you would achieve from seperate databases over using multiple filegroups spread over seperate disks. The backups are just as easy as backing up a database.
3. If all of the databases in Option1 are in the same sql instance then you're using the same resources as if you used filegroups. If the databases are in seperate instances on seperate cluster nodes then you would gain some resources. However the same could be achieved by using filegroups and one database; just load up the server with RAM, and make sure it's 64-bit.
It's hard to just comment without knowing what type of servers and environment you moving to.
August 12, 2009 at 3:53 am
Following are my replies to your question:
"Option 2 (Filegroups and RAID1+0)"
http://msdn.microsoft.com/en-us/library/ms187087.aspx
Improving Database Performance
Using files and filegroups improves database performance, because it lets a database be created across multiple disks, multiple disk controllers, or RAID (redundant array of independent disks) systems. For example, if your computer has four disks, you can create a database that is made up of three data files and one log file, with one file on each disk. As data is accessed, four read/write heads can access the data in parallel at the same time. This speeds up database operations. For more information about hardware solutions, see Database Performance.
Additionally, files and filegroups enable data placement, because a table can be created in a specific filegroup. This improves performance, because all I/O for a specific table can be directed at a specific disk. For example, a heavily used table can be put on one file in one filegroup, located on one disk, and the other less heavily accessed tables in the database can be put on the other files in another filegroup, located on a second disk
You also will be clustering this instance? Yes
How large is this database? Terabytes? Not yet. It will be in Terabytes soon
What are the servers that you're moving to?
Processor Intel(R) Xeon CPU E7340 2.4 GHZ
RAM 30 GB
Windows Server 2003 Ent. Ed. R2, 32-bit, SQL Server 2005, SQL memory configured 28 GB
1. You're trying to achieve maximum availability. What happens if the "lookup tables" database is down, then the application stops functioning? One reason to rule out option 1.
We will have active/active cluster nodes configured, so no worry untill u have One Node alive and all nodes get down. the database will be functioning.
2. I don't see what you would achieve from seperate databases over using multiple filegroups spread over seperate disks. The backups are just as easy as backing up a database.
Plz. check the MSDN http://msdn.microsoft.com/en-us/library/ms190764.aspx
3. If all of the databases in Option1 are in the same sql instance then you're using the same resources as if you used filegroups. If the databases are in seperate instances on seperate cluster nodes then you would gain some resources. However the same could be achieved by using filegroups and one database; just load up the server with RAM, and make sure it's 64-bit.
It's hard to just comment without knowing what type of servers and environment you moving to.
While I was looking MSDN for topic “Database Performance” I found this link.
http://msdn.microsoft.com/en-us/library/ms190619.aspx
After some study, Hardware based solution are typically implemented using
RAID;
http://msdn.microsoft.com/en-us/library/ms188252.aspx
• What type of disk hardware to use, such as RAID (redundant array of
independent disks) devices. For more information, see About Hardware-Based
Solutions.
• How to put data onto the disks. For more information, see Using Files and
Filegroups.
• Which index design to use to improve query performance in accessing the
data. For more information, see Designing Indexes.
• How to appropriately set all configuration parameters so the database can
perform well. For more information, see Optimizing Server Performance.
Based on this information I'm willing to have someone with good knowledge and reply with your recommendations and help me out.
Thanks,
Shamshad Ali.
August 12, 2009 at 7:54 am
The choice of RAID level is independent of your choice of filegroups vs. separate databases. You're using a cluster, presumably that dictates the use of some form of SAN. Setting up the SAN properly for use with SQL Server is (in my opinion) overwhelmingly the most important consideration. I would recommend getting some outside expertise to set it up, don't trust the vendor completely.
When you go about partitioning your database (either with databases or filegroups), are you really using separate physical disks? If your separate logical drives are mapped to LUNs on the SAN that are on the same drives as other LUNs, they aren't really separate. They may be able to take advantage of some parallelism in the I/O path, but each drive has only one set of heads and any physical drive failure will affect all the logical drives that use it. Are your log files going to separate physical disks than the data files? SAN vendors say this doesn't matter, I have yet to be convinced. One recommendation I do take seriously is that you should have (at least) one file per CPU in each filegroup. If all the files are on the same LUN, or on different LUNs on the same RAID set, you're not really spreading out the I/O (although there is still a separate thread on the server to manage I/O to each file). The files for other databases/filegroups should be on their own RAID sets. Then you want tempdb on another RAID set (or sets, 1 per CPU), and logs on another RAID set. Unfortunately, I've never worked anywhere where they would buy me a system with that many RAID sets. My point is that there are a lot of recommendations on disk structure, but you'll never be able to follow them all. In practice, I try to keep tempdb and log files on separate physical drives but gave up worrying about the placement of each file, filegroup, and database. I try to create LUNs or meta-LUNs across all the available RAID sets to get as many disk heads as possible, and use that for all the data files. That is not the best solution for everyone, but it has the advantage of simplicity. I've never had the situation where one database (except tempdb) was so critically important (compared to the other databases on the same server) that it warranted separate hardware.
If you split your tables between multiple databases, you now have to maintain security in multiple places. This may be good or bad, depending on your application. Do you manage security by user or by application, and will your security be improved by putting sensitive tables in a separate database with limited users? Could you achieve the same benefit in one database through schema separation? Are there external considerations such as SOX compliance that would be easier to deal with using separate databases?
How much do you rely on ownership chaining, and can you afford to turn on cross-database chaining?
You have to backup multiple database, but again it is hard to say whether that is a benefit or an inconvenience. Different sets of tables may have different usage patterns and different backup requirements. Putting static tables and transaction tables in separate databases would allow using different backup schedules.
You can maintain relational integrity with foreign keys in a multi-filegroup database, between databases you have to resort to triggers. On the other hand, if the sets of tables in each database are completely disjoint this is not an issue.
I don't think you can choose between one database/multiple filegroups and multiple databases based on performance alone, and I don't see a big performance advantage either way. I think some of the above considerations are more important.
Finally, if you're worried about performance then why on earth aren't you using 64-bit SQL Server?
August 13, 2009 at 7:34 am
I gotta say that without knowing some more justifications and reasonings I think you are embarking upon a lot of effort for little to no gain - actually potentially problems. Nothing I have seen so far even comes close to making it worthwhile to split into multiple databases. My guess is that you have a very large number of things to take care of with much higher priority than segregating your tables into different databases.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply