Category: Data Design

All about Azure Databases, Azure single database, Azure managed instance, Azure Cosmos database, Azure VM based databases

Azure SQL Database (SAS) Backup/Restore

This is tricky. Azure SAS SQL Database has master database only. If programmer is taking any advantage of MSDB databases or synonmous, it will not work there directly.

Use Deploy Database to Microsoft Azure SQL Database

The simple method to restore to Azure SQL Database is;

This will create a new database on Azure SQL Server. You can rename it to your actual database.

Migrate to Azure SQL by restoring from Database

This method requires Azure storage account.

Create a blob storage. Upload database backup. From SQL Management Studio, right-click to [YourLOCALDatabase] and open Tasks>Export Data-tier Application. You can export your database to Azure Blob Storage on this wizard. After backup, connect your Azure SQL Server from SQL Management Studio. Go to your [YourREMOTEDatabase], right-click Databases folder from treeview and go to Import Data-tier Application. Choose your backup file from Blob Storage and enjoy!

This will create a new database on Azure SQL Server. You can rename it to your actual database.

Use Azure Data Migration Assistant Tool

This is unlike other methods mentioned above. This is the most efficient tool for migration databases to Azure SQL. This gives you a chance to select individual objects (tables, stored procedures, views, functions etc). Here is the link;

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

This does not create a new database but copy schema and data to existing database.

Use 3dr party tool

3rd party tools can be used to backup/restore Azure SQL database. Here is the list;

https://sqlbackupandftp.com/

The tool allows us to create azure backup on local/remote. We can restore those backups on local/remote machine. I have a schedule job on my local that run everyday 10:00AM to create full backup in Azure blob container “myContainer”.

This tool also allows you to backup on your local computer.

Other methods that can be used with Azure SQL Database;

  • SQL Server Import and Export Wizard
    • Simple process
    • Works even with old SQL Server / SSMS
    • Can export data into different file formats
    • Only data is imported, all other objects will be lost
    • Requires SQL Server Management Studio
    • Manual procedure

Use if you need to move data from Azure to a specific destination (e.g. your old SQL Server) or in a particular format (e.g. flat file) with SQL Server Management Studio tools

  • SSIS Tools
    • Can export data into different file formats
    • Can be run unattended/automatically
    • Only data is imported, all other objects will be lost

Similar to SQL Server Import and Export Wizard, but enables automatic process

Use when you need to create BACPAC file with SQL Server Management Studio tools

  • SqlPackage utility
    • Creates the most exact copy of the database
    • Can be run unattended/automatically
    • Requires the latest DAC library installed
    • Creates a specific BACPAC file

Use if you need to create BACPAC file from a command line

  • BCP utility
    • Can export data into different file formats
    • Can be run unattended/automatically
    • Only data is imported, all other objects will be lost
    • Imports only one table at a time

Use if you need to save data from one or several tables in a readable format

  • SqlBackupAndFtp
    • Simple UI
    • Can create scheduled backups
    • Doesn’t require DAC library installed
    • Creates a specific BACPAC file

Use when you need to perform automatic backups into BACPAC file regularly

  • From Azure Portal
    • Everything online, no software installation required
    • Creates a specific BACPAC file
    • Azure storage account is required

Suitable if you only have a browser

Use AzCopy

This tool can be used to transfer data between storage accounts. Here is the link;

https://adamtheautomator.com/azcopy-setup/

Azure Storage Explorer

This is a GUI that can be used to move files between storage accounts and premises. Here is the link;

https://azure.microsoft.com/en-us/features/storage-explorer/

How to confirm Access Database Engine (ACE Driver) is installed on Database Server

Follow these steps to verify Access Database Engine (ACE Driver) installation;

Double check to make sure you did install the x64 version of Microsoft Access Database Engine here – http://www.microsoft.com/download/en/details.aspx?id=13255.  Please note that only one version x64 –OR– x86 can be installed, not both.

Here is a SQL Server x64 server with the Microsoft Access Database Engine x32 installed – see it does not show up.

