Over the last year, I’ve been in consolidation mode at work with our SQL Server instances. The primary reason for the consolidation was driven by SQL Server 2008/2008R2 going out of support, but it was also a great exercise in reviewing all our SQL Server instances and asking the question “Does this instance need to live on its own?”. I was surprised by the number of instances that we could combine and databases that have lived on after an application was decommissioned.
The great part about this project is it has bled into my personal life. Doing a review on life is a good way to trim the fat. I found junk around the house to send to Goodwill, extra credit cards that I didn’t need anymore and found a way to stick with my YNAB budget to reach some saving goals. Take a walk through your house/apartment and see if you can find things that are collecting dust. Donate those items and give them a new life for someone else. Take stock in your finances and see if there are any advantages in consolidating your debt. As I learned from YNAB, give every dollar a job. Review your expenses and decide if everything is in check. Do you have software subscriptions that you don’t use anymore? Do you have multiple cloud storage providers and you could consolidate and make managing your data easier?
As the 1st quarter of 2019 comes to and end, now is a great time to consolidate things in your life and set yourself up for success with your goals in 2019.
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:
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.
Use Microsoft’s Data Migration Assistant to scan the SQL08/08R2 database for any objects that might cause issues under SQL16.
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.
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.
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:
Restore a copy of the production database under dev/test.
Script out the permissions under the old instance so we can make sure and carry them over.
Drop any production database users from the dev/test restored database.
Create the server and database principals for the applications to authenticate with.
Set the correct database owner.
Update the compatibility level to 130.
Create a SECONDARY file group and move all user objects to the new file group.
Create database security roles and assign database principals.
Enable query store
Run SQL Vulnerability Assessment and baseline/correct any issues.
Update any SSRS & SSIS projects under dev/test with new connection strings.
Test
Test
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.
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.
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.
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.
What a trip! I was able to attend an Elon Study Abroad trip across the pond to learn about some of the great composers. This was a three week trip with stops in Germany, Austria, and Hungary. We studied Bach, Mozart, Beethoven, Wagner, and Bartok. We attended a few operas, a chamber music concert, and listened to a 90 piece orchestra (outstanding). I attend as a staff member, one of the many perks working for Elon University and higher education.
London Philharmonic Orchestra in Budapest
The trip started back in the fall with a weekly course to get everyone introduced to the composers. The students were required to write a few papers during the course but luckily for me, I didn’t have to do all the course work. We had a few lessons on the German language and some of the cultural difference with the United States.
Monument to the Battle of the Nations
As the course started, I was known as Photographer Doug. My goal was to document the trip with photos of the group interacting with the guides and various locations we visited. This was the reason I hauled all my photography gear over with my laptop. This allowed me to process daily photos and get them posted to my photo site. The benefit of this was some of the students linked to my photos within some of the blog posts that were required during the trip. I received some great feedback on the photos and it motivated me to take more shots throughout the trip. By the third week though, I was definitely ready to take a break from the camera.
When I wasn’t behind the camera, I was known as Student Doug. I attended all the events that the students were attending. The goal of Elon’s Study Abroad program is to create global leaders for our students. I wanted to immerse myself within the class and take advantage of what the class had to offer. It is not every day you get to sit through a 5 hour Richard Wagner opera in Vienna, Austria. The chance to walk through Beethoven’s living quarters and Mozart’s birth house gives you a different perspective on classical music and the history in Europe.
The part of the course I was not expecting was the interactions with the students and the professors. There were 23 students in the course along with two professors. I was happy that the course consisted of some classroom work before we all hopped on a plane together. This gave all of us a chance to get to know each other a bit. It was one thing to talk during class, but once you step on a plane, train, or bus with 26 other people for 3 weeks, you really get to know everyone.
Matt Buckmaster and Andrea Sinn really made me feel comfortable during the class and trip. Because of my love of photography, I even did a little presentation on mobile phone photography tips and tricks for the students. Matt and Andrea approached the course from two different angles and it really worked great. Matt was the music side of the team and Andrea brought the history lessons.
Matt, Andrea, and myself
It took me a few days to get comfortable with the students. I’m an introvert at heart, but all it took was a dinner invite from a group of students and I was back in college again, can you say Old School! It was great exchanging stories with them outside of the traditional classroom. I graduated from App State in 1993 so college is not fresh in my mind. With my job at Elon, I don’t get the opportunity to interact with students, so this was an excellent opportunity to understand Elon students better. It was great being able to toast over beers and talk about what it is like to be a college student at Elon.
It took me a few days to get comfortable with the students. I’m an introvert at heart, but all it took was a dinner invite from a group of students and I was back in college again, can you say Old School! It was great exchanging stories with them outside of the traditional classroom. I graduated from App State in 1993 so college is not fresh in my mind. With my job at Elon, I don’t get the opportunity to interact with students, so this was an excellent opportunity to understand Elon students better. It was great being able to toast over beers and talk about what it is like to be a college student at Elon.
Prost!
One of the highlights was learning about Darties, a.k.a. Day Parties, from the students. These are held on Saturday afternoon mostly at off-campus housing. I’ve been experimenting with BBQ catering and we talked about the plentiful opportunities to sell some BBQ during these parties. This is one of my goals for spring before some of the student on this trip graduate.
I’m now a raving fan for the global learning opportunities for staff members at Elon. 6 staff members are selected each year to accompany classes around the world. The application is simple and the chances to be selected from a random drawing are very high. Here is a video produced to promote the program for staff members.
I’ll never forgot this trip and the friendships made.
Today we took a day trip to Dachau Concentration Camp. This was the 1st and model camp for all of the other concentration camps. The camp had prisoners from 1933 up till 1945. It is hard to explain how powerful it is to walk on the grounds of Dachau. Pictures don’t do it justice, but I believe I did a good job being respectful while capturing the horrors of Dachau.
You can see the rest of the photos under my photo site.
Our first full day in Munich was spent at the Deutsches Museum and a walking tour of the inner city of Munich. The Deutsches Museum is the world’s largest science and technology museum. Our guide walked us through the highlights since we only had an hour and a half to walk through. Much like the Smithsonian Museum in Washington D.C., you would need at least 3 full days to take in all the exhibits. It was cool to see an Enigma machine from WWII and the first Z3 programmable calculator.
The walking tour through Munich was amazing. Most of the city was destroyed during the bombing runs by the Allies but the rebuild efforts have paid off. Munich is known for their beer houses and Oktoberfest. As we walked you could see beer houses tucked away in various alleyways. I stopped for lunch at Zum Spöckmeier close to St. Mary’s square and had some really nice dark beer from Paulaner Brewery.
I have a free day on Wednesday and plan to go back through the town and take some photos.
You can see the rest of the photos from the museum and Munich on my photo site.
We left Leipzig in the morning for a bus ride to Munich. On the way, we stopped in Bayreuth, home of the Bayreuth Festspielhaus and the Richard Wagner Museum. Bayreuth has the charm of a small German Town. Most of the Christmas decorations were still up, as with Leipzig, so it was nice to see the town decorated.
Richard Wagner was born in Leipzig Germany but had his own opera house built in Bayreuth. This opera house has perfect acoustics and is still studied today on how that was accomplished. We were not allowed to take photos inside the opera house and within the museum, but I did have some time to walk about town and take photos outside of Wagner’s museum.
Most people don’t know about Wagner’s effect but all you have to do is watch a few clips from movies and cartoons to see his impact. Bugs Bunny’s Kill The Rabbit Opera was made possible by Wagner’s composition. The “Ride of the Valkyries” from Apocalypse Now has Wagner’s music in the background.
You can see the rest of the photos from Bayreuth under my photo site.
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.
