Category Archives : Database

17

May

Announcing SQL Advanced Threat Protection (ATP) and SQL Vulnerability Assessment general availability

We are delighted to announce the general availability of SQL Vulnerability Assessment for Azure SQL Database! SQL Vulnerability Assessment (VA) provides you a one-stop-shop to discover, track and remediate potential database vulnerabilities. It helps give you visibility into your security state, and includes actionable steps to investigate, manage and resolve security issues, and enhance your database fortifications. VA is available for Azure SQL Database customers as well as for on-premises SQL Server customers via SSMS.

If you have data privacy requirements or need to comply with data protection regulations like the European Union General Data Protection Regulation (EU GDPR), then VA is your built-in solution to simplify these processes and monitor your database protection status. For dynamic database environments where changes are frequent and hard to track, VA is invaluable in detecting the settings that can leave your database vulnerable to attack.

New SQL Advanced Threat Protection (ATP)

VA is being released to general availability (GA) as part of a new security package for your Azure SQL Database, called SQL Advanced Threat Protection (ATP). ATP provides a single go-to location for discovering, classifying and protecting sensitive data, managing your database vulnerabilities, and detecting anomalous activities that could indicate a

10

May

Extract management insights from SQL Data Warehouse with SQL Operations Studio

SQL Operations Studio can be leveraged with Azure SQL Data Warehouse (SQL DW) to create rich customizable dashboard widgets surfacing insights to your data warehouse. This unlocks key scenarios around managing and tuning your data warehouse to ensure it is optimized for consistent performance. Previously, developers had to manually and continuously execute complex DMV queries to extract insights from their data warehouse. This leads to a repetitious process when following development and tuning best practices with SQL DW. Now with SQL Operations Studio, customized insight widgets can be embedded directly within the query tool enabling you to seamlessly monitor and troubleshoot issues with your data warehouse.

The following widgets can be generated by using the provided T-SQL monitoring scripts within SQL Operations Studio for common data warehouse insights.

Data Skew

Detect data skew across distributions to help identify and troubleshoot query performance issues:

Columnstore health and statistics

Leverage views to help maximize columnstore row group quality and ensure table statistics are up to date for optimal query performance:

User Activity

Identify and understand workload patterns through active sessions queries, queued queries, loads, and backups:

Resource Bottlenecks

Ensure adequate resources are allocated such as memory and TempDB:

09

May

Announcing new Async Java SDK for Azure #CosmosDB
Announcing new Async Java SDK for Azure #CosmosDB

We’re excited to announce a new asynchronous Java SDK for Cosmos DB’s SQL API open sourced on GitHub. This SDK leverages the popular RxJava library to add a new async API surface area for composing event-based programs with observable sequences. It also features an improved user experience and is also lighter weight than our previous synchronous Java SDK (yielding a 2x performance improvement on the client-side)!

You can add the library from Maven using:

<dependency> <groupId>com.microsoft.azure</groupId> <artifactId>azure-cosmosdb</artifactId> <version>1.0.1</version> </dependency> Connect to Cosmos DB

The new SDK uses convenient builder pattern to specify connectivity options:

asyncClient = new AsyncDocumentClient.Builder() .withServiceEndpoint(HOST) .withMasterKey(MASTER_KEY) .withConnectionPolicy(ConnectionPolicy.GetDefault()) .withConsistencyLevel(ConsistencyLevel.Eventual) .build(); Insert an item

To execute and coordinate Cosmos DB data operations asynchronously, and get the results you use observables:

Document doc = new Document(String.format(“{ ‘id’: ‘doc%d’, ‘counter’: ‘%d’}”, 1, 1)); Observable<ResourceResponse<Document>> createDocumentObservable = asyncClient.createDocument(collectionLink, doc, null, false); createDocumentObservable .single() // we know there will be one response .subscribe(documentResourceResponse -> { System.out.println(documentResourceResponse.getRequestCharge()); });

Note that the createDocument request will be issued only once .subscribe is called on the corresponding observable result.

Query

In Cosmos DB queries can return multiple pages of data. To efficiently read all the pages, simply subscribe and

07

May

Sharing provisioned throughput across multiple containers in Azure #CosmosDB

Azure Cosmos DB is Microsoft’s globally distributed, multi-model database service for mission-critical applications. Azure Cosmos DB provides turnkey global distribution, elastic scaling of throughput and storage worldwide, single-digit millisecond latencies at the 99th percentile, five well-defined consistency models and guaranteed high availability, all backed by industry-leading comprehensive SLAs. Azure Cosmos DB automatically indexes all your data without requiring you to deal with schema or index management. It is a multi-model service that supports document, key-value, graph and column-family data models and exposes wire-compatible APIs for MongoDB, Apache Cassandra and Apache Gremlin, in addition to a native SQL dialect. As a natively born in the cloud service, Azure Cosmos DB is carefully engineered with multi-tenancy and global distribution from the ground up.

Unlimited flexibility, elasticity and scalability

Azure Cosmos DB offers a provisioned throughput model, where you configure throughput that you need and pay only for the throughput that you have reserved. As a multi-model database, depending on your choice of data model and API, Azure Cosmos DB allows you to create collections, tables or graphs – they all map internally to a Cosmos DB container. Azure Cosmos DB will transparently, automatically and elastically scale throughput and storage of its containers,

01

May

Explore SaaS analytics with Azure SQL Database, SQL Data Warehouse, Data Factory, and Power BI

Continuing our series of tutorials on SaaS application patterns with SQL Database, we are delighted to announce an additional cross tenant analytics tutorial. This new tutorial shows how to extract and load tenant data into Azure SQL Data Warehouse (SQL DW) using Azure Data Factory (ADF) and then analyze it in Power BI.

 

In this tutorial, ADF is used to orchestrate data movement from tenant databases into a SQL Data Warehouse. Parameterized ADF V2 (preview) pipelines are defined to iterate across tenant databases, loading data from multiple databases in parallel. To accelerate loading, ADF stages extracted data in blob files and then uses PolyBase to load into SQL DW. Staging the data and enabling PolyBase are simple check-box operations in ADF.

The tutorial uses an Extract, Load and Transform (ELT) pattern – once data is loaded into staging tables in SQL DW, ADF invokes a stored procedure to upsert the data into star-schema tables, ready for query. Power BI is then used to visualize the data and extract insights that in the tutorial scenario can help the ISV improve their ticket selling application and business.

Get started

To get started check out the analytics tutorial which provides step-by-step

30

Apr

Region expansion for the next generation of SQL Data Warehouse

Azure SQL Data Warehouse (SQL DW) is a fast, flexible and secure, cloud data warehouse tuned for running complex queries fast and across petabytes of data. Continuing to deliver on this promise, we have announced the general availability of the next generation of SQL DW which includes an average of five times the performance boost, five times the increase in compute scalability, and four times the increase in concurrency. The release of Azure SQL DW Compute Optimized Gen2 tier comes with an expansion of 14 additional regions bringing the global region footprint of SQL DW Gen2 to 20 surpassing all other major cloud providers. The following regions are available:

Australia East

Australia Southeast

Canada Central

Central India

Central US

East Asia

East US

East US 2

Japan East

Japan West

Korea South

North Central US

North Europe

South Central US

South India

Southeast Asia

UK South

West Europe

West US

West US 2

With more global regions than any other

25

Apr

Azure #CosmosDB: Secure, private, compliant
Azure #CosmosDB: Secure, private, compliant

Azure Cosmos DB is Microsoft’s globally distributed, multi-model database. Azure Cosmos DB enables you to elastically and independently scale throughput and storage across any number of Azure’s geographic regions with a single click. It offers throughput, latency, availability, and consistency guarantees with comprehensive service level agreements (SLAs), a feature that no other database service can offer.

