Voice of the DBA

Tag: sql server

Fixing logins
I ran into an issue recently where I couldn’t get a Windows login to work with some application software. This software had been configured with a user account, which appeared to be in SQL Server, but it couldn’t connect.

It turned out that the local name of the workstation had changed. In this case, I’d renamed a Windows host in a VM, and my login was automatically renamed in Windows, but not in SQL Server.

A quick fix. In this case, I needed to just alter the computername in the login with this code:
```
ALTER LOGIN [WIN2016\fileshareuser]
WITH NAME=[SJ-DEMOSITC1\fileshareuser]
```
Once I did this, everything started working again.

It’s rare that we rename items, but it does happen, especially in lab environments. It’s good to know how to remap any of those principals that might be affected if it happens to you.
January 10, 2018
It’s Time to Patch and Upgrade

I don’t want to be chicken little here, but the Meltdown/Spectre bugs have me concerned. I don’t know the scope of the vulnerabilities, as far as exploits go, but I do know the lax ways in which humans interact with machines, including running code, opening untrusted documents, and just making silly mistakes. No matter how careful you think you are, can you be sure everyone else in your organization is just as careful? Are you sure they won’t do something silly from a database server? Or do something from a server (or workstation) that has access to a database server? Or use a browser (yes, there’s an exploit)

PATCH your system, soon.

Vulernabilities in hardware are no joke, and even if you think you’re fairly safe, it’s silly to let this one go by and assume you won’t get hit. The advent of widely deployed scripting tools, botnets, and more mean that you never know what crazy mechanism might end up getting to your database server. Is it really worth allowing this when you can patch a system? This is a no brainer, a simple decision. Just schedule the patches. With all the news and media, I’m sure you can get some downtime approved in the next few weeks. After all, your management wouldn’t want to explain to their customers any data loss from this any more than you’d want to explain it to your boss.

We’ve got a page at SQLServerCentral that summarizes the links I’ve found for information, patches, etc. I’m sure things will change rapidly, and I’ll update the article as I get more information. The important things to note are that not all OSes have patches yet, and there are situations where you might not need to change anything. That’s good, as there are some preliminary reports of patches causing issues with performance (degrading it) for PostgreSQL And MongoDB systems. I did see this tweet about no effects on SQL Server, which is good, but YMMV.

Most of us know patching matters, and we need to do it periodically (even if it’s a pain), however, many of you are like me in that you rarely upgrade systems. Once they work, and because I have plenty of other tasks, I don’t look to necessarily upgrade a database platform for years. One downside to that is that a major vulnerability like the Meltdown/Spectre attacks is that patches likely won’t come out for old system and versions of SQL Server. That is the case here.

That means that if you’re on SQL 2005-, or even on older Windows OSes, you might really consider planning an upgrade. Even if you aren’t overly worried about this exploit, you won’t want a vulnerability to live for a long time in your environment. You never know when a firewall will change, server will move, or some malware will slip through (did I mention the browser exploit?). Plan on an upgrade. I’ve started asking about accelerating our upgrade plans, and you might think about that as well. I know management doesn’t want to spend money unneceesarily, but this feels necessary, and a good time to refresh your system to a supported version.

In general I like to delay my patches slightly from the world and not be on the bleeding edge. That’s fine, but don’t wait too long with this one. I would hope that most people get systems patched in the next month. If not, don’t expect any sympathy if you lose data.

Steve Jones

The Voice of the DBA Podcast

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

January 5, 2018
Restoring a Copy Only Backup–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

There was a question posted recently at SQLServerCentral about whether a copy only backup could be restore with a transaction log backup from a database. I was positive this could, but decided I needed to repro and test for someone as there wasn’t a good BOL reference.

The Tests

Here’s what I did. First, I created a table in a database. I often do this and drop in messages to allow me to track the progress of backups and restores. This post follows my progress.

The Backups

Here’s my basic script:
```
CREATE TABLE logger(msg VARCHAR(200), msgdate DATETIME DEFAULT GETDATE())

INSERT logger (msg) SELECT 'pre full backup'
```
Next, I made a backup and added a message.
```
INSERT logger (msg) SELECT 'pre-log backup 1'
BACKUP LOG nba TO disk = 'nba_1.trn'
INSERT logger (msg) SELECT 'log backup 1 complete'
```
Once this is done, I’m in a state that I expect. A normal full backup, a normal log backup, and some data to help me track where I am.

