Virtual Infrastructure SQL Server won’t start after SP Install


As your virtual infrastructure is growing and changing rapidly a result is that the configuration of your SQL Servers could be changing also. Virtual infrastructure growth can result in additional drives and drive letter changes for our SQL servers.


Unfortunately, if the default Data and Log paths are not updated when the drive letters change, there will be errors after installing (or uninstalling) a Service Pack or Cumulative Update. The GUI portion of the install will complete successfully, but there are updates that occur on the initial service start after the GUI portion of the install is complete. One update that occurs that that initial service start is database upgrades. If your default paths for data and log files are not correct the upgrade of the master database will fail and the SQL Server service will crash.


In a production setting, this can induce fear, panic, and mass hysteria (or at the very least mass Google searching, which is likely how you arrived at this page to begin with). However, the solution is pretty simple as the default paths are stored in the Windows registry.


Launch Registry Editor (regedit.exe) and navigate to the following section of the registry tree:

HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Server{SQL Version}MSSQLServer

Note: If you are running a 32-bit version of SQL Server on a 64-bit OS, you’ll need to navigate to the Wow6432Node tree (HKEY_LOCAL_MACHINESoftwareWow6432NodeMicrosoftMicrosoft SQL Server{SQL Version}MSSQLServer)

SQL 2000 {SQL Version} = MSSQLServer
SQL 2005 {SQL Version} = MSSQL.1
SQL 2008 {SQL Version} = MSSQL10.MSSQLSERVER
SQL 2008 R2 {SQL Version} = MSSQL10_50.MSSQLSERVER
SQL 2012 {SQL Version} = MSSQL11.MSSQLSERVER

Then update the paths specified in the following keys:
DefaultData
DefaultLogs

Once the paths are correct, the master database upgrade will complete successfully (as SQL Server will be able to create the temporary files in the default paths) and once all other system and user DBs are upgraded you should be back up and running.

Note: The database upgrades will take some time if you have a lot of user DBs, but you can monitor the ERRORLOG to observe the progress.

SQL Server Compact Code Snippet of the Week #15 : flush data to disk immediately

Under normal operation, SQL Server Compact keeps all pending disk writes in memory, and flushes them to disk at least every 10 seconds. The connection string property to control this is called “Flush Interval”, and valid values are between 1 and 1000 seconds. But you may want to flush to disk immediately under certain circumstances, and this weeks code snippet demonstrates how to do just that. This is possible via the CommitMode property on the SqlCeTransaction object Commit method, as demonstrated below:

using (SqlCeConnection conn = new SqlCeConnection(@”Data Source=C:dataAdventureWorks.sdf;”))
{
    conn.Open();
    // Start a local transaction; SQL Server Compact supports the following
    // isolation levels: ReadCommitted, RepeatableRead, Serializable
    using (SqlCeTransaction tx = conn.BeginTransaction(IsolationLevel.ReadCommitted))
    {
        using (SqlCeCommand cmd1 = conn.CreateCommand())
        {
            // To enlist a command in a transaction, set the Transaction property
            cmd1.Transaction = tx;
            try
            {
                cmd1.CommandText = “INSERT INTO FactSalesQuota ” +
                    “(EmployeeKey, TimeKey, SalesAmountQuota) ” +
                    “VALUES (2, 1158, 150000.00)”;
                cmd1.ExecuteNonQuery();

                // Commit the changes to disk immediately, if everything above succeeded;
                // Use Deferred mode for optimal performance; the changes will
                // be flashed to disk within the timespan specified in the
                // ConnectionString ‘FLUSH INTERVAL’ property (default 10 seconds);
                //
                tx.Commit(CommitMode.Immediate);
            }

            catch (Exception)
            {
                tx.Rollback();
            }
        }
    }
}

 

SQL Server Compact Code Snippet of the Week #14 : script all data in a table

Another entry in the scripting API samples, you will find an overview of getting started with the API here.

This weeks entry demonstrates the general pattern for scripting smaller chunks of SQL based on a single table, in this case a INSERT statement for each row in the table.

using (IRepository repository = new DBRepository(@”Data Source=C:Northwind.sdf”))
{
    Generator generator = new Generator(repository, null);
    foreach (var tableName in repository.GetAllTableNames())
    {
        generator.GenerateTableContent(tableName, false);
    }
    System.IO.File.WriteAllText(@”C:script.sqlce”, generator.GeneratedScript);
}

For SQL Server Compact 4.0, the scripting library is now available on NuGet, making it even easier to get started. https://nuget.org/packages/ErikEJ.SqlCeScripting

SQL Server Compact Code Snippet of the Week #13 : reseed (reset) an IDENTITY column

A question that keeps re-appearing in the forum is, how can I reseed/reset an IDENTITY column in SQL Server Compact, as no DBCC command is available. You can simply use a special syntax of the ALTER TABLE command to do this, as follows:

ALTER TABLE [MyTableName] ALTER COLUMN [Id] IDENTITY (1, 1)

This will make the value of the next generated IDENTITY value 1 and increment with 1. Notice that you do not specify the column type, only the IDENTITY specification.