• Home
• About

Log Shipping Without Maintenance Plans

I have this crazy idea spinning around in my head–setting up log shipping without using a maintenance plan. It’s either stupid, crazy, or brilliant. It’s probably a combination of the three.

I’m working with a company that hosts a database application for clients. In this environment, we’re growing quickly. I need a DR solution that will scale incredibly well. To support scalability, I’ve banished maintenance plans. They’re great, but they don’t exactly scale well.

So I’m thinking of writing the log shipping into our transaction log backup process. Clearly, I need to test it, but I’m thinking it has the potential to work if written properly.

The concept is that I know the filename based on the transaction log backup I’ve just done, and by passing that into an xcopy command line, I can copy the log file to the DR server. The DR server will have a similar process running that will look for files that have been copied, get the correct data from the source and restore the log file.

Will this work? Maybe. But at this point, I’ve thought about it enough to know that I’ve got to try.

The PASS Summit Approaches

According to Delta Air Lines, my outbound flights from Boston to Seattle (via Atlanta) for the SQL PASS Summit depart in 16 days. And I’m really excited about this.

That’s all I have to say on the matter.

Cancel Your Plans

I have a confession to make. I’m not a fan of maintenance plans on SQL Server. Sure, they’re easy to set up and they work well in small environments. But what happens when you migrate from one server to the next? What happens when your company is rapidly growing and you find yourself adding new database servers every week? Perhaps you’re a nut for consistency like me and want to change transaction log backup retention across all your servers without having to go change it on all of your servers.

Here is the dirty little secret: Maintenance plans don’t scale very well.

I’m currently working on a presentation that I’m hoping to give at SQL Saturday 92 in Oregon. My belief is that anything you can do with a maintenance plan, I can do with T-SQL. By leveraging a MSX-TSX relationship with SQL Server Agents, this can scale far and fast.

This is going to be fun to put together. Stay tuned.

Speaking Epiphany

The more I think about it, the more I realize that presenting at SQL Saturday was good for me. You see, I was actually doing myself a disservice. Over the past two years, I had met some really amazing people who know tons about SQL Server. What’s wrong with that, you might ask? I was comparing my skills to people who have been working with SQL Server for years and/or have even written books on the product. I felt like I didn’t have much to offer the community.

Saturday, I realized that when I’m presenting on my topics, I know what I’m talking about and shouldn’t consider myself a second stringer. When I worked for Lotus Consulting, my mentor Carol once told me “There are many people who know more about this than you. Fortunately, none of them are in this room.” You see, when you share what you know with other people, there are more people who want to learn from your presentation or blog rather than dispute what you’re presenting. That was a huge epiphany for me.

One of the participants in my session asked me if I preferred to back up to disk first or use a backup agent for the backup software. Having been through that struggle in the past, I shared what I thought and mentioned that I had written a blog post about that in the past.  Another question I fielded was how I would go about getting developers away from touching the production environment.  Without missing a beat, my response was “Management buy-in.” I proceeded to say that without getting management to buy into this, it can’t happen. I also suggested hiding behind the auditors. Your auditors don’t want to see developers touching production. Leveraging that should help getting management to agree.

That was my epiphany. I was fielding questions as an experienced DBA. How did that happen? I know what I’m talking about.

At the after party, I was talking to a DBA who is doing some BI consulting. He told me that he’s not sure he knows enough to present a session at an event like this. That’s when I reminded him that he has customers willing to pay a lot of money for his experience. Who wouldn’t take that same advice for free? There are a lot of people who are really hungry to learn as much as they can and he shouldn’t be afriad to share it.  Many companies are stingy/cheap with their IT training budgets and local PASS events are a great way to get some free training.

Now it’s time to eat my own dogfood.

Karla Landrum (blog | twitter) from PASS says that local user groups are always looking for speakers and that we shouldn’t be afraid to volunteer to present to our local group. Yeah, it’s time to eat my own dogfood.

Thank you @SFSSUG #sqlsat79

Saturday was SQL Saturday 79 South Florida held just outside of Fort Lauderdale. It was an absolutely incredible day.

