Posted on

How Do I Measure My DBA Skills Part 6

Development DBA II

With the last post, we began talking about the Development DBA and the skills needed at the beginning of this career path. Now it’s time for you to ponder what the next level looks like.

Development DBA II – 2 to 4 Years of Experience

  1. All competencies from level 1
  2. Develop basic to intermediately complex stored procedures, triggers, views and other database objects.
  3. Will have some experience with SSRS, SSIS, and/or SSAS development, deployment and possibly administration.
  4. May administer source control systems for the environment.
  5. Promotes SQL changes from Dev up through all environments, including production.
  6. Contributes to automation, particularly using native SQL Server methods, but could also leverage PowerShell or other languages for automating tasks (Python, C#, etc.)
  7. Designs pre-prod testing of SQL code prior to production upgrades or migrations. This includes performance and regression testing.
  8. Basic to mid-level understanding of execution plans, indexes, SQL Server statistics, and query tuning.
  9. Investigate basic to moderately complex data integrity/repair issues within SQL Server
  10. May begin leading in knowledge sharing in some capacity in one or more areas from level I or II.
  11. May take an active interest in leadership and in development of leadership skills, including emotional intelligence.

In years 2 through 4 the Dev DBA will work with more complex T-SQL such as recursive CTE’s, and error handling with TRY CATCH or the THROW syntax will likely be introduced at this stage. You may begin writing database views during this part of your career and leveraging them to make query writing a bit simpler for yourself and others. You are likely to be exposed to T-SQL Triggers and you’ll need to understand them and even maybe write a few triggers of your own.

You will likely start to get some sort of regular exposure to SSRS, SSIS or SSAS at this point. This will of course be based partly on your own interest in learning the tools of the trade and what is in use already at your employer.

Source control and the process of promoting changes from source control up into QA, and even production, may be something that begins at this level. After all, as a person who is writing a fair amount of code, you will have an interest in ways to manage code changes.

Automation

 

automation example

Let me ask you a question. Would you rather wash clothes manually with a washboard and tub or use a washing machine? It seems like a silly question. You would want to use a washing machine. You probably can’t imagine washing clothes without one.

In a similar thought process, there are things that you and others at your job will not want to do over and over in a manual way.  Accordingly, you will make more significant contributions to automation of tasks at this level in your career. SQL Agent will become your friend and learning to script out processes to make them easily repeatable, rather than doing everything in the SSMS GUI by hand, will occur more frequently. You may automate tasks with PowerShell for sure, especially using DBATools, but Python may also be an option. In case you didn’t know, Python does integrate with SQL Server via a specific driver.

Testing Code and Performance Tuning

An important part of the Dev DBA’s work is testing SQL code. You’re going to be doing more of that at this level in your career. You will need to verify query results and probably automate repeated testing of code as well so that you can test not only results but performance. Maybe the query returned the results you expected but it took too long to do it. This is where learning how to read execution plans will come in.  Execution plan operators will give you clues about what is happening with your T-SQL and those clues can help you with re-writes or with indexing.

Aaah yes. Indexes.  You’ll need to begin understanding indexes as well.  You’ll need to know what they are, the different types of indexes and when to use each type. Types of Indexes are as follows:

  1. Clustered indexes, which may be the result of creating a primary key. These may be clustered or non-clustered primary keys.
  2. Non-clustered row-store indexes.
  3. Non-clustered columnstore indexes.
  4. Clustered columnstore indexes.
  5. Filtered indexes

As a corollary, you’ll need to look into the concept of SQL Server statistics as well.

Next Steps to Take

  1. If you’ve read the other parts of this series, then you know I’m going to tell you to copy the skills list above to a Word doc and place an “X” next to the things you need to work on.
  2. Use those “X” marks to build a training plan. Put the training plan somewhere that you will see every day. One thing that should be on your training plan at this stage is learning more T-SQL.
  3. Work the plan. I have a post that will help you find resources for your learning plan.
  4. Contact me here for questions about this post or the skills listed in it. You can also reach out to me via Twitter using the handle @leemarkum.
Posted on

How Do I Measure My DBA Skills Part 5

Development DBA

We’ve been on a journey recently in this series as we discuss measuring your DBA skills. Previous posts have been intriguing to you. You like where this is going and yet, your skills are different from what we’ve been discussing in this initial part of the series.

Maybe you are a Developer who works with databases and you find SQL Server very interesting. You build some tables and write some queries as part of your work and you really like doing it. You like trying to figure out how to make your initial draft query for your project run faster. You have conversations about this with other Developers or maybe even a DBA or two at your company. What you hear resonates with you and deepens the interest in SQL Server. You wonder, “Is there a place for me in the SQL Server world? What would I do if I did a career pivot to work with SQL Server almost exclusively?” Enter the Development Database Administrator.

 

What Does a Development DBA Do?

Before we jump into a discussion of skills, we need to try to answer the question, “What does a Development DBA do?” After all, if you think you’re interested in this, what are you signing up for?

A Development DBA will often be responsible for the management of the non-prod SQL Servers, SQL development work for applications and processes, task automation and perhaps source control and the change deployment process. Non-prod SQL environments need care and maintenance. The Dev DBA role can assume this responsibility. This isn’t because the Dev DBA is a “Junior” role, but because this non-prod environment plays the critical role of testing grounds for development work. This non-prod environment will be where the Dev DBA works and experiments with new SQL Server development such as stored procedures, SSIS work SQL Agent job development etc.

This work will often be done in tandem with Developers from other areas of the company to produce new products and applications. The Dev DBA spends most of their time in non-prod so there is familiarity and as a result it makes sense to have the Dev DBA manage the environment.

Related to the experimental nature of the work, and the need to promote that work to production, the Dev DBA will often participate in or wholly manage source control and change processes such as source control software and the design of the process of promoting code through the various developmental levels of the environment. Once deployments are tested, scripts are then often handed off to Production DBAs for deployment of new code in production, but even this could be automated by the Dev DBA with control over when the deployment happens being wielded by the Production DBA.

At higher levels, the Dev DBA role may begin to overlap some with the Production DBA role and skillset. Let’s dive into the skills of a Development DBA.

The Development DBA Role

DEV DBA – 0 to 2 Years Experience

  1. Design tables utilizing basic normalization techniques.
  2. Basic T-SQL development. SELECT, INSERT, UPDATE, DELETE. Stored procedure development with guidance from more experienced DBAs.
  3. Manages most administrative aspects of non-prod environments with assistance from more experienced DBAs, including the application of patches to pre-production.
  4. May participate in pre-prod testing of SQL code prior to production upgrades or migrations.
  5. May have some exposure to SSIS, SSAS or SSRS.
  6. Demonstrates understanding of basic backup/restore processes.
  7. Demonstrates values driven behaviors such as humility, integrity, teamwork and is teachable.

T-SQL Skills

Now that you’ve looked over the list, let’s talk about it. T-SQL skills and overall development skills are at the heart of this role. The Dev DBA will spending a lot of time manipulating data and the first and foundational way that will happen will be with the core skills of SELECT, INSERT, UPDATE and DELETE. Introductory and basic T-SQL skills will be learned, practiced and built upon as you spend time in this role. A corollary to that will be learning all about the relational database table. At this phase of the career the Dev DBA will be learning what a good relational table should look like. Even if you can’t quote them, the principles of first, second and third normal form will become familiar as you design more and more tables.

Stored procedure development will be learned in this part of your career. You’ll be exposed to what a stored procedure is, why it should be used, advantages to it, the basic form of a stored procedure and so on. Of course, you’ll begin to write some of your own stored procedures as well. After all, there’s no point in learning about them, but not actually writing them!

Database Security

In this phase of your career you maybe be asked to make some logins and users as part of your development work. You’ll need to learn what those database objects are, how they are different and how they work together to provide authentication and permissions. You will, I hope, be doing this with the help of other DBAs who have walked this path before you so that you’re not scratching and clawing to figure it out on your own. My point is, other people will be guiding your work at this phase.

You will be testing code, a lot. After all, in this role you are creating things in non-prod that have to be reliable. You will be making sure that result sets make sense.

Depending upon the technologies in use at your company, you may be exposed to things beyond the T-SQL and basic security that we’ve already mentioned. SQL Server Integration Services (SSIS) may be  important at your work, and as a result, you will need to have some basic understanding of it. Maybe your employer has invested heavily in SQL Server Reporting Services (SSRS). In which case, you will be exposed to report development and deployment.

What Happens When I Lose Some Data?!

At this point in your career, you probably aren’t taking backups or restoring backups. But you should begin learning backup and restore concepts. You’re going to need that understanding as your career grows. Trust me!

You are going to make a mistake in a non-prod environment and wipe out a small, or large, amount of data. Please accept that this is going to happen. You’re going to make a mistake. You’re going to freak out and think, “I’m going to be fired!” I hope that you won’t be fired over it, especially since you’re not working in production. If you introduce me to a person who says that they’ve never messed up or lost any data, I will have met either a liar or a person who hasn’t worked with data nearly long enough!

When this happens, and it will happen, relax, and ask for help. If the other people on the DBA team have done their first and most important job, you’ll be able to get the data back. If they haven’t done their first and most important job, then maybe they are the ones who should be worried!

Do I Really Need People Skills?

Just like the other posts in the series, the last thing on the list is about people skills. Yes, you’re going to need them.  You have to interact with other people on your team, people on other teams and maybe even external customers. You need to be teachable, develop good listening skills and be willing to help other people. All of these things will help you lean your craft. You need the other people around you because they know things and have experience that you need. Remember that example above where you’ve deleted data you need to get back? If you don’t work on your people skills and have good relationships with co-workers, then that conversation where you have to ask for help is going to be reeeaaallllly awkward!

Particularly at this level of your career, other people can help you avoid pitfalls in your code as well as help you get your next promotion. So, be humble and willing to ask questions. When someone answers your question, don’t argue. If you don’t like the answer or you think the person is wrong, ask more questions to get clarification. Say something like, “I’m not sure I follow. Can you try explaining that another way?” Or you might say, “I hear what you’re saying. Would there be anything wrong with doing it like ‘X’? Is there a reason that wouldn’t work?”

Next Steps To Take

  1. Copy/paste the skills list into a Word doc and place an “X” next to anything you need to work on.
  2. Pick two or three things to focus on and build yourself a training plan. Put that training plan somewhere that you will see it every day.
  3. Ask a person on your team how to do a task that is important in your environment, but that you don’t know anything about yet.
  4. Talk to me. Contact me here if you would like specific help with anything in this post or other things related to SQL Server. If there is an issue with the form, you can reach me at leem@leemarkum.com. I will be glad to help.
Posted on

How Do I Measure My DBA Skills Part 4

Dear reader, we’ve been on a bit of a journey recently so that you can discover what skills are needed at various levels or phases of your career.  The first three phases have been covered in previous blog posts and you have now come to the Senior level for the Production DBA. So, how do you measure the skills a Senior Production DBA should have?

Senior Production DBA – 7+ years of experience

  1. Most competencies from the previous levels.
  2. Advanced analysis of SQL Server issues using native tools such as Extended Events, Perfmon, Query Store and 3rd party monitoring tools.
  3. Makes decisions regarding SQL Server architecture, hardware, virtualization and storage, often in tandem with an infrastructure team.
  4. Designs high availability and DR solutions and may provide guidance to other team members during implementation.
  5. Plans SQL Server migrations.
  6. Leads knowledge sharing in four or more areas.
  7. May possess some competencies from DEV DBA levels up to level 2 or 3.
  8. Regularly demonstrates good self-awareness, self-management, social awareness and relationship management skills (emotional intelligence)

You will notice that in previous lists of skills, I have listed skill 1 as “All competencies from the previous level.” However, I have not done that in this case, why? There are at least two reasons that I will detail.

Varying Experiences

You will notice that in previous posts I started listing skills and saying that the person “may” have a certain skill. Maybe you were asked to do a small project that you decided to use SSIS to complete, and SSIS was a new skill at that point. Afterward, you never really needed to use that again or it was used very infrequently. As a result, you have a little exposure but you’re not particularly skilled at it. Consequently, at years 7 and beyond where you are either a Senior DBA or you’re approaching that phase of your career, you may have some skills with SSIS, but maybe you don’t because your job roles never required that. In some roles, you maybe needed to do a fair amount of table design and stored procedure development. This might have been due to the fact that you were the only DBA so you had to do at least some development work. At other employers, those skills weren’t needed. Based on your employer’s needs, you may have used SSRS quite heavily up to this point in your career, but maybe not.

SQL Server Specialization

And then there is the matter of specialization.

The various parts of SQL Server are careers unto themselves. The database engine, SSIS, SSRS, SSAS are all so deep that a person could specialize in any of those areas. Even within some of those areas there are places of focus and skill that can be developed.

For a Production DBA, you will primarily be specializing in the database engine in some way. That could mean you’re really good with HA and DR technologies such as AlwaysOn Failover Clusters or Availability Groups. Maybe you love digging in to the various kinds of replication and leveraging those for business needs. Maybe you love performance tuning and you have found that the Query Store and Extended Events are things you love to use for that. You’re knowledge and skill with those things may be fairly deep as a result. Perhaps the SQL Server environments you have worked in have been heavily virtualized and you took a deep interest in that.

As a result,  if you have 7 + years as a DBA, you may have touched on a wide variety of things, but fell in love with a few select things that you’re now really good at.  This also means, there are things, you’re not that great at.  That’s ok. No one can do everything.

Designing SQL Server Solutions

So, let’s move on to discuss some of the other things in the list. At this point in your career, you’re dong things at a more advanced level, obviously, since this is a Senior phase to your career.

You are most likely collaborating frequently with other Infrastructure people like SysAdmins/VMWare specialists and storage folks at your company in order to design SQL Server solutions. Virtualization for SQL Server is another specialty I previously mentioned and you might be getting into some minutiae about that by this point.

You are the person who is analyzing HA/DR needs for when a new application is being brought on board from a vendor or from your own company’s Dev team. Decisions being made about how to ensure the application always has a back end connection, even when that river next to your building overflows its banks, are things you’re heavily involved in. You might be the person implementing the solution, or you may be simply assisting other people in your company with the implementation.

Knowledge Sharing

Knowledge sharing is definitely prevalent at this point. You regularly share what you know and what you’re learning.  That knowledge should be in several different areas at the Senior level and not just one or two. Your knowledge and skills may also include things of a more Development DBA nature. As previously discussed, this is heavily dependent on employer needs and your own interests.

Native SQL Server Tools

For a Senior Production DBA, native tools like Extended Events, Query Store and Perfmon counters are likely skills and knowledge that you’re comfortable with. You can use them easily in a wide variety of situations, but especially to analyze SQL Server issues that perhaps can’t be solved in other ways or can’t be solved as easily using other methods. Some places want their DBAs to be well-versed in native tools. Some are concerned so much with that because they have invested heavily in monitoring software of some kind.

SQL Server Monitoring Software

You should be comfortable and familiar with using monitoring tools at this point in your career. Platforms like SolarWinds have full environment monitoring like in Server and Application Monitor, that also include templates for monitoring SQL Server. SolarWinds also has Database Performance Analyzer. Sentry One has a full suite of monitoring tools as do Idera,  RedGate, and Quest. There are other options I’m sure.  These are just the one I know about right now that might be of use to you and that you are likely to encounter at your job.

Being able to use these types of platforms to find things like deadlocks, issues indicated by changes in Perfmon counters, and to do query analysis is going to be essential at this point in your career. Software like Brent Ozar’s First Responder Kit may also be something that you’re familiar with and can use easily to diagnose and resolve issues in your SQL Server environment.

Next Steps to Take

  1. Copy/paste the skills list into a Word doc and place an “X” next to any skill that you need to develop.
  2. Create a training plan to learn those skills.
  3. Buy a book on emotional intelligence and read it this month.
  4. Stay tuned because in the next part of the series we will introduce the skills for a Development DBA.
  5. If you would like help with anything in this post or other things related to SQL Server, reach out to me here and I’ll be happy to talk to you.
Posted on

How Do I Measure My DBA Skills Part 3

In this series on measuring your DBA skills, we’ve been discussing what skills you should have at each phase of your career.  So far we’ve covered the entry level skills and the phase 2 skills a Production DBA needs. In this part of the series you’re discovering what a Production DBA needs to know or be able to do at each stop along the career journey.  If you want career development, you need to work on these skills in order to move forward.

So what’s next after that second phase of career development for a Production DBA?  Once you’re into your career about 5 or so years, what should you be reaching toward?  I’m glad you asked! Let’s take a look at the skills in the next phase!

Production DBA III/Lead DBA: 5 – 7 years of Experience

  1. All competencies from the previous level.
  2. Designs backup/restore strategy.
  3. May participate in T-SQL development and database design of advanced complexity.
  4. Investigates potentially complex issues within SQL Server using native approaches, and when available and appropriate, 3rd party software, such as monitoring software.  Deploys changes based on that investigation.  These could be performance issues at the server level or query level.
  5. Implements high availability/DR solutions, often in tandem with infrastructure teams and may participate in the design of HA and DR solutions.
  6. Manages more complex troubleshooting scenarios for high availability and disaster recovery technologies (DB mirroring, replication, log shipping, potentially AlwaysOn Availability Groups or Failover Cluster Instances)
  7. Interacts with Systems Administrators or Infrastructure teams in matters of hardware, virtualization and storage, primarily concerning performance investigation.
  8. Regularly automates tasks using T-SQL and PowerShell.
  9. Demonstrates a basic proficiency with Extended Events.
  10. May lead SQL Server migration planning and implementation.
  11. Leads knowledge sharing in three or more areas from levels I, II, or III.
  12. Mentors Junior DBA’s.
  13. May possess competencies from the DEV DBA career path up to level 2.
  14. Demonstrates good self-awareness, self-management, social awareness and relationship management skills (emotional intelligence).

Working Independently

At this level in your career as a Production DBA your work is done in a fairly independent manner. You are capable of handling all the things from levels 1 and 2 and you’re continuing to advance your skill progression on things like design decisions for SQL Server. You’re doing more designing for things like backup/restore strategy, high availability, as well as participating in the planning of database and server migrations.

Adding More Advanced Skills

At this level you’re also adding skills with technologies like Extended Events and Query Store for troubleshooting and monitoring as well as learning how to use 3rd party software to find and troubleshoot issues in the environment.  You’re getting hands on experience with more advanced troubleshooting scenarios such as problems related to AlwaysOn Availability Group or Failover Cluster Instances, log shipping, etc.

Typically, you’re doing less and less entry level work, like troubleshooting and resolving problems like SQL Agent job failures. This could be in part because you’ve been at the company for awhile and you yourself have stabilized performance for things like SQL Agent jobs or you’re working at a place that has been taken care of by another DBA in a fairly good, consistent way.  Also, depending on the size of your environment and your employer you probably aren’t the only DBA at the company at this point so you may have teammates who carry at least some of the workload for phase 1 career tasks.

Sharing Knowledge as a Database Administrator

Knowledge sharing becomes more prominent in this part of your career.  This teaching aspect of your skills will be across several areas of SQL Server data management based on topics and skills in phases 1, 2, or 3. Perhaps you’re directly mentoring Junior DBAs on the team.  You may be giving presentations at work or maybe even your local SQL Server Users Group or a SQL Saturday on things like backup/restore functionality and design, how to automate various tasks, or maybe on something like Extended Events.

You’ll notice that there is some mention of development skills in this skills list.  Now, production DBAs typically don’t do much development. However, I realize that some employers don’t have a clean break between Production and Development responsibilities for their DBAs. Sometimes an employer needs or wants an overlap of skills. I also acknowledge that your own interests might mean that you decide to work on some development tasks like writing stored procedures for some application work that is being done or maybe working on table and database design considerations.

This begins to bleed over into a category of DBA that we will cover later in the series called the Dev DBA.  Again, your work environment may dictate the need for these skills or you may have an interest in being competent with some entry level Dev DBA skills. Either way, those skills may begin to show up in your career at this level.

Next Steps to Take

  1. Copy/paste the above numbered list to a Word doc.  Think carefully about each one and whether or not you currently meet this criteria.  Put an “X” next to any item you need to work on.
  2. For each item you placed an “X” next to, create a plan for improving skills related to that item. Are you going to watch Pluralsight videos, find resources on YouTube or read blogs about the topic?  Make some decisions and a plan about your approach. Also, at this career level and higher, you need to give yourself 30-60 days for each new skill you want to learn. You won’t be working on this skill for an actual 30 days, but with everything else you’re doing in your day job, it may take you a month or more to accumulate enough time with the skill for you to gain competency.
  3. If you would like help with anything in this post, or with something else related to SQL Server, reach out to me here  or on Twitter, and I’ll be glad to offer assistance.

 

Posted on

How-Do-I-Measure-My-DBA-Skills-Part-2

You may be wondering, “How can I know what phase of my career I am in? How can I see what the next steps might look like in terms of skills I may need to move forward?”

As people change jobs, the gears of career progression are turning.  Some new roles may be the same as the recent previous one. Some new roles will have larger spheres of influence and some may have better titles that require new skills. Like the gears in this picture, your job roles fit together and show a progression of sorts.

So if you’re a production  SQL Server Database Administrator, what does phase 2 look like in your career? You’ve survived those first couple of years and you would like to know what is ahead. You want to know the answer to the question, “What skills do I need to get to the next level?” Glad you asked.

Production DBA II – 2-4 years of experience

  1. All competencies from the previous level.
  2. Assist with triage of user issues, job failures and reactive tickets.
  3. May participate in the design of a backup/restore strategy.
  4. Improves existing processes for ongoing SQL Server management, such as configuration changes, in response to ongoing issues.
  5. Installation of new SQL Server instances without supervision.
  6. Looks critically at patch release notes to advise when a security update, cumulative update, or Service Pack is critical to apply and applies those patches to production.
  7. Demonstrates understanding of high availability and disaster recovery technologies and participates in troubleshooting related issues. (DB mirroring, replication, log shipping, potentially AlwaysOn AGs)
  8. Contribute to automation, particularly using T-SQL and PowerShell.
  9. Familiarity with Windows Performance Counters and how to leverage monitoring software to assess performance.
  10. May participate in T-SQL development of stored procedures, triggers, views, etc as well as database design.
  11. May write and troubleshoot basic SSIS packages and handle deployments for SSIS.
  12. Some familiarity with SSRS development and administration.
  13. Participates in SQL Server migrations with some guidance.
  14. May begin leading in knowledge sharing in some capacity in one or more areas from level I or II.
  15. May take an active interest in leadership and in development of leadership skills, including emotional intelligence.

In phase 2, skill and job functions that you were doing all the time, like handling initial triage of break/fix issues, may be things that you assist other people with rather than have the sole responsibility for yourself. Activities you were doing under guidance during phase 1, the first few years of your career, you will do now with less guidance because you’re better at it and people can see that you’re better at that particular thing. This might include something like installing and configuring SQL Server on a new instance. In phase 2 of your career you may still reference someone else’s guide for this process, but no one is going to be watching you while you do the work.

At this stage you also begin to be more of an independent contributor. You will start to independently recognize opportunities for and make changes in the SQL Server environment that will benefit performance or some other aspect of database management. Automation of work will be something you begin to make your own contributions in, whether that’s automating something with T-SQL, or a new skill showing up in this part of your career, like PowerShell, SSIS, or SSRS.

If you’re fortunate, in this part of your career the more Senior people will be leading a SQL Server migration project.  When you were in phase 1, you only vaguely knew this sort of thing was being worked on and you certainly weren’t working on the project with anyone.  Here in phase 2 of your career, you will likely be given at least some smaller tasks to do related to a larger task, like a SQL Server migration.

Participating in a migration project is great for your skill and career development  because it is usually at least a moderately complex operation to migrate a SQL Server.  This means more people are needed and more parts of the SQL Server management skill set are touched on during the work. A project like this also means more exposure to working directly with the more Senior people, which will get you noticed.

As you stay in phase 2, you will gain increased proficiency at tasks from phase 1 of your career.  This may lead to you occasionally teaching others what you know about those skills and those work activities. Some leadership skills and opportunities may begin to develop from teaching others what you know.  You have to demonstrate good people skills as you teach technical subjects and people may begin to think of you as something of a leader.

Next Steps to Take

  1. Copy/paste the above numbered list to a Word doc.  Think carefully about each one and whether or not you currently meet this criteria.  Put an “X” next to any item you need to work on.
  2. For each item you placed an “X” next to, create a plan for improving skills related to that item. If you’re not sure what Log Shipping is or you don’t know anything about how to use PowerShell with SQL Server, then open your favorite search engine and look around.  There will be people and tutorials that explain it. Maybe you know SSIS or SSRS are used at your company and you’ve always wanted learn the technology.  Perhaps your company has monitoring software for your SQL Server environment and you have been interested in learning how it works. Put these things on your training plan that you’re making in this step.
  3. If you would like help with anything in this post, or with something else related to SQL Server, reach out to me here or on Twitter, and I’ll be glad to offer assistance.
Posted on

How Do I Measure My DBA Skills Part – 1

 

Career development and progression is a hot topic for everyone.  It starts really early in life like when some adult asked you, “What do you want to be when you grow up?” You might have said “firefighter”, “ballerina”, “ice skater”, or “gymnast”.  I’m certain you didn’t say “DBA”, but here you are anyway, at my blog, trying to figure out a career as a Database Administrator.

So, what does career progression look like for a Database Administrator?  How do you move forward in your career with SQL Server? What skills should you have at year 2? Year 5? Year 10? This post will help you answer those questions.

There are all kinds of DBA’s who do all kinds of different work.  That was the challenge of coming up with the information I’m going to share with you over the next several posts. I chose to create two separate career path skill lists.  One for a production DBA and one for a Development DBA. The primary reason for this is that people typically become a DBA through one of two paths – Systems Administration and Developer.

Were you a System Administrator or Network Engineer when you got your start? You will probably become a Production DBA.  Did you get your start as a .Net or Java Developer? You’ll likely be more of a Development DBA. Different specialties within those general paths can develop from there.

What Does a Production DBA Do?

Before we jump into skills a Production DBA needs, let’s answer what a Production DBA does. As an oversimplification, a Production DBA keeps the lights on. She makes sure that break/fix items are resolved for customer facing processes. As a Production DBA, you will be resolving performance issues in the production environment and at least occasionally these issues will be live issues affecting a large number of people, either internal or external to the company. You will often be either designing or assisting with the design of systems, answering questions like :

  • How will this system interact with other systems?
  • Does interaction with other systems need to be real time or can it be asynchronous?
  • How will we make sure this system is always available?
  • What technologies will be used once the above decisions are made?

These, of course, aren’t the only questions you’ll need to answer as a Production DBA. There will be others like:

  • Why did this system go down?
  • How do we prevent this outage in the future?
  • Who granted the permissions that allowed this to happen?

Sounds exhilarating and terrifying all at the same time, right?!

On that note, here is the first level of skills for a production DBA. Look it over, ponder it and see what you think about the list.  Keep in mind, this is level 1, the entry level Production DBA skillset. I’m trying to answer the question, “What does a beginner Production SQL Server Database Administrator do and what skills should they have?”

Production DBA Level 1

  1. Production DBA – 0-2 years of experience
  2. Handles level 1 triage for things like SQL Server Agent job failures, user issues, and reactive tickets.
  3. Installs SQL Server instances following a process designed by others.
  4. Manages most administrative aspects of non-prod environments with assistance from more experienced DBAs, including the application of patches to pre-production.
  5. Carries out defined tasks like managing new users, running established audit processes, restoring databases to non-prod environments as required.
  6. Basic T-SQL development. SELECT, INSERT, UPDATE, DELETE
  7. May participate in SQL Server migrations in a guided manner.
  8. Demonstrates understanding of backup/restore processes.
  9. Demonstrates values driven behaviors such as humility, integrity, teamwork and is teachable.

A beginner production DBA should at some point be handling break/fix triage work.  This will often comes in the form of SQL Server Agent job failures and requests from users that often say little more than, “It’s broken” whatever “It” is.  At this level, you may have a more senior level DBA hand you a guide and say, “We need a SQL Server installed. Here are the instructions for that. Please have this done by noon.”  You need to understand enough to get through that task successfully.

At least some beginner DBA’s maybe be given one or more non-production environments that they are the primary DBA for.  This provides a mostly safe place for mistakes to be made and for learning to occur. The environment isn’t production so you aren’t going to break anything that is used by external customers.  Now, you might break something that the Developers are using, but that’s less impactful than breaking external customer technology. Managing a non-prod environment gives you a place to learn the other skills in this list.  Developers will need new Logins and Users created as they build new applications.  They will need data refreshed from production and so at this level, you’ll get experience with restoring backups and you will have the opportunity to grow you skills with T- SQL.

People Skills for Information Technology

Now about that #9 you see in the list –  “Demonstrates values driven behaviors such as humility, integrity, teamwork and is teachable.”

If you thought you would get a job in technology so you didn’t have to deal with people or work on your people skills, I’m hear to tell you that you’re mostly wrong.  Sure, you can be that person who hides in their cubicle and pushes off the “people” part of their job to someone else.  But, it is unlikely your career is going to progress all that far with that approach.  You’re going to need to know how to interact appropriately with your direct co-workers on your team, Developers, DBA’s, end users, your boss, vendors, etc.  So, if you suck with your people skills, get a good book, or two or three, and incorporate that knowledge into your life.  Click here for a book I recommend and look at the other suggested reading at that bottom of the page. A primary reason I really recommend the book by Travis Bradberry and Jean Graves is that it gives you concrete examples not only of high and low emotional intelligence, but specific actions you can take to improve your people skills.

Next Steps to Take

  1. Write down the list above, or copy paste to a Word doc.  Think carefully about each one and whether or not you currently meet this criteria.  Put an “X” next to any item you need to work on.
  2. For each item you placed an “X” on, create a plan for improving skills related to that item. The internet is your friend here. If you’re not sure how to create Logins and Users, then open your favorite search engine and look around.  There will be people and tutorials that explain it.
  3. If you would like help with anything on this post, or with something else you’ve seen on the site, reach out to me here or on Twitter and I’ll be glad to offer assistance.
Posted on

3 Ways to Restore SQL Server Databases

This is likely the last in a series of three posts about backups and restores.  My aim with this series has been to give you an idea of the options available and a basic start on using those options. I’m not trying to go in-depth on the subject of backups and restores.

Today will be about getting you going on the concept of restoring databases in SQL Server.  I’m using a local install of SQL Server 2017 on my desktop PC to demonstrate these techniques.

Obtaining the StackOverflow Database

Before I dive into demonstrating the restore options, I want to talk a little bit about the set up. I’m using the StackOverflow2010 database.  Anonymized data dumps of the StackOverflow database are available here. This particular version of the database I’m using came from a page on Brent Ozar’s site.

I then used the below PowerShell code to take backups.  Between the Full and Diff backup I added one user to the Users table.  Between the Diff and Log backup I add two more users.

backup-dbadatabase -SqlInstance MySQLServer2017InstanceName -Database 'StackOverflow2010' -Path 'C:\Program Files\Microsoft SQL Server\MSSQL14.KRONOS2017\MSSQL\Backup\StackOverflow2010' -Type Full -FilePath dbname-backuptype-timestamp.bak -ReplaceInName -CompressBackup -Verify

backup-dbadatabase -SqlInstance MySQLServer2017InstanceName -Database 'StackOverflow2010' -Path 'C:\Program Files\Microsoft SQL Server\MSSQL14.KRONOS2017\MSSQL\Backup\StackOverflow2010\' -Type DIFF -FilePath dbname-backuptype-timestamp.bak -ReplaceInName -CompressBackup -Verify

backup-dbadatabase -SqlInstance MySQLServer2017InstanceName -Database 'StackOverflow2010' -Path 'C:\Program Files\Microsoft SQL Server\MSSQL14.KRONOS2017\MSSQL\Backup\StackOverflow2010' -Type Log -FilePath dbname-backuptype-timestamp.trn -ReplaceInName -CompressBackup -Verify

Below is the T-SQL I used to insert some rows.  That value for Age though!  Ah, to be 25 again!

 

INSERT INTO dbo.Users ([AboutMe], [Age], [CreationDate], [DisplayName], [DownVotes], [EmailHash], [LastAccessDate], [Location], [Reputation], [UpVotes], [Views], [WebsiteUrl], [AccountId])
VALUES
('I make SQL Server more stable, highly available and easier to manage', 25, GETDATE()-7, 'Lee Markum', 0, NULL, GETDATE(), 'Saint Louis, MO', 0, 1000000, 2000000, 'LeeMarkum.com', NULL)

--Took a differential backup here so I would have some changes to restore

INSERT INTO dbo.Users ([AboutMe], [Age], [CreationDate], [DisplayName], [DownVotes], [EmailHash], [LastAccessDate], [Location], [Reputation], [UpVotes], [Views], [WebsiteUrl], [AccountId])
VALUES
('I make T-SQL solutions that just work', 25, GETDATE()-7, 'T-SQLGuru', 0, NULL, GETDATE(), 'Saint Louis, MO', 0, 1000000, 2000000, NULL, NULL),
('I make SSIS solutions that just work', 25, GETDATE()-7, 'SSISGuru', 0, NULL, GETDATE(), 'Saint Louis, MO', 0, 1000000, 2000000, NULL, NULL)

--Took a log backup here

Using SSMS to Restore a Database

Just like for creating a backup, SQL Server Management Studio has menu options for restoring a database. Open your copy of SQL Server Management Studio and connect to a non-production or lab environment of some kind. You’re going to right click the database you want to restore and use the fly out menu to navigate to Tasks > Restore > Database.

 

Selecting the Database option from the fly-out menu above brings you to the below page. Notice in the following screen that SQL Server finds the applicable backup files for you. No in-depth knowledge of restore operations is needed in this case because the work is done for you. It has found the full, differential and log backups that I took earlier.  Each row represents a backup file.

If you only want to restore to the time represented by the differential backup, then uncheck the box next to the log backup.  SQL Server will then restore the full and differential backup, but not the log backup. You might want to do something like this if you know that the data in the database was corrupted or changed in an unexpected or undesired way immediately after the differential backup.  As a result, you restore the database to the point in time represented by the differential in order to restore the database to its last known good condition.

In the Destination section, SSMS fills in the name of the database that you selected earlier for restoring.  However, if you give the database a different name here, then you can restore the database and compare the restored database to the current database.  You might want to do this in order to attempt to repair data in the current database by getting data from the restore ddatabase. Please note the yellow banner that informs you that this process will take a tail log backup.  This is a preventative measure.  It captures the last portion of the transaction log called the tail of the log.  It allows for complete recovery.  Without this backup, if you restore this database, you could lose transactions.

 

One other option I want to point out on this screen is what the “Timeline” button is for. When clicked, you’ll arrive at a screen like the below.  This allows you to choose a point in time to restore to based on the available backups.  This point in time restore is only available for databases in the full and bulk-logged recovery model. For bulk-logged recovery model, certain rules apply as to when this option would be available.

If you select the radio button next to “Specific date and time” then you can either specify a certain date and time in the Date and Time boxes or you can also use the slider icon along the bottom.  There is also a Time Interval drop down that controls what you see in the colored timeline above the slider icon. Using this option is the same as using the WITH STOPAT command using T-SQL for the restore. For my demo, I have not used this option, but I want you to be aware that it is available. This Timeline feature is new in SQL Server 2012.

 

Once you have the General page looking the way you need it, click on the Files selection on the left to bring you to the page below. Here you have one option to work with and that is whether you want to relocate the database files to another folder. Checking this option allows you not only to relocate the files, but rename them as well.  You might want to do that if you’re restoring the database to a different name so that you don’t have two databases with different names, but the same file names. That might make it more difficult later to figure out which files belong to which database without running a query to figure it out.

Should You Use the WITH REPLACE Option?

Now click on the Options selection on the left.  You arrive at the below page. The option “overwrite the existing database (WITH REPLACE)” will bypass certain checks that would normally occur during the restore process.  As a result, you really would rarely use this option, if ever. This option will do what it sounds like and replace the target database with the database in the backup file.  If you mistakenly point this restore to database on a different server that isn’t the same database that is in the backup and use this WITH REPLACE option, then you will overwrite the other database on the other server. There is a large section in this page about caution using the WITH REPLACE option.

https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql?view=sql-server-2017

Here is where the option for taking the Tail-log backup is located.  You should leave this checked and note the location for where that backup will be written.  If it isn’t the location you would like, change it in the Backup file box.

There is also an important option under “Server connections.” If you leave this unchecked and there are current connections to the database then the restore will fail.  A restore operation needs exclusive access to the database in order to perform a restore so other connections must be terminated first.  When this option is selected, SSMS will take care of that for you.

Once all the options you want are filled in, I would encourage you to click the Script option at the top so you can obtain the T-SQL equivalent for what SSMS is going to do. You can either click OK in the lower right of SSMS to execute the restore, or click Cancel and go to the T-SQL to review what will be done and then execute the T-SQL.

 

Now let’s turn our attention to using T-SQL for restoring a database.

Using T-SQL to Restore a Database

Below is the generated T-SQL for restoring the StackOverflow2010 database based on the available backup chain on my local SQL Server. I have added comments so you can better understand what each part is doing.  T-SQL syntax for performing a restore is, in most scenarios, not too difficult.  I am not showing things like file and filegroup restores or piecemeal restores.  These are more advanced scenarios.

USE [master]
/*This is the first part of the T-SQL generated when the "close existing connections" option is chosen in the GUI. This gives the session doing the restore the exclusive access needed.
*/
ALTER DATABASE [StackOverflow2010] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

--This is the tail-log backup
BACKUP LOG [StackOverflow2010] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.KRONOS2017\MSSQL\Backup\StackOverflow2010_LogBackup_2020-10-04_10-23-33.bak' WITH NOFORMAT, NOINIT,  NAME = N'StackOverflow2010_LogBackup_2020-10-04_10-23-33', NOSKIP, NOREWIND, NOUNLOAD,  NORECOVERY ,  STATS = 5

/*Here is the full backup.  Database restores need a place to start from and the full backup is that place.
*/
RESTORE DATABASE [StackOverflow2010] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.KRONOS2017\MSSQL\Backup\StackOverflow2010\StackOverflow2010-Full-202010030919.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5

--Here is the differential backup
RESTORE DATABASE [StackOverflow2010] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.KRONOS2017\MSSQL\Backup\StackOverflow2010\StackOverflow2010-Differential-202010030938.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5

--Her are the two log backups to restore
RESTORE LOG [StackOverflow2010] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.KRONOS2017\MSSQL\Backup\StackOverflow2010\StackOverflow2010-Log-202010030947.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [StackOverflow2010] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.KRONOS2017\MSSQL\Backup\StackOverflow2010_LogBackup_2020-10-04_09-20-23.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 5

/*This is the second part of the T-SQL generated when the "close existing connections" option is chosen in the GUI. This puts the database back to multi_user so it is ready to be used*/
ALTER DATABASE [StackOverflow2010] SET MULTI_USER

GO

 

Now let’s take a look at doing this with PowerShell

Using PowerShell to Restore a SQL Server Database

There are commands in the PowerShell on your local PC or server that will perform restores of SQL Server backups.  However, we will be discussing the PowerShell module called DBATools, which has to be installed before it can be used.

Please go to https://dbatools.io/download/ to see how to obtain and set up the module for use with this part of the blog post. I won’t be covering that here as the material is well documented on their site.

Once you have that, open PowerShell as an Administrator.  Type in the below code and hit F5.

Help Restore-dbadatabase -Detailed

This will give you a full description of the parameters and switches available in this command.  Numerous example code snippets will also be shown. From that help information I wrote the two examples that follow.  The first example will not perform the restore but instead writes to a .sql all the T-SQL needed to perform the command.  Strictly speaking, the -DatabaseName and value aren’t needed because the only backup files in that directory are for the StackOverflow2010 database. This option is useful so you can see what the command is going to do.

In the second example, the PowerShell will actually do the restore and then write it’s actions out to a text file. This is useful for having a history of what the command did.

In the third example, this will  use the folder structure from Ola Hallengren’s world famous maintenance solution. Notice the following switch:

-MaintenanceSolutionBackup

There are also switches for maxtransfersize, buffercount, and blocksize.  These can all be experimented with for your databases to find an optimal combination that restores the database as fast as possible. Additionally, there is a -RestoreTime option that is the equivalent of the WITH STOPAT syntax in T-SQL and the TimeLine function in the SSMS GUI.

<#This will not perform the restore, but will instead create a .sql file of the necessary T-SQL to do the requested restore.
#>
Restore-DbaDatabase -SqlInstance MYPC\Kronos2017 -DatabaseName StackOverflow2010 -Path 'C:\Program Files\Microsoft SQL Server\MSSQL14.KRONOS2017\MSSQL\Backup\StackOverflow2010' -WithReplace -OutputScriptOnly | Out-File 'C:\DBAToolsRestoreScripts\RestoreStackOverFlow2010.sql' 

<#This will do the restore and create a text file showing what the PowerShell command actually did
#>
Restore-DbaDatabase -SqlInstance MYPC\Kronos2017 -DatabaseName StackOverflow2010 -Path 'C:\Program Files\Microsoft SQL Server\MSSQL14.KRONOS2017\MSSQL\Backup\StackOverflow2010' -WithReplace | Out-File 'C:\DBAToolsRestoreScripts\RestoreActionsStackOverFlow2010.txt' 

<# This will do the restore based on Ola Hallengren's directory structure from his world famous maintenance solution.  Notice the switch -MaintenanceSolutionBackup
#>
Restore-DbaDatabase -SqlInstance MYPC\Kronos2017 -Path 'C:\Program Files\Microsoft SQL Server\MSSQL14.KRONOS2017\MSSQL\Backup\StackOverflow2010' -MaintenanceSolutionBackup -WithReplace -OutputScriptOnly | Out-File 'C:\DBAToolsRestoreScripts\RestoreStackOverFlow2010.sql'

Next Steps to Take

  1. Download and install DBATools.  It’s useful for a lot more than handling SQL Server restore scenarios!
  2. If you’re not familiar with PowerShell, you need to learn it, if for no other reason than the options that DBATools provides you for working with SQL Server.
  3. Connect to a test server and try out each method. Get some experience with these methods so you know which one you like the most and so you’re not trying to figure out how you want to perform a restore when you actually have to do it for real.
  4. If you would like help with anything in this post, or with something else related to SQL Server, reach out to me here, or on Twitter, and I’ll be glad to offer assistance.
Posted on

5 Ways to Make SQL Server Backups

Last time we discussed 5 backup types for SQL Server.  Now I want to introduce you to five ways to make backups in SQL Server. I can’t possibly explore all the options available with each of these six methods. There is a lot to cover for even a “simple” topic like backups.

Method 1: Using the SSMS GUI to Make a SQL Server Back up

 

For those readers who like the SSMS GUI, there is good news.  SQL Server Management Studio offers a fairly straightforward method for backing up a database. Once you connect to the SQL Server instance containing the database you want to back up, left click on the “+” sign next to the Databases folder.  Then right click the name of the database you want to backup, choose Tasks, then choose the Backup option in the fly out menu.

That brings you to the General Backup Menu page in SSMS.  In this menu you can access a number of settings related to the backup that will be generated.

With the “Backup type” drop down you can control whether this will take a Full, Differential or Log backup.

The “Backup component” section can be adjusted to take either a backup of files and filegroups or the default selection of “Database.”

In the Destination section you can backup to the default of “Disk” or choose “URL” from the drop down to make a backup to Azure as the target. When the destination of Disk is left you will be supplied with the destination of the backup and a filename for the backup.  This directory will be the default backup directory selected during the SQL Server installation.  If this is not where you want the backup, simply click “Remove” then click “Add” to navigate to the location you want to use.  The “Add” menu does accept share paths.

The Media Options selection on the “Select a Page” allows you to choose options for things like whether you want to append this backup to the same media set or start over with a new one.

The media options seem to me to be from an earlier day when backups were written to physical tapes.  Those tapes then had to be rotated from time to time.  It’s not a best practice to append backups to the media set because you’re adding backup files into a single media set.  If something happens to the media set and it is unusable, then the backups are not accessible.

In the Reliability section, you should check the options for “Verify backup when finished” and “Perform checksum before writing to media.” Thes options will cause your backup to take longer to complete, but they do help with validating the integrity of the backup at the time it was written.

The Backup Options are in the “Select a page” menu has what I think is one very important feature to note.

On this page there is an option related to backup compression.  Back in older versions of SQL Server, like 2005 and 2008 this was an Enterprise Edition only feature.  As of SQL Server 2008R2 it is available in Standard Edition.  To make using this compression the default for all of your backups, simply run the code below on your SQL Server.  Then, when you get to this option in the SSMS GUI, just leave it set to “Use the default server setting.”  You’ll want the space savings that compression offers.  Why use more space on your separate storage for backups than is necessary? I mean, you are storing your backups somewhere other than on the SQL Server, right?!

EXEC sys.sp_configure N'backup compression default', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO

Once you work through these meu options, simply click “Ok” and SQL Server will make the backup for you.  You can also click on the “Script” option at the top middle of the wizard to have SQL Server show you the T-SQL it is about to run.  You could save this as an example script to review later.

Method 2: Using T-SQL To Make a SQL Server Backup

T-SQL is a tried and true method for backing up databases.  You have more options available when backing up with T-SQL versus the GUI.  Most of those options are going to be more advanced options. A very basic example of the backup command that produces a full SQL Server backup is below. That is followed by examples for a differential backup and a log backup.

BACKUP DATABASE [MyDB] TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL14\MSSQL\Backup\MyDB_Full.bak'

BACKUP DATABASE [MyDB] TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL14\MSSQL\Backup\MyDB_Differntial.bak' WITH DIFFERENTIAL

BACKUP LOG [MyDB] TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL14\MSSQL\Backup\MyDB_log.trn'

Buffer Count and maxtransfersize are two options worth noting. You can experiment with these T-SQL options to take backups faster. The buffer count value conrols how many I/O buffers are used for processing the backup and the maxtransfersize controls how much data is moved at any one time.

Below I’ve provided three examples from my testing on my home lab PC. The initial buffercount and maxtransfersize data was captured by turning on trace flags 3605 and 3213 then looking in the error log after the first backup was taken.  After that I simply experimented with values.  Be aware that increasing the buffer count too much can cause an out of memory error.

As you can see, the initial throughput was 219.412 mb/sec and the elapsed time for that part was 39 seconds. This was using SQL Server defaults.

Increasing the buffer count to 8 increased the throughput to 258.653 mb/sec and the elapsed time dropped about 6 seconds. Combining the second change with a maxtransfersize of 4MB increased the throughput to 270.095 and pushed the time down another 1.4 seconds. I shaved 8 seconds off the backup time.  This was for a small database of about 14 GB.  For larger databases the increased throughput could represent a significant time savings.

BACKUP DATABASE [StackOverflow2010] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.KRONOS2017\MSSQL\Backup\StackOverflow2010\StackOverflow2010_backup_2020_09_20_180836_9111962.bak' WITH NOFORMAT, INIT,  NAME = N'StackOverflow2010-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10, CHECKSUM
GO
/*
buffercount = 4 and maxtransfersize = 1024kb
no options
Processed 1096456 pages for database 'StackOverflow2010', file 'StackOverflow2010' on file 1.
100 percent processed.
Processed 2 pages for database 'StackOverflow2010', file 'StackOverflow2010_log' on file 1.
BACKUP DATABASE successfully processed 1096458 pages in 39.041 seconds (219.412 MB/sec).
The backup set on file 1 is valid.
*/

BACKUP DATABASE [StackOverflow2010] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.KRONOS2017\MSSQL\Backup\StackOverflow2010\StackOverflow2010_backup_2020_09_20_180836_9111962.bak' WITH NOFORMAT, INIT,  NAME = N'StackOverflow2010-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10, CHECKSUM, BUFFERCOUNT = 8
/*
Buffer Count = 8
Processed 1096456 pages for database 'StackOverflow2010', file 'StackOverflow2010' on file 1.
100 percent processed.
Processed 2 pages for database 'StackOverflow2010', file 'StackOverflow2010_log' on file 1.
BACKUP DATABASE successfully processed 1096458 pages in 33.118 seconds (258.653 MB/sec).
The backup set on file 1 is valid.
*/

BACKUP DATABASE [StackOverflow2010] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.KRONOS2017\MSSQL\Backup\StackOverflow2010\StackOverflow2010_backup_2020_09_20_180836_9111962.bak' WITH NOFORMAT, INIT,  NAME = N'StackOverflow2010-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10, CHECKSUM, BUFFERCOUNT = 8, maxtransfersize = 4194304

/*
Buffercount = 8 and maxtransfersize = 4MB
Processed 1096456 pages for database 'StackOverflow2010', file 'StackOverflow2010' on file 1.
100 percent processed.
Processed 2 pages for database 'StackOverflow2010', file 'StackOverflow2010_log' on file 1.
BACKUP DATABASE successfully processed 1096458 pages in 31.715 seconds (270.095 MB/sec).
The backup set on file 1 is valid.
*/

 

Method 3: Using Powershell to Make a Backup

 

If you’re not using PowerShell with your SQL Servers, you should be.  If you’re not using the DBATools module with your SQL Servers, get it now. PowerShell can do fantastic, wonderful things and DBATools can do powerful, amazing things for you with respect to all things SQL Server. Below is a simple example of using the DBATools command Backup-DbaDatabase to make a full backup.  The command has a full range of options available, including backing up all the databases on a SQL Server when you don’t pass the -Database parameter. You should check into it right away.

Backup-DbaDatabase -SqlInstance MySQLServer -Database Stackoverflow2010 -path 'C:\Program Files\Microsoft SQL Server\MSSQL14\MSSQL\Backup'

 

Method 4: Using Maintenance Plans to Make a Backup

 

Because this post is already running long and there are a lot of other places that show how to use maintenance plans, I’m not going to step through how to do this here. You can go here and here to see how to use maintenance plans.  Also Jonathan Kehayias has a Pluralsight course on using them.

I am going to give you some quick thoughts on using maintenance plans though. First, Maintenance Plans are another GUI method to setting up backups.  In that respect, they are an easy point and click method to handle taking backups and handling backup retention, which we haven’t even talked about. Secondly, because of the nature of this method, which allows you to pick Backups as an option for a plan and then steps you through each part of a workflow wizard, Maintenance Plans can be a common approach for IT professionals coming from the SysAdmin side of things. No need to know or understand the options presented in the SSMS Backup wizard, for example.

Some downsides exist in backups inside maintenance plans prior to SQL Server 2016. Namely, the below options don’t exist.

– Perform checksum
– Continue on error
– Block size
– Max transfer size

Method 5: Using Ola Hallengren’s Maintenance Solution to Make A Backup

 

You can get help with Ola’s scripts here. Ola’s scripts are known and used world wide by DBAs.  They just work.  When you go to his page, you’ll download the file maintenancesolution.sql.  That file contains not only the backup routine but routines for CHECKDB and index maintenance.  You can download each components scripts individually as well. On his support page for the backup script, he explains all the available options and provides a laundry list of real world examples for how to set up the scripts for various scenarios.

Ola has options for the buffer count and maxtransfersize I demonstrated earlier.  He has options for using his scripts with AlwaysOn Availability Groups and he has options for backup retention. His scripts even work with third party vendor applications like LiteSpeed and Red Gate SQL Backup Pro.

What to Do Next:

  1. Review your current backup strategy and look for servers that aren’t being backed up at all.
  2. If you are a SysAdmin or an “Accidental DBA”, look at those links about setting up maintenance plans and get busy backing up your servers.
  3. If you didn’t know about DBATools, then stop what you’re doing and go to their web page.
Posted on

5 Types of SQL Server Backups

One of the foundational responsibilities of a DBA is making sure that backups are available.  SQL Server has several foundational types of backups that can be made. Before I dive into that though, I want to ask a question that seems to have a fairly obvious answer.

Why take backups?

  1. You want to be able to recover from data corruption or mistakes made when people make changes directly to the data – like when they write and run a script in SSMS, outside of an application that’s using a set of tested stored procedures.
  2. You want to be able to refresh you non-prod environments so developers have data that looks like prod  to develop against.
  3. You want to have backups so you can test whether you can restore and how long it will take
  4. You want backups as they are often an essential part of the migration process from one SQL Server to another – usually newer SQL Server, or to Azure.

SQL Server Full Backups

What does a full backup accomplish?  A full backup, creates a backup of the entire database. It is all-inclusive, containing the entire database structure and all the data.  When a full backup starts, the Log Sequence Number is recorded and when the full backup completes, the Log Sequence Number is recorded.  This Log Sequence Number is the mechanism used by SQL Server to know what order INSERTS, UPDATES or DELETES occurred in.  As such, having the beginning and ending Log Sequence number recorded as part of the full backup allows for a transactionally consistent backup because the full backup is aware of the changes that took place while the backup was being made.  This allows for recovery of those transactions during the restore process. This full backup serves as the base backup, or starting point for all differential and log backups after it.  Each time a new full backup is taken, a new base backup point is created.

SQL Server Differential Backups

What does a differential backup do for you?  A differential backup works off the concept of a differential bitmap.  SQL Server is composed of eight kilobyte pages. These pages are collected in groups of eight called “Extents.” There is a bitmap that is used to mark any Extents that have experienced data changes since the last full backup. When a differential backup is executed, it is this bitmap that determines what data is backed up.

Based on the size and rate of change in a database, differential backups are often going to complete faster than a full backup.  Additionally, the differential backups will be smaller in size since only changed extents are backed up, rather than the entire database.

When the full recovery model is used, the power of the differential can be very important.  The differential backup reduces the number of log backups that have to be restored. For example, if there is a full backup taken on Sunday night, and transaction logs every 15 minutes after that, when a DBA needs to restore the database to Thursday at 5:53 AM, she needs to construct a restore process based on the Full backup and every transaction log since Sunday evening!  That’s a lot of transaction logs to have to include.  With a daily differential occurring at 5 AM, the DBA now only needs to restore the full backup from Sunday evening, the one differential that was created at 5 AM on Thursday, and only the transaction log backups that occurred after 5 AM Thursday. Being able to skip over using all those log backups is powerful.

This makes for much simpler script creation and readability, and can save a lot of time if the DBA is trying to write this script manually.  You should have an automated process to create the backup script, but some DBAs don’t have that set up yet. We’ll talk about automating the backups script creation  later in the series.

SQL Server File and FileGroup Backups

What are File and FileGroup backups? Well to start with, this is a more advanced topic than these other backup types. As such, it is less likely that you will need this type of backup. However, knowing what your options are is always a good idea.

File and FileGroup backups allow a more granular backup, and by extension, a more granular restore process. It does what it sounds like.  A file or filegroup backup backs up an individual file or a collection of files contained in a filegroup. This allows for restoring a small part of a database to fix a problem rather than having to restore the entire database. This option, however, does introduce complexity and, in my opinion, is really only useful for large databases, particularly in the range of 500 GB or more.

These file and filegroup backup types can be used when a larger database has been divided into multiple files.  For example, a large database containing sales information might be divided into various Sales files, perhaps by year, or even by month if there are a high volume of sales recorded in the database.  These files are then aggregated into FileGroups. See below for an example of creating files and filegroups in a database. This example was taken from the  MS docs page here.

USE master;
GO
-- Create the database with the default data
-- filegroup, filestream filegroup and a log file. Specify the
-- growth increment and the max size for the
-- primary data file.
CREATE DATABASE MyDB
ON PRIMARY
  ( NAME='MyDB_Primary',
    FILENAME=
       'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB_Prm.mdf',
    SIZE=4MB,
    MAXSIZE=10MB,
    FILEGROWTH=1MB),

--Here is where a separate file is created and assigned to a FileGroup, which is little more than a container.
FILEGROUP MyDB_FG1
  ( NAME = 'MyDB_FG1_Dat1',
    FILENAME =
       'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB_FG1_1.ndf',
    SIZE = 1MB,
    MAXSIZE=10MB,
    FILEGROWTH=1MB),

--Here is where another separate file is created and assigned to a FileGroup.
  ( NAME = 'MyDB_FG1_Dat2',
    FILENAME =
       'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB_FG1_2.ndf',
    SIZE = 1MB,
    MAXSIZE=10MB,
    FILEGROWTH=1MB)
LOG ON
  ( NAME='MyDB_log',
    FILENAME =
       'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB.ldf',
    SIZE=1MB,
    MAXSIZE=10MB,
    FILEGROWTH=1MB);
GO
ALTER DATABASE MyDB 
  MODIFY FILEGROUP MyDB_FG1 DEFAULT;
GO

-- Create a table in the user-defined filegroup.
USE MyDB;
CREATE TABLE MyTable
  ( cola int PRIMARY KEY,
    colb char(8) )
ON MyDB_FG1;
GO

SQL Server Transaction Log Backups

What is a transaction log backup? A transaction log backup will take a backup of SQL Server’s transaction log so that data changes stored there can be recovered during the restore process.  Again the Log Sequence Number concept mentioned earlier is key.  Because transactions occur in a certain order, the log backups contain that order of transactions.  Transaction log backups must be restored in order.  If you try to restore a sequence of log backups in the wrong order, SQL Server will generate an error.

Having these log backups allows you to do what is called a point in time restore.  Having the option for a point in time restore can be critical for a business. Let’s suppose someone is making changes directly to one or more tables.  If a mistake is made and an unexpected data change occurs, point in time restore will allow you as the DBA to recover the data up to the moment just before the erroneous change was made.

SQL Server Copy Only Backups

What are Copy-only backups?  Copy only backups are backups that do not affect or track the Log Sequence Number information.  Why would you use this? Well, let’s suppose that you are already taking full, differential and log backups.  You are asked to provide a backup file of a database to another department or perhaps just a single developer who is working on a project. That Developer, hopefully, doesn’t have access to the backup directory for your backup processes.  Rather than you trying to copy a set of backups to a share or other location that the Developer does have access to, you could simply create a Copy Only full backup that is written to a shared location.  The Developer can then use that backup to restore locally to her machine, and you receive the benefit of not changing the base backup for your backup chain.

What to Do Next

  1. Examine your environment to see if you are leveraging the “Power of the Diff” by taking differential backups to help simplify your recovery process.
  2. Consider the size of your databases and determine if you have a situation that might benefit from having a database with multiple files and filegroups.
  3. If you would like help with anything in this post, or with something else related to SQL Server, reach out to me here, or on Twitter, and I’ll be glad to offer assistance.
Posted on

How Do You Find Good SQL Server Training Resources?

What process can you use to find SQL Server training that will be valuable? After all, not everything on the internet can or should be trusted.  If you want training on something in this constantly changing world of technology, how do you locate that training?  How do you find information that is worth spending your time on?  How much of it costs something?  How much of it is free and is the free stuff any good?

I live in the Microsoft world in general and specifically in the SQL Server universe so that is where I am going to focus.  But, to be clear, this process, and the methods I am going to talk about, will be relevant even if you are a C# Developer or some other kind of IT knowledge worker that has little or nothing to do with databases. I would be surprised if Linux and other open source software didn’t have some equivalencies to what I am going to discuss later on.

Let’s suppose you’ve been sitting next to the SQL Server for awhile and you’ve fixed a few things from time to time.  Congratulations!  You are now what is called an “Accidental DBA.”  You’ve fallen, quite by accident, into managing SQL Server in some capacity.  You’re sensing, though, that there’s a lot more that needs to happen and you have no idea where to start to find information.  After all, when you Google “SQL Server” you get back about a million hits and you don’t know who is a reliable source of information or what you should focus on first.

  • Microsoft MVPs

Have you heard of Microsoft’s Most Valuable Professional program?  If not, this is a good entry point for finding quality content. A Microsoft MVP is a person who has invested significant time into making the Microsoft community a better place.  They do this through three main avenues.  First MVPs may make significant contributions to coding projects.  Second, they certainly spend time helping others.  This usually comes from answering questions at places like MSDN, StackExchange.com or SQLServerCentral.com. Third, they create content.  This comes from doing things like blogging, writing articles, and publishing written materials.

So, what does this have to do with training? I’m glad you asked. Remember how we want to know how to sift through all the hits on the internet when you type in something like “SQL Server training”? You want to find the good stuff, right? Well, since you want to learn about something in the Microsoft world, why not find out who the MVPs are and learn from them?  Many of them have their own websites where they create content. Some of them will have special training that they have created on a variety of subjects.  The blog content is going to be free.  Usually you’ll need to fork over some money for the special training they have made.

Here is how to find Microsoft MVPs.  Go here and click the link at the top of the page for “Find an MVP.” For the SQL Server category, select Data Platform from the “Award Category” drop down. If you want to see MVPs in your area, hit the “Region” drop down and select your region to filter the results. From there, click on the name of an MVP and you will be directed to a profile page that will provide a summary of that person.

The summary page for an MVP will show where they have presented and what the presentation title was.  The profile summary will often have links to the person’s blogging locations as well, their Twitter account, and even books they have written. Want to see if someone you follow is an MVP?  Type the person’s name or part of their name into the Keyword box and click “Search”. Beyond the information on the MVP site for a particular person, sharpen your internet search skills and type in the person’s name.  See what you can find!

  • Paid Video Training

  • Pluralsight – In the interest of fairness in terms of the comparison to CBTNuggets, you should know that I’ve had a Pluralsight subscription of my own for years.  I have only in the last year or so learned about CBTNuggets.

On Pluralsight you will see courses from SQL Server people like Paul Randal, Kimberly Tripp, Erin Stellato and others from SQLSKills.com. People like Gail Shaw, Pinal Dave, and Mike MCKeown also have SQL Server courses on Pluralsight.  The platform has reasonably priced monthly and yearly plans. Personal plans on Pluralsight are $29 a month.  You can download exercise files that allow you to work through the content as you watch the presenter or later on your own. The platform also has skills test for a variety of subjects.

A couple of my favorite features  are called Paths and Channels.  Paths are pre-built collections of content that Pluralsight provides to you for learning a topic.  You can subscribe to Paths and you can combine a pre-built path with other content you’re interested in via the creation of a Channel.  Let’s say you’re interested in a group of courses that aren’t part of a path.  What do you do?  Well, don’t fret. That’s what Channels are for.  You can build you own custom path by adding courses to a Channel that you name.  You can also add a pre-built Path from Pluralsight to your custom channel.

Pluralsight also tracks weekly minutes spent watching videos so you can see how much time you’re spending on training and there is a history page so you can see what courses you’ve watched and how much of that course has been viewed.

  • CBTNuggets – At $59 per month, this platform is ore expensive than Plauralsight.  However, CBTNuggets has a few features that Pluralsight doesn’t.

The CBTNuggets platform offers virtual labs that can be spun up from directly inside a course. Additionally, there are IT Certification practice exams from Kaplan as part of the monthly subscription fee.

They also have accountability coaches.  These coaches help subscribers stay on track with their learning goals and they provide additional resources for learning the material as well as learning strategies.

The SQL Server  training on this platform is geared toward the Microsoft Certifications and are taught by people I am less familiar with.  Most of these certifications retire on Jan 31, 2021.  It doesn’t mean there is no value in taking these courses after that date, as I’m sure you would learn a lot from the courses, assuming CBTNuggets keeps them up after the certification expiration.  I certainly think these course could be helpful for someone trying to earn a Microsoft certification before Jan 31, 2021.

 

  • Free Video Learning

There is a growing body of great, free training on the internet.  When looking for this type of learning, search for people who are Microsoft MVP’s in the area you want to train for.

  • Search YouTube for MVPs or well known people who have advanced knowledge of a topic you’re interested in and subscribe to their channels.  YouTube also has playlists of pre-aggregated content on a particular topic.  You can search for playlists by topic like “SQL Server DBA Playlist” and find many playlists to save and watch later. Below shows a playlist for Wise Owl Training on SQL Server Reporting Services. Notice the icon in the lower left corner?  The three dashes and the + sign allow you to save this playlist to your YouTube page.  Then the playlist will appear on the main YouTube home screen on the left.

Here is a sample from my own YouTube playlists. I am not currently watching all of these, but they are playlists I definitely intend to watch.  When I need training on a topic, these playlists provide a handy way for me to find something quickly.

  • Find companies that offer free video training on topics of interest. For example, PragmaticWorks has free videos on a wide range of SQL Server topics. RedGate has a lot of free stuff on SQL Server and Dev-Ops for the database.
  • Find websites of individuals who blog about topics of interest. Besides the blog, some of those people are likely to make videos that are free to watch. This is true of Brent Ozar and Kendra Little, for example.

 

  • Free Online Conferences

Free online conferences are popping up like crazy now that in-person events are being shelved because of Covid.  More and more vendors are assembling virtual conferences, many of them free.

  • EightKB – This is a brand new SQL Server internals conference.  So far they have hosted one virtual event, and I attended.  The content was amazing.
  • Groupby.org – This was initially started by Brent Ozar but is now being headed up by Blythe Morrow.  This is event is held quarterly and has excellent speakers from around the world.
  • New Stars of Data – This is a brand new platform launched in 2020 that provides a place for up and coming data experts to speak.

 

  • Paid Conferences

  • DevIntersection – This is a yearly conference hosted in Las Vegas.  It is a broad conference, covering a wide variety of technology.  There is a SQL Server specific track called SQLIntersection. Early bird registration for this event runs anywhere from $1,700 to just over $3,000 depending on how many workshops you attend.
  • PASS Data Community Summit – This is now owned and ran by RedGate. Pre-cons are paid on 2021, but the rest of the conference is free.
  • SQLBits – This is a data platform conference focused on the SQL Server platform that is hosted in the UK.  Current prices and currency exchanges place the cost of a 2 day conference and 3 workshops at just over $,1000 US dollars.  It is a virtual conference this year.
  • DataGrillen – This in-person conference was cancelled for 2020.  It is hosted in Germany.

 

  • Blogs/Websites/People

  • Brent Ozar: Microsoft Certified Master for SQL Server who focuses on making SQL Server faster and more reliable (i.e. he does performance tuning)
  • Kendra Little – Microsoft Certified Master for SQL Server and now also blogs about DevOps for the Database for RedGate Software.
  • Erik Darling –
  • Paul Randal and friends – Paul Randal and Kimberly Tripp worked at Microsoft and are now married.  The site SQLSkills.com was founded by Kimberly and she and Paul run that site and company.
  • Itzik Ben-Gan: Itzik is a co-founder of SolidQ, a company that provides training on leveraging the T-SQL language to solve query problems.  If you want to learn T-SQL inside and out, look up this company and find resources from Itzik.
  • DBATools – Chrissy LeMaire and others have built numerous PowerShell commands into a powerful module that can be used to manage most aspects of SQL Server.
  • SQLServerCentral – I was first introduced to this site because of the forums where a lot of well-known SQL Server experts hang out and answer questions.  But, they also have a lot of training series articles on a wide variety of topics that they call their “Stairway Series”.
  • Microsoft Learn – This is Microsoft’s newest iteration of it’s free training platform. It provides a lot of information on the newest Microsoft certifications as well as free training paths to help users earn a certification. There are a number of data related paths, primarily connected to learning Azure.

 

  • Books

  • “Pro T-SQL 2019: Toward Speed, Scalability, and Standardization for SQL Server” Elizabeth Noble
  • “Beginning T-SQL: A Step-by-Step Approach” Kathi Kellenberger
  • “Murach’s SQL Server 2019 for Developers” Joel Murach, Bryan Syverson
  • “T-SQL Querying” Itzik Ben-Gan
  • “SQL Server 2017 Administration Inside Out” Randolph West and others
  • “SQL Server 2019 Administration Inside Out” Randolph West and others
  • “SQL Server Execution Plans” Grant Fritchey
  • “SQL Server 2019 Revealed” Bob Ward

 

  • Data Platform User Groups

With the unfortunate demise of the PASS organization, the SQL Server Users Groups have mostly converted to using Meetup as a platform. These groups follow the same format as before, they are just doing it remotely. If you want to find a User Group, click that first link, and then I’ve given you some links to a couple of groups I frequent.

Reader, we haven’t even talked about classes that might be held at a training center in your local area, or SQLSaturdays.  There is a wide range of possibilities for you to obtain training and help on the SQL Server platform.

  • What to do next

  1. Find at least one sub-point under each of the above main bullet points and investigate that item thoroughly.  Look up MVPs and start following one of their blogs. Investigate YouTube and save some playlists. Check out Microsoft Learn. Pick one thing from each main category above.
  2. Use those selected items to build a training plan for yourself.
  3. Put that training plan somewhere so that you will see it, everyday. Mine is on the white board behind me in my home office.