Battle of Leipzig 1813

On day 3 in Leipzig, the students had independent research so that enabled me to have a free day to do some photo walks around the city. The two faculty members on the trip, Matt Buckmaster and Andrea Sinn, accompanied me in the morning. I choose a monument erected in 1913 that commemorates Napoleon’s defeat at Leipzig in 1813. Völkerschlachtdenkmal (Monument to the Battle of the Nations) is a huge concrete structure with granite façade. The interesting part of the concrete foundation is no steel support was used. It is build entirely using compressed concrete pillars.

The walk to the top takes you through a crypt with 31-foot tall sculptures. There was a bit of light let in from stained glass windows which made for some great photography. There are about 500 total steps to the top but we decided to take the elevator up part way and walk down. The corridors for the stairs are very small a presented a problem in spots with my camera gear and 6’ 3” frame.

At the top, you get an amazing view of the entire town of Leipzig. The weather was a bit dreary but I was able to get a few pics of the surrounding town.

You can see the rest of the photos under my photo site.

guten Abend!

Across the pond

Guten Nachmittag, from Germany! I work at Elon University and they are known for their Study Abroad programs. These programs are designed to help students become global leaders in their field. As a staff member at Elon, one of the benefits is to apply and accompany one of 6 global education classes. I was lucky enough to have my name pulled from the hat and attend the Lives of the Great Composers. This is a 3 week trip to Germany, Austria, and Hungary.

Lunch time…

Our first stop is Leipzig, Germany. This is the resting place for Johann Sebastian Bach. We are on our second day in Leipzig and I love the feel of the inner city. All the streets have cobblestone and you are surrounded by buildings on all sides. In the main square of the inner city, they host a city market. It feels just like a farmer’s market but the food options are much more extravagant. I passed by rolling butcher shops and even someone with rotisserie meat.

St. Thoms Church

We took a tour of St. Thomas Church today where Bach is buried. The church has some beautiful architecture and the old organs sound amazing with the acoustics of the church. Tonight we are going to attend a concert in the church and I’m looking forward to hearing what the organs can do.

The Bach organ

You can see all my photos over on my SmugMug site. I’ll be keeping the site updated as we travel through other sites.

Guten Tag

Doug Purnell

Backup automation with MySQL

One of our vendor applications we host on-premises was moving from SQL Server to MySQL (don’t get me started). Our responsibility was to make sure the MySQL databases were being backed up on a daily basis. I didn’t know the first thing about supporting MySQL but was looking forward to learning a new RDBMS to see how things worked.

My first stop was Plural Sight, Pinal Dave had a few MySQL courses I wanted to review to start understanding what it takes to support MySQL. I watched the following courses:

  • MySQL Fundamentals
  • MySQL Backup and Recovery Fundamentals
  • MySQL Workbench Guided Tour

Pinal did a great job introducing me to MySQL and diving into all the areas I needed to understand how to support MySQL. Now that I had the basics, it was time to set up our MySQL backups. MySQL uses a command line tool to execute the backups, mysqldump.exe (insert joke here). MySQL backups are a bit different than SQL Server because the backup files are just text files with SQL statements to rebuild the database schema (DDL) and insert the data (DML).

I knew I could just create a simple Windows Tasks to run each night but I wanted something with some smarts. We have other RDBMS systems send us nightly emails with the status of the backup so we can review it during our morning checklist. My goals were to have the backup file have the date/time stamp within the filename, remove backups older than 30 days, and send an email with success/failure message. Time to hit the Google…

I found a great post with a sample Windows batch file that I started from. I forgot how powerful the Windows batch language was, you can do some pretty cool things (but PowerShell is much easier). I didn’t want to use ComMail from Microsoft, so I did another search for ways to send emails from Windows batch, and sure enough, PowerShell was the ticket. Looking back at this project, I should have started with PowerShell to build the backup script.

My next search was how to process return codes from the mysqldump.exe process. Now I had everything to build the process I needed. I took me a few hours of trial and error, but at the end of the day, we had a production-ready process. We have been running the scripts for a few weeks now and we have not had any issues. Remember, your backup is only as good as your restore.

Here is our batch file to manage the backups:

@echo off

c:
cd\
cd “Program Files\MySQL\bin”
 
For /f “tokens=2-4 delims=/ ” %%a in (‘date /t’) do (set dt=%%c-%%a-%%b)
For /f “tokens=1-4 delims=:.” %%a in (‘echo %time%’) do (set tm=%%a_%%b_%%c_%%d)
 
set bkupfilename=%dt% %tm%.sql
set logdir=E:\MySQLBackups
 
For /f “tokens=2-4 delims=/ ” %%a in (‘date /t’) do (set dt=%%c-%%a-%%b)
For /f “tokens=1-4 delims=:.” %%a in (‘echo %time%’) do (set tm=%%a:%%b:%%c:%%d)
set logtime=%dt% %tm%
 
echo Beginning MySQLDump Process > %logdir%\MySQLBackupLog.txt
echo Start Time = %logtime% >> %logdir%\MySQLBackupLog.txt
echo ————————— >> %logdir%\MySQLBackupLog.txt
echo Backing up to file: %bkupfilename% >> %logdir%\MySQLBackupLog.txt
 
mysqldump.exe –user=backup –password=backup –all-databases –single-transaction –flush-privileges >e:\mysqlbackups\”%bkupfilename%” 2>e:\mysqlbackups\DatabaseError.txt
 
if %errorlevel%==0 echo Backup success! >> %logdir%\MySQLBackupLog.txt
if %errorlevel%==0 del e:\mysqlbackups\DatabaseError.txt >> %logdir%\MySQLBackupLog.txt
if %errorlevel%==1 echo Backup warning, check DatabaseError.txt >> %logdir%\MySQLBackupLog.txt
if %errorlevel%==2 echo Backup error, check DatabaseError.txt >> %logdir%\MySQLBackupLog.txt
if %errorlevel%==3 echo Backup error, check DatabaseError.txt >> %logdir%\MySQLBackupLog.txt
if %errorlevel%==4 echo Backup error, check DatabaseError.txt >> %logdir%\MySQLBackupLog.txt
if %errorlevel%==5 echo Backup error, check DatabaseError.txt >> %logdir%\MySQLBackupLog.txt
if %errorlevel%==6 echo Backup error, check DatabaseError.txt >> %logdir%\MySQLBackupLog.txt
echo Errorlevel returned by mysqldump.exe %errorlevel% >> %logdir%\MySQLBackupLog.txt
 
For /f “tokens=2-4 delims=/ ” %%a in (‘date /t’) do (set dt=%%c-%%a-%%b)
For /f “tokens=1-4 delims=:.” %%a in (‘echo %time%’) do (set tm=%%a:%%b:%%c:%%d)
set logtime=%dt% %tm%
 
echo Done with MySQLDump >> %logdir%\MySQLBackupLog.txt
 
echo ————————— >> %logdir%\MySQLBackupLog.txt
echo MySQLDump Process Finished >> %logdir%\MySQLBackupLog.txt
echo End Time = %logtime% >> %logdir%\MySQLBackupLog.txt
echo ————————— >> %logdir%\MySQLBackupLog.txt
 
echo Deleting old backups >> %logdir%\MySQLBackupLog.txt
 
forfiles /p e:\mysqlbackups /s /m *.* /d -30 /c “cmd /c del @file : date >= 30days” >> %logdir%\MySQLBackupLog.txt
 
echo ————————— >> %logdir%\MySQLBackupLog.txt
dir %logdir%\*.* >> %logdir%\MySQLBackupLog.txt
echo Send email via PowerShell >> %logdir%\MySQLBackupLog.txt
 

powershell -executionpolicy Unrestricted -File “c:\software\SendEmail.ps1”

Here is our PowerShell file to send an email:

$smtp = “smtp.mysql.com
$from = “mysql@mysql.com
$attachment1=”e:\mysqlbackups\mysqlbackuplog.txt”
$attachment2=”e:\mysqlbackups\DatabaseError.txt”
 
if (Test-Path -path $attachment2)
{
    $subject = “MySQL Backup FAILED!!!!”
    $body = “MySQL Backup failed, see attached logs for errors
”    
    send-MailMessage -SmtpServer $smtp -To $to -From $from -Subject $subject -Body $body -BodyAsHtml -Attachments $attachment1, $attachment2
}
else
{
    $subject = “MySQL Backup Successful”
    $body = “MySQL Backup Succedded
    send-MailMessage -SmtpServer $smtp -To $to -From $from -Subject $subject -Body $body -BodyAsHtml -Attachments $attachment1

}

If you run into any MySQL DBA responsibilities, I hope this gives you a starting point for managing your backups.

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

Staying Focused

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

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

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

A little SQL Curry goes a long way

We hired a Director of Information Security a while back. One of the things that excited me the most about this new position was the ability to exchange ideas around security under SQL Server. Our initial discussion was over lunch at a local Indian restaurant, Taaza Bistro (w|t), and I found it very beneficial. We talked about our best practices and from that we agreed that there wasn’t any immediate need for us to alter our course (that was good news).

From that initial conversation, we continue to get together at the same restaurant every other month or so to talk about new ideas, active and future projects, or changes in the industry. The nice part about these lunches is the ability to bounce around ideas from a pure security standpoint. I leave each lunch with a better take on how to architect more secure solutions with SQL Server.

Our latest project is using a proxy server for all of our SSIS SFTP transmissions to vendors. The advantage of using proxies is they can lower your risk footprint by not have egress connections directly from your secure network. This allows you to place the proxy box under the DMZ for outbound connections.

The one thing I wanted to relay from this experience is how these lunches can be helpful for other databases administrators. It may not be with your Security Director, but it could be with the service desk, SAN/Domain admins, or any other department in your organization that you don’t normally speak to daily basis. It’s a great way to bounce ideas and understand pain points from other co-workers.

Here’s to plenty more SQL Curry lunches!

Doug Purnell

Accountability Partner

The #tsql2sday topic in December 2017 was around goal setting. I wrote about my trouble with keeping some of the goals I set. There were some really good posts if you want to look through the roundup for some inspiration. The issue I have with goals is I’m an obliger, always helping others before myself. The best outcome of the blog post was having Glenda Gable (b|t) reach out to me about being an accountability partner.