When I submitted to speak at the event, I got brave and submitted three sessions, thinking they’d accept one or two of them. I really had no expectation of giving three presentations in one day. Imagine my surprise when I saw that I had three sessions on the schedule. My goal for attending was to work on my SQL Server and presentation skills. My two “Top Ten” sessions were a pretty big hit, but the MSDB session wasn’t quite as well received. Perhaps it was because I was nervous, or perhaps it was because it will still early in the day. It didn’t suck, but it wasn’t my best work.

For my two “top ten” sessions, I had great participants and they really kept me energized. This is definitely something that I want to keep polished and present again in the future.

I want to thank Scott and everyone in the South Florida SQL Server User’s Group for having me. It was a really well-run event.

Top Ten Presentations #sqlsat79

Today is SQL Saturday 79 in South Florida! I have two “Top Ten” presentations I’m giving.

Here are my bullet points. And the slide deck is attached below.

Mike’s Top Ten Rules for Managing a Production Environment: Best practices that will keep you sane

• Developers don’t touch production. Ever.
• Limit Access to SA and Service Account Passwords
• Services run as a domain user
• Plan for when things go bump in the night
• Access is Controlled via Active Directory Groups
• Triggers don’t send mail. Ever.
• Database servers are that and that alone.
• Look at things through the eyes of an auditor
• Test Restores. Frequently.
• Optimize for OLTP and not Reporting
• Plus Bonus Rules…

Cool Things Your MSDB Database Will Tell You (If You Ask) #sqlsat79

Another presentation I’m giving at SQL Saturday 79 is Cool Things Your MSDB Database Will Tell You (If You Ask). This is a new presentation for me, and it’s different from my other two in that it’s less heavy on the Power Point and more heavy on the demonstrations.  This blog post contains the scripts I use in my presentation.

-- sysschedules contains a lot of great details. 
-- Check out the BOL page for more details
-- Take note that when I create a schedule, I always
-- make the name of the schedule correspond to the
-- actual schedule.
select sj.name, ss.name, ss.*
from msdb.dbo.sysjobs sj
inner join msdb.dbo.sysjobschedules sjs
on sj.job_id = sjs.job_id
inner join msdb.dbo.sysschedules ss
on sjs.schedule_id = ss.schedule_id
order by sj.name

select sjh.server,sj.name, sj.description, sjs.step_name, sjh.message,
sjh.run_status, sjh.run_date, sjh.run_time,
sjh.run_duration
from sysjobs sj
inner join sysjobsteps sjs
on sj.job_id = sjs.job_id
inner join sysjobhistory sjh
on sjs.job_id = sjh.job_id
and sjs.step_id = sjh.step_id
order by sjh.server, sj.name, sjs.step_name, run_date, run_time

--The code to convert run_date and run_time into an actual
--datetime is NOT my code, and I can't remember what blog post
--I found it from. But it's not mine.
select sj.name, sjs.step_name,
CONVERT ( DATETIME, RTRIM(run_date))+(run_time * 9+ run_time % 10000 * 6
       + run_time % 100 * 10) / 216e4
from msdb.dbo.sysjobs sj
inner join msdb.dbo.sysjobsteps sjs
on sj.job_id = sjs.job_id
inner join msdb.dbo.sysjobhistory sjh
on sjs.job_id = sjh.job_id
and sjs.step_id = sjh.step_id
where sjh.run_status = 0
and datediff (hour, (CONVERT ( DATETIME, RTRIM(run_date))+(run_time * 9+ run_time % 10000 * 6
       + run_time % 100 * 10) / 216e4), current_timestamp) <= 24

DECLARE @HoursToCheck INT
SELECT @HoursToCheck = 24
SELECT      sd.name, max(bs.backup_finish_date)
FROM        master.sys.databases sd
LEFT JOIN   msdb.dbo.backupset bs
ON          bs.database_name = sd.name
where       ISNULL(type,'D') = 'D'
-- Replace D with L for TLog Backups
AND                     sd.name <> 'TEMPDB'
GROUP BY    sd.name
HAVING      max(backup_finish_date) IS NULL
OR          datediff(hour,max(backup_finish_date), getdate()) >= @HoursToCheck
order by    sd.name

