Voice of the DBA

Tag: administration

Create a SQL Azure Database–Old Portal

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

I worry about beta software, so when I wanted to create a new Azure database, I stuck with the old portal. Call me nervous, but the new portal worries me at times. It works, but since the default is the old one, I often use that one.

You can use either, but here I’ll show you how to create a database with the old (which will be gone someday) portal. It’s simple and easy, and this only takes a few minutes.

Creating a Database

Here’s the “old” portal. I wish it had a major version number, because that’s what it needs.

I’ve got a few things running here. In the lower left corner is a “New” button, which I can pick. When I do, I choose Data Services and get this.

I select SQL Database and then click “Custom.” You could click quick create, but I wanted to show more options. There aren’t a lot, but you can put in a few.

You get the settings page. The first thing you need is a database name. You can pick your subscription as well as the size of the system. Be aware of charges when you pick sizes. S0 is not cheap.

I had an existing server, so I used that, but the system will create one for you if you need it. This is really the name you connect to, so if you care, create one first and then use it here.

The next screen is really for the main admin account. Enter a decent password and save it. You can reset this form the portal once it’s up, so don’t be too concerned, but build good habits. Don’t use the same password everywhere and use a password manager so you pick strong ones.

Once that’s done, create the database. It will take a little time, but you’ll see a moving “Creating” item in the status. There’s no percentage, but I think it was about 3-4 minutes for me one afternoon.

Once it’s done, the database list will show you the database as online.

If you go to the dashboard, you’ll see a number of items listed.

Scrolling down, you might see the important item that you care about. How to connect. The server is listed, and if you’re using an app with ADO, PHP, OLEDB, etc, you can click the link and get the strings you will use.

That’s it. Simple, easy, and ready for you to use.

SQLNewBlogger

I was actually working on something in the new portal and wanted to go back to the old portal to see how things worked. As I waited for the main screen to refresh, I though, hey, write a post.

So I captured screen shots. It took maybe 10-15 minutes to type this up.

References

None used.

August 18, 2015
Gauges and Alarms

I was looking through the news this week and found a piece by Michael J. Swart that I really enjoyed. It talks about different types of metrics that he has used when monitoring and alerting on his database servers. Michaal talks about the difference between alarms and gauges, which are usually base on the time scale in which a DBA or administrator needs to react to a situation. Alarms are created when something grows in a short term and causes an issue. The gauges tend to be longer term items that eventually cause a problem.

I’ve had a similar view in the past. However I look at these types of metrics from the standpoint of how I tackle my job as a DBA. I also try to think about these issues as a developer, ensuring that telemetry helps track issues that occur over time and acute issues cause a graceful degredation (or exit) and not an unexpected one.

When I’ve needed to manage systems, I ensure that I have alarms set up. I need these for the situations where something goes wrong in a short period of time. However I never want alarms to be based on a lack of visibility into the system over time. I want to try and eliminate as many alarms as possible with better long term monitoring that looks for indicators that the system might have issues. I’ve found that many (not all) of the alarms that I receive could have been prevented if I’d been paying close attention.

In other words, I’m a proactive DBA, looking at gauges (using MIchael’s terms), not a reactive one counting on alarms.

I can’t predict everything, but when I find that looking back at data is helpful. If I find a trend that could have alerted me, I’ll evolve my monitoring system to try and track those potential issues in the future. I’ve written my own custom metrics in the past, which track data, either PerfMon type counters or business data, and then let me know when the growth, decline, or change is unexpected. In this way, I tend to find I can often proactively prevent issues. If I can use my data to “predict” an issue my boss doesn’t believe in, I’ve also found my budget for resources may increase a bit, allowing me to manage systems even better in the future.

Steve Jones

August 8, 2015
Start SQL Server in Single User Mode
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I saw someone recently ask how to reset the sa password, and myself (along with a few others) suggested starting SQL Server in single user mode as an administrator. The poster had problems and at one point I suggested using the command line, which I had used in the past. However that didn’t’ work, and when I tried it myself, it gave me errors.

The errors were Operating System errors, which indicated that the errorlog location wasn’t accessible by me. So I decided to elevate my privileges.

Once I did that, I was able to get the service to start:

I’m not sure what was hard here, and this is how I’ve always managed to start and stop SQL Server in single user mode from the command line. I then see the output of what is sent to the error log at the console.

However I also searched around and found a few other ways that are easy to accomplish.

Net Start

I haven’t often used net start for commands, but I have a few times. In this case, I looked at BOL and found I could do this:

This is essentially what clicking "start" in the services applet or Configuration Manager does. However I can add in my "m" parameter with a slash (/) instead of a dash.

To stop the service, I use NET STOP.

Configuration Manager

If you run Configuration Manager, you can also add parameters. First, right click the service and get the properties. Then you want the "Startup Parameters" tab. In there, you can add a parameter in the top box, as I’ve done here.

Clicking "Add" will put it in the list.

When you stop the service, the next startup will have this parameter take effect.

Beware that when you start things up, the first connection that successfully logs into SQL Server will be the only one allowed. Any applications looking to connect, monitoring programs (SQL Monitor or DLM Dashboard) or other clients can take your connection, so be careful and quick.

SQLNewBlogger

This was really a quick writeup. I stopped my service and played for 10 minutes, ran a few searches, and took some screenshots. I would have been faster, but I wanted to document this.

This is great practice for a skill you need rarely, but when you do, you’ll be stressed. Be sure you can start and stop SQL Server a few ways, and add parameters like trace flags and the -m for emergencies.

References
- BOL: How to: Start an Instance of SQL Server (net Commands) – SQL Server 2005-2008 R2
- Start, Stop, Pause, Resume, Restart the Database Engine, SQL Server Agent, or SQL Server Browser Service – SQL 2012 – SQL 2016
August 3, 2015
Using the DAC
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

The DAC is a really important tool for anyone managing a SQL Server. You might not use it often, but when you need it, you really need it. I’d suggest you connect to your instances one a month or so using the DAC, just to be sure you know how to do it.

The most common reason to use the DAC is when a runaway query is consuming so many resources the user can’t connect. The DAC is guaranteed resources, so you can always connect.

When you use SQLCMD, the -A switch lets the connection method know you want to use the DAC. The DAC only allows one connection, so only one admin can connect. This also means it’s important to disconnect quickly if you don’t need the DAC.

To connect, I use a normal set of parameters, -S for my server and -E for Windows auth. However I then add the -A, which connects me through the DAC.

Once I connect, I can run any of the normal commands, I need, like rebuilding master or killing a connection.

Keep this as a tool in your toolbox and practice regularly.

SQLNewBlogger

This was a quick post that follows on from starting SQL in single user mode as well as using the DAC in SSMS. I really just took some shots as I connected from SQLCMD.

References

The first link I saw was from my friend, Pinal Dave.
- sqlcmd – Using a DAC to Kill Currently Running Query
August 3, 2015