Let's build a Data Warehouse

Our reporting needs have outgrown our existing tools. Actually, that's not true. We have all the right tools but are not using them as well as we could be. It all starts with our data. Right now it all sits in our vendors schema. That works well for the transaction nature of the application, but not so much for reporting.

We have done a lot with what we have. Every night, we take the most recent database backup and load it onto a second server that is used for reporting. I take about a dozen of our core queries and dump them to tables for use the next day. We do the basics like indexes and primary keys. Or issues is that these are designed for specific reports. As the demands and needs of the reports change, we put in a good deal of time reworking the queries.

We started building our reports with Reporting Services and have not expanded our use of the tools that SQL has to offer yet. In the mean time, I have gotten more involved in the SQL community. Attending user groups, SQL Saturdays, and other Microsoft Tech Events. I have been introduced to a lot of features and ideas that I was previously unaware of. I think it's time we built a data warehouse.

I don't think our dataset is large enough for me to truly call what I am going to make a data warehouse. My database sits at 30 some gig in size. I also have a huge maintenance window. The core activity of our business ends by 5:00 pm so I have all night to process whatever I want. So my ETL process can process my entire dataset every time. In the beginning anyway. I'll deal with slowly changing dimensions later.

I want to build a star schema for my data and take advantage of Analysis Services. I want to be able to expose my data to PowerPivot and PowerView. I see a lot of power in these tools and there is no better way to learn than to jump into it. Even if I can't get my user base to use these tools, it will help me parse our data and they will still benefit.

SQL backups revisited. Just use Ola Hallengren's Scripts

I made a quick post about sql backups not that long ago. Take it for that its worth, but there is a much better way to deal with back ups.  Ola Hallengren has a set of maintenance scripts that could not be easier to use.  I can't tell you how much time I have spent tweaking and adjusting my scripts in the past.  I knew of his scripts but never took the time to look at them.

All you do is run the script and then add a schedule to the jobs it creates.  The jobs are very clear in what they do.  If you review his site, he even gives a suggested schedule and job order that will fit most people.  Those scripts handle many special cases.  It knows if your database needs log back ups or not. It even takes into account Always On backup priorities.

I don't know why I never looked into them before, but I will use them on every database I administrate now.

Discovering SQL Server: TempDB

TempDB is a very unique database. It is critically important but wiped out every time you restart SQL. SQL server does a lot of important work with the TempDB, but it's all temporary.  It is a scratch file if you will.

TempDB gets its own disks.  The faster the better for both reads and writes.  This file can get a lot of activity and the disk contention it creates will be noticeable.  Some query operations can spill out of ram into the TempDB and it's possible to sort indexes in there as well.

Use more than one TempDB if you have lots of cores in your server.  Lots of people have different ideas on how many files you should use for TempDB.  One rule of thumb is one file per 4 cores.  A few files is OK, but don’t go overboard with it.  The important detail is to manually size the files so they are all the same size.  SQL will use them more evenly when you do that.

Another good tip I picked up from one of my local SQL user groups is to make TempDB your default database instead of master for all users that don't have a more appropriate default.  The idea is that if you forget to change to the right database in management studio, your scripts will run in TempDB instead of Master.  So if you create a bunch of tables in TempDB, no big deal because they will clean themselves up.

Discovering SQL Server: Backups

It is very important to pay special attention to SQL backups.  SQL is not your average server and a little extra care is in order to make sure you are doing it correctly.  The database files have constant activity, so you can’t just ask Windows make a copy.
Here is a quick SQL command to get you started:

BACKUP DATABASE MyDatabase
                TO DISK '\\server\share\MyDatabase.bak'
                WITH BUFFERCOUNT=35

BACKUP LOG MyDatabase
                TODISK '\\server\share\MyDatabase.bak'
                WITH BUFFERCOUNT=35

This takes a fresh full backup of your database and a tail backup of your log file.  Make sure you are backing up your logs. If this is a production database, you should backup the log frequently.  I used a network path in my example because I want those backups off the server.

Now that you have your backups in a file on another server, use your favorite backup method to back them up.  Every environment is different, but I do full backups nightly and keep 7 most recent backups on the network share.  My transaction logs run every 15 minutes on databases that need backed up more often than daily.

There are several options you can use when running your backups.  I also add COMPRESSION and CHECKSUM along with the BUFFERCOUNT=35 option. The buffer count one is kind of a magic number that speeds up your backups.  It allows the backup process to stream more data from disk into ram as you save it to the network.