select mp.name profile, mi.mailitem_id, mi.recipients, mi.copy_recipients, mi.blind_copy_recipients,
mi.subject, mi.body
from msdb.dbo.sysmail_mailitems mi
inner join msdb.dbo.sysmail_profile mp
on mi.profile_id = mp.profile_id

Things Your MSDB Database Can Tell You

I Blame Brent Ozar

Yes, I blame Brent Ozar.

A little more than two years ago, I started following a few people on Twitter who knew about SQL Server. I must have been looking for answers to some particular problem. One of those people was Brent Ozar (twitter | blog). The other was Tom LaRock (twitter | blog). And I started reading their blogs religiously. And I learned a ton. One of the things I learned from both of them was that there is an entire community of SQL Server people out there who are willing to help younger DBAs develop their skills. And they don’t ask for much in return. I’ve tried to contribute what I can to that community.

In most major cities, there is a local user’s group of SQL Server users and they get together regularly to discuss particular topics (Free training!) In metro Boston, we have a great group run by Adam Machanic (twitter | blog). And that’s where I got to meet both Brent and Tom, who have spoken at our local group. And along the way over the past two years, everywhere I go, I have met people I learn from. It’s not because they’re getting paid for it. It’s because they’re just as passionate about SQL Server as I am. And they love to talk about it just as much as I do.

There is so much to learn, and every time I hear someone speak at an event, I learn tons. That’s when I started asking myself a question: What can other people learn from me?  I’ve not been a DBA my entire career, and I haven’t been focused solely on SQL Server until very recently in my career. I’ve had the title of DBA for over five years, but I’ve continued to manage applications and servers, not just databases. A few months ago, I spoke at SQL Saturday Boston about my experience virtualizing SQL Server at my previous employer. I had a good story to tell, but I’m not the most knowledgable person on the subject, and there are certainly people who know more than me. But my audience gave me some great feedback.

Three weeks ago, I started a new contract position and it hit me. I’m working with and for two really good DBAs. My boss’ boss knows a ton about SQL Server, and she’s a brilliant woman. I was sitting in her office last week and we were talking about the environment. She was telling me about performance problems due to transaction logs having too many virtual log files. I have to admit that I had never heard of a VLF until then. She asked what I thought of the environment so far. That’s when I have an epiphany. I was telling her that the current process of creating a new maintenance plan on each new server for backups, transaction log backups, index maintenance, updating statistics, and the like weren’t going to scale. Sure, it works today with three production servers. But at ten, fifteen, or even twenty servers, that’s going to be incredibly difficult. You see, I do have something to bring to the table. Somehow, she trusted that I knew what I was talking about and said “Make it scale.” They’re planning on adding countless SQL Servers in the coming months and need every process to be repeatable and scalable. I’m working on replacing every maintenance plan with a SQL Agent job that has a script that can be repeated on every server.

I’ve worked in IT operations most of my career. I’m a nut for automating manual processes. If I have to do something more than twice, you’d better believe that I’m going to look for a way to automate that process so that it can either be done in less time or be done by a junior staff member, such as a help desk technician. And that’s the stuff I’m good at. Oh, and I’m a complete nut for consistency in my servers. I believe that’s the key to stability in an environment. We manage by rules and not exceptions.

For the past week, I’ve been reading up on master/target relationships with the SQL Server Agent. There isn’t a whole lot out there, and it seems perfect for the environment I’m managing. And this environment is just screaming for policy-based management. That’s something I have to learn.

Saturday, I’m speaking at SQL Saturday 79 in Ft. Lauderdale, Florida. I’m not a bad speaker and I want to get a lot better. That’s my opportunity to give back to the community, and this is what I have to teach other people.

Over the past two years, Brent has become a friend and mentor, and I take every opportunity I can to hear him speak or just to hang out with him. As I was looking for a new position, I had someone in the SQL Server community who could help me once again with some pretty good advice.

