FixieBreaky: when purging database records keep the transaction log in mind

The space on a database server was shrinking. That was not a danger level yet, though the trend left no mistakes: in about two month it would be over. A colleague of mine decided to delete a large amount of obsolete records in a database on the server.

Neither he nor anybody else around did not give a second thought about the transaction log growth caused by the deletions. That included me, even though I stepped into same trap a year ago.

Lessons:
Database backup is extremely important.
Heroic actions is not a substitute for planning.
No matter how large and expensive a storage is, one day it'll be filled
No magic software tool is a substitute for planning
Stay alert, whatever comes to you, look into your previous experience
Blaming and punishing is not a substitute for planning

Using SQL Server aliases vs messing with configuration files

For creating SQL Server aliases use either SQL Server Configuration Manager or cliconfg.exe utility. The utility apparently exists on virtually any Windows computer. Safe way would be creating same aliases for 32-bit (System32) and 64-bit environments (SysWow64).

With aliases properly set there is no need to modify connection strings in project's configuration file when switching between different environments, e.g. development and production.

Moving SQL Server database to SQL Azure

The task was simple: a database -- the structure and also the data -- had to be copied from a physical SQL Server up to the Cloud, which was in that case the SQL Azure.

The first tool I tested was SQL Azure Migration Wizard. This is very decent and handy tool I must say. With just a few minor glitches I had 240 Mb database moved up in two successfully completed tries. The first one took 40+ minutes, and 80 minutes was the second. I assume that SQL Azure responsiveness may vary, especially for trial accounts the kind I was using.

The migration tool uploads the structure and the data in a single package, and simplifies the process significantly.

You have to be aware of the differences between SQL Server 2008 R2 and SQL Azure. Naming a few: unsupported DBCC CHECKIDENT should probably make you review some stored procedures in your database. Note that the migration tool moves all stored procedures to the cloud regardless of whether they will continue working properly or not.

A less subtle difference is no FILESTREAM in SQL Azure. My database initially contained some FILESTREAM data. SQL Azure compatibility forced me to review and alter the structure switching from FILESTREAM to varbinary(max).

The second thing I wanted to try was creating empty SQL Azure database by running a script. The script was to be generated in SQL Server Management Studio. As I found, generating and applying such script took minimal time and efforts.

The ability to connect to SQL Azure database in the Management Studio is a really great thing. While online SQL Azure database management (Silverlight) works quite decently, it is not yet quite on par.

You operate in familiar environment, though some features are disabled and some might not work as they used to. For example, each my attempt to open a stored procedure or a trigger for modifications failed. Disabling a trigger also failed while invoked from the context menu, but succeeded through executing a query.

The part of my present project is conversion of data stored in Visual FoxPro database to SQL Server database. To transfer the data up to the cloud I intended to use same routines I used to convert the data to the local SQL Server database.

First I modified the remote connection in VFP database container. For both local and cloud connections I already had SQL Server data sources created. The following command creates connection to my local SQL Server using data source name.
CREATE CONNECTION MyDbConnection DATASOURCE "LocalSqlServer"

Another command creates connection to SQL Azure server.
CREATE CONNECTION MyDbConnection DATASOURCE "SqlAzure" USERID "[my Azure login]" PASSWORD "[my Azure password]"

Since both connections share the name "MyDbConnection", the VFP database really "sees no difference", or let's call it has no awareness (and does not need to) of what kind the target database is. At any time, by launching CREATE CONNECTION command in VFP command line, I can reconnect the VFP database to either local or cloud SQL server.

I also made small changes in my conversion routine giving it means of recognizing the target database whether it SQL Server or SQL Azure. That was accomplished by adding a remote view, the last column of which carried the required information (5 for Azure, 3 for SQL Server 2008 R2).

CREATE SQL VIEW SqlServerProperty REMOTE as SELECT cast(SERVERPROPERTY('productversion') as varchar(50)) as product_version, cast(SERVERPROPERTY('productlevel') as varchar(50)) as product_level, cast(SERVERPROPERTY('edition') as varchar(100)) as edition, cast(SERVERPROPERTY('EngineEdition') as int) as engine_edition

So far so good. I set the connection to SQL Azure and started data conversion routine. Noticeably slower, it was running for some time without glitches, while I occasionally monitored the progress by launching short queries in the Management Studio. Unfortunately the routine failed with fairly non-descriptive message informing of failed data connection -- connectivity error dbnetlib connectionwrite send().

Partially that was caused by designing the conversion routine with local SQL Server in mind. Once the routine hits anything that may result in incompletely converted data, it simply throws an exception, and exits without trying to repeat the failed action.

. . .
Based on my brief tests, now I am more inclined to have VFP to SQL Server data conversion completed locally, sending converted data up to the cloud with SQL Azure Migration Wizard or with a similar tool.

Creating SQL Server database backup using the Microsoft.SqlServer.Management.Smo

Assemblies to be referenced can be found in C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies. The "100" part in the path may need to be changed to reflect the SQL Server version in use.

Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Management.Sdk.Sfc
Microsoft.SqlServer.Smo
Microsoft.SqlServer.SmoExtended

A backup can be created in either sync or async mode. Below is a basic console implementation built around  the Backup class of the library.

using System;
using Microsoft.SqlServer.Management.Smo;

namespace SqlServerBackupStudy
{
    public class Program
    {
        public static void Main(string[] args)
        {
            TestCreateBackup(
                ".", 
                "MyDatabase", 
                @"c:\backup\test_backup.bak");

            Console.Write("\n\nAny key...");
            Console.ReadKey();
        }

        private static void TestCreateBackup(
            string serverName, 
            string databaseName, 
            string backupPath)
        {
            try
            {
                var server = new Server(serverName);

                var backup = new Backup
                    {
                        Database = databaseName, 
                        Initialize = true
                    };

                backup.Devices.Add(new BackupDeviceItem(
                            backupPath,
                            DeviceType.File));

                backup.SqlBackup(server);
            }
            catch (Exception ex)
            {
                Console.WriteLine(
                    "{0}\n{1}", 
                    ex.GetType(), 
                    ex.Message);
            }
        }
    }
}

Microsoft.SqlServer.Management.Smo.FailedOperationException can be caused by non-existent or unreachable server or database, as well as by invalid backup path. My guess that insufficient disk space should cause same exception.

It is important to understand that in case of DeviceType.File the destination path is meant to be a path on the machine that runs the SQL Server. In other words, do not expect creating backup of a remote database on your local drive. This may change eventually.

Database restoring routine uses the Restore class, and otherwise looks almost identical to the backup routine.



private static void TestRestoreFromBackup(
    string serverName, 
    string databaseName, 
    string backupPath)
{
    try
    {
        var server = new Server(serverName);

        var restore = new Restore
            {
                Database = databaseName
            };

        restore.Devices.Add(new BackupDeviceItem(
            backupPath,
            DeviceType.File));

        restore.SqlRestore(server);
    }
    catch (Exception ex)
    {
        Console.WriteLine(
            "{0}\n{1}",
            ex.GetType(),
            ex.Message);
    }
}

The exception may have one and more inner exception levels. The lowest one is the most instructive. Here is an example of three levels of exception.

1. Restore failed for server [server]
2. An exception occurred while executing a Transact-SQL statement or batch.
3. Cannot open backup device '...'. Operating system error 3(The system cannot find the path specified.).\r\nRESTORE DATABASE is terminating abnormally.

Also one has to ensure that the target database is not in use. Otherwise the following exception would pop up.

Exclusive access could not be obtained because the database is in use.\r\nRESTORE DATABASE is terminating abnormally.