Tag: SQL server

If you want to run SSIS Packages from SQL Server Agent, the job might fail because of security context. Here are the steps that can help solve this problem;

Creating a credential to be used by proxy

USE MASTER
GO
--Drop the credential if it is already existing
IF EXISTS (SELECT 1 FROM sys.credentials WHERE name = N'SSISProxyCredentials')
BEGIN
DROP CREDENTIAL [SSISProxyCredentials]
END
GO
CREATE CREDENTIAL [SSISProxyCredentials]
WITH IDENTITY = N'server\ServiceProxyAll',
SECRET = N'secret'
GO

Creating a proxy account, Drop the credential if it is already existing

USE msdb
GO
--Drop the proxy if it is already existing
IF EXISTS (SELECT 1 FROM msdb.dbo.sysproxies WHERE name = N'SSISProxyDemo')
BEGIN
EXEC dbo.sp_delete_proxy
@proxy_name = N'SSISProxyDemo'
END
GO

Create a proxy and use the same credential as created above

--Create a proxy and use the same credential as created above
EXEC msdb.dbo.sp_add_proxy
@proxy_name = N'SSISProxyDemo',
@credential_name=N'SSISProxyCredentials',
@enabled=1
GO

To enable or disable you can use this command

EXEC msdb.dbo.sp_update_proxy
@proxy_name = N'SSISProxyDemo',
@enabled = 1 --@enabled = 0
GO

Granting proxy account to SQL Server Agent Sub-systems

USE msdb
GO
--You can view all the sub systems of SQL Server Agent with this command
--You can notice for SSIS Subsystem id is 11
EXEC sp_enum_sqlagent_subsystems
GO

Grant created proxy to SQL Agent subsystem, You can grant created proxy to as many as available subsystems

EXEC msdb.dbo.sp_grant_proxy_to_subsystem
@proxy_name=N'SSISProxyDemo',
@subsystem_id=11 --subsystem 11 is for SSIS as you can see in the above image
GO

View all the proxies granted to all the subsystems

EXEC dbo.sp_enum_proxy_for_subsystem

Granting proxy access to security principals

USE msdb
GO
--Grant proxy account access to security principals that could be
--either login name or fixed server role or msdb role
--Please note, Members of sysadmin server role are allowed to use any proxy
EXEC msdb.dbo.sp_grant_login_to_proxy
@proxy_name=N'SSISProxyDemo'
,@login_name=N'LABSVR01\ServiceProxyAll'
--,@fixed_server_role=N''
--,@msdb_role=N''
GO

View logins provided access to proxies

EXEC dbo.sp_enum_login_for_proxy
GO

Follow this article setting up proxy account using UI and setting up SSIS environment to use Azure Storage.

SQL Server Agent configuration to run SSIS Packages

Reference

https://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/

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.