Another take on SQL Server backups
Part of my job description requires that I perform sysadmin / DBA functions, one of which is database backups. SQL Server makes database backups easy by way of maintenance plans and such, but I was never quite satisfied with the arrangement. I wanted something more… automatic. Most of our projects are short lived applications which means I spend more time than the average DBA creating and dropping databases and, as a result, editing the backup jobs. To fix this, I created a couple of stored procedures:
Backup Databases
USE [master]
GO
/****** Object: StoredProcedure [dbo].[BackupDatabases] Script Date: 10/10/2009 14:08:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[BackupDatabases] as
declare @Name varchar(50); -- database name
declare @Model varchar(50); -- recovery model
declare @Path varchar(256); -- path for backup files
declare @DbFile varchar(256); -- filename for db backup
declare @LogFile varchar(256); -- filename for log backup
declare @FileDate varchar(20); -- used for file name
set @Path = '\\elser-web3\Backups\Elser-db2\'
set @FileDate = convert(varchar(20),getdate(),112)
declare db_cursor cursor for
select [name], [recovery_model_desc] from master.sys.databases
where name <> 'tempdb';
open db_cursor;
fetch next from db_cursor
into @Name, @Model;
while @@fetch_status = 0
begin
set @DbFile = @Path + @Name + '_' + @FileDate + '.bak';
set @LogFile = @Path + @Name + '_' + @FileDate + '.trn';
backup database @Name to disk = @DbFile;
if @Model = 'FULL'
begin
backup log @Name to disk = @LogFile;
end
dbcc shrinkdatabase (@Name, 10);
fetch next from db_cursor into @Name, @Model;
end
close db_cursor
deallocate db_cursor
Backup Transaction Logs:
USE [master]
GO
/****** Object: StoredProcedure [dbo].[BackupTxLogs] Script Date: 10/10/2009 14:11:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[BackupTxLogs] as
declare @Name varchar(50); -- database name
declare @Path varchar(256); -- path for backup files
declare @LogFile varchar(256); -- filename for log backup
declare @FileDate varchar(20); -- used for file name
set @Path = '\\elser-web3\Backups\Elser-db2\'
set @FileDate = convert(varchar(20),getdate(),112)
declare db_cursor cursor for
select [name] from master.sys.databases
where name <> 'tempdb' and recovery_model_desc = 'FULL'
open db_cursor;
fetch next from db_cursor
into @Name;
while @@fetch_status = 0
begin
set @LogFile = @Path + @Name + '_' + @FileDate + '.trn';
backup log @Name to disk = @LogFile;
fetch next from db_cursor into @Name;
end
close db_cursor
deallocate db_cursor
Open note to Discovery/History/Nat Geo channels…
ENOUGH with the “reality” TV.
Look, guys, I know everyone is doing it and it’s the hot thing these days. Sure, why not film a bunch of people already doing something, slap a narrative on top of it and call it a show. It’s cheap, ready made content, right? The problem is, it sucks. Not necessarily the first time, but pretty much every time thereafter.
Shows I liked
Deadliest Catch (Even the first time around, this was mediocre as a concept, but Mike Rowe narrating helped…)
Ice Road Truckers (Only because of the personality of Hugh Rowland.
Dirty Jobs (Ok, Mike Rowe is a legitimate gold mine. Top of the line dude and excellent TV personality)
Man Vs. Wild (Yeah, the accent did it for me)
Cash Cab (Interesting twist on the game show concept, and Ben Bailey’s an interesting personality, too)
How Its Made (Pure edutainment right here – can’t get enough of this stuff)
Digging for the Truth (Please, bring back Josh Bernstein)
Shows I never liked
Deadliest Catch: Lobstermen (WTF… really? who’s lame idea was it to try and capitalize on the original here? It sucks)
Extreme Logging (Dull profession, dull personalities, dull show. I’m not saying their job is easy, but it’s rather uninteresting as TV material)
Axe Men (A logging version of Ice Road Truckers? No thanks…)
UFO Hunters (This is quite possibly the worst show I’ve ever seen on the History Channel, and honestly, a disgrace to the name)
Detonators (Quit trying to cater to the belief that your audience is mostly young adult males who like explosions. That’s NOT the main reason for Mythbuster’s success, believe it or not).
Verminators (I can’t even bring myself to watch this load of crap)
Smash Lab (This was atrocious on top of atrocious)
One Way Out (Yeah, by turning off the TV)
And perhaps the worst crime against edutainment television…
Destroyed in Seconds (and all of its kin).
Look, taking a bunch of old videos, especially ones that have already been featured in a dozen highlight reel type shows, and slapping a narrative over them as if they are new and interesting is about the worst possible way to offend your viewers. You’re not even filming people at work now. You’re just force feeding us stale content from the basement archives and trying to convince us it’s interesting. Guess what… it’s not, and you suck for it.
IIS 6.0 and gzip compression
I just got done reviewing some sites for a client and found that gzip compression was not working although it had very explicitly been configured. The servers were running IIS 6.0 (Windows 2003) and the compression had been enabled through a standard script. The problem was that the script was setting the extensions to be compressed using spaces as delimiters, but the metabase expects this parameter’s values to be CR/LF delimited. It took me a while to figure out how to add CR/LF when setting a parameter using adsutil.vbs from the command line, so I’m posting the final updated script here in case it helps anyone else.
jsbandwidth
I’ve just released jsbandwidth 0.1 over at google code. It’s a pretty simple project, but useful enough, I think, to share with the world. It enables you to run a browser-based bandwidth test locally on a LAN or WAN to test internal network links, rather than your connection to the internet. I’m aware there are tools out there that perform this function, but not many are free and most all require some sort of installation on the server or client (or both).
Using jsbandwidth is pretty darn simple.
- Identify a web server you want to host the files on.
- Drop the files into a directory on the web server
- Access the default.htm page remotely from the client
For the latest version, head on over to the project page at http://code.google.com/p/jsbandwidth.
Low cost load balancing with Nginx and HAProxy on Linux
This post is more for future personal reference than anything, but maybe it will help someone else. It assumes nginx and haproxy running on the same front end box with nginx listening on port 80 and forwarding requests to haproxy on port 81. nginx could also be used as an SSL accelerator in this configuration, but the directives are not included in the example configuration shown here.
The basic idea is that nginx sits at the front doing rewrites, SSL decryption, etc… and then forwards the traffic to haproxy which distributes the traffic among the nodes on the backend. I’d love to have used just haproxy or just nginx, but nginx supports url rewriting and SSL accelerator features among others, while haproxy provides better load balancing algorithms and such. I haven’t had the chance to install yet in an environment that is good for benchmarking, but when I do, I’ll post the results here.
All servers were running default “server” installs of CentOS 5.3 for this configuration. The only dependencies I had to install were:
- gcc
- openssl-devel
- pcre-devel
Install HAProxy
Create or modify /etc/haproxy.cfg
Create or modify /etc/init.d/haproxy
Set haproxy to start on boot (and start now)
Install Nginx
Create or modify /usr/local/nginx/conf/nginx.conf
Create or modify /etc/init.d/nginx
Set nginx to start on boot (and start now)
If everything works, you should now be able to access your nginx box on port 80. This will proxy the requests through haproxy on port 81 to whatever back end nodes you have configured in haproxy.cfg and return the content. Spiffy, eh?
BitNami: Oh the wonderful things you do
Open source software is great. I use a lot of it. I contribute to some of it, and I overwhelmingly support its use whenever possible (and reasonable). That said, a lot of open source software today is linux based. This is only natural because there are so many variations of Linux designed to suit every user and they’re almost all entirely free to download, install, and use on a daily basis.
However, for someone who works in a Microsoft shop, getting your hands on this great software can sometimes be a pain. A lot of open source web apps were written in PHP and designed for Apache, but have you tried installing Apache and configuring a web app designed for the LAMP stack recently? While not incredibly difficult, it’s mildly annoying to the point that I wouldn’t wish it upon anyone frequently. Furthermore, the added dependencies of PHP, MySQL, and the headaches associated with the various version requirements can make installing these apps on Windows a real pain at times.
Enter BitNami.
This group takes popular open source packages like MediaWiki and Drupal and packages them with their required components (Apache, PHP, MySQL, etc..) into an easy to install distribution for multiple platforms. Just the other day I installed the MediaWiki app on our Windows development server and I have to say, it was a pleasure doing so. The entire process was streamlined and my new wiki was up and running in less than 5 minutes. I didn’t have to worry about the latest version of apache and whether the package I downloaded came with mod_php, nor did I have to worry about downloading PHP and enabling the MySQL support (two of the biggest hurdles I’ve had in the past). Everything installed right into my C:\Program Files\BitNami MediaWiki Stack folder just like I told it and functions pefectly.
I plan to experiment with some of the other packages BitNami has created, and I sincerely hope they (and encourage them to) continue to develop these packages for folks like me. BitNami, you rock!
Keeping your passwords safe and secure with KeePass
Since I started using KeePass a couple of years ago, I’ve marveled at the simplicity and security of the software. I appreciate the knowledge that if I ever forget a password (and let’s face it, I have several hundred to remember), I can always look it up in my “little black book” aka KeePass. The program works like this:
- Supply a master password
- Enter your usernames and passwords in the database
- Never forget a password again!
Each time you open a KeePass database, you are prompted for your master password, and your database cannot be unencrypted without it. If you choose, you can forego a master password (or supplement it) by using a key file instead. This key file can be securely tucked away on a USB thumb drive and attached to your key chain. No key file? No access.
Areas in ASP.NET MVC
It wasn’t long after I started using ASP.NET MVC that I realized I needed to be able to split functionality based on specific sections of my site (admin, user, etc…). I wanted a separate set of controllers, models, and views for each section, because each section would be working with the same data but in vastly different ways. A quick google search led me to posts by Phil Haack and Steve Sanderson, and the resulting AreaViewEngine class derived from their code worked well, with one major issue: performance.
As I was developing a site for a sweepstakes for a nationally syndicated TV show by a very famous TV/media personality, the site needed to perform incredibly well. Profiling of the application during development revealed serious problems with the VirtualPathProviderViewEngine as documented here at stackoverflow.com. It seemed no matter what code existed in my app, the highest consumer of CPU time was the FindPartialView method.
Some research led me to the question of view resolution caching documented here but the problem was, I was profiling in release mode. How could it be that the view resolution was being cached and still causing such a problem? I dug into the code (downloaded from CodePlex) and confirmed that the cache should have been enabled, but still, the performance problem persisted. I then spent 3 days drilling into the problem and came up with two improvements that almost nullified the impact of partial view resolution.
- If the view name passed to the view engine starts with “~/”, assume an absolute path and skip area view resolution altogether.
- Avoid the slower resolution of views by the VirtualPathProviderViewEngine and resolve it myself.
The resulting code is below. Testing on my local machine revealed that the enhancements noted above (and commented in the code) increased my requests per second from ~30 to ~110 (a 350% improvement)! On our production environment, testing a static page with no database access, this code allowed us to approach 2000 requests per second using a Zeus ZXTM LB (v5.1) with 4 Windows 2003 web server nodes.
2009/06/19: Alexander reported a missing slash in the path name formatting. Good catch! The code has been updated to reflect the change.
using System;
using System.Collections.Generic;
using System.IO;
using System.Web;
using System.Web.Routing;
using System.Web.Mvc;
namespace ElserInteractive.Framework.Web.Mvc
{
public class AreaViewEngine : WebFormViewEngine
{
public AreaViewEngine()
: base()
{
ViewLocationFormats = new[]
{
"~/{0}.aspx",
"~/{0}.ascx",
"~/Views/{1}/{0}.aspx",
"~/Views/{1}/{0}.ascx",
"~/Views/Shared/{0}.aspx",
"~/Views/Shared/{0}.ascx",
};
MasterLocationFormats = new[]
{
"~/{0}.master",
"~/Shared/{0}.master",
"~/Views/{1}/{0}.master",
"~/Views/Shared/{0}.master",
};
PartialViewLocationFormats = ViewLocationFormats;
base.ViewLocationCache = new DefaultViewLocationCache(TimeSpan.FromMinutes(30));
}
public override ViewEngineResult FindPartialView(ControllerContext controllerContext, string partialViewName, bool useCache)
{
string controller;
string areaPartialName;
ViewEngineResult result = null;
// Performance enhancement #1:
// Don't attempt to resolve absolute paths as area paths
// ========================================================
if (partialViewName.StartsWith("~"))
return base.FindPartialView(controllerContext, partialViewName, useCache);
if (controllerContext.RouteData.Values.ContainsKey("area"))
{
areaPartialName = FormatViewName(controllerContext, partialViewName, true);
result = base.FindPartialView(controllerContext, areaPartialName, useCache);
if (result != null && result.View != null)
return result;
areaPartialName = FormatSharedViewName(controllerContext, partialViewName, true);
result = base.FindPartialView(controllerContext, areaPartialName, useCache);
if (result != null && result.View != null)
return result;
}
// Performance enhancement #2:
// Resolve the view path internally, if possible. This avoids the
// slower method of view path resolution used by the ViewPathProviderViewEngine
// ========================================================
controller = controllerContext.RouteData.GetRequiredString("controller");
foreach (string fmt in base.ViewLocationFormats)
{
var path = string.Format(fmt, partialViewName, controller);
var path2 = controllerContext.HttpContext.Request.MapPath(path);
if (File.Exists(path2))
return base.FindPartialView(controllerContext, path, useCache);
}
return base.FindPartialView(controllerContext, partialViewName, useCache);
}
public override ViewEngineResult FindView(ControllerContext controllerContext, string viewName, string masterName, bool useCache)
{
string controller;
string areaViewName;
ViewEngineResult result = null;
// Performance enhancement #1:
// Don't attempt to resolve absolute paths as area paths
// ========================================================
if (viewName.StartsWith("~"))
return base.FindPartialView(controllerContext, viewName, useCache);
if (controllerContext.RouteData.Values.ContainsKey("area"))
{
areaViewName = FormatViewName(controllerContext, viewName, false);
result = base.FindView(controllerContext, areaViewName, masterName, useCache);
if (result != null && result.View != null)
return result;
areaViewName = FormatSharedViewName(controllerContext, viewName, false);
result = base.FindView(controllerContext, areaViewName, masterName, useCache);
if (result != null && result.View != null)
return result;
}
// Performance enhancement #2:
// Resolve the view path internally, if possible. This avoids the
// slower method of view path resolution used by the ViewPathProviderViewEngine
// ========================================================
controller = controllerContext.RouteData.GetRequiredString("controller");
foreach (string fmt in base.ViewLocationFormats)
{
var path = string.Format(fmt, viewName, controller);
var path2 = controllerContext.HttpContext.Request.MapPath(path);
if (File.Exists(path2))
return base.FindView(controllerContext, path, masterName, useCache);
}
return base.FindView(controllerContext, viewName, masterName, useCache);
}
private static string FormatViewName(ControllerContext controllerContext, string viewName, bool isPartial)
{
string controllerName = controllerContext.RouteData.GetRequiredString("controller");
string area = controllerContext.RouteData.Values["area"].ToString();
return "~/Areas/" + area + "/Views/" + controllerName + "/" + viewName + (isPartial ? ".ascx" : ".aspx");
}
private static string FormatSharedViewName(ControllerContext controllerContext, string viewName, bool isPartial)
{
string area = controllerContext.RouteData.Values["area"].ToString();
return "~/Areas/" + area + "/Views/Shared/" + viewName + (isPartial ? ".ascx" : ".aspx");
}
}
}
Attempt #8
I like blogs. I read them every day. I often muse about keeping my own and on occasion, I actually try. For one reason or another, though, my attempts at joining the blogosphere have generally ended in failure. I simply can’t keep up after the first few articles. Sometimes I lose interest, sometimes I get busy, and sometimes, I just plain run out of things to write. Blogging isn’t easy for everyone, you know.
With that in mind, I’m going to try this again. Throughout my work week I invariably reference several mainstream programming blogs and a few not-so-work related as well, and a well written article often leaves me wanting to contribute rather than merely consume. It’s not like I don’t have things to write about, either. My recent adoption of the ASP.NET MVC framework and Ling 2 SQL at work have led me into many dark places where documentation is scant and help is less than readily available. I’ve undertaken many experiments and gained much knowledge that should be shared. It wants to be shared. Let this be my attempt to share it (along with other random less relevant musings).
leave a comment