Voice of the DBA

Tag: administration

Disabling SQL Server Network Protocols

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

I ran across a question on network protocols recently, which is something I rarely deal with. Often the default setup for SQL Server is fine, but there are certainly times you should add or remove network connectivity according to your environment.

Here’s a short post on turning off (or on) a network protocol for SQL Sever.

What’s Enabled?

The easiest way to verify what’s enabled is to use the SQL Server Configuration Manager. You’ll need administrative permissions on the host to run this, but it’s easy to find.

Once you open it, typically you’ll have a list of the items that can be configured.

We want the SQL Server Network Configuration, which is the server level configuration for this host. The Client configurations are for the host being used a client to connect to a SQL Server.

As you can see here, I have Shared Memory and TCP/IP enabled for this instance, but Named Pipes disabled.

Disabling a Protocol

As you might expect, this is easy. I right click on a protocol, and I can change the status. In this case, I’ll disable Shared Memory

Once I do that, the protocol is disabled. However not on the instance. I’ll get this message.

I need to restart the server. Once that’s done, no one will be able to use Shared Memory on the host.

I can fix this

Of course, I need to restart my instance again.

Checking the Log

When SQL Server starts, quite a bit of configuration information is written into the log. This is useful for troubleshooting in many cases. One of the things you’ll find is the network listeners, as shown here.

This is usually after the database startup information, so if you look, you can see I have some local pipes and some TCP/IP settings here.

SQLNewBlogger

After reading a question, this was less than 10 minutes to write, with making screenshots. However I’ve done this before. If this was your first time, then it might take you longer to research and write, but I bet most DBAs could do this in 30-45 minutes.

January 18, 2016
Tracking Logins with Extended Events
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as#SQLNewBloggers.

I was building a question the other day on Extended Events and needed to build a new session. One of the easier sessions to build is with the logins, so I decided to use that, and then wrote this post on how to build the session.

The first thing is to to to the Management tab in SSMS and then the Extended Events folder. I right click the Sessions folder and select New Session.

This gives you a dialog. Like many in SSMS, we start with a name. You could select run at server startup, which I’d do in the case of this being a production system.

When I click next, I get to the "Events" tab, which lists all events. I’ll scroll down to login and select that. I need to click the arrow to the right.

Once I do that, my event is in the session.

After I pick the events, I choose the fields I’m going to capture. There is a "Configure" button in the upper right that you click. This scrolls the dialog over.

I can select any number of fields for capture. You can see I’ve picked the client_hostname. I would also add the NT_Username and Username from the list. You could add more, but in this case, I’m more concerned with seeing who’s logged in.

I could add filters, but I choose not to. I click on Data Storage to determine where to store this data.

For auditing, I might want a file. In this case, for testing, I’ll use the ring buffer, in memory storage.

That’s it for getting the session set up. However it’s not started. To do that, I need to right click the session and select Start.

This will allow the server to start collecting data. Is it working? Let’s see. We’ll watch the data. Right click the session again and select Watch Live Data

This pops open a window. I usually make this a separate vertical tab group. Once that’s open, I’ll click "New Query" in SSMS, which will log me in again

As you can see, a few events popped up here. I am capturing data. Don’t forget to stop the session after this if you don’t need it.

SQLNewBlogger

This post came out of work I was doing, and which I’d likely do as a DBA. However as soon as I got things working and tested, I knew this was a good post. In fact, I got a couple posts from the process. The setup and testing took about 20 minutes, including a little research. However the writing for this was about 10 minutes.

References

A few things I used.
- Using Extended Events to review SQL Server Failed Logins – http://www.sqlshack.com/using-extended-events-review-sql-server-failed-logins/
- Mapping Extended Events to SQL Trace – https://www.sqlskills.com/blogs/jonathan/an-xevent-a-day-20-of-31-mapping-extended-events-to-sql-trace/
December 16, 2015
The Writeable Warm Standby
I saw a question recently that went like this: I get one full backup from FTP. I’ll get daily log backups through FTP after this, but never another full. I need to restore this daily log backup and allow the group receiving the database to read/write the copy, and then reset it with the new log backup overnight.

First, this is untenable. At some point you’ll have some issue with transfer, lose a log, or the database will go corrupt. I can guanantee you that at some point you will need another full backup. Not every week, or even every month, but you will need one.

Second, this is a tough situation. I saw some answers, which I agreed with, but I started thinking about ways to get that data moved. My first thought it use STANDBY and move the data every day to a clean database. I’ve done this before, and in the GB range, even 100s of GB, this can work. It helps if you can whack indexes and constraints on the destination, but a copy of data table-by-table goes fast.

However then I thought about other ways. You can’t take a backup of a standby database, nor can you take a snapshot. However while searching, I saw an answer to this post on SO.

TL;DR: copy the mdf/ldf to a new database.

That was interesting, so I decided to test it. Turns out, it works pretty well. HOWEVER, it’s dangerous, and I think you should be very careful about this. I wouldn’t count on this being production stable, and certainly not data stable. You better have other copies of this data.

