Sysadmin Questions & Answers

How to exclude indexes from backups in SQL Server 2008

asked Apr 30 2009 06:49 on sysadmin

Our nightly full (and periodic differential) backups are becoming quite large, due mostly to the amount of indexes on our tables; roughly half the backup size is comprised of indexes.

We're using the Simple recovery model for our backups.

Is there any way, through using FileGroups or some other file-partitioning method, to exclude indexes from the backups?

It would be nice if this could be extended to full-text catalogs, as well.

Answers (4)

We've struggled with the RAID controller in our database server, a Lenovo ThinkServer RD120. It is a rebranded Adaptec that Lenovo / IBM dubs the ServeRAID 8k.

We have patched this ServeRAID 8k up to the very latest and greatest:

  • RAID bios version
  • RAID backplane bios version
  • Windows Server 2008 driver

This RAID controller has had multiple critical BIOS updates even in the short 4 month time we've owned it, and the change history is just.. well, scary.

We've tried both write-back and write-through strategies on the logical RAID drives. We still get intermittent I/O errors under heavy disk activity. They are not common, but serious when they happen, as they cause SQL Server 2008 I/O timeouts and sometimes failure of SQL connection pools.

We were at the end of our rope troubleshooting this problem. Short of hardcore stuff like replacing the entire server, or replacing the RAID hardware, we were getting desperate.

When I first got the server, I had a problem where drive bay #6 wasn't recognized. Switching out hard drives to a different brand, strangely, fixed this -- and updating the RAID BIOS (for the first of many times) fixed it permanently, so I was able to use the original "incompatible" drive in bay 6. On a hunch, I began to assume that the Western Digital SATA hard drives I chose were somehow incompatible with the ServeRAID 8k controller.

Buying 6 new hard drives was one of the cheaper options on the table, so I went for 6 Hitachi (aka IBM, aka Lenovo) hard drives under the theory that an IBM/Lenovo RAID controller is more likely to work with the drives it's typically sold with.

Looks like that hunch paid off -- we've been through three of our heaviest load days (mon,tue,wed) without a single I/O error of any kind. Prior to this we regularly had at least one I/O "event" in this time frame. It sure looks like switching brands of hard drive has fixed our intermittent RAID I/O problems!

While I understand that IBM/Lenovo probably tests their RAID controller exclusively with their own brand of hard drives, I'm disturbed that a RAID controller would have such subtle I/O problems with particular brands of hard drives.

So my question is, is this sort of SATA drive incompatibility common with RAID controllers? Are there some brands of drives that work better than others, or are "validated" against particular RAID controller? I had sort of assumed that all commodity SATA hard drives were alike and would work reasonably well in any given RAID controller (of sufficient quality).

Answers (8)

Best practices for keeping UNIX packages up to date?

asked Apr 30 2009 07:48 on sysadmin
  • How do you keep your servers up to date?
  • When using a package manager like Aptitude, do you keep an upgrade / install history, and if so, how do you do it?
  • When installing or upgrading packages on multiple servers, are there any ways to speed the process up as much as possible?

Answers (11)

Our database currently only has one FileGroup, PRIMARY, which contains roughly 8GB of data (table rows, indexes, full-text catalog).

When is a good time to split this into secondary data files? What are some criteria that I should be aware of?

Answers (3)

What is the best enterprise virus-scanning system?

asked Apr 30 2009 07:57 on sysadmin

What enterprise virus-scanning systems do you recommend?

Answers (9)

Best mixed environment (OS X + Windows) backup?

asked Apr 30 2009 07:58 on sysadmin

Maybe I'm chasing a pipe dream here, but I'd really like to find an easy to use backup solution that supports both Windows and OS X, presumably something people never have to think about until the worst happens.

Suggestions?

Answers (9)

[This was originally posted at stackoverflow.com but might be more suitable here and I'm sure it's a server configuration issue. Apologies if this is considered bad form.]

As part of our database revision control (and auto-installation) procedures we need to be able run sqlcmd.exe on various .sql files from within an ASP page. The code I'm using to do this is:

