SQL Server

SQL Server 2008/2008R2 Migration

Doug
End of Life…

As many of you know, mainstream support for SQL Server 2008 and 2008R2 ended back on July 9th of 2014. Extended support is active now and that is set to expire on July 9th of 2019. After July 9th, no security fixes will be made available. It is extremely important for you to move all your database off of and 2008/2008R2 instances to a supported instance of SQL Server.
We started our process back in 2017 to understand some of the application changes required for the database move. We have a mix of custom and vendor applications. The main goal of the migration was to update our documentation around the database & application relationships and bring all our custom databases up to SQL16 standards. 


We’re big fans of OneNote. It is a great way to document our databases and applications along with projects like our migration off of SQL Server 2008/2008R2. We created a template checklist that we could use for each custom database that we created. For the vendor databases, it was more of a manual process because each vendor had a different process for migrating databases.


The checklist consists of these pre-migration steps:

  1. Compare schema between prod and dev/test. We want to make sure we don’t overwrite new objects under dev/test if they have not been deployed to production. Our main gain was to try and use a copy of the production database as a fresh start for dev/test.
  2. Use Microsoft’s Data Migration Assistant to scan the SQL08/08R2 database for any objects that might cause issues under SQL16.
  3. Do a metadata search across other databases no the same instance. Because some of our databases cross-reference other databases, we wanted to search all objects and look for references to the current database that we are migrating. If there are references, we need to validate they are in use, and if so, migrate all databases together.
  4. Search through SSRS & SSIS for data sources that use the database that we are migrating. If we find a reference, we need to make sure and update all SSRS & SSIS projects with the new connection string.
  5. The above findings were discussed as a group to determine the best migration steps.

Once the plan was approved by all members of the team, we moved forward with the dev/test migration so the developers had a platform to test against. Here are the high level steps for our migration script:

  1. Restore a copy of the production database under dev/test.
  2. Script out the permissions under the old instance so we can make sure and carry them over.
  3. Drop any production database users from the dev/test restored database.
  4. Create the server and database principals for the applications to authenticate with.
  5. Set the correct database owner.
  6. Update the compatibility level to 130.
  7. Create a SECONDARY file group and move all user objects to the new file group.
  8. Create database security roles and assign database principals.
  9. Enable query store
  10. Run SQL Vulnerability Assessment and baseline/correct any issues.
  11. Update any SSRS & SSIS projects under dev/test with new connection strings.
  12. Test
  13. Test
  14. Test

Once we were happy with testing, we scheduled the production cut-over with our business partners and ran through the same steps as above. We are more than 75% of the way through our SQL08/08R2 databases and are on target to have everything migrated by June. This project would have not been possible without scripts posted by members of the SQL Server Community. Special shout outs to Aaron Bertrand for his script to move objects between file groups, Jason Strate for his script to index all FKs, and S. Kusen for his script to script out permissions within a database.

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

T-SQL Tuesday #112 Cookie Monster

Doug

Shane O’Neill (b|t) is hosting this month’s edition of #TSQL2sDay. Shane’s charged to us is to think back when we had a difficult time with project/task and used our internal mojo (cookie jar) for energy to keep going.


We have custom applications written to support the faculty, staff, and students. Most of these have a SQL Server database to store the information. As with most shops, we have used various versions of SQL Server over the years to support our business partners. The old adage, “if it ain’t broke, don’t fix it” holds true for some of our older SQL Server instances. For those instances, we didn’t make an attempt to migrate off of them until it was required. Well, that time has come. We realized last year that SQL Server 2008 and 2008 R2 were losing their extended support and that would mean no more security updates. We didn’t want to increase our security risk to our data so we started the process of planning the migration to SQL Server 2016. 


After reviewing our database inventory, we found ~30 databases that needed to be migrated or decommissioned by July 9th, 2019. Having a hard deadline for a project is sometimes a nice way to back into the required tasks. But as we started looking through the databases, we realized that some of the apps that fronted the databases have not been touched in years. The first round of eliminations allowed us to ask questions like “Is this application still in use”. You would be surprised how many apps serve their purpose for a year or so and just fall off (similar to SSRS reports). 


We also started to realize that some of our applications had cross-references to other databases and that moving one database could break other applications. The review gave me a headache thinking about the dependencies and how we were going to approach the migrations. 


This is when it is time to dig deep and reach into my cookie jar for inspiration. I have found over the years that large problems can be easily solved by breaking them into smaller chunks. The quote that I use frequently is from a book written by Gary Keller and Jay Papasan titled “The ONE Thing: The Surprisingly Simple Truth Behind Extraordinary Results”:


“What’s the ONE Thing you can do such that by doing it everything else will be easier or unnecessary?”


This sentence has helped me countless times starting on projects that seemed like monsters. It even helps me combat procrastination because by completing a simple step it helps me start down the path of project completion. 


I hope you can benefit from my cookie jar and keep it stored for your next monster project.

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

T-SQL Tuesday #111 – Why do you do what you do?

Doug

This month’s #tsql2sday is being hosted by Any Leonard (b|t). He asks a simple question that gets at the heart of what motivates us, “Why do you do what you do?”.

For me, it is about helping and sharing with others. From the database administration point of view, I love helping others with solving business problems with data. I have a knack for data and enjoy understanding what the business issue is so that I can see how data can solve it. By getting involved in various projects, I get a better understanding of how the business operates and allows me to be proactive and start suggesting solutions.

Outside of the office, photography and cooking are my passions. I love sharing both of these with others. I used to have a small photography business but found the money got in the way of the joy of photography. Now I just use photography as a service project and share the results and it brings me a ton of joy. On the cooking side, watching someone enjoy a few ribs is the best feeling in the world. I’m starting to think about catering but worry about how the money will take away from the joy of cooking. I’m hoping I can get some help on that side so I can just concentrate on the grill.

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

Healthy Living

Doug

I remember the days when my metabolism allowed me to eat anything in sight. I was staying busy playing outside without the draw of electronic devices. Jump ahead 30 years and life catches up with you. I’ve struggled over the past 30 years with the roller coaster ride of weight gain/loss. There are times when my head is straight and I can workout multiple times a week and eat healthy foods. Contrast that with loafing on the coach and downing a back of chips in one sitting; this is my struggle.

The good news is I have the foundation for change, I just need to make sure and utilized the correct pillars. There are folks in the SQL Server community that have written about there own journey and I’m using them as motivation. Thomas LaRock (b|t), Jason Hall (b|t), Brent Ozar (b|t), John Morehouse (b|t), and Andy Leonard (b|t) have written about what works for them and they have inspired me to create my own list of success factors.

My first pillar is making sure I bringing lunch into work. I’ve found that if I don’t bring something in from home I tend to head out at lunch and end up eating a heavy lunch. As you can imagine, the afternoon is harder to get through, and that causes me to go grab some soda (trying to limit) for a caffeine boost. Planning ahead when cooking dinner at home enables you to set aside a few portions for lunch later in the week or freezer bags for the following month.

My second pillar is getting the family on board. Without family support, you’ll find yourself coming home after a long day with takeout waiting for you. Eating healthier is easier when everyone in the house is doing the same thing. Instead of frying some chicken, spend the time and fire up the Weber and grill the chicken. Simple changes can pay dividends on the scale.

My third pillar is fitness. I’ve been a member of our local F3 workout group for almost 4 years. Even though fitness is not the main ingredient for weight-loss, it keeps me active. The men of F3 keep me motivated on a daily basis. There are different types of workouts each day that pushes me physically. I’m looking forward to getting back into running and using the fitness to help me drop some additional weight. The FIA organization is just like F3 but for women. I recommend both organizations for a different take on exercising and service to your community.

My first small win is bringing lunch into work each day this week and not having any soda for the last 24 hours. I’m working on keeping those going and treat each day as a new opportunity/challenge. I’ll keep you posted how this goes.

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

Choo Choo Leipzig

Doug

After a nice lunch at Burgermeister (thanks Matt!) located at Höfe am Brühl (a local shopping mall) with Matt and Andrea, I ventured out towards the train station (Leipzig Hauptbahnhof). This is a huge train station with 25 corridors with 21 tracks that end within the station. The station was opened in 1915 and serves as a major hub within Germany.

The best part about the station was most of the Christmas decorations were still up. This made for some great shots with the lights erected around the station. I spend about 2 hours roaming around taking photos and ended up a local grocery store (similar to Aldi) to buy some snacks for the bus ride down to Munich the next day.

Below the main train terminal were two lower levels. Those levels were dedicated to shopping. There were traditional mall stores along with food vendors. Although the train station was relatively quiet, the shopping area was very busy.

The rest of the photos can be found under by photo site.

guten Abend!

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

T-SQL Tuesday #102 – Giving Back with GTD

Doug

This month’s #tsql2sday is being hosted by Riley Major (b|t). He ask us to “Pick some way you can help our community”. I realized my improvement with time management this year is how I can help the community.

Adapting my time management processes around David Allen’s (b|t) Getting Things Done (GTD) has been very beneficial for me this year. I’ve been a GTD practitioner for a couple of years but lost focus in 2017. When Mala Mahadevan (b|t) ask us to write down our learning goals for 2018 I knew this was the opportunity to reevaluate my GTD process. I believe this is what I can do to help our community, help others with time management. We all are being asked to do more with what seems like less time in the day. How many of you have a hard time keeping track of projects at work and home? GTD is the answer.

I’ve already written about my daily and weekly routines under GTD, but I believe it is time to provide a series of blog posts introducing you to GTD and how to get started. It took me a while to understand how to implement GTD and I hope I can provide you with the steps necessary to get rolling without the bumps that I had. Having the ability to know exactly what to work on without having to worry about all other active projects/tasks is what true time management means to me. This is the place I want to get you to, worry-free time management.

Doug Purnell

T-SQL Tuesday #101 – Essential SQL Server Tools

Doug
This month’s #tsql2sday is being hosted by Jens Vestergaard (b|t). His topic is about the essential SQL Server tools in my stack. I’m looking forward to reading the summary post to find out what tools I’m missing out on.
One of the foundation tools we put on all our instances is Ola Hallengrens’s maintenance scripts. It makes backups, indexes, and integrity checks very easy. This is hands down a much better tool to use over the built-in Maintenance Plans under SSMS.

Restore the database now!

One of the best practices around backups is having restore scripts within arms reach. This functionality is not built into Ola’s solution but Jared Zagelbaum (b|t) has written an extension
that handles this for you. The idea is when the call comes in a 3:00 am about corruption or another reason that requires a restore, you have the scripts ready without having to slap your brain out of a sleeping fog.

Jared’s extension works by adding an additional step to Ola’s backups jobs to create and text file with restore statements for all database with the latest full, diff, and/or transaction log backup files in the backup chain.
You can find his blog post that talks about his solution here and a link the GitHub files here. Thanks to Jared for sharing and allowing me to sleep better at night.

The Ozar Effect

Doug

Back in February one of my blog posts was highlighted on Brent Ozar Unlimited’s Weekly Links. Wow, what a bump in traffic! I usually receive a hand full of views a week, but on February 26th it reached 1,212. The cool part was it was actually the #2 link on Brent Ozar Unlimited’s newsletter for the day. If you don’t currently subscribe, here is a link to start.

This particular blog post was about my daily routines under the Getting Things Done (GTD) time management methodology. Based on the hit count and activity on Brent’s newsletter, I realized that time management & personal productivity are topics I need to spend more time on with my writing. Like other DBAs, I wear multiple hats and trying to keep all projects moving forward along with daily DBA operations can be difficult. I’m guessing there are few other DBAs with the same issues so I’m hoping I can help.

If you’re looking for other #sqlfamily that have posted about time management and personal productivity, check out K. Brian Keely (b|t) and Marlon Ribunal (b|t). They each have their own technical blogging sites but head over to their other sites focusing on personal productivity. Brian is also known as the Goal Keeping DBA and Marlon has posted around improving the life of knowledge workers. I would suggest following both of them and reading back through the archives.

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