This has been the best thing for my goals this year. We talk every few weeks about how our goals are going and accomplishments since the last time we talked. Knowing there is going to be someone to hold me accountable for my goals is the type of motivation I need.

Blogging was one of our shared goals for 2018. Glenda had the great idea of reaching out to MVPs in the SQL Server Community each quarter for guidance. For the 1st quarter, we enlisted Tim Michell (b|t). We scheduled a call to talk about how he approaches blogging and some of the goals we were working on. This had two benefits, because of his consistent blogging we wanted to get some tips and tricks and it also served as someone else to hold us accountable.

The 1st quarter of 2018 was a success. I reached my blogging goals and am working on making it a habit as I move on to other goals for the year. I’m excited about the 2nd quarter and keeping my momentum going strong.

Doug Purnell

Weekly Routines

The key part to keeping my Getting Things Done (GTD) methodology up to date is the weekly review. Having all your projects and tasks under a single trusted system has its advantages, but you need to make sure and view everything so nothing slips through the cracks. The weekly review is a time where you give your projects a tune-up. This is a more in-depth exercise than the daily routine. At a high level, you review each project and make sure they each have a next action to keep them moving forward.

Reviewing the projects is just a single step in the weekly review. As you work through your tasks during the day it is often best to get out of the weeds and review everything from a higher level. This is the key thing for me as I try to manage operations, projects, and my yearly goals. In 2017 I spend most of my time in the weeds and lost track of the key goals for the year. Here are the items I perform during my weekly review:

  • Collect loose papers and materials
  • Get inbox to zero
  • Empty your head
  • Review previous calendar
  • Review upcoming calendar
  • Review waiting for list
  • Review agenda list
  • Review quarterly/monthly goals
  • Review projects for next action
  • Review someday maybe lists
  • Update The ONE thing

Let’s review each one in more detail:

Collect loose papers and materials
This is the heart of GTD, getting everything under your trusted system. Look around your desk, home, and anywhere else that you might have papers lying around. Now is the time to get them put into your trusted system.

Get inbox to zero
Review all your ‘inboxes’ for items that need to be brought into your trusted system. Inboxes might include post-it notes on your desk, your email inbox(es), or the pile of bills that need to be paid at home. As you review your inboxes, if the item is going to take less than 2-minutes to complete, go ahead and complete it.

Empty your head
Has there been anything on your mind taking your attention away from your priority tasks, if so now is the time to get that item into your trusted system as a project or task? Quick note, my definition of a project is two or more tasks related to each other. I have a generic bucket/project for all one-off tasks that don’t belong under an official project.

Review previous calendar
This is the time to review your previous week and decide if there are any items that need to be followed up on. This is your chance to play catch-up on any tasks that didn’t make it to your trusted system.

Review upcoming calendar
Looking forward to the week ahead, are there any events (business or personal) that require some preparation? Add a project/task so it can be accounted for as a priority for the week.

Review waiting for list
Do you have any tasks that are waiting for someone else? By keeping track of items that you are waiting for someone else to complete, this allows you to address them during a meeting or a hallway conversation. You may be responsible for the project as a whole, so it is a good idea to keep tabs on delegated tasks so they don’t get lost.

Review agenda list
I like to keep up with agenda items that need be addressed in project and department meetings. I use this list to keep them organized so the next time we meet together I have agenda items ready so I don’t forget them. Make sure each all the items are still relevant or take the time to add a few more things stuck in your head.

Review quarterly/monthly goals
Before we dive into each project on your list, do you have any quarterly or monthly goals that need to be accounted for during the upcoming week? This was my major fault in 2017, I spend most of the year in the weeds (operations) and didn’t take time to look at my goals and plan time around them. I like to take the yearly goals and plan our quarter and month goals to keep the yearly goals on track.

Review projects for next action
This the heart of the weekly review. This is where you review all projects on your plate. Whether they are on hold or almost complete, you need to spend time reviewing each action and decide if you need to plan for time in the upcoming week or add the next action if the project is stalled.

Review someday maybe lists
We all have projects that we would love to work on but are not a priority at this point in time. This is a great place to store all those projects and each week, decide if this is a good time to start working on them.

Update The ONE thing
The ONE Thing is a book written by Gary Keller and Jay Papasan. The main point of the book can be summed up by this quote, “What’s the ONE Thing you can do such that by doing it everything else will be easier or unnecessary?”. This is a powerful statement. Think through all the projects you just reviewed and decide if there is one thing that would make things easier. Create a task for this ‘one thing’ and act on it during the week.

As you can see there are quite a few steps to perform for a weekly review. Having a trusted system helps you track all these items. Whether you follow GTD or not, this is a great way to stay on top of your projects and manage your time effectively. I generally plan around 1 hour to perform my weekly review. If I have the time, Friday afternoon is my preferred time to complete my weekly view. This allows me to make a plan for the weekend and know that on Monday morning I can hit the ground running.

 

Doug Purnell

T-SQL Tuesday #101 – Essential SQL Server Tools

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.