Voice of the DBA

Tag: administration

  • Handling Data Corruption

    I wrote about dealing with data loss recently, ansking what your plans are for a situation. Some of you might rely on digital systems, some rely on humans, and some just accept some data loss. All of those are valid responses, depending on your environment. Today I wanted to take another step on this journey.

    Imagine that one of your systems has data corruption. Any system you have, but pick one that might cause you some anxiety. Maybe you discover it from a failed query, maybe from an entry in the error log, maybe from a DBCC execution (I hope you use these). You have no idea how long the corruption has been there. Therefore, you don’t know how many backups are valid.

    What do you do? What are your plans? Certainly there could be data loss potential here. There will likely be some questions about why this wasn’t known immediately, and maybe you’ll experience some embarrassment in the moment. This will be a stressful moment in your career, and one with which you will want to be able to cope.

    The question today is to get you to prepare a bit and game the possibilities. Some of you might never experience corruption, but you never know, so it does make some sense to think in advance and anticipate the reaction you want to have in the moment.

    We always want to be prepared, but we won’t have actual preparation plans (scripts, documents, etc) for all situations. It’s much easier to think about the possibilities, rather that actually build plans for every possibility. Maybe you want to even discuss and debate them among your peers. Today’s question is good practice for the real situation, and great mental preparation for a real event. Plus it can be a bit fun to brainstorm, have someone shoot holes in your ideas, you do the same for them, and then come up with another solution.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.0MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • Adding Performance Counters back for SQL Server

    I had a strange situation the other day, where a number of things went wrong with my instance. First, I lost permissions to detached databases. The SID was listed in the file permissions, but apparently unlinked to an account.

    Next, I went to add an alert, and I only had the XTP counters.

    2017-06-14 12_16_20-SQLQuery4.sql - (local)_SQL2016.sandbox2 (PLATO_Steve (63))_ - Microsoft SQL Ser

    The counters are also missing in Performance Monitor. What is interesting is that I show the correct SQLAgent counters for each of my three instances.

    2017-06-16 08_54_19-Add Counters

    A quick search found me this blog on MSDN, where it recommends the following:

    unlodctr mssqlserver

lodctr perf-mssqlserversqlctr.ini

    I had a named instance, so for me I entered:

    unlodctr mssql$sql2016

