July 21, 2006 at 9:28 am
Here is my task with in the next couple of months.
1). Design database which handles approximately 4 million transactions per day (the size of the record would be around 400 bytes)
2). Planning to partition the table which holds those 4 million
transactions. (We need to load the data real time as and when they arrive....frequency is about 1 minute)
3). Customer request us to keep at least 6 months of online data available to access.
4). The transactions older than 6 months would be archived.
I am initially thinking to create 6 partitions to hold one month of
transactions on each partition. Planning to use sliding window architecture (split & merge) to accommodate the ongoing months. Friend of mine suggesting that I should create 12 partitions one for each month so that I don't have to worry about sliding. I am little bit confused about how should I design. The bottom line is once I design the partition it should be completely automated and don't want this to be a maintenance headache in the long run.
Any suggestions would be appreciated.
Satish
July 24, 2006 at 8:00 am
This was removed by the editor as SPAM
July 24, 2006 at 6:58 pm
If you are speaking of 4 million transactions (rows) per day I'd probably opt for a couple of things on the hardware storage side first. SAN storage for starters using Meta-LUNs as opposed to just LUNs. This way your SAN does not have disks just spinning away doing nothing 95% of the time. It distributes the I/O activity over many more disks.
Keep the system databases master, model and msdb on their own set of LUNs. Keep the tempdb on it's own set of LUNs.
If the application has reporting functions (I'm guessing so) you'll probably want more than a 4 CPU server. Oh, by the way, pay the extra $$$ for Xeon CPUs - 4 physical CPUs look like 8 CPUs to Windows - not quite dual core, but at the price and potential performance gain it's an easy sell. This way you can separate out the reporting functions into another instance. Then one can tune each instance for what it is for - one instance OLTP and the other instance DSS. So what of you have to query across instances, they are on the same box (you will have linked servers, but is you use SPs no one will ever even know). So in order to have multiple instances, you'll probably need more than 4 Gb of RAM, I'd guess 8-12 Gb. The OLTP instance really should not need a lot of this RAM - 2-4 Gb, but I'll bet that the DSS side will. So you are right at the cusp of Standard Edition 32 bit (4 Gb max RAM per instance) and Enterprise Edition 32 bit (8 Gb max per instance).
Next I'd look at partitioning in a more granular fashion, weeks, possibly even days. Each partition should reside on it's own filegroup (the maximum is 32,767 !) This type of partitioning (weekly, although daily is better) will have a number of advantages:
Now there is the part about accessing the data. All inserts should be via SPs. All DSS access should be through VIEWS (using the appropriate UNIONs/UNION ALL). Granted, the granularity of your partitioning, weekly or daily, will determine how often that you change your INSERT SPs and your DSS views. However this is not a great concern since you can automate these tasks like anything else - write it once, schedule it and forget it !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
July 25, 2006 at 9:45 am
Thank you very much for an excellent input. Well for reporting part of the application we are having another read only server and the database restored from the primary database backup on a daily basis.
I am planning to go for 12 partitions one for each month for simplicity sake. However you are recommending weekly or daily partitioning. I think maintaning daily partitions would be difficult correct?
Satish
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply