Category: Data Design

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

Azure SQL Database Models and pricing

Azure single SQL database is great if we don’t want to build and maintain datacenter infrastructure; management and patching overhead is completely owned by the vendor. This helps the organization and application owners concentrate on just the design-and-usage of the database.

Azure SQL Cost

This is divided into two models;

Azure SQL Deployment models

Azure SQL deployment models determine how to structure the “SQL Server” and its database;

Azure SQL Database and Azure SQL Manage instance are manage service instances.

SQL Server on Azure VMs gives you full control over the SQL Server instance.

Azure SQL Pricing models

Depending on deployment model, there are two purchasing options;

vCore based purchasing model is available for both Azure SQL and SQL Manage instance.

The DTU-based purchasing model is available for Azure SQL database.

To better understand, use Azure pricing calculator.

https://azure.microsoft.com/en-us/pricing/calculator/

Azure SQL Service Tiers

There are two service tiers used by Azure SQL, each with a different architectural model;

A general-purpose tier for common workloads

A business-critical tier for high throughput OLTP applications requiring low latency and high resilience.

A Hyperscale tier for very large OLTP systems with faster auto-scaling, backup and restore support.

Azure SQL Compute Tiers

Under Azure SQL Database deployment option, under the vCore pricing model with General purpose storage, you will see two options;

Provisioned = Azure SQL provides Azure resources that run your database with a fixed amount of compute resources for a fixed hourly price.

Serverless = The database is provisioned as a serverless component with auto-scaling compute and billing for use per second.

Azure SQL VM Cost (IaaS model)

These are dev pricing and Windows Standard, SQL Server (both included)

8 vCPUs, 32GB RAM, 64 GB Temporary storage, $0.384/hour (With standard support = 380.32/month)

Azure SQL Managed instance cost (PaaS model)

Instance = vCore-4, 20GB memory, 736/moth license included

The only difference between these two options is administration and patch management.

Managed Instances have several key features that do not exist in Azure SQL Database most notably among these are:

Cross Database Queries and Transactions;

CLR;

SQL Server Agent, and Database Mail;

Linked Servers;

Service Broker (within the instance);

Multiple Database file groups and files;

Native Azure vNet deployment; and,

Azure Active Directory Integration;

Keep in mind that Azure SQL Managed instance does not offer SQL Server integration services and Reporting Services. You would have to provision Azure Data Factory for SQL Server integration services and Power BI for reporting.

Azure VM for development seems to be cheaper than Azure managed instance by 40%. Choice is yours.

Single database and Managed Instance comparison

Just out of curiosity, here is top level comparison; first one is managed instance, second one is Single database;

Top level comparison;

Databases level comparison;

System level databases;

Single database has just master database;

master database object layout

System level security

Single database does not have these root level objects;

Server Objects, Replication, Management, SQL Server Agent, XEvent Profiler

Integration Services Catalogs doesn’t exists on both services. Azure Data Factory integration services need to be provisioned to create SSIS database under Integration Services Catalogs.

Reporting services does not exists here. Power BI integrated Reporting services needs to be provisioned.

Azure database connection string

If we want to connect to your local database server using windows security;

{
  "ConnectionStrings": {
    "MyDbConnection": "Server=(local);Database=MyDatabase;Trusted_Connection=True;MultipleActiveResultSets=true"
}

If we want to connect to your remote database server using windows security;

{
  "ConnectionStrings": {
    "MyDbConnection": "Server=1.1.1.1;Database=MyDatabase;Trusted_Connection=True;MultipleActiveResultSets=true"
}

If we want to connect to Azure SQL database server using SQL Server security;

{
  "ConnectionStrings": {
    "MyDbConnection": "Data Source=databaseserver.database.windows.net;Initial Catalog=MyDatabase;User ID=dbuser;Password=dbuserpassword;Connect Timeout=30;Encrypt=True;MultipleActiveResultSets=true;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"
}

if we want to connect to Azure SQL database using Azure AD identity

Server=tcp:myserver.database.windows.net,1433;Authentication=Active Directory Integrated;Database=mydatabase;

If we want to connect to Azure SQL database using Azure AD identity username and password

Server=tcp:myserver.database.windows.net,1433;Authentication=Active Directory Password;Database=myDataBase;UID=myUser@myDomain;PWD=myPassword;

If we want communication to be always encrypted

Data Source=myServer;Initial Catalog=myDB;Integrated Security=true;Column Encryption Setting=enabled;

Run an SSIS package from SSMS with Transact-SQL

This quickstart demonstrates how to use SQL Server Management Studio (SSMS) to connect to the SSIS Catalog database, and then use Transact-SQL statements to run an SSIS package stored in the SSIS Catalog.

SQL Server Management Studio is an integrated environment for managing any SQL infrastructure, from SQL Server to SQL Database. For more info about SSMS, see SQL Server Management Studio (SSMS).

Prerequisites

Before you start, make sure you have the latest version of SQL Server Management Studio (SSMS). To download SSMS, see Download SQL Server Management Studio (SSMS).

An Azure SQL Database server listens on port 1433. If you’re trying to connect to an Azure SQL Database server from within a corporate firewall, this port must be open in the corporate firewall for you to connect successfully.

Supported platforms

You can use the information in this quickstart to run an SSIS package on the following platforms:

You cannot use the information in this quickstart to run an SSIS package on Linux. For more info about running packages on Linux, see Extract, transform, and load data on Linux with SSIS.

For Azure SQL Database, get the connection info

To run the package on Azure SQL Database, get the connection information you need to connect to the SSIS Catalog database (SSISDB). You need the fully qualified server name and login information in the procedures that follow.

  1. Log in to the Azure portal.
  2. Select SQL Databases from the left-hand menu, and then select the SSISDB database on the SQL databases page.
  3. On the Overview page for your database, review the fully qualified server name. To see the Click to copy option, hover over the server name.
  4. If you forget your Azure SQL Database server login information, navigate to the SQL Database server page to view the server admin name. You can reset the password if necessary.

Connect to the SSISDB database

Use SQL Server Management Studio to establish a connection to the SSIS Catalog on your Azure SQL Database server.

  1. Open SQL Server Management Studio.
  2. In the Connect to Server dialog box, enter the following information:TABLE 1SettingSuggested valueMore infoServer typeDatabase engineThis value is required.Server nameThe fully qualified server nameIf you’re connecting to an Azure SQL Database server, the name is in this format: <server_name>.database.windows.net.AuthenticationSQL Server AuthenticationWith SQL Server authentication, you can connect to SQL Server or to Azure SQL Database. If you’re connecting to an Azure SQL Database server, you can’t use Windows authentication.LoginThe server admin accountThis account is the account that you specified when you created the server.PasswordThe password for your server admin accountThis password is the password that you specified when you created the server.
  3. Click Connect. The Object Explorer window opens in SSMS.
  4. In Object Explorer, expand Integration Services Catalogs and then expand SSISDB to view the objects in the SSIS Catalog database.

Run a package

Run the following Transact-SQL code to run an SSIS package.

  1. In SSMS, open a new query window and paste the following code. (This code is the code generated by the Script option in the Execute Package dialog box in SSMS.)
  2. Update the parameter values in the catalog.create_execution stored procedure for your system.
  3. Make sure that SSISDB is the current database.
  4. Run the script.
  5. In Object Explorer, refresh the contents of SSISDB if necessary and check for the project that you deployed.

SQLCopy

Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Package.dtsx',
    @execution_id=@execution_id OUTPUT,
    @folder_name=N'Deployed Projects',
	  @project_name=N'Integration Services Project1',
  	@use32bitruntime=False,
	  @reference_id=Null
Select @execution_id
DECLARE @var0 smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,
    @object_type=50,
	  @parameter_name=N'LOGGING_LEVEL',
	  @parameter_value=@var0
EXEC [SSISDB].[catalog].[start_execution] @execution_id
GO

Reference

https://docs.microsoft.com/en-us/sql/integration-services/ssis-quickstart-run-tsql-ssms?view=sql-server-ver15

How to run ssis package in asp.net core application?

You can package the assembly into a nuget package, Create a Lib folder inside your solution to hold the nuget package, then, create a nuget.config file to set the package sources to include the Lib folder inside your solution.

The following links contains more details about creating nuget package and hosting it locally:

Alternate Method – 1

To run SSIS package you need below DLLs in the code

  1. Microsoft.SqlServer.ManagedDTS.dll
  2. Microsoft.SqlServer.PipelineHost.dll
  3. Microsoft.SqlServer.DTSRuntimeWrap.dll
  4. Microsoft.SqlServer.DTSPipelineWrap.dll

It is easy to add DLLs in MVC projects, however in asp.net core it needs to be in form of a Nuget package.

So nuget package can be easily created using nuget package explorer. Below is the link

https://docs.nuget.org/create/using-a-gui-to-build-packages

In the nuget package explorer add a lib folder, inside that add a .net folder dnxcore50 and add the above DLLs. Click on tools analyse package and save the nuget.

In the visual studio 2015 solution, you can refer local packages. Tools – Nuget Package Manager – Package Manager Settings – Package source add the local package path.

After which you will be able to add the nuget package using nuget package manager and select local package as source

"dependencies": {
"Microsoft.AspNet.Hosting": "1.0.0-rc1-final",
"Microsoft.NETCore.Portable.Compatibility": "1.0.1-rc2-24027",
"SSISPackage": "1.0.0"
 }

"frameworks": {
"netcoreapp1.0": {
  "imports": [
    "dotnet5.6",
    "portable-net45+win8",
    "dnxcore"
  ]
  }
}

After which you will be able to use code to run SSIS package similar to MVC projects.

Application app = new Application();
        Package package = null;
        try
        {
            package = app.LoadPackage(@"C:\Files\Package.dtsx", null);
            Variables vars = package.Variables;
            vars["status"].Value = "ACTIVE";

            DTSExecResult results = package.Execute();

        }
        catch
        {
            return false;

        }
        finally
        {
            package.Dispose();
            package = null;
        }

Alternate Method – 2

You may now reference the dlls ( one from each) directly in your .net core project from the below locations to run ssis packages now

C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.SqlServer.ManagedDTS
C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.SqlServer.PipelineHost
C:\Windows\Microsoft.NET\assembly\GAC_64\Microsoft.SqlServer.DTSRuntimeWrap
C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.SqlServer.DTSPipelineWrap

you no longer need to create a nuget package

Reference

https://stackoverflow.com/questions/38791987/how-to-run-ssis-package-in-asp-net-core-application