Month: July 2018

Staying Focused

Doug

One of my goals this year is to stay more focused on my active projects without losing focus of my yearly goals. During 2017, I found myself getting caught up in the day to day requirements of being a Database Administrator rather than planning my day/week/month around my goals. Come review time for 2017, I realized I had not paid enough attention to my goals throughout the year, just the daily operational tasks. This was the catalyst for purchasing Shawn Blanc’s (t|b) Focus Course. I heard about Shawn’s work through a few different podcasts and decided to give it a try.

The course was laid out in 8 easy sessions. Each session was a single page to read through or you could use the accompanying video if you wanted to sit back and listen to Shawn talk through the topics. Each video average around 6 minutes, so it didn’t take long to get through the course. Here are sections of the course and the key points that stood out to me:

Why We Need a Focused Life
Change your mind about life vision. A focused life is a vision for your life (your “why”), life goals (your “what”), action plan (your “how”), daily schedule (your “when”), lifestyle practices (your safety net).

Meaningful Productivity
Consistently giving our time and attention to the things that are most important

Personal Integrity
Are there little things you can do today to improve your day tomorrow?

The Note
Leave a note out for yourself that is the first thing you’re going to do tomorrow when you begin your workday. Let your mind think about it overnight.

Schedule Your Most Important Tasks
Budgeting time is similar to budgeting money, big rocks first.

The Echo Chamber
Turn off notifications. Use downtime to be creative: take a walk or listen to a podcast.

Understanding Overwhelm
Recharge your brain by reading (30 minutes) and get enough rest.

The Jolt
What event in your life caused you to reflect on things?

I really enjoyed the course and am still using some of the topics in my daily and weekly routines. The first thing I did was turn off notifications for all my social media accounts. I still look through them, but during a time I choose, not the notifications dictating it to me. During my weekly review, I review a list of keys areas of my life and decided “Are there little things you can do today to improve your day tomorrow?”. This helps set the stage for a focused week. Trying to schedule the “big rocks” during the first part of my day helps me schedule time for those projects that I don’t have time to solve in one day, but rather pick away at the project, week over week, til the solution is in sight.

If you are having problems staying focused, give Shawn’s course a try, it helped me.

Doug Purnell
@SQLNikon
sqlnikon@gmail.com
https://sqlnikon.com

T-SQL Tuesday #104 – Managing Databases Snapshots

Doug

This month’s #tsql2sday is being hosted by Bert Wagner (b|t). His topic is about code we have written that we would hate to live without.

We have a pretty good handle on change control here and one of the steps we perform before any change is made to a database is to take a database snapshot. Database Snapshots used to be an Enterprise only feature, but with the release of 2016 SP1, it is now available under Standard, Web, and Express editions. If you have been using Standard Edition for a while, you may not have realized this feature is available to you.

Database snapshots are read-only versions of the database at the time the snapshot was taken. Database snapshots start off at almost a zero byte databases. It is only when changes occur to the original database that the original pages are copied to the database snapshot. Let’s say you took a database snapshot at 2:00 pm, after that point, before any pages are modified in the original database, the unaltered pages are copied to the snapshot database. This process enables you to keep an ‘as-of view’ of the database, in this example as of 2:00 pm. The snapshot database is visible under SSMS and you can see the schema/data just like it was a live database. The big difference is the snapshot database is read-only and only grows as data pages are modified in the original database.

When you create a database snapshot before a schema or data change request, you are enabling yourself to have visibility into the database just before the change. Just imagine if you had an UPDATE or DELETE change go south, with the database snapshot in your back pocket, you can pull the original data from the snapshot database or restore the database from the database snapshot.

Because of the number of databases and changes we manage, we keep up with a shared script that has all the scripts for the last database snapshots. This makes it easy to create a new database snapshot without having to re-create the T-SQL each time. Here is an example of what a create database snapshot statement looks like.

CREATE DATABASE DBA_ss_20180703_0858 ON
(NAME=DBA,FILENAME=
'D:\SQLData\DBA_20180703_0858.ss'),
(NAME=DBA_data1,FILENAME=
'D:\SQLData\DBA_data1_20180703_0858.ss'),
(NAME=DBA_data2,FILENAME=
'D:\SQLData\DBA_data2_20180703_0858.ss'),
(NAME=DBA_data3,FILENAME=
'D:\SQLData\DBA_data3_20180703_0858.ss'),
(NAME=DBA_data4,FILENAME=
'D:\SQLData\DBA_data4_20180703_0858.ss')
AS SNAPSHOT OF DBA;

If you need help with understanding where your database files are located, you can use EXEC sp_helpfile under the context of your database. This will return the necessary information to build your CREATE DATABASE AS SNAPSHOT statement. We chose to key the snapshot files in the same folder structure as the MDF/LDF files.

We generally keep our snapshots for 3 business days. To keep track of all the database snapshots across our instances, we run a query against one of our Central Management Server folders on a weekly basis and delete the older database snapshots.

SELECT name
FROM sys.databases d
WHERE d.source_database_id IS NOT NULL;

I hope you can see the advantage of database snapshots and keeping a history of the T-SQL used to create all your database snapshots. One of the main goals of a Database Administrator is keeping the data safe and database snapshots do the job for us.

Doug Purnell