| Author |
Topic |
|
LOOKUP_BI
Constraint Violating Yak Guru
295 Posts |
 Posted - 2008-03-24 : 13:54:32
|
| Hi,Im a Jr DBA and have been given an assignment by my lead to find information on the following.We are to migrate existing db of size 4TB to a DELL PowerEdge 2950[Mem:Up to 32GB]OS : Windows Server 2003 Std Edition X64 SP2DB : SQL Server Enterprise Edition x64I am to find on how to design the db to provide optimum performance,fail over and consider the growing factor of the db.1)What would be the recommended RAID settings?2)Placement of the tempdb ?3)Should we do clustering and why ?4)What Data partioning would do to help?5)Any Other aspects to be considered for sizing db ?6)Placement of data files and log file on separate physical disk ?7)Indexing?I have read many sites.I would appreaciate if someone could write suggestions and opinions based on their current db design spec or previous experience,by selecting best db design points.Thank You. |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2008-03-24 : 14:13:41
|
| Well I can offer you some general suggestions, unfortunately a lot of the answers are going to be "it depends". It depends a lot on your needs your table structure and what your database does. Is it an OLTP system or a data warehouse. Different needs..different setups.1. The "microsoft" reccomended approach is raid 1 (or 10) for logs, 5 for data files. However I've been concerned lately with putting highly transactional tables on Raid 5 because of the extra overhead incurred by parity.2. If you can put temp db on it's own disk3. Clustering is an "it depends" answer. Clustering gives you an HA environment. Do you need one? Expensive to do it if you don't. How often does your data get updated and by how much. If your 4 TB is old info and your updates are small then you might be better served by mirroring4. Partitioning helps you to organize your data into logical groups and provides sql server a way to only query the partition that it needs to to find the data. For example if you have an order history table and you partition by month/year, then queries for a particular month year only scan within the partition it needs not the entire order history. So again, it depends. Not all data is made for partitioning, depends on your needs and how the data is used.5. This one is pretty open. If you decide to go with clustering definitely order both servers at the same time, don't fool yourself into ordering one to "get started" and then get the other one in a few months. Hardware changes, service packs change things..you want the servers to be as identical as possible or something obscure is sure to bite you in the a$$. Second, you didn't mention data growth. How much data will you put into your DB in a year. 1 TB? 50 MB? Figure it out and make sure you have enough space for at least 2-3 years out. Nothing worse than getting the error that the disk is full right in the middle of your nice peaceful day.6. Yes. This is always a good practice.7. This one is a purely "it depends" answer. I'd have to know a lot more about your DB. For simplicity sake, yes I can't think of many reasons where you wouldn't index except maybe on a very small table. Leverage clustered indexes and partitioned indexes where possible.I really like a book calle "Microsoft SQL Server 2005 Administrators Companion" microsoft press, author Edward Whalen is the first author listed. It goes much more in depth into some of your questions and answers. Definitely worth a read and will go a long way to helping you.Mike"oh, that monkey is going to pay" |
 |
|
|
LOOKUP_BI
Constraint Violating Yak Guru
295 Posts |
Posted - 2008-03-24 : 15:57:00
|
| Hi Mike,Thank You for your time in explaining.Yes, It is a huge Data Warehouse and the current size is 2.5TB with an expectation to grow up to 4TB [growing fast]Everyday there are about 14 files coming in [about 20GB]that will go through ETL and be stored into the datawarehouse.1)Could you explain further on the steps I can take for the tempdb,best approach placement,location,sizing,any settings?2)My lead has also informed me that there are considering of purchasing a Dell Power Vault Storage as well.Any advise on physical structure of disk? Placement of data file and log file?3)How Indexing would help?4)How to ensure log file does not outgrow?Do provide me with more good ideas and advise.Thank You! |
|
|
|
LOOKUP_BI
Constraint Violating Yak Guru
295 Posts |
Posted - 2008-03-24 : 22:49:59
|
| Could someone also explain to me on how to manage the evergrowing tempdb? What are the settings I should choose?How will data partioning help?Would adding data files to separate filegroups on same physical disk help?OR should it be placed on separate physical disk |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-24 : 23:05:20
|
| Tempdb should be on its own disk array. You can create multiple data files for it if see contention. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-24 : 23:08:38
|
| >> I've been concerned lately with putting highly transactional tables on Raid 5 because of the extra overhead incurred by parity.Depends on number of disks in the array. We converted 4+4 raid 10 to 7+1 raid 5 recently, read/write speed is much faster. |
|
|
|
LOOKUP_BI
Constraint Violating Yak Guru
295 Posts |
Posted - 2008-03-25 : 08:53:09
|
| Thanks for your advise,As I am pretty new.Could you explain further what this means?"We converted 4+4 raid 10 to 7+1 raid 5 recently, read/write speed is much faster"1 More question how do you allocate physical disk space of server for both data file and log file.Could you give me a simple example.Thanking You in Advance. |
|
|
|
stephe40
Posting Yak Master
218 Posts |
Posted - 2008-03-25 : 16:09:02
|
| There is plenty of books out there and articles online that discuss these issues. Read up, learn this stuff for yourself so you can be better prepared to handle this type of thing in the future.Here is my (bit smug) recommendation. :)Always use 64 bit.Use RAID 10 for OLTP data and log files, use RAID 5 for OLAP data files, RAID 10 still for OLAP log files.Place tempdb on sepearte physical disks.Seperate your data and log files for you main database on different physical disks.Use clustering for HA. Use mirroring for off site disaster recovery. Use both if you can.Use the DMV's to analyze index usage in the database.Wikipedia has a decent article about RAID (http://en.wikipedia.org/wiki/RAID). It will explain what rmiao ment with his statement.Ohh.. and tell your boss to buy the largest most powerful servers you can. Nobody likes to tell their boss that the business is running slow because you tried to save a couple thousand bucks a year ago when you bought the hardware.- Eric |
|
|
|
LOOKUP_BI
Constraint Violating Yak Guru
295 Posts |
Posted - 2008-03-27 : 11:44:24
|
| Thank You All.The upgrade is scheduled for next week and if there are any hiccups, you would be seeing more of me in the forum.I would consider all your opinions while performing my upgrade.I have also come up with a migration checklist.Do revert if I'm missing on somethings http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=99767 |
|
|
|
|
VLDB Design Ideas and Suggestions-Appreciated