0

The stackoverflow system says this question is going to be closed, so hopefully a really fast gun can get me a good answer quickly.

As part of my role at the firm I'm at, I've been forced to become the DBA for our database. Some of our tables have rowcounts approaching 100 million and many of the things that I know how to do SQL Server(like joins) simply break down at this level of data. I'm left with a couple options

1) Go out and find a DBA with experience administering VLDBs. This is going to cost us a pretty penny and come at the expense of other work that we need to get done. I'm not a huge fan of it.

2) Most of our data is historical data that we use for analysis. I could simply create a copy of our database schema and start from scratch with data putting on hold any analysis of our current data until I find a proper way to solve the problem(this is my current "best" solution).

3) Reach out to the developer community to see if I can learn enough about large databases to get us through until I can implement solution #1.

Any help that anyone could provide, or any books you could recommend would be greatly appreciated.

2 Answers 2

2

This is an answer I provided on a community wiki a while ago about general administration of databases - sounds like you've moved from a more dev role to a DBA role so a lot of them should be useful to you.

Checkout the series of articles and Q&A columns I write for TechNet Magazine - they're mostly written with the Accidental (we call it 'involuntary') DBA in mind.

Top Tips for Effective Database Maintenance was written specifically as a primer for involuntary DBAs to understand DB maintenance issues.

Understanding Logging and Recovery in SQL Server

Common SQL Server Security Issues and Solutions

Understanding SQL Server Backups - part 1 of a 3-part series. Part 2 will be on using restore (in the Sept 09 issue) and part 3 will be on recovering without backups (in the Nov 09 issue)

You should also checkout my blog and my wife's blog (not advertizing or anything just info) - we both blog a huge amount on a variety of technical levels.

One good series of posts to look through are the editorials for the results of my weekly surveys. They are usually around a broad topic that would help involuntary DBAs. The editorial posts start with 'Importance of' or 'Important'. In fact this week's survey is on being an involuntary DBA - very timely!

We understand the involuntary DBA thing really well - in fact Kimberly and I teach a couple of days of the SharePoint Microsoft Certified Masters class so the SharePoint admins know what to do with their SQL Servers (we also teach a full week of the SQL one).

Hope this is useful to you.

1

A variant of (2) is a reasonable short-term solution. You can set up another database and restore copies of your production data into it at regular intervals (as often as nightly). Analytic queries are quite anti-social on production databases so it is a win to get them off your production system. Make sure this database is on a separate server or at least physically separate disks from your production system. This type of solution is commonly used for reporting.

For a longer term solution consider building a data mart with star schemas which are optimised for analytic reports. For a single data source on a simpler business domain you might be able to get a contractor to do it in just a couple of months, so the cost may be quite reasonable.

If you build a data mart of this sort you can put end-user reporting tools such as Report Builder (which comes SQL Server) or Analysis Services (which also comes with SQL Server but may need a third-party front-end tool such as Proclarity). This substantially empowers the business to do a lot of their own reporting, which may reduce workload on I.T. staff.

There are quite a few good books on SQL Server (see this Stackoverflow posting for lots of fan-out and follow the links.) and a few MVP types who hang out on these forums enough that you can probably get specific questions asked.

Not the answer you're looking for? Browse other questions tagged .