January 16, 2009 at 9:09 am
when i started at my present company i was astounded by how many different DB's they had which, in some way shape or form reference each other but do not have any RI. There are some functions in place which do the same task as a FK (across databases) but this is obviously not an ideal solution.
The idea has been proposed of creating 1 Db with many schemas / filegroups (one for each current DB, size would equate to around 600GB+) and partition the data. Now, this is obviously a massive project to undertake and i'm interested to know everyones thoughts / experiences of doing this. Is it worth it? Will we get enough performance / redundancy gains to warrant the time and effort? I dont want to over complicate the setup we have if we won't get any benefit from it.
Is partitioning in 2005 an Enterprise edition only feature? If so, is there a way to backup and restore to a test/dev/support servers which are all standard edition?
any information is greatly appreciated 😀
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
January 16, 2009 at 10:18 am
I was contracting at a company that had a 7TB database of historical transactions.
For this database it was def. worth the effort, as we could partition the transactions by day. Several procedures ran against daily information and this sped up the process greatly. We also put each 250GB month into a seperate database, and joined all the months together thru a union view.
ok, from a speed and backup solution this was great, and as the transactions were seperated by date, the partitioning was easy. using the views we could easily drop the old month ( history was kept for 24 months )
The downside, we had to create new partitons for each day and databases for each month.....this took some time and was a bit of a pain for the new dba's to learn comming into the group.
At least for this much data.....it was worth the extra effort.
Hope this helps
Eric
January 16, 2009 at 11:35 am
Partitioning is the only way to handle big databases but do consider the increased admin overhead in managing all those files, filegroups and partitions!
Yup it's enterprise only, I have to admit I've never restored a database with partitioned tables to a standard sql server.
You need to carefully plan this type of work; and then tests and test and test.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 25, 2009 at 1:51 pm
does anyone know of a way to to back up a partitioned db and restore to a standard version? I would really like to implement this strategy but can't justify the extra cost of puttin enterprise onto all our dev/support/test servers!
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
January 25, 2009 at 3:22 pm
well your dev servers will support partitioning because dev edition is essentially enterprise. I'd happily run a test but I don't have a test server with std edition on, sorry.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
March 9, 2009 at 7:12 am
I tried it today. Won't work :crying:
I was upgrading a SQL2005 Enterprise edition on a 32 bit platform to a 64 bit platform.
Installed SQL2005 64bit standard edition. The backup didn't work, got the message only available in enterprise edition.
March 12, 2009 at 5:32 am
Cheers for the posts so far chaps, i think i may have a solution to the partitioing issues. I may be able to get a new server and virtualise it (VMWare possibly) and use an enterprise (per processor) licence on this box. That way, i could potentially have, Dev, Support and Test boxes virtualised onto here. Performance may not be ideal, suppose it depends on the spec of the new server.
I've got another question regarding filegroups. I understand the concept of seperate files / filegroups but how is the transaction log affected? In my example, i have numerous DBs (each with their own TLog) which i'm looking to bring into one partitioned / schema'd / filegrouped DB. So now i'll have numerous Files / filegroups and only 1 transaction log. Should i create seperate TLogs?
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
March 13, 2009 at 4:17 am
Anyone?
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
March 17, 2009 at 6:08 pm
Creating multiple log files for the same database doesn't seem to help anything unless disk space is an issue.
MJ
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply