Continuous Integration

It seems like a magic silver bullet: Continuous integration (CI) is the practice, in software engineering, of merging all developer working copies with a shared mainline several times a day. It means that everything should stay inline, that no-one steps on anyone's toes. That all should be shiny and lovely and

Oh. Hang on.

Didn't we all learn in Comp Sci 101 that there's no such thing as a silver bullet? I'm sure that turned up on my course 14 years ago: and Continuous Integration has been around since well before then (1991 if we believe wikipedia). Mind you, my Comp Sci course also involved the networks tutor getting very excited about 10BASE5 thick coax, when possibly he should have been getting nostalgic about it.

So. Anyway. Continuous integration is supposed to make it much easier for everyone to work together. And, in order to work together, you need good tools. You need to be using the same tools, you need the tools to work together without causing conflicts, and, crucially, you all need to be using the tools properly!

Enter Redgate. Hurrah!  I do rather have a DBA crush on Redgate. They've made my life so much simpler in so many ways (except for discontinuing Hyperbac. I did rather love Hyperbac, and I still have it running on the one SQL 2000 box that we're trying to decommission. It works. Period.).

Back in March, I went on the Continuous Integration Workshop hosted by Redgate. Primarily, I wanted to understand how CI works - we've got some source control in place where I work, but it very often feels that developers are stepping on each other's toes, that we're not always testing as robustly as we might, that our databases are completely out of step between environments, and, as DBA, I'm running scripts and copying databases from Live to Test to Dev more than I might otherwise like.

By the end of the day: I could see how it was supposed to work in the world of databases. From a standing start (I've not been involved with our Developers' source control system at all, and I can't exactly say I'm intimate with TFS), we looked at how to use SQL Source Control to link to a source control system - it works with pretty much any system, and DLM Dashboard (their super, free, product - which I had a lot of fun testing last year) to keep track of changes, to be informed as databases changed, and to automate the process. I took copious notes.

And, the automation of the process would be fantastic. Because this morning, I ran a script a whole week early, and had to roll it back again fairly pronto. On the plus side, I'd taken a backup before I'd got going, so I could restore that on the Dev box and then use SQL Data Compare, which made it an absolute doddle to establish which rows in the Live database had been updated, and to roll them back, based on a comparison between the two versions of the database.

(Did I say I love Redgate?)

Disclaimer: Redgate are offering me a free place at a Redgate Workshop in return for posting.

Reporting Services Errors

Or: Why does it work on the source server, but give us errors on the client machine?

An error occurred during client rendering.
An error has occurred during report processing.
Cannot read the next data row for the dataset ThisDataSet.
For more information about this error navigate to the report server on the local server machine, or enable remote errors

Before I got as far as enabling remote errors, I tried running the report on the local server, where, of course, it was impossible to even navigate to the report as this error popped up:

User 'Domain\UserName' does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed,

which was fixed by running Reports in Internet Explorer using Run As Administrator permissions, and then granting the appropriate access via the Security settings within SSRS (a little bizarre, I'm sure you'll agree, as if I could get to the report using the client machine, I should have been able to see it on the local server using exactly the same Windows login.

Then, however, the report ran perfectly happily. I poked about for the original error. I found an article telling me where to find the Report Server Log files, and looking in them, found this:

WARN: Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot read the next data row for the dataset ThisDataSet. ---> System.Data.SqlClient.SqlException: The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.webserver!ReportServer_0-29!26e4!07/19/2011-13:05:43:: e ERROR: Reporting Services error Microsoft.ReportingServices.Diagnostics.Utilities.RSException: An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot read the next data row for the dataset ThisDataSet ---> System.Data.SqlClient.SqlException: The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

I looked at the dates on both versions of the report. They were both definitely dates. However. On the local server, the format was

m/dd/yyyy hh:mm:ss AM

and on the client machine it was

dd/mm/yyyy hh:mm:ss

Aha.

The date on the local server is in UTC, because that's what the third party supplier wanted. The date everywhere else is in local time. And, as a result, the date on the local server is in the format that the report expects, whereas the date on the client machine isn't...

And, since whomever wrote the report didn't think that around the world, people do use different date formats, and that reports might not be run on the local server, the whole thing came to a crashing halt.

The workaround, other than running the report on the server, is to manually change the date format to the one which works. But, my goodness, that's a faff: and what's wrong with having one of those little calendar thingies to click on?

OSQL -E

Sometimes, you get a black-box installation by a third party supplier: and, when you politely enquire as to whether you can install SSMS on the server so that you can pull out pertinent information for your SQL Estate Audit (because, naturally, the installation on the server is such that you cannot connect remotely: you just know that the server is there), the answer is no. Grrr.

But not the end of the world. Via the command prompt, we can open a connection to that instance of SQL, and run whatever queries we fancy with a local administrator's account (because, let's face it, if they haven't installed SSMS, and they're a third party supplier, the likelihood is that they haven't disabled the BuiltIn\Administrators group....).

Start off by checking the Services on the Server, to get an indication of what you've got: also check SQL Server Configuration. This will let you know how many instances of SQL are on the server, and whether they have a name other than the default servername. On the server I'm looking at, I've got two instances of SQL Express. I can connect to one by typing the following in the command prompt:

OSQL -E

For the second, I need to be a bit more specific, and type

OSQL -E -S ServerName\InstanceName

Once I've got OSQL running, I can query merrily. Every T-SQL statement that I can run in SSMS or Query Analyzer can be run in OSQL. I can execute stored procedures. I can change databases. I just need to remember that instead of hitting 'F5' to run my statement, I need to end my statement with 'GO', and press enter. It's worthwhile not running 'SELECT *', since the formatting leaves something to be desired, and it's a bit tricky....

So, what might my useful statements be? I've used a selection of the following today

To list my databases
SELECT name
FROM sys.databases
GO

To check authentication mode

SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly')
WHEN 1 THEN 'Windows Authentication'
WHEN 0 THEN 'Windows and SQL Server Authentication'
ELSE 'Unknown'
END
GO

To check recovery mode

SELECT recovery_model_desc
FROM sys.databases
GO

To check collation

SELECT name, collation_name
FROM sys.databases
GO

To check database owner

SELECT name, SUSER_SNAME(owner_sid)FROM sys.databases
GO

To check SQL Version, Windows Version and SQL Service Pack

SELECT @@VERSION
GO
SELECT ServerProperty('ProductLevel')
GO

To check database size

USE dbname
GO
EXEC sp_spaceused
GO

It's pretty easy to find out what you need by searching for the criteria, with an additional search of 'T-SQL' slapped at the front of the string. More additions will be made here as I come to need them, however, this got me what I wanted for my spreadsheet....

And, now it makes sense...

I had another email from Lightspeed, in response to my request to know why my databases were shrinking even though autoshrink = false, and this got to the root of the matter.

Previous versions of Total Traffic Control could be run on SQL Express, which has a 4GB database size limit: so, TTC has a subroutine that automatically shrinks databases, in order to get round this issue.

Now, however, they don't support running the system on SQL Express, so this code is redundant. However, it's not yet been removed. They're working on that: it'll have to be thoroughly tested first, so it won't be instant.

I'm very glad I asked: and I'm very pleased to have had such a good reply!

A response from Lightspeed

Remember this post? Well, I emailed Lightspeed. Their response?

"By default, the database is set to autogrow and autoshrink to save space."

In the last week, I have had the following.

Initial size 5430MB
Manually grown database to 8000MB
Autogrow to 8800MB
Autoshrink to 5430MB
Autogrow to 5900MB

There is plenty of space on the drive on which the database files reside (C:\, because Lightspeed recommend that SQL be installed on the C:\drive running under the local system account).

Moreover, autoshrink is set to false for every single database on the server, so I don't know what exactly is causing this shrinkage, but it ain't SQL. In fact, I would posit that it is completely inaccurate to say that the databases are autoshrinking, as this shrinkage does not show up in the 'Data/Log files Autogrow/Autoshrink' events part of the Disk Usage reports for the databases. It just happens, it doesn't show up in the logs, and it is really rather annoying me.

3rd Party Software Again...

This time, Lightspeed System's TTC seems to be the culprit. It has a nifty little system that sets autoshrink = true for all its databases, and then it gets terribly upset if the database file fills up. Of course, if it wasn't autoshrinking, then it wouldn't need to autogrow, and it wouldn't get its knickers in a twist because it couldn't autogrow on the terms of SQL's default, which is an autogrow of 10%.

When autoshrink = true, SQL will try to shrink all database files with more than 25% free space. This creates a performance hit for the CPU: the default setting for SQL is to set autoshrink = false (except on the Desktop Edition, where the default setting for SQL is autoshrink = true).

When autogrow = true, SQL will try to grow the database file while running an INSERT. The query may well time out, the autogrow will fail, and the database file will fill up. The default for SQL is to set autogrow = true, with a growth of 10%.

Both autogrow and autoshrink cause problems with file fragmentation and index fragmentation. It is vastly more healthy to set the initial database file size to the anticipated database file size, and then leave it well alone. There will be less load on the CPU, neither files nor indexes nor pages will fragment and the server will run more efficiently. It's like the difference between ironing a shirt on an ironing board, unimpeded, and ironing a shirt on a folded up towel on one corner of your work desk. Yes, it can be done, but no, we really don't want to as it takes longer and is vastly more exhausting.

Why Lightspeed Systems seems to think that Total Traffic Control's databases should be set to autoshrink is something I haven't yet established. My Google-fu is not strong today, so if you do know the answer, I would love to hear it.

Sometimes, it ought to be obvious....

Over the Christmas break, we moved our Raiser's Edge database files, so they weren't sitting on the c:\ drive (where they installed as default), but on the data drive for that server. One of my colleagues used the BlackBaud Manager for the system, rather than me doing anything clever in the back end.

At which point, the backup job, created via BlackBaud, failed.

First it failed owing to permissions. It decided that it couldn't connect to the (local) server. Easy enough to fix.

Then it failed owing to that hoary old chestnut "[SQLSTATE 42000] (Error 22029)". You know. That really useful error message that has you looking in six different places to find out why it failed: diskspace, file locked, unable to access backup file location etc etc etc. No SQL maintenance plan that I could right click and 'View History' - the thing's done by a T-SQL string invoking xp_sqlmaint. Well, the string *looked* OK: same as the version that did execute successfully when run manually.

Yup. String looked fine. Beautiful syntax. Until the second cup of coffee kicked in.

It helps if you're trying to backup a database that exists! In the process of moving the database files about, the database got renamed. The user connections got updated to reflect this new database name, but not the backup job.

Backups are now running.