A database that holds sensitive data across international borders must meet high standards for security, privacy, and compliance. Additionally, the cloud service provider must anticipate and be ready for new standards, such as the General Data Protection Regulation (GDPR), which will soon govern the collection and use of EU resident’s data. Microsoft has pledged that Azure services will be GDPR compliant by the May 25 implementation date.

Privacy

Microsoft’s cloud privacy policies state that we will use your customer data only to provide the services we have agreed upon, and for purposes that are compatible with providing those services. We do not share your data with our advertiser-supported services, nor do we mine it for marketing or advertising. 

Encryption

Azure Cosmos DB also implements stringent security practices. All the documents, attachments and backups stored in Azure Cosmos DB are encrypted at rest and

25

Apr

Accelerate real-time big data analytics with Spark connector for Microsoft SQL Databases

Apache Spark is a unified analytics engine for large-scale data processing. Today, you can use the built-in JDBC connector to connect to Azure SQL Database or SQL Server to read or write data from Spark jobs.

The Spark connector for Azure SQL Database and SQL Server enables SQL databases, including Azure SQL Database and SQL Server, to act as input data source or output data sink for Spark jobs. It allows you to utilize real-time transactional data in big data analytics and persist results for adhoc queries or reporting.

Compared to the built-in Spark connector, this connector provides the ability to bulk insert data into SQL databases. It can outperform row-by-row insertion with 10x to 20x faster performance. The Spark connector for Azure SQL Databases and SQL Server also supports Azure Active Directory authentication. It allows you to securely connect to your Azure SQL database from Azure Databricks using your AAD account. The Spark connector also provides similar interfaces with the built-in JDBC connector and is easy to migrate your existing Spark jobs to use this new connector.

The Spark connector for Azure SQL Database and SQL Server utilizes the Microsoft JDBC Driver for SQL Server to move data between

17

Apr

Transparent Data Encryption with customer managed keys in Azure SQL Database generally available

Today, we are excited to announce the general availability of Transparent Data Encryption (TDE) with Bring Your Own Key (BYOK) support for Azure SQL Database and Azure SQL Data Warehouse. This is one of the most frequently requested features by enterprise customers looking to protect sensitive data and meet regulatory or compliance obligations that require implementation of specific key management controls. TDE with BYOK support is offered in addition to TDE with service managed keys, which is enabled by default on all new Azure SQL Databases.

TDE with BYOK support uses Azure Key Vault, which provides highly available and scalable secure storage for RSA cryptographic keys backed by FIPS 140-2 Level 2 validated Hardware Security Modules (HSMs). Key Vault streamlines the key management process and enables customers to maintain full control of encryption keys and allows them to manage and audit key access.

Customers can generate and import their RSA key to Azure Key Vault and use it with Azure SQL Database and Azure SQL Data Warehouse TDE with BYOK support. Azure SQL Database handles the encryption and decryption of data stored in databases, log files, and backups in a fully transparent fashion by using a symmetric Database Encryption Key

12

Apr

Enhanced capabilities to monitor, manage, and integrate SQL Data Warehouse in the Azure Portal

Azure SQL Data Warehouse (SQL DW) continues to introduce updates to the Azure portal to provide a seamless user experience when monitoring, managing, and integrating your data warehouse.

Support for Azure Monitor metrics

SQL DW now supports Azure Monitor which is a built-in monitoring service that consumes performance and health telemetry for your data warehouse. Azure monitor not only enables you to monitor your data warehouse within the Azure portal, but its tight integration between Azure services also enables you to monitor your entire data analytics solution within a single interface. For this release, data warehouse metrics have been enabled to enables you to identify performance bottlenecks and user activity:

Successful/Failed/Blocked by firewall connections CPU IO DWU Limit DWU Percentage DWU used

These metrics now have a one-minute frequency for near real-time visibility into resource bottlenecks of your data warehouse. There is a default retention period of 90 days for all data warehouse metrics with Azure Monitor.

Configure metric charts in the Azure monitor service through the Azure Portal or programmatically query for metrics via PowerShell or REST:

Pin configured charts for your data warehouse through Azure dashboards:

Safely manage costs by pausing

The pause feature for SQL