lodctr perfMSSQL$SQL2016sqlctr.ini

    from an elevated command prompt. Running the last command again shows the counters loaded.

    2017-06-16 09_19_26-cmd (Admin)

    I also checked my registry, which appeared to be fine:

    2017-06-16 09_08_38-Adding Performance Counters back for SQL Server - Open Live Writer

    I next found another blog that noted I might need to resynch WMI, so I ran winmgmt, using the PID from Task Explorer (details tab):

    2017-06-16 09_24_32-cmd (Admin)

    I didn’t see counters at first, but I restarted the instance. Once that was done …

    2017-06-16 09_23_29-New Alert

    A nice fix, and one I probably won’t forget after this blog.

  • The Need for DRE

    “Today’s database professionals must be engineers, not administrators”

    That’s a quote from an interview at O’Reilly on Database Reliability Engineering. I don’t want to quibble about whether those of us working in technology are engineers in name or not, but in function, I do think the world is changing to one where we need to do more than be caretakers of systems. We need to be actively involved in ensuring we have very reliable, well-designed systems that use the best practices and patterns of highly available and reliable database systems.

    There is advice to learn and work closer with developers. I like that, and I do think the idea of learning to script and automate tasks is important, perhaps more important all the time, for data professionals. Some of the advice to look towards the quick moving, limited lifetime of many software components today doesn’t make sense. A database, at least some parts of a database, don’t fit within that model. There are valid reasons why a datastore must provide some stability and persistence for an application and won’t fit within a model of multiple systems. Or at least not practically within a model.

    However, there is good advice in the piece to learn more about different types of database services and patterns, and consider other ways of implementing datastores other than a RDBMS. I wouldn’t necessarily abandon a RDBMS for some NoSQL store just because developers think it’s easier, but I would consider whether I actually need to very tight coupling between different parts of the database and complete consistency. I think there are a fair number of domains where a CQRS pattern or some distributed store would work well. The move to microservices might be an enabler for your business if you consider the advantages for OLTP type transactions.

    The flip side of all the power that many stores provide is that many also make reporting and aggregating information more difficult. I would venture that in many cases, a data warehouse (perhaps even a RDBMS-based or columnar store) is necessary, along with the ETL process necessary to keep it up to date. These aren’t simple processes, and take resources to build. For most of us, because we don’t work at extreme scales, I’m not sure it’s worth leaving a relational platform, but I do think that we can learn to evolve and enhance our relational databases faster with DevOps ideas and techniques.

    Steve Jones

     

  • Renaming MDF/LDF Files–SQLNewBlogger

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    I would guess many people would run into this situation at some point. A developer or DBA creates a database, then decides to rename it, but the logical and physical names aren’t correct. This post will look at how to do this. A couple of notes and then the process below.

    This might not matter to many of you, but in development, I sometimes find I’ll rename a database and then attempt to recreate (or deploy) a new database with the old name. The mdf/ldf files don’t match, and I realize it’s because I’m using defaults.

    However, I’d also say this is an issue in a DR situation. If the filenames don’t seem to match, someone might restore the wrong database or the wrong files. Or worse, think the can delete a file on the file system because there’s no database with that name.

    Renaming the Database

    This is easy. Right click, select Rename.

    2017-06-07 09_38_49-

    Then type the name name. In this case, I’m going from WideWorldImporters-SSDT to WideWorldImporters-RR.

    2017-06-07 09_38_59-SQLQuery1.sql - (local)_SQL2016.msdb (PLATO_Steve (52)) - Microsoft SQL Server M

    That renames the database, but what about the files? If I run this:

    sp_helpdb ‘WideWorldImporters-RR’

    I get this:

    2017-06-07 09_42_00-SQLQuery1.sql - (local)_SQL2016.msdb (PLATO_Steve (53)) - Microsoft SQL Server M

    Not really what I want. I need these mdf/ldf files to be changed. How do I do this?

    I can get to the properties for the database and select the “Files” pane to get a list of files. Here I can change the logical name by clicking that field and typing a new name. I’ve done that here.

    2017-06-07 09_43_29-Database Properties - WideWorldImporters-RR

    However, if I scroll to the right to the File Name column, I can’t change anything.

    2017-06-07 09_43_44-SQLQuery1.sql - (local)_SQL2016.msdb (PLATO_Steve (53))_ - Microsoft SQL Server

    What I need to do is use the ALTER DATABASE command with the MODIFY FILE command. I need to do this twice.

    1. Change the physical file name
    2. Change the logical file name

    Let’s do that. Here’s the code to change the physical name.

    ALTER DATABASE [WideWorldImporters-RR]
 MODIFY FILE
 (   NAME = 'WideWorldImporters-SSDT_Data',
     FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\WideWorldImporters-RR.mdf'
 );

    I need to repeat this for the log file and the MOT file. Once I change the names, I get this message.

    2017-06-07 09_48_38-SQLQuery1.sql - (local)_SQL2016.WideWorldImporters-RR (PLATO_Steve (53))_ - Micr

    This is key. If I were to restart my system now, when the database attempted to start and go through recovery, the files would not have been found. Now, I need to change the physical file names.

    To do that, I first need to take the database offline.

    USE master
go
ALTER DATABASE [WideWorldImporters-RR] SET OFFLINE

    Then I go to the location of the physical files and rename them in Windows Explorer.

    2017-06-07 09_53_35-DATA

    Now I bring the database online.

    ALTER DATABASE [WideWorldImporters-RR] SET ONLINE

    Once that’s done, I can then use ALTER DATABASE again to change the logical file names.

    ALTER DATABASE [WideWorldImporters-RR]
  MODIFY FILE (NAME='USERDATA_612671E2',
               NEWNAME = 'WWI_UserData'
               );

     

    And run a final sp_helpdb.

    2017-06-07 09_54_13-SQLQuery1.sql - (local)_SQL2016.master (PLATO_Steve (53))_ - Microsoft SQL Serve

    SQLNewBlogger

    An easy task, with a touch of research in Books Online, but not too difficult. This took me about 10 minutes to do, and since I realized this was a good skill, I took screenshots and saved code as I went.

    Then about 10 minutes to write this up.