Voice of the DBA

Tag: administration

Adjusting Model

In July the T-SQL Tuesday theme was defaults. There were lots of posts about various items, but I found one by Kevin Kline interesting. Kevin noted that the settings for model are not necessarily what many of us would want in production.

Is that really the case? This week I wanted to take a poll and see if many of you actually do create defaults for your new databases by changing model that are different than what Microsoft has done.

Do you change the model database?

Certainly there are some people that have create scripts with various settings for their databases. Some people prefer to customize the setup each time, based on the purpose of the database. Still others have scripts that alter the database to change settings after a someone has installed the instance.

There are any number of ways to ensure you have good settings for your production (or other) databases. Let us know this week if you have a step to change the model database when a new instance is installed.

Steve Jones

The Voice of the DBA Podcast

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

August 28, 2015
The Log Chain

I ran across a question recently from someone that received a similar message to the one showed in this image:

In this case, the log chain is broken. I’ve received this message, usually from a script that is automatically restoring all the logs in a folder. I’ve also had clients call me with this message, saying their log shipping system is broken.

What has happened is that you (or someone) is restoring the logs out of order. Each log backup forms part of a sequence that we call the log chain. The restore process requires that each log backup be restored in the same sequence in which is was created.

In the error message above, SQL Server is letting you know that the Log Sequence Number (LSN) contained in the file from which a restore was attempted doesn’t match the last LSN in the restored database.

Fixing This

The fix is easy; restore the correct files in the correct order. In this case, I knew I’d taken 3 log backups, and I was restoring in the incorrect order. I’d restored log backup 1, and was now trying to restore log backup 3.

If I restored log backup 2 (using with norecovery), the restore would work. I could then restore log backup 3 and eventually bring the database online.

If you can’t find the correct log backup, then your database is still usable. Just run RESTORE DATABASE WITH RECOVERY, and you can access your data. However the data will only be correct to the point in time of the last successful restore.

That could be a serious problem if you are missing a log backup early in your restore sequence. It could be a career limiting problem.

Always be sure you keep all log backups since the earliest full backup you keep handy for restores and protect those files. Losing one could be a major problem for your organization.

August 25, 2015
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