Watch the dot

You have to be careful when typing commands.  One character out of place can cause strange problems or even total break down.  So is the case of a recent SQL backup scheme.

One of the other DBA’s I work with had a problem.  They were trying to run full backups of a live database but the backup drive kept filling up.  The reason it would fill up was because the old backup files (*.bak) were not being deleted when the next backup ran.  One backup file takes up 20+ gigabytes of space (heavily used database).  My coworker had tried different commands to make it work but nothing was fixing the issue.

I opened up SQL Server Management Studio and took a look at the Maintenance Plan being used.  The maintenance plan had two subplans.  The first subplan made a full backup of the database at 3 a.m. every day.  The second subplan made a transaction log backup every two hours.  I opened up the first subplan in order to review the tasks.

Everything looked normal at first.  When I opened up the first task in order to view the details, I saw the problem.  The first task, a maintenance cleanup task, is the one set to delete old backup files.  My coworker had set it to search the backup folder for the .bak extension.  The problem is that you can’t add the dot before the file extension to be found.  I removed the period, saved the subplan and launced the full backup job.  We knew it was working when it immediately deleted the old backup files and then created the new backup.

One other piece of advice I gave him about the backups was the frequency.  This database is used about 18 hours a day.  Currently, a database failure at 2 a.m. would lose a full days worth of data.  I suggested that he perform differential backups at least every 2 hours and leave the full backup as it was.  We did the calculations for the drive space needed and found that he had more than enough room for running differential backups every hour if he wanted.  That will definitely help to keep Murphy at bay.

I don’t know the key to success, but the key to failure is trying to please everybody. – Bill Cosby

22.Dec.08 Microsoft SQL Comments (0)

Moving the WSUS database

We use WSUS 3.0 on our corporate network in order to maintain Microsoft patches for the 5,000+ computers.  For the last seven or eight months, everything had been running smoothly.  I received a call from the network engineer in charge of that server this past Tuesday.  He said that he couldn’t connect to the admin interface and he had a lot of SQL errors in the event log.

When I looked at the logs, I saw that the errors were for licensing.  A default installation of WSUS will install Microsoft SQL Server 2005 Embeded Edition. This version of SQL Server is limited version of Microsoft SQL Server 2005 Express Edition that only allows connections from a short list of Microsoft products (i.e. WSUS, Sharepoint, etc.).  SQL Server Express Edition has a file size limit of 4 GB (database files, not logs) and the WSUS database had grown over that limit.

I told the engineer that we should move the database to my SQL Server 2005 Enterprise cluster.  Not only would that allow us to have a larger database, it would be much faster than the embedded SQL Server he was running on the WSUS server.  I found the instructions for performing the move on the Microsoft Technet Windows Server Update Services site.  The article is called Migrating from Windows Internal Database to SQL Server 2005.

There were two things I had to do in order to make the WSUS server work with my SQL Server cluster. Under Migrating the WSUS database from a Windows Internal Database instance to a SQL Server 2005 instance on a remote server:

Our WSUS server is running much better now. The engineer told me that the reports he pulls from the WSUS admin interface run a lot quicker thanks to the better SQL Server. Adding the WSUS database to the SQL Server caused a very small resource hit, even while running large reports that perform multiple queries. I was glad that something went right this week. :)

A positive attitude may not solve all your problems, but it will annoy enough people to make it worth the effort. – Herm Albright

21.Sep.08 Microsoft SQL, Networking, Software, Technology Comments (6)

Redo

Ever heard the term “GIGO”? If not, it means “Garbage In, Garbage Out”. That’s exactly what has happened to the SQL data warehouse I worked over a year on. The main data source, a student information system (SIS), is so fubar that I can’t guarantee the data.

The problem stems from lazy end users (about 80) of the SIS. Instead of checking if a student already existed, or heeding the error that a student already existed, they would just change some small detail about the student. This little “work around” causes duplicate entries in the SIS. Trying to code around the problem is out of the question simply because there are too many variables. Plus, when the duplicates are found in the SIS, I wouldn’t know which entry is correct so I may import the invalid entry while ignoring the valid one.

This sucks on so many levels. The highest level is the fact that the best option is to blow away what I have and start over. That will involve not only importing information from the SIS but also importing every standardized test (ACT, SAT, etc.) again in order to match it to a student.

I have brought this issue to the attention of those in charge but it was pointless. The answer is always “Maybe they just need more training”. Yeah, that’s it. The other four times they were trained to not do it was wrong. Fifth times a charm.

Idiocy – Never underestimate the power of stupid people in large groups. – Despair, Inc. Idiocy poster

25.Jul.08 Microsoft SQL Comments (2)

A new race

One of the database projects I’m working on involves importing data from an existing student information system (SIS). The main data being imported is student name, date of birth, gender and ethnicity. The SIS stores the ethnicity of the student, in the student table, using an ID number that corresponds to the ID in the ethnicity table. I thought I’d just do a straight import of that table in order to keep from having to create a different ethnicity ID.

I start creating tables on my development SQL Server database by looking at the SIS tables, data types and data. I do that so that I don’t accidentally create a column with a data type of integer that has to be changed later to varchar or something. When I get to the SIS ethnicity definition table, I got a little confused. I saw the normal ethnic categories: White/Caucasian, Black/African American, American Indian, etc. The last three made me say, “Huh?”.

It seems that someone in the school system decided that the available ethnic categories didn’t really fit their students so they created new categories.

Seriously. I know at least one school was using those categories because I was able to query the student table using the ID numbers for those ethnicities. Thankfully there were only 5 or 6 results out of about 20,000 kids. I brought it to the attention of the people in charge of the SIS. They said they would get it fixed right away and couldn’t understand how it happened.

Lesson they learned: Never underestimate your users.

The unforgivable crime is soft hitting. Do not hit at all if it can be avoided; but never hit softly. – Theodore Roosevelt

07.Feb.08 Humor, Microsoft SQL Comments (0)

Trend Micro and MS SQL

Trend Micro software doesn’t play well with SQL logins. OK, SQL logins work but not in the way that is considered a security best practice: Windows Authentication. This became a nuisance during a recent Trend Micro IMSS installation.

The person in charge of installing the upgrade wanted to move the suite and database to another server. When he got to the step to provide the SQL Server username and password, he would put in the Windows domain account information. IMSS would then give an error indicating it couldn’t communicate with the SQL Server. I checked the SQL logs and it was showing that the login failed. He tried several times, making sure the password was correct, but it would fail every time. I had him try changing the domain username to domain\user and it would still fail.

We both checked the admin and installation guides for IMSS. The guides didn’t have any information about what could be causing the error. After about 20 minutes, it finally hit me. We had the same problem when we moved the Trend Micro OfficeScan Server to a new Windows and SQL server a couple of months ago. The admin guide for the OfficeScan server specifically stated that the installation did not support Windows Authentication with SQL Server. You must use a login that is set up on the SQL Server.

We tried the installation again, using the SQL Login I had set up for the OfficeScan Server install, and it worked fine. I did have to give Create any database rights to the login because the installation will not use an existing database. Why it won’t use an existing DB and just drop the tables/views/stored procedures is unknown to me. Instead, if a database with the name provided already exist, the install will drop the database and recreate it.

Untold suffering seldom is. – Franklin P. Jones

17.Oct.07 Microsoft SQL, Software Comments (0)

« Previous Entries