Welcome back for part 3 of my SQL Snack Pack on Table Partitioning! If you have not watched the first two videos, I would highly encourage you to do so.
Tag Archives: database administration
I’ll be doing double time over the next two weeks with Two Presentations on SQL Server Internals. It is essentially just a repeat of the same presentation but with two chances to attend 🙂
Here are the details of the timings for both presentations:
April 29th @ 12 Noon EST – Dell Software
I will only cover the first half of the slides during this time slot
Click here to Register
May 6th @ 12 Noon EST – PASS DBA Fundamentals Virtual Chapter
Click here to Register
Welcome back for part 2 of my SQL Snack Pack on Table Partitioning! If you have not watched the first video, I would highly encourage you to do so.
I hope you’re hungry for another SQL Snack! In fact, this will be one of a series of snacks (dare I call it a SQL Snack pack?). Table partitioning is a fantastic feature that is easy to learn and can significantly improve your OLTP and Data-warehouse environments. It can be a little intimidating because it is tricky to get started with, but once you get the basics down you’ll realize it’s pretty straight forward and a very useful feature to have. I will be providing the code and outline for each of the SQL Snacks related to table partitioning so that you have a chance to practice on your own. Happy partitioning!
SQL Saturday has been a fantastic experience for me here in the DC area (I blogged about it here) and I hope for the same thing in Richmond. This is my first time to attend a SQL Saturday in a city outside my area of residency, and I will also be speaking there. This is a bit of a new journey and one that I think I will enjoy.
This is a new experience and one that I have been excited about since speaking with Wayne Sheffield about it at the DC SQL Saturday in December 2013. I have him to thank for encouraging me to spread my wings and I hope for a smooth ride upward from here. That is the embodiment of the Professional Association for SQL Server (PASS) after all; to establish life long learning and grow the community by giving back. I think I could probably do a commercial for them or be a PASS spokesperson. Seriously though, I’ve learned so many things that have helped my career for free or a very low cost.
For this SQL Saturday, I’m also planning to attend the PreCon event scheduled for the day before. There is still time to register by going to the main site for the event here. I’ve selected to go to session by Robert Davis for my PreCon and it was a hard choice because the “Murder Thy Wrote” PreCon was very appealing as well and I hope to catch that one at the next SQL Saturday I attend.
Instant File Initialization (IFI) is an interesting topic with regards to how SQL Server works with storage. It is an easy feature to turn on and can improve the performance of your server; specifically with creation and growth of data files including TempDB rebuilds with SQL Server restarts. There is a slight security risk where a professional data thief could potentially recover bits of data that have not been over written since IFI was turned on, but the chances of that happening are slim. Plus, if they have physical access to the hard drives on your server, you will have bigger problems to fix.
So without further ado here is the next delicious SQL Snack for Instant File Initialization:
There are many features/options we sometimes overlook and then wonder later what went wrong. The COPY_ONLY option with backups is one that I felt is important to highlight to SQL Server DBAs. This backup is independent backup that is not part of the regular cycled backups that you perform; hint if you’re not performing regular backups please get up and schedule them NOW! Sorry for yelling 🙂
Using this option when doing backups allows you to take backups that do not interfere with your regularly scheduled backups in order to move them off to a QA, Development, or Staging area where you can test against that database or fix bugs without interrupting your production environment. Many times, I have seen that off-cycle backups are taken which become part of the backup set and then are deleted. This can cause negative consequences when doing restores as I will demonstrate in today’s SQL Snack:
Code is provided below if you would like to test it yourself. Please watch the video in order to understand how to test this yourself: