Category Archives: SQL Schoolhouse

While preparing my newest presentation, I thought the best way to teach backup and recovery is through humorous stories revolving around horrific backup and/or recovery incidents. I have a couple, but I thought I would open this up to the SQL community and see if there were some really outrageous ones out there. I can cite you (or not if you choose to remain anonymous) in my presentation and tell your story to help teach the world about SQL Server Backup and Recovery. You can simply e-mail me at Ed at SQLGator.com or comment on this post.

Recently, I was asked the difference between a primary key and a unique key and this seemed like a good topic for a blog post.

First of all, the primary key is the key that will uniquely identify the record and enforce entity integrity. By default this will create a clustered index on the column or columns selected. The unique key, like the primary key, will be unique, however it creates a non-clustered index by default. The unique key will also allow one NULL record where the primary key will not allow any.

In addition, there is only one primary key per table, whereas you can have multiple unique keys. Enjoy!

Today I am going to bring you a great site, SQL Customer Advisory Team (SQLCAT) presents the Top Ten Hidden Gems in SQL Server 2008 R2. If you haven’t perused the SQL CAT website, do yourself a favor and check it out. Enjoy!

Today is the semi-annual 24 Hours of PASS free training day. Sessions started at just a little while ago, so you still have time to sign up here. There is something here for everyone from business intelligence to professional development to hard core database administration. I personally am signed up to watch eight different sessions throughout the day, work permitting; it would be more but there is some stuff that I just do not work with. So get out on out there and get your SQL Learning on. Enjoy!

The stored procedure, sp_helpuser, is useful in showing various information about the users such as login name, default database, default schema and the SID. You can also see if a user name is aliased. I hope you find this useful. Enjoy!

Yesterday concluded my month-long journey with a question and answer series. That was fun having a structured series to follow and it really helped me with two projects I was working on because I had limited amount of time to dedicate to my blog. I think I will do that again sometime in the near future, but I missed being able to update everyone with daily solutions to problems or changes to my goals.

Next week I will look back into the last month of my life and see what things you have missed while on our journey and try to revisit them for you. In essence we can DVR the moments and fast forward the commercials. Enjoy!

Let us continue our question of the day series with our final day, day thirty.

What is replication?

SQL Server replication is a process of distributing database objects across different instances and then synchronizing the data between the instances involved. This is great for remote application as well as mobile users across slower networks. I’ve personally worked with replication where data was synchronized over air cards to laptops with workers in the field with great success.

Snapshot backups are a specialized form of backup which minimize the use of the server resources to produce the backup. They are typically done in conjunction with solutions provided by hardware and/or software vendors.

Snapshot replication is similar to a database snapshot in that you can distribute a replica of the original database at a given point in time, however it does not update as the data changes going forward. This snapshot is then replicated to subscribers. This is a more appropriate solution if you have data that you want replicated but it does not change very often.

A SQL Server database snapshot is a static view that is essentially a read-only image of an existing database. When the snapshot is taken it is consistent with the original database and must reside on the same server and as data is modified in the original database, so will it be modified in the snapshot. There are quite a few limitations in using database snapshots.