Here’s what I did. First, create a database with some data.
```
CREATE DATABASE mydb;
GO
USE mydb;
GO
CREATE TABLE mytable(id INT);
GO
INSERT mytable SELECT 1;
GO
```
Next, let’s back this up, take if offline, and then copy files.
```
USE master;
GO
BACKUP DATABASE mydb
 TO DISK = 'mydb.bak';
GO
```
Now we can copy the files to new files. There are UAC issues here, so you’ll need to add some rights to the files if you do this regularly. I left a comment in my script, but I actually did a CTRL+C,CTRL+V in the data folder, allowing the UAC permissions to work.

Once that was done, I had new files:

I tried to run at attach, but got a permissions error:

Msg 5120, Level 16, State 101, Line 30
Unable to open the physical file “D:\SQLServer\MSSQL11.MSSQLSERVER\MSSQL\DATA\mydb_reporting.mdf”. Operating system error 5: “5(Access is denied.)”.

The solution I found was to run SSMS as an administrator. Annoying, but it works.

At least for the OS error. However, then you get this:

Msg 1824, Level 16, State 1, Line 35 Cannot attach a database that was being restored.

You can’t do this. At least not easily.

You can do this. First, delete the files you copied over, then run this:
```
CREATE DATABASE mydb_reporting
go
alter database mydb_reporting set offline;
```
The next step is to delete the MDF and LDF files, which will be mydb_reporting.mdf and mydb_reporting_log.ldf by default. I could specify other names, and would if this were something I needed to script.

Once those files were deleted, I’d next copy my files again and rename them. That would result in this:
- mydb_reporting_base.mdf –> mydb_reporting.mdf
- mydb_reporting_base_log.mdf –> mydb_reporting_log.ldf
Now I can go back to SSMS. In SSMS, I do a simple ALTER.
```
ALTER DATABASE MYDB_Reporting SET ONLINE
```
Then I can run this:

I have a copy of my database. Can I apply logs and move forward? Let’s try. First, let’s add data and make a log backup
```
USE mydb
GO
INSERT mytable SELECT 99
GO

BACKUP LOG mydb TO DISK = 'mydblog.trn'   
GO
```
Next we restore again. We also set the databases offline again.
```
RESTORE LOG mydb_reporting_base
 FROM DISK = 'D:\SQLServer\MSSQL11.MSSQLSERVER\MSSQL\Backup\mydblog.trn'
 WITH  MOVE N'mydb' TO N'D:\SQLServer\MSSQL11.MSSQLSERVER\MSSQL\DATA\mydb_reporting_base.mdf'
    ,  MOVE N'mydb_log' TO N'D:\SQLServer\MSSQL11.MSSQLSERVER\MSSQL\DATA\mydb_reporting_base_log.ldf'
    , STANDBY = 'D:\SQLServer\MSSQL11.MSSQLSERVER\MSSQL\Backup\undo.log';
GO
USE master
GO
ALTER DATABASE mydb_reporting_base
 SET OFFLINE;
GO
ALTER DATABASE mydb_reporting SET OFFLINE
GO
```
Once again it’s file copy time. The UAC comes into play again as I copy and rename the _base files. However once that’s done, things work.

This works, but I am not recommending this as something you should do, especially for critical systems. This can work, but it’s dangerous, and really not supported by MS.
December 14, 2015
Virtual Failback

I saw a very interesting tweet from Scott Stauffer asking this: “… looking for quick’n’easy way to make workload virtual w/ fail-fast method to physical if performance doesn’t meet expectation.” This was part of a conversation Scott was having in looking to move to a virtual environment from his current physical one. That’s something I think more and more people are being asked to do, whether they do in inside of their own data center, or they move to some type of hosted or cloud environment. There is increasing pressure from management to consider using cloud-type environments to reduce the capital expenditures for new systems and move to an operating cost model.

I don’t have a fundamental problem with cloud environments, though I think it is important to carefully consider the pros and cons, but I can certainly appreciate Scott’s concern. No matter how well we architect things or prepare for the movement of a physical environment to a virtual one, there could be problems. Having a fall back plan becomes important, and even more important if we discover problems when some time has passed.

While there are utilities that can move a physical machine to a virtual environment, there aren’t any (or any I know of) to reverse the process. Honestly, though, I think virtualization has so many advantages, that if I really had performance issues and needed to return to a physical host, I’d continue to virtualize my instance, but I’d have only one VM on my physical host, with access to almost all the resources on the hardware. Today’s hypervisors have so little overhead, I wouldn’t hesitate to run one virtual machine on a host.

Ultimately, moving to a virtual environment is very much like moving to new hardware. There are definitely different configuration options you may need to set, but you can contract for some help with configuring your system. In the worst case, just use a single VM on a host, get hardware abstraction, and manage the machine like any other. Just don’t forget to have the hypervisor and your guest start up automatically after a reboot.

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.

November 3, 2015