February 18, 2012 at 9:39 pm
1) Maintenance plans and maintenance wizards aren't practical with big databases (they are too slow and time-consuming to set up). T-SQL queries must be used.
2) There are multiple ndfs and one mdf as opposed to one mdf file.
3) backups take a long time over a network.
4) can I get one more significant difference?
February 19, 2012 at 3:21 am
It's not about maint plan vs T-SQL. It's about targeted, sensible maintenance not everything.
2 is not necessarily true, and multiple filegroups doesn't really make maintenance harder
3 - backups on VLDBs take a long time regardless of where they are written to.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 19, 2012 at 3:55 am
Rowles (2/18/2012)
2) There are multiple ndfs and one mdf as opposed to one mdf file.
Nice article on performance with multiple filegroup.
http://www.sql-server-performance.com/articles/per/optimize_filegroup_performance_p1.aspx
February 19, 2012 at 4:58 am
sumitagarwal781 (2/19/2012)
Rowles (2/18/2012)
2) There are multiple ndfs and one mdf as opposed to one mdf file.Nice article on performance with multiple filegroup.
http://www.sql-server-performance.com/articles/per/optimize_filegroup_performance_p1.aspx
Pity it's wrong in a few key places.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 22, 2012 at 3:17 am
One point could be added, ie the disk space consumption will be more so the cost involement will be higher compared to smaller databases
February 22, 2012 at 3:26 am
A VLDB requires that you need to be more proactive , make sure that a lot of the problems associated with the database are identified before hand.
for
Example the sizing for the database needs to be perfect, asking for 10 Gb is easier than asking for 500Gb in most companies.
Your DR plan needs to be practiced coz the backups take a long time , restore take longer.
You need to start worrying about how archival and data purging happens
You need to avoid frequent deployment as they will increase the risk on a VLDB.
Some options you take for granted in a smaller db might not be available when dealing with a large db.
Know the impact your large db creates on shared resources. like tempdb etc.
February 22, 2012 at 6:09 am
I'll take #4 for $500 🙂
When working with small databases even crappy code - better saying "sub standard code" appears to work properly and perform fine; on top of it, if something goes wrong the time-to-fix-it is usually very small.
When working with VLDB bad code will show immediatelly in the form of agonizing performance and when something goes wrong ... well, you will find yourself in a pickle.
VLDB are usually mission critical ones where in most cases you can call yourself lucky if you get a two to four hours maintenance window from time to time. In VLDB performance tunning and recoverability should be your major concerns.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply