Performing database deployments using AliaSQL

Most continuous integration build processes have a need to perform database deployments over the course of development, which is why a tool such as AliaSQL arose to fulfill that need!

You can find out about AliaSQL from here: https://github.com/ClearMeasure/AliaSQL

AliaSQL is available for download directly as an executable or can be installed via a NuGet package.

Once you download the tool, you will probably want to learn how to use it, so you should check out the "Getting Started" guide here: https://github.com/ClearMeasure/AliaSQL/wiki/Getting-started

If you want to look at a demo project, you can find that here: https://github.com/ericdc1/AliaSQL-Demo/tree/master/Source/Database.Demo/scripts

While I was working with AliaSQL, however, I found a problem in the samples that were provided in the Getting Started Guide:

AliaSQL.exe Update .\sqlexpress Demo ./scripts

First of all, the example assumes that you have SQL Server Express installed.  Fortunately, the name of the database server can just as easily be substituted as follows:

AliaSQL.exe Update SQL2014Svr Demo ./scripts

But what about remote SQL Server instances?  What do you do then?  Thankfully, a solution for that is also available:

AliaSQL.exe Update SQL2014Svr Demo ./scripts mysqluser mysqlpassword

Lastly, I could never get this command to work!!  So what was wrong?  Well, even though the AliaSQL.exe file is placed in the root of the scripts directory, the path that is used in the command assumes that AliaSQL resides OUTSIDE of the scripts directory!!  This was fixed by using the following command instead:

AliaSQL.exe Update SQL2014Svr Demo . mysqluser mysqlpassword

Notice that I no longer reference the relative path to the ./scripts directory and simply assume that the AliaSQL.exe exists at the root of the scripts directory (as follows the pattern in the demo and the KickStarter project).

That was all that was needed for working with AliaSQL to do my database deployments!

For your reference, I have also provided a sample PowerShell script on doing database deployments using AliaSQL:

SQL Server Developer Edition is now FREE!

If you are a Visual Studio Dev Essentials member, you can now obtain a FREE copy of SQL Server 2014 Developer Edition!

https://blogs.technet.microsoft.com/dataplatforminsider/2016/03/31/microsoft-sql-server-developer-edition-is-now-free/?MC=Vstudio&MC=SQL&MC=IE&MC=HTML5&MC=JavaScript

You can sign up for a Visual STudio Dev Essentials membership here: https://www.visualstudio.com/en-us/products/visual-studio-dev-essentials-vs.aspx

Migrate from Oracle to SQL Server--for FREE!

Microsoft is offering FREE licenses of SQL Server for customers wishing to migrate away from Oracle!

You can claim this limited-time offer here: https://www.microsoft.com/en-us/server-cloud/sql-license-migration.aspx

SQL Server on Linux!!

If you haven't already heard the news, Microsoft has recently announced that SQL Server will become available on the Linux platform!!

https://blogs.microsoft.com/blog/2016/03/07/announcing-sql-server-on-linux/

Unfortunately, you will have to wait until sometime next year (in 2017) to get this exciting release of SQL Server, but you can follow the progress of this product development here: https://www.microsoft.com/en-us/server-cloud/sql-server-on-linux.aspx

Connecting to LocalDb from within Visual Studio 2015

If you have worked with SQL Server LocaldDB in the past, you may have connected to LocalDB using the following type of connection string: (localdb)\v11.0

However, this changes with the release of Visual Studio 2015.  Instead, of using (localdb)\v11.0, you will instead use a connection string such as this: (localdb)\MSSQLLocalDB

You can then connect to the Visual Studio 2015 instance of LocalDB in the following manner:

Scripting your SQL Server Maintenance Tasks

When you normally set up maintenance for your SQL Server databases, most of the time you will end up manually setting up these jobs and tasks from within the SQL Server Management Studio User Interface.

However, if you like to simplify the management of these tasks, then it makes sense to use scripts to accomplish this.

Fortunately, a set of scripts already exist that allow you to do just that!

You can download the SQL Server Maintenance scripts from here that allow you to perform a wide variety of SQL Server Maintenance operations effortlessly: https://ola.hallengren.com/

Database Migration Tools to SQL Server

If you have ever had to perform a database conversion, you might be wondering what tools are available to perform database conversions.

I have used several tools in the past for my wide variety of database conversions from different platforms such as Informix and Oracle to SQL Server:

1. Altova MapForce: http://www.altova.com/mapforce.html
2. EMS Data Pump: http://www.sqlmanager.net/en/products/datapump
3. Spectral Core Full Convert: https://www.spectralcore.com/fullconvert
4. SQL Server Migration Assistant: http://blogs.msdn.com/b/ssma/archive/2014/08/21/latest-update-microsoft-sql-server-migration-assistant-ssma-v6-0-is-now-available.aspx

Out of all the tools, the one that I found the easiest to use and most user friendly was definitely Spectral Core Full Convert.  It was also the most affordable and the technical support they provided was also excellent!

You can decide for yourself which tool is your favorite!   

Cannot find server certificate with thumbprint when restoring a SQL Server database

I was recently attempting to restore a database backup that I received from a vendor when I suddenly encountered the following error message:

Well, as it turns out, based on this Microsoft support article: https://support.microsoft.com/en-us/kb/2300689, the database was backed up using Transparent Data Encryption (TDE).  Therefore, without the certificate information, I would not be able to restore the backups back to my database server.

If the vendor was willing to provide me with the certificate information, then I could use the following method to restore the database: http://sqlserverzest.com/2013/10/03/sql-server-restoring-a-tde-encrypted-database-to-a-different-server/

However, considering that would probably be a compromise of their security, the best thing to do is as advised in the Microsoft support article:


Alter database testDB set encryption off 

Once TDE encryption is turned off, a backup of the database could be created and then provided to us to restore!

Using the Invoke-Sqlcmd Cmdlet in PowerShell

If you want to be able to run SQL Scripts in PowerShell, you will definitely want to use the Invoke-SqlCmd cmdlet as outlined here: https://msdn.microsoft.com/en-us/library/cc281720.aspx?f=255&MSPPError=-2147217396

Unfortunately, the article does not mention how to get the Cmdlet to appear in Intellisense for your PowerShell scripts!

Fortunately, the answer is really rather simple.

You have to add the following PowerShell snap-ins to your PowerShell window:

Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100

Once you have these loaded, you will be able to use the Invoke-Sqlcmd Cmdlet in your PowerShell scripts!!

AddCurrentUserAsSQLAdmin can only be specified for SQL Server 2008 R2 Express

I was recently running a SQL Server 2008 R2 installation using an existing ConfigurationFile from a PowerShell script when I suddenly encountered the following error message:

Well, as it turns out, the Configuration File contained this particular line:

; Provision current user as a Database Engine system administrator for SQL Server 2008 R2 Express.

ADDCURRENTUSERASSQLADMIN="True"

Based on the advice for creating a ConfigurationFile.ini file for SQL Server outlined in this article (http://mycodelog.com/2010/09/28/sqlsilent/), I was able to determine that I needed to remove this particular line from my ConfigurationFile.ini file.


Once I did this and re-ran my setup, I was able to successfully install SQL Server without this error message!

Free Toad Data Modeler

If you are working with small databases (25 objects or less), you can use the Freeware edition of Toad Data Modeler here: http://www.toadworld.com/m/freeware/553

If you want to try out the full functionality of Toad Data Modeler, you can download a trial version of Toad Data Modeler from here:http://www.toad-data-modeler.com/download/  or here: https://software.dell.com/register/54826/

Free ERWin Data Modeling software

If you have done any data modeling for any major organization, chances are that you have used ERWin Data Modeler at some point in time.

However, ERWin is a very expensive tool for independent consultants or small businesses, therefore, many companies look for cheaper alternatives such as Microsoft Visio.

Well, there is good news for those companies with relatively small databases!

Computer Associates now is offering a free version of ERWin called CA ERWin Data Modeler Community Edition!  http://erwin.com/products/data-modeler/community-edition

This version has a limit of 25 objects as well as requiring to re-download the software every 6 months or so to renew the license, but for being able to leverage the full power and functionality that accompanies CA ERWin Data Modeler is well worth it!

You can download CA ERWin Data Modeler Community Edition from here: https://www.ca.com/us/register/forms/ca-erwin-data-modeler-community-edition-evaluation-software.aspx

Dealing with SQL Server Timestamp columns in ADO.NET

I recently had a requirement to copy/export SQL Server data to MS Access.

Since I could not use SSIS, I used ADO.NET to perform the table copy based on this article: http://stackoverflow.com/questions/17253453/how-to-export-data-from-sql-server-compact-to-access-mdb

Unfortunately, one of the limitations with doing this is that you have to manually create the SQL to generate the MS Access database tables which therefore requires a knowledge of the underlying DataTable datatypes and what they map to in MS Access.

I found this article which describes the MS Access datatypes: https://msdn.microsoft.com/en-us/library/ms714540%28v=vs.85%29.aspx

I was able to map most of the SQL Server datatypes accurately based on the listing, however, I encountered a hurdle when I encountered the SQL Server timestamp column!!

After doing some debugging, I discovered that it was translating the timestamp column into a Byte[] array, therefore, I needed to do some special handling for Byte[] and figure out its appropriate datatype mapping.

Based on this SQL Server MSDN article: https://msdn.microsoft.com/en-us/library/ms182776%28v=sql.90%29.aspx?f=255&MSPPError=-2147217396

I discovered that I could map the timestamp column to a VarBinary data type in MS Access when creating the target database table in MS Access.

Once I did that, the copy table operation worked beautifully!!

 

This was the code that I used to generate the MS Access database table SQL:

 

public string GetCreateTableSql(DataTable table)

{

    StringBuilder sql = new StringBuilder();

    int i = 0;

    sql.AppendFormat("CREATE TABLE [{0}] (", table.TableName);

    while (i < table.Columns.Count)

    {

        bool isNumeric = false;

        bool usesColumnDefault = true;

        string columnSeparator = ",";

        sql.AppendFormat("\n\t[{0}]", table.Columns[i].ColumnName);

        switch (table.Columns[i].DataType.ToString().ToUpper())

        {

            case "SYSTEM.BYTE":

                sql.Append(" Byte");

                break;

            //The SQL timestamp column is translated into a System.Byte[] array data type

            case "SYSTEM.BYTE[]":

                sql.Append(" VarBinary");

                break;

            case "SYSTEM.INT16":

                sql.Append(" Integer");

                isNumeric = true;

                break;

            case "SYSTEM.INT32":

                sql.Append(" Integer");

                isNumeric = true;

                break;

            case "SYSTEM.INT64":

                sql.Append(" Long");

                isNumeric = true;

                break;

            case "SYSTEM.DATETIME":

                sql.Append(" DateTime");

                usesColumnDefault = false;

                break;

            case "SYSTEM.BOOLEAN":

                sql.Append(" YesNo");

                break;

            case "SYSTEM.CHAR":

                sql.Append(" Text");

                break;

            case "SYSTEM.STRING":

                sql.AppendFormat(" Text");

                break;

            case "SYSTEM.SINGLE":

                sql.Append(" Single");

                isNumeric = true;

                break;

            case "SYSTEM.DOUBLE":

                sql.Append(" Double");

                isNumeric = true;

                break;

            case "SYSTEM.DECIMAL":

                sql.AppendFormat(" Double");

                isNumeric = true;

                break;

            default:

                sql.AppendFormat(" Text");

                break;

        }//switch

        if (table.Columns[i].AutoIncrement)

        {

            sql.AppendFormat(" AutoNumber");

        }//if

        //Increment the counter

        i++;

        //Add a column separator for the Create Table SQL statement

        if (i < table.Columns.Count)

        {

            sql.AppendFormat("{0}", columnSeparator);

        }//if

    }//while

    sql.AppendFormat(" {0}", ")");

    return sql.ToString();

}//method: GetCreateTableSQL() 

 

 

Truncating and shrinking SQL Server Log Files

If you have your SQL Server databases in Full Recovery mode, you may notice that your SQL Server Log Files will quickly grow in size.

On one of our database servers, I discovered a SQL Server Log File that was over 300 GB in size!

As you can probably guess, I needed to truncate and shrink the log file in order to recover a large amount of disk space.

Fortunately, the process is very easy as is outlined in this MSDN article: https://msdn.microsoft.com/en-us/library/ms189493.aspx

You basically have to set the database to Simple Recovery mode and then run the DBCC SHRINKFILE command.  

SQL Server database connection string on an alternate port

I recently was setting up multiple SQL Server databases on different servers operating on different ports.

However, one of the points of confusion I had remaining was how to connect to these databases in my connection strings when they were operating on ports other than the default port of 1433?

Fortunately, this article came to the rescue!! http://www.connectionstrings.com/sql-server-2012/

In order to connect to a database operating on an alternate port, you specify a connection string such as the following:

Data Source=DBServer,1434;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;

Therefore, you simply separate the port number by a comma, from the database server name (just as you would do in SQL Server Management Studio).

That is all there is to it!!

User, group or role already exists in the current database

I was recently working on creating security users in a new installation of SQL Server when I attempted to assign a user to a restored database and encountered the following error message:

Fortunately, I came across this article which offered a very handy fix:  http://blog.sqlauthority.com/2007/02/15/sql-server-fix-error-15023-user-already-exists-in-current-database/

It just told me to run this command to map the database user name back to the database:

EXEC sp_change_users_login 'update_one', 'ColdFusion', 'ColdFusion'

Once I did that, my database login was working once again!!

Latest Cumulative Updates for SQL Server

If you are looking for the latest updates for your version of SQL Server, then you definitely need to check out this TechNet article: https://technet.microsoft.com/en-us/sqlserver/ff803383.aspx

This article lists out all of the various Cumulative Updates for each release of SQL Server (based on whether or not you have a Service Pack installed).

SQL Server 2014 SP1 has been released!

SQL Server 2014 SP1 has been released!  You can download SP1 from here:

Microsoft® SQL Server® 2014 Service Pack 1 (SP1)

https://www.microsoft.com/en-us/download/details.aspx?id=46694

Microsoft® SQL Server® 2014 Service Pack 1 (SP1) Express

https://www.microsoft.com/en-us/download/details.aspx?id=46697

In addition, slipstreamed/integrated SQL Server 2014 media is available for download from MSDN.

SQL Server database in Recovery Pending state

I recently encountered a problem with several of the SQL Server databases I was managing/administering whereby the databases were displaying a "Recovery Pending" state in SQL Server Management Studio.

When I attempted to detach the databases to see if I could look at resolving the issue with these particular databases, I received an error message stating that the transaction log was in an inconsistent state.

Well, being the good SQL Admin that I am, I had set up Full SQL Server database backups as well as periodic SQL Server Transaction Log backups.

Therefore, all I needed to do in SQL Server Management Studio was right click on the individual databases and select the option for "Restore". 

As soon as I did that, I could select the most recent Database and Transaction Log backups and restore the databases to their original state.

Since I had scheduled the Transaction Log backups to occur every 4 hours, I was able to recover the data until 4 hours earlier.

After reviews of a few missed transactions by the team, everything was soon backup and running smoothly just as it was before the database failures!!

Changing a SQL Server database from Single User Mode to Multi User mode

I was recently attempting to detach/drop a database from SQL Server Management Studio when the detach suddenly failed.

Of course, this suddenly placed the database in Single User Mode.  When I subsequently tried to connect to the database again, I could not access it!

If I tried to open a query and run the following command:

USE master
ALTER DATABASE MyDatabase Set MULTI_USER

The query inevitably failed because the database was already locked and in use by an existing process.

Therefore, in order to figure out who was holding the locks on the database, I had to run the following command:

sp_who2

Once I got a list of all the sessions and their associated databases, I had to run the following command:

KILL <session id>

Now, once I had killed all of the sessions that were attached to the database, I could once again run the following command:

USE master
ALTER DATABASE MyDatabase Set MULTI_USER

Now, the database was back in Multi User mode again and I could proceed with detaching/dropping the database directly from SQL Server Management Studio!