Now let’s make a copy only backup.
```
INSERT logger (msg) SELECT 'pre copy-only backup '
BACKUP DATABASE nba TO DISK  = 'nba_copy.bak' WITH COPY_ONLY
INSERT logger (msg) SELECT 'copy-only backup complete'
```
This now means I have an open log sequence in the first log backup (post full backup) and a few log records since then. Some of these are inside the copy only backup.

Now let’s add more data and make a new, regular, log backup.
```
INSERT logger (msg) SELECT 'pre-log backup 2'
BACKUP LOG nba TO disk = 'nba_2.trn'
INSERT logger (msg) SELECT 'log backup 2 complete'
```
It’s at this point that I have this sequence:
- Full backup
- Log backup
- Copy-Only Full backup
- Log backup
The Restore

What I want to test is can I restore the Copy-Only backup and a log backup? I think I can, so let’s do that. First, restore from the copy-only backup, using the MOVE option.
```
USE [master]
RESTORE DATABASE [NBA2] 
FROM  DISK = N'D:\SQLServerBackup\MSSQL13.SQL2016\MSSQL\Backup\nba_copy.bak' 
WITH  FILE = 1,  
      MOVE N'NBA' TO N'E:\SQLServer\MSSQL13.SQL2016\MSSQL\Data\NBA2.mdf',  
      MOVE N'NBA_log' TO N'E:\SQLServer\MSSQL13.SQL2016\MSSQL\Data\NBA2_log.ldf',  
      MOVE N'nba_mo_file1' TO N'E:\SQLServer\MSSQL13.SQL2016\MSSQL\Data\NBA2_mo',  
      MOVE N'nba_mo_file2' TO N'E:\SQLServer\MSSQL13.SQL2016\MSSQL\Data\NBA2_mo2'
,  NOUNLOAD,  STATS = 5
, NORECOVERY
```
Tip: Always use NORECOVERY

Now let’s try to restore the log.
```
RESTORE LOG NBA2 FROM DISK = 'nba_2.trn' WITH NORECOVERY

RESTORE DATABASE nba2 WITH RECOVERY
```
This works:

That should prove things. Let’s check the logger table.

That’s what we expect. The final message after log backup2 wasn’t captured in our backup files.

Copy Only Backups

What is a copy only backup? If we check the Copy-Only Backups page, we find that this is a regular backup in and of-itself, but it has the restriction that it cannot be used with differential backups. This also doesn’t change the differential bitmap, so that any differentials that are made ignore this backup and go back to include data changed since the last “normal” full backup.

SQLNewBlogger

Understanding backup and recovery is critical for a data professional. I’d say this is the most important skill, and it’s always worth writing about. Spend a few minutes reviewing scenarios and creating some posts like this to show you understand how the system works.
December 27, 2017
Not Useless Features

Some time ago I noticed Adam Machanic had written a post titled The SQL Hall of Shame. In it, Adam notes there are features that Microsoft will introduce in SQL Server and then let die. These are the useless features that aren’t widely used, or even useful for many projects. You can read his list, and agree or disagree.

I think there certainly are some features that were silly experiments and I wish they had been introduced as experimental, or beta, features early, allowing users to give feedback on whether they’re useful and where they need refinement. I certainly think too many resources were initially wasted on items like MDW and Query Notifications, and far too little investment later. These days I think we’ll see more early releases of items in Azure where users can experiment and provide feedback to help Microsoft decide if more investment is needed. At least, that’s my hope.

Today I’m wondering if you think there are features in SQL Server that do need more attention. Those features you use, but appear forgotten and you’d hope they are improved in the next version.

For me, I certainly wish replication would get more attention, tooling, and resilience. I’m not sure if this needs an overhaul, like SQL Server 2005 was, or there just needs to be some reworking of the code, but in today’s distributed world, we need a more reliable way to hook up portions of databases and move data around. Not ETL, not Availabilty Groups, a better replication architecture and implementation.

There might be other features you use or want, but let me know today. What’s a not useless feature that you want improved.

Steve Jones

The Voice of the DBA Podcast

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

December 1, 2017