Dim cmd : cmd = "sqlcmd -S " & DATABASE_SERVER & " -U " & DATABASE_UID & " -P " & DATABASE_PWD & " -d " & DATABASE_NAME & " -i """ & scriptPath & """ -b"
Dim wshShell : Set wshShell = Server.CreateObject("WScript.Shell")
Dim return : return = wshShell.Run(cmd, 0, True)

I have the code working fine on two separate development machines. These are both running XP and required no additionally steps to get the code working. Having deployed the code to a Windows 2003 server it's having problems. The problem being that the value for return is always 1. This also happens if I try to get it to run a batch file or anything else I can think of (if I change the value for cmd to an non-existing file it bombs out as I'd expect)

I've tried adding I_USR and I_WAM to have execute permissions on both sqlcmd.exe and cmd.exe but it still returns 1. If I open a command prompt at the server and do a "runas /user:servername\i_usr sqlcmd.exe" that works fine but running from the ASP page still doesn't work.

Also, when running the .sql scripts manually everything runs smoothly so there's no problem with them.

Are there any security settings on the server that I've forgotten to change within IIS or Windows generally to make it work?

I have also tried this on another Server 2003 machine and am getting exactly the same problem.

Halp.

Answers (1)

(Soft) RAID 6 on Ubuntu 7.10, should I migrate to 8.10?

asked Apr 30 2009 08:00 on sysadmin

My System is a Ubuntu 7.10. I have a (soft) RAID 6 with 5 disks (Samsung 500GB SATA disks), formatted with jfs. Should I upgrade to 8.10 or isn't it worth the hassle? What are the chances that something could happen to the RAID configuration? I don't want to be in the position to rebuild the RAID from scratch.

Answers (2)

I have a website that a number of customers are using to host their sites. They each have their own domain name, but are using the same hosted service. Is it better to have them run off the same IIS website with all the host headers on that site, or should I create a different website & app pool for each domain that point at the same physical directory on the server?

Answers (2)

What's the top the reason you're unable - or unwilling - to upgrade to the latest available operating system verions?

Answers (21)

Open source applications for resizing drive partitions

asked Apr 30 2009 08:06 on sysadmin

I am looking for open source applications that will allow resizing hard drive partitions. I have used partition magic in the past but am looking for open source free applications that can handle this task.

Answers (1)

Originally my system had two SATA disk drives - I added an extra SATA disk drive to my system and when accessing this disk it sometimes take about 50 times longer to access the drive. (Windows Explorer can take about 2 mins to populate the basic directory and there is nothing special about the directory)

This doesn't happen all the time, but can't identify a pattern at the moment.

Any ideas what I've done wrong?

(Using Windows Vista Home Premium - PC is custom made - this is just on my development PC. What other useful info can I give?)

Answers (2)

Backup solution for 10TB of data and 3 servers

asked Apr 30 2009 08:06 on sysadmin

I am looking for a backup solution for this scenario:

  • 3 servers (One Linux mail server, 2 Windows Servers)
  • ~ 10TB of data
  • 2 TB of which are in daily use
  • a few GB of activity/change per day
  • ability to restore data from any point in time
  • backing up is performed on a live system (international office)
  • A MSSQL database needs backing up as well
  • There is no sufficient network connection to back data up off-site
  • Uncomplicated access (native file system) to the secured data.

At the moment the backing up of data is handled by carrying out the delta on portable disks to an off-site location. But the software is not completely reliable (something home cooked).

We pondered whether to purchase a tape library solution from Dell, but are not very convinced that tapes are the way to go for this relatively small amout of data.

How would you set up this kind of data backup? What software would you recommend?

Carrying disks with the delta to an off-site server to a mirror server is an option. What kind of hardware would you use?

Answers (3)

Does a clean install of PowerShell on its own have enough functionally to Admin a Windows domain environment and/or SQL Servers?

If not what are good, free or pay for, third party add-ons?

Answers (8)

What is your favorite cloud computing provider?

asked Apr 30 2009 08:07 on sysadmin

Which cloud computing provider would you recomend for a windows stack, in terms of cost and reliability?

Answers (2)

What's your checklist for when everything blows up?

asked Apr 30 2009 08:07 on sysadmin

Users can't get to their e-mail, the CEO can't get to the company's home page, and your pager just went off with a "911" code. What do you do when everything blows up?

Answers (20)

As I see it:

  1. Microsoft has always provided backward compatibility for legacy applications;
  2. As far as the end user is concerned, things won't be behaving any different from what they normally expect.

Answers (9)

Can I install Exchange 2007 onto a Windows 2008 single server, which will be the only server in the LAN? I'm guessing this server will also have the AD on it, etc.

Answers (3)

Bandwidth utilization tools?

asked Apr 30 2009 08:09 on sysadmin

What commercial or open-source tools are available to measure bandwidth utilization on a network? The tools should provide current and historical snapshots.

Answers (1)

Best practice for IIS 6.0 (Windows Server 2003) backups?

asked Apr 30 2009 08:10 on sysadmin

What is the best backup strategy for saving IIS 6.0 data: web metadata, files, logs etc. for disaster recovery?

Answers (2)