Data loading into Azure SQL Data Warehouse

Azure SQL Data Warehouse (SQL DW) is a new platform-as-a service (PaaS) that distributes workloads across multiple compute resources, called massively parallel processing (MPP).  Loading data into a MPP data warehouse requires a different approach, or mindset, than traditional methods of loading data into a SMP data warehouse.

To help you with understanding how best to load data into SQL DW, Microsoft has released an excellent white paper by Martin Lee, John Hoang, and Joe Sack.  It describes the SQL DW architecture and explores several loading techniques to help you reach maximum data-loading throughput and identify the scenarios that best suit each of these techniques.

Check it out: Azure SQL Data Warehouse loading patterns and strategies

Posted in Azure SQL DW, Data warehouse, SQLServerPedia Syndication | 1 Comment

Microsoft Azure Government

I’m sure you are aware of Microsoft Azure, but are you aware there is special version of Azure for U.S. governments?

Microsoft Azure Government is a cloud computing service for federal, state, local and tribal U.S. governments.  It was generally available in December 2014 after a year in preview.  To see the Azure services available for the government, see the services available by region.

By default, Azure Government ensures that all data stays within the U.S. and within data centers and networks that are physically isolated from the rest of Microsoft’s cloud computing solution, operated by screened U.S. persons.  It’s in compliance with FedRAMP, a mandatory government-wide program that prescribes a standardized way to carry out security assessments for cloud services.  It also supports a wide range of other compliance standards, including Health Insurance Portability and Accountability Act (HIPAA), Department of Defense Enterprise Cloud Service Broker (ECSB), and the FBI Criminal Justice Information Services (CJIS), which is meant to keep safe fingerprint and background-check data that has to be shared with other agencies.

Microsoft also offers government versions of Office 365, which is hosted in a dedicated “cloud community” reserved only for government customers.  There is also a Microsoft Dynamics CRM Online Government.

Also just announced:
Two new physically isolated regions, which will become available later this year, are part of Azure Government and are meant to host Department of Defense (DoD) data.  These regions will meet the Pentagon’s Defense Information Systems Agency (DISA) Impact level 5 restrictions and are, according to Microsoft, “architected to meet stringent DoD security controls and compliance requirements.”

Level 5 data includes controlled unclassified information.  Classified information (up to ‘secret’) can only be stored on systems that fall under the level 6 classification.  To gain level 5 authorization, cloud providers have to ensure that all workloads run (and all data is stored) on dedicated hardware that is physically separated from non-DoD users.

In addition to its new work with the DoD, Microsoft is also expanding its support for FedRAMP, the standard that governs which cloud services federal agencies are able to use.  The company today announced that Azure Government has been selected to participate in a new pilot that will allow agencies to process high-impact data — that is, data that could have a negative impact on organizational operations, assets or individuals.  Until now, FedRAMP only authorized the use of moderate impact workloads.  Microsoft says it expects all the necessary papers for this higher authorization will be in place by the end of this month.

Azure Government is also on track to receive DISA Level 4 authorization soon.

More info:

Microsoft Cloud for Government

Posted in Azure, SQLServerPedia Syndication | Leave a comment

SQL Server on Linux!

Looks outside: pigs are flying!

In an announcement yesterday, SQL Server will be made available on Linux.  The private preview of SQL Server on Linux is available now, and Microsoft is targeting availability in mid-2017.  Microsoft will offer both on-premises and cloud versions of the product (via Linux VMs).  It will include the Stretch Database capabilities that Microsoft is building into SQL Server 2016.  Right now, SQL Server on Linux is available on Ubuntu or as a Docker image, and Microsoft intends to support Red Hat Enterprise Linux as well as other platforms over time.  The private preview is based on SQL Server 2016.

Considering how anti-Linux Microsoft was a few years ago, this is very surprising, but not so surprising if you have followed the changes over the past two years as Microsoft has come to embrace Linux and other open source technologies and tools (see Microsoft Loves Linux).

To find out more about SQL Server on Linux, you can sign up to get regular updates and provide input to the team, as well as apply to the private preview.

More info:

Microsoft is porting SQL Server to Linux

8 no-bull reasons why SQL Server on Linux is huge for Microsoft

Posted in SQL Server, SQLServerPedia Syndication | 1 Comment

Cross-database queries in Azure SQL Database

A limitation with Azure SQL database has been its inability to do cross-database SQL queries.  This has changed with the introduction of elastic database queries, now in preview.  However, it’s not as easy as on-prem SQL Server, where you can just use the three-part name syntax DatabaseName.SchemaName.TableName.  Instead, you have to define remote tables (tables outside your current database), which is similar to how PolyBase works for those of you familiar with PolyBase.

Here is sample code that, from within database AdventureWorksDB, selects data from table Customers in database Northwind:

--Within database AdventureWorksDB, will select data from table Customers in database Northwind

--Create database scoped master key and credentials

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

--Needs to be username and password to access SQL database

CREATE DATABASE SCOPED CREDENTIAL jscredential WITH IDENTITY = '<username>', SECRET = '<password>';

--Define external data source

CREATE EXTERNAL DATA SOURCE RemoteNorthwindDB WITH 
           (TYPE = RDBMS,
            LOCATION = '<servername>.database.windows.net',
            DATABASE_NAME = 'Northwind',  
            CREDENTIAL = jscredential 
            );

--Show created external data sources

select * from sys.external_data_sources; 

--Create external (remote) table.  The schema provided in your external table definition needs to match the schema of the tables in the remote database where the actual data is stored. 

CREATE EXTERNAL TABLE [NorthwindCustomers]( --what we want to call this table locally
	[CustomerID] [nchar](5) NOT NULL,
	[CompanyName] [nvarchar](40) NOT NULL,
	[ContactName] [nvarchar](30) NULL,
	[ContactTitle] [nvarchar](30) NULL,
	[Address] [nvarchar](60) NULL,
	[City] [nvarchar](15) NULL,
	[Region] [nvarchar](15) NULL,
	[PostalCode] [nvarchar](10) NULL,
	[Country] [nvarchar](15) NULL,
	[Phone] [nvarchar](24) NULL,
	[Fax] [nvarchar](24) NULL
)    
WITH
(
  DATA_SOURCE = RemoteNorthwindDB,
  SCHEMA_NAME = 'dbo', --schema name of remote table
  OBJECT_NAME = 'Customers' --table name of remote table
);

--Show created external tables

select * from sys.external_tables; 

--You can now select data from this external/remote table, including joining it to local tables

select * from NorthwindCustomers

--Cleanup

DROP EXTERNAL TABLE NorthwindCustomers;

DROP EXTERNAL DATA SOURCE RemoteNorthwindDB;

DROP DATABASE SCOPED CREDENTIAL jscredential;  

DROP MASTER KEY;  

More info:

Elastic database query for cross-database queries (vertical partitioning)

Posted in Azure SQL Database | 5 Comments

HP Superdome X for high-end OLTP/DW

No, Superdome X is not the name of the stadium where they played in the last Super Bowl.  Rather, Superdome X is HP’s top of the line server running Windows Server 2012 R2 and SQL Server 2014.  It can handle up to 288-cores and 24TB of memory!  It use the HPE 3PAR StoreServ 7440c storage array which consists of 224 SSD drives (480GB/drive) for a total of 107TB of disk space.

It set the highest TPC-H metric for 10TB SQL Server 2014 workloads (see HPE Integrity Superdome X achieves two world records on TPC-H benchmark).  It is perfect for high-end OLTP and medium-sized mixed workloads.  It is a mixed workload system, which is a system that will support running two independent workloads (OLTP and data warehouse) concurrently on the same platform on two physical partitions.

The Superdome X platform is ideally suited for solving the scalability and performance needs of these mixed workload environments, allowing a single hardware platform to be logically partitioned to support multiple environments and workloads with dynamic adjustments to the processor, memory, and storage needs of each environment over time.

It’s efficiently bladed form factor allows you to start small and grow as your business demands increase.  As your databases grow or you need to support new applications, or when your application usage increases, you can efficiently scale up your environment by adding blades.  You can start as small as a 2-socket configuration and scale up all the way to 16 sockets.

More info:

HPE Reference Architecture for Microsoft SQL Server 2014 mixed workloads on HPE Integrity Superdome X with HPE 3PAR StoreServ 7440c Storage Array

Video When To Run Mission Critical Applications on Superdome X

Posted in Appliance, SQLServerPedia Syndication | 1 Comment

Azure SQL Database security

Life we be so much easier if we could just trust everyone, but since we can’t we need solid security for our databases.  Azure SQL Database has many security features to make you sleep well at night:

More info:

Securing your SQL Database

Security Center for SQL Server Database Engine and Azure SQL Database

Security and Azure SQL Database technical white paper

Azure SQL Database security guidelines and limitations

Microsoft Azure SQL Database provides unparalleled data security in the cloud with Always Encrypted

 

Posted in Azure SQL Database, SQLServerPedia Syndication | 2 Comments

Azure SQL Database monitoring

Even though an Azure SQL Database stores all data on the Azure cloud, it does not mean that your options for managing and monitoring the databases are limited compared to on-prem databases.  In fact, the options available are very similar to on-prem, including 3rd-party products that support Azure SQL databases.  Here are the various options:

Posted in Azure SQL Database, SQLServerPedia Syndication | Leave a comment

Scaling Azure SQL Database

One of the advantages Azure SQL Database has over on-prem SQL Server is the ease in which it can scale.  I’ll discuss the various options for horizontal scaling, vertical scaling, and other similar features.

Horizontal scaling refers to adding or removing databases in order to adjust capacity or overall performance. This is also called “scaling out”.  Sharding, in which data is partitioned across a collection of identically structured databases, is a common way to implement horizontal scaling.

Vertical scaling refers to increasing or decreasing the performance level of an individual database—this is also known as “scaling up.”

Elastic Database features enables you to use the virtually unlimited database resources of Azure SQL Database to create solutions for transactional workloads, and especially Software as a Service (SaaS) applications.  Elastic Database features are composed of the following:

You can change the service tier and performance level of your SQL database with the Azure portal, PowerShell (using the Set-AzureSqlDatabase cmdlet), the Service Management REST API (using the Update Database command), or Transact-SQL (via the ALTER DATABASE statement).  You can use DMVs to monitor the progress of the upgrade operation for a database.  This allows you to easily scale up or down a database, and it will remain online and available during the entire operation with no downtime.  This is vertical scaling.  See Change the service tier and performance level (pricing tier) of a SQL database.

Another feature is called Stretch Databases, to let your on-prem SQL Server database hold just the core data, with old/cold data that continues to grow sidelined transparently in Azure SQL Database.  This is a feature only available in SQL Server 2016.  See Stretch Database.

More info:

Elastic Database features overview

Video Azure SQL Database Elastic Scale

Video Elastic for SQL – shards, pools, stretch

SQL Azure Performance Benchmarking

Azure SQL Database DTU Calculator

Posted in Azure SQL Database, SQLServerPedia Syndication | 3 Comments

Microsoft Azure Stack in Preview!

Microsoft made available the first technical preview of its new Microsoft Azure Stack offering today.  It was announced last week.  Azure Stack brings the cloud model of computing to every datacenter – your own private cloud.  Azure Stack is a new hybrid cloud platform product that enables organizations to deliver Azure services from their own datacenter in a way that is consistent with Azure.  You can download it now, but first check out the hardware and deployment requirements.  Once installed, check out the Tools and PaaS services for Azure Stack which will be updated frequently.  If you run into any issues and need help, or if you’d like to provide feedback or make requests, visit the Azure Stack forum.

Also released is a whitepaper providing more information on key Azure Stack concepts and capabilities that should help you gain a much richer understanding of Microsoft’s approach.  Check out the full documentation.

On February 3, Mark Russinovich and Jeffrey Snover demonstrated Azure Stack in a webcast, followed by a question and answer session.  Also check out An early look at Azure Stack and what it means for IT, with Jeffrey Snover.

Azure Stack essentially is Microsoft’s better bridge to using its cloud services, both the platform-as-a-service (PaaS) and infrastructure-as-a-service (IaaS) kinds.  It uses the same code as Azure.  Scripting tools for management, such as PowerShell and command-line interfaces, will work across Microsoft’s Azure cloud computing services as well as local datacenter implementations of Azure Stack.  This allows a company to build a private cloud, and then easily migrate parts to a public cloud, allowing you to have a true hybrid cloud platform.  Microsoft is the only company to have both a private and public cloud option.

General availability is scheduled for Q4 of this year, but some services will only be in preview at that time:

azure-stack-services-100642771-orig (1)

More info:

First Microsoft Azure Stack Preview Sees Daylight

Microsoft readies first technical preview of Azure Stack hybrid-cloud bundle

Microsoft Azure Stack: Hardware requirements

Posted in Azure, SQLServerPedia Syndication | Leave a comment

Migrate from on-prem SQL server to Azure SQL Database

This blog describes the various approaches you can use to migrate an on-premises SQL Server database to Azure SQL Database.

In this migration process you migrate both your schema and your data from the SQL Server database in your current environment into SQL Database, provided the existing database passes compatibility tests.  Fortunately with SQL Database Version 12 (V12), there are very few remaining compatibility issues other than server-level and cross-database operations (see Azure SQL Database Transact-SQL differencesAzure SQL Database General Limitations and Guidelines, and Azure SQL Database resource limits).  Databases and applications that rely on partially or unsupported functions will need some re-engineering to fix these incompatibilities before the SQL Server database can be migrated.

If not done already, create a server to host the database you wish to migrate, and then set up a firewall rule for it (see SQL Database tutorial: Create a SQL database in minutes using sample data and the Azure portal).

Note: If you are fairly confident your database is compatible and it is not large a large database, you can skip testing for compatibility and just use the SSMS Migration Wizard.  If this wizard detects compatibility issues, errors will be displayed to the screen and the migration will not continue – you can then proceed to the steps below to test and fix the compatibility issues.  Or, you can run the SQL Azure Migration Wizard and it can fix most compatibility issues (the few that remain) while creating the SQL Database, so this could be the only tool you need to use since it also migrates the data.

UPDATE: The SQL Server 2016 Upgrade Advisor Preview has an option to migrate from on-prem SQL Server to SQL Database (schema and data).

Test compatibility

First you will want to test for SQL Database compatibility issues before you start the migration process:

Fix compatibility issues

If there are any compatibility issues, you will need to fix them before proceeding with the migration by using:

  • SQL Azure Migration Wizard, available on CodePlex, will generate a T-SQL script from the incompatible source database that is then transformed to make it compatible with the SQL Database.  It will then connect to the SQL Database and execute the script on the target database.  It also has options to analyze trace files to determine compatibility issues and the script can be generated with schema only or can include data in BCP format.  It will also process the body of functions or stored procedures which is normally excluded from validation performed by SSDT (see next option), so it may find issues that might not otherwise be reported by SSDT alone.  See the wizard in action via the video SQL Database Migration Wizard
  • SQL Server Data Tools for Visual Studio (SSDT).  Import the database schema into a Visual Studio database project for analysis.  Specify the target platform for the project as SQL Database V12 and then build the project.  If the build is successful, the database is compatible.  If the build fails, resolve the errors in SSDT.  Once the project builds successfully, you can publish it back as a copy of the source database and then use the data compare feature in SSDT to copy the data from the source database to the Azure SQL V12 compatible database.  You can then migrate this updated database
  • SQL Server Management Studio (SSMS).  Use SSMS to fix compatibility issues using various Transact-SQL commands, such as ALTER DATABASE

Migrate database

And lastly you will migrate the compatible SQL Server database to SQL Database.  There are several migration methods for various scenarios.  The method you choose depends upon your tolerance for downtime, the size and complexity of your SQL Server database, and your connectivity to the Microsoft Azure cloud.

To migrate schema and data from on-prem SQL Server (or Azure SQL Database) to Azure SQL Data Warehouse, use the Data Warehouse Migration Utility (Preview).

There is also a SQL Server Migration Assistant for Oracle, Sybase, DB2, MySQL, and Microsoft Access to migrate from SQL Server to Azure SQL Database.

More info:

Migration cookbook now available for the latest Azure SQL Database Update (V12)

Migrating a SQL Server database to Azure SQL Database

How to Migrate from On-Premises to Azure SQL Database

Migrating an on premise SQL Server Database to Azure

Free ebook: Microsoft Azure Essentials Migrating SQL Server Databases to Azure

Posted in Azure SQL Database, SQL Server, SQLServerPedia Syndication | Leave a comment