What’s next for me? From Three to Three Hundred  Servers: Making Your SQL Server Environment Scale. Look for that at the next SQL Saturday in Boston.

Look for me at #sqlsat79

On Saturday, August 13, I will be presenting at SQL Saturday 79 in South Florida. It sounds like this is going to be a pretty amazing event.

I will admit that I’m a little nervous. When I submitted three sessions, my expectation was that they’d accept one, maybe two. Little did I know that they would be accepting all three of my presentations. The good news is that I had two of my presentations ready to go. Only one of them needed to be started from scratch.

I will be giving the following presentations:

Top Ten Rules for Managing a Production Environment

Top Ten Operational Mistakes to Avoid

Cool Things MSDB Will Tell You (If you ask)

The two Top Ten presentations have been in the hopper for a while. The MSDB presentation is going to be the most technical. Your MSDB database stores a ton of really useful information, and I’m excited to share what I’ve learned.

Interview Questions

I’ve mentioned that I’m going to be moving on in my career. My company has started the search for my replacement. Just in case I’m not able to interview potential candidates for my job, I’ve prepared a list of interview questions for my boss. He’s not a database guy, so I thought I’d give him some things to look for.

When is it a good idea to enable auto shrink on a database?

When you don’t care about performance. Other than that, never. Auto shrink puts a lock on the entire database, fragments the hell out of your indexes, and you have no control over when it runs. On top of that, the next time you rebuild indexes, the file will just grow again.

What is the difference between a primary key and a clustered index?

These go hand-in-hand but are frequently mistaken as one and the same. A primary key will be used to as a unique identifier to define a single record in a database. The clustered index is used to define how the records are laid out in the data pages. You almost always create the clustered index on the primary key. Almost. But not always.

When would you disable auto growth on a data file?

When a data file has reached a certain size and you want to prevent it from getting bigger. In that case, you would add additional data files to the database and new data would flow into those files.

Another case, although unlikely, is when a database isn’t growing.

What you’re looking for is to make sure you don’t grow data files too large and that your files don’t outgrow available disk space.

Microsoft’s default for data file growth is 1 MB. When would you change this?

Always. Not changing this fragments your disks because your data files won’t be contiguous. This is a setting that DBAs hate. The problem is that nobody can come up with a better number. It always depends on the specific instance

Fizz Buzz

Using pseudo code, list all integers between 0 and 100. If the number is divisible by 3, replace the number with the word FIZZ. If it’s divisible by 5, replace the number with the word BUZZ. If the number is divisible by both 3 and 5, replace the number with FIZZBUZZ.

Refer to http://imranontech.com/2007/01/24/using-fizzbuzz-to-find-developers-who-grok-coding/.

The goal here is to see how the person thinks. There is no best correct answer. You’re looking more for analytical and creative thinking.

When would you use SIMPLE recovery mode on a database?

Simple recovery is used when you don’t need to restore to a specific point in time. Simple recovery doesn’t back up transaction logs, so you only do full database backups.

This is useful for static databases and for databases where the data can be recreated with minimal effort. An example in our environment is the installbase on DBWarehouse. All of the data comes from other data sources, so transaction logging isn’t necessary.

Why would you use a GUID (goo-id) for a primary key? Why wouldn’t you?

A GUID is a string of 36 characters. It appears to be random, but SQL Server has a methodology for creating them. A GUID works well for a distributed application that will need to be consolidated later. This is exactly what Sharepoint does. For most applications, it’s a very bad design. Sequential integers almost always make a better primary key because the data will be laid out in data pages sequentially. By using a 36-character non-sequential primary key , this will cause page splits, and those are very expensive for performance.

If you don’t have the SA password, how else could you get administrative access to a database server?

Any other user with the sysadmin role.

I have an account that is a domain administrator that keeps getting Access Denied messages connecting to a SQL Server. Any idea what the problem could be?

With the introduction of SQL 2005, Microsoft removed the local administrators group from automatically being SQL system administrators. If you need your local administrators group to be SQL system administrators, you can do that at installation. Otherwise, you can create a login that corresponds to your AD Domain Admins group.

This is just my first few to start with. Any suggestions?