Here is the same server with the Microsoft Access Database Engine x64 installed – see how it shows up.

I do not know of a query to get a list of the providers – however if you look in the registry at

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\Providers and check for the key Microsoft.ACE.OLEDB.12.0

This will tell you that it is installed.  If you have a 32-bit version installed on a 64-bit box you would need to look under the Wow6432Node, that key would be HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\Providers.

You would have to make sure that both SQL Server and the ODBC/OLEDB driver are the same bitwise either both x64 or x32.

Possible ways to run SSIS Packages

As far as launching the package, I think we have three reasonably safe options.  The first is xp_cmdshell, which would probably work fine and be pretty simple to implement on a queue (probably the best option in my opinion). 

The second would be to create a single job agent job for each allowed broker process and have the queue processor call sp_start_job only if the job is not already running (pretty easy to check). 

The third option would be to use a CLR procedure to call a web service and have the web service start the SSIS package.  You would have to dive into C# a bit and create your own web service to start an SSIS package, but it is not much code.  Doing it this way, you would not have to allow unsafe assemblies because the web service would be out of process.

Keep in mind that starting a job is asynchronous, and would require additional coding in both the job and the application that starts the processes, as well as creating a job for every SSIS package that needs to be run.  Using xp_cmdshell is synchronous, and requires that xp_cmdshell access be enabled (requires security configuration).  It does require the writing of a command line for each package, but that can also be stored in a table and pulled when needed.

BOL shows code for running SSIS packages in an application.  I am hoping, though not encouraged, that it may be possible to use a CLR stored procedure to run a SSIS package inside the database by simply passing the name of the package to run.  We won’t need 100’s of jobs, or enable xp_cmdshell to accomplish the task.  I am, however, pragmatic and will use which ever method turns out to be the best/easiest/quickest/etc way to meet the goals of the system: to reliably import the data from numerous source systems in the most efficient way possible and to recover from hardware/network failures/outages both planned and unplanned.

Here is another option;

https://www.mssqltips.com/sqlservertip/2992/how-to-execute-an-integration-services-ssis-package-from-a-sql-server-stored-procedure/

Read Excel Data using C#

I am assuming that you already have an Excel file with one “Sheet1” data table in it. Microsoft ACE driver is already installed;

Standard syntax to query Excel table is “SELECT * FROM [Sheet1$]”. Here is the code that does the magic.

// Connect EXCEL sheet with OLEDB using connection string
 using (OleDbConnection conn = new OleDbConnection(connectionString))
    {
        conn.Open();
        OleDbDataAdapter objDA = new System.Data.OleDb.OleDbDataAdapter
        ("select * from [Sheet1$]", conn);
        DataSet excelDataSet = new DataSet();
        objDA.Fill(excelDataSet);
        dataGridView1.DataSource = excelDataSet.Tables[0];
    }
			
	//In above code '[Sheet1$]' is the first sheet name with '$' as default selector,
        // with the help of data adaptor we can load records in dataset		
	
	//write data in EXCEL sheet (Insert data)
 using (OleDbConnection conn = new OleDbConnection(connectionString))
    {
        try
        {
            conn.Open();
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = conn;
            cmd.CommandText = @"Insert into [Sheet1$] (month,mango,apple,orange) 
            VALUES ('DEC','40','60','80');";
            cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            //exception here
        }
        finally
        {
             conn.Close();
             conn.Dispose();
        }
    }
			
//update data in EXCEL sheet (update data)
using (OleDbConnection conn = new OleDbConnection(connectionString))
	{
        try
        {
            conn.Open();
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = conn;
            cmd.CommandText = "UPDATE [Sheet1$] SET month = 'DEC' WHERE apple = 74;";
            cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            //exception here
        }
        finally
        {
            conn.Close();
            conn.Dispose();
        }
    }

$ in select statement means that table already exists in Excel File. If we are going to create a new worksheet then we will not use $ sign. OLEDB does not support DELETE query.

Resource;

https://www.codingame.com/playgrounds/9014/read-write-excel-file-with-oledb-in-c-without-interop