terça-feira, 20 de março de 2018

[From Technet] SSMS 17.6 is now available: Managed Instance and many bug fixes

This post is authored by Alan Yu, Program Manager, SQL Server.

Download SSMS 17.6 and review the Release Notes to get started.

SSMS 17.6 provides support for almost all feature areas on SQL Server 2008 through the latest SQL Server 2017, which is now generally available.

In addition to enhancements and bug fixes, SSMS 17.6 comes with several exciting new features:

  • Added more support for Azure SQL Database Managed Instance.
  • Fixed a key performance issue in SMO where scripting tables on SQL Server 2016 took 30 seconds, but now take less than one second.
  • Object Explorer: Added settings to allow users not to force brackets around names when dragging and dropping from Object Explorer to Query Window.
  • Data Classification: Improvements and bug fixes.

SSMS 17.6 also includes key bug fixes to Always On, SMO, and Database mail, which can be found in the Release Notes.

Azure SQL Database Managed Instance

Azure SQL Database Managed Instance (preview) is a new flavor of Azure SQL Database, providing near 100 percent compatibility with SQL Server on-premises, a native virtual network (VNet) implementation that addresses common security concerns, and a business model favorable for on-premises SQL Server customers.

Source from Managed Instance Documentation

In SSMS, there is now support for common management scenarios such as:

  • Create and alter databases
  • Backup and restore databases
  • Importing, exporting, extracting and publishing Data-tier Applications
  • Viewing and altering Server properties
  • Full Object Explorer support
  • Scripting database objects
  • Support for SQL Agent jobs
  • Support for Linked Servers

To learn more about Managed Instance, check this Azure blog post.

Bug fixes

In SSMS 17.6, there were many bug fixes across Data Classification, Always On, Setup, SMO, Object Explorer, and Database mail.

To highlight a few of these:


  • Fixed performance issue where scripting tables on SQL2016 and above could take up to 30 seconds (now, it’s down to less than one second)

Data Classification:

  • Fixed an issue in Data Classification which was causing newly added classification to be displayed with stale “information type” and “sensitivity label”.
  • Fixed an issue where Data Classification was not working when targeting a server set to a case-sensitive collation.

Always On

  • Fixed an issue in AG Show Dashboard where clicking on “Collect Latency Data” could result in an error when the server was set to a case-sensitive collation.
  • Fixed an issue where SSMS was incorrectly reporting an AG as Distributed when the Cluster service shuts down.
  • Fixed an issue when creating AG using create Availability Group dialog the ReadOnlyRoutingUrl is required.

To learn more about other bug fixes covered in this release, check the Release Notes.

Call to Action

Try it out and let us know what you think! You can message us on our twitter @SQLDataTools or reach out to Ken Van Hyning’s twitter @SQLToolsGuy.

from SQL Server Blog http://ift.tt/2u7tDYs

quinta-feira, 15 de março de 2018

[From Technet] Complying with General Data Protection Regulation (GDPR) on Microsoft Data Platform technologies

This post is authored by Frederico Pravatta Rezende, Senior Product Marketing Manager, CADD & AI.

Is your organization prepared for the General Data Protection Regulation (GDPR)?

If your company does business in Europe, you’ll need to be aware of this new privacy law, which is set to bolster data protections for individuals living within the European Union (EU) starting on May 25, 2018.

The GDPR introduces several specific rights for EU residents, such as the right to access their personal data, correct inaccuracies in their data, erase data, object to the processing of their data, and to obtain a copy of their data. It aims to ensure that personal data is protected no matter where it’s sent, processed, or stored.

For your organization, this means taking a fresh look at how you control exposure to personal data, employ security mechanisms to protect personal data, detect and notify supervisory authorities of breaches within a timely manner, keep records of data-processing activities, and document risks and security measures.

The cost of non-compliance is high, reaching up to €20 million or 4 percent of the worldwide annual revenue of the prior fiscal year, whichever is higher.

Microsoft is committed to the GDPR, and we support you in complying with its obligations. We’ve addressed data privacy across several of our products and services, many of which are based on Microsoft SQL-based technologies— including SQL Server on-premises, SQL Server on Azure Virtual Machines, Azure SQL Database, Microsoft Analytics Platform System, and Azure SQL Data Warehouse.

Microsoft recommends a four-step process to guide you through the journey to comply with GDPR:

  1. First, you’ll need to discover where personal data is located. This involves understanding the attack area and how this sensitive data can be accessed, which allows you to identify the potential gaps in GDPR compliance you’ll need to address;
  2. In the second step, you’ll govern who can access this data and how it’s used. Azure SQL Database Firewall and SQL Server Authentication are just a few of the capabilities available to help with this;
  3. Next, you’ll want to strengthen your protection efforts, reducing risk and minimizing the impact of data. This requires different methods for different data types and scenarios. Microsoft SQL offers several ways to help you with this step.
  4. Lastly, you need to keep the records and create the reports necessary for meeting GDPR obligations. This also includes ensuring transparency with the subjects of this sensitive data.

While your journey to comply with GDPR may seem challenging, Microsoft’s various data platform solutions will help you along the way. Microsoft helps you simplify your privacy journey to expose areas of risk and respond with agility and confidence, leveraging guidance from experts and our partner network.

Start planning your data platform modernization to adhere to GDRP signing up for our March 22nd webinar to learn how you can start your journey with SQL Server 2017 and Azure Data Services.

Register today!

from SQL Server Blog http://ift.tt/2DuSEwb

terça-feira, 6 de março de 2018

[From Technet] February Cloud data and AI services training roundup

Looking to transform your business by improving your on-premises environments, accelerating your move to the cloud, and gaining transformative insights from your data? Here’s your opportunity to learn from the experts and ask the questions that help your organization move forward.

Join us for one or all of these training sessions for a deep dive into a variety of topics, including products like SQL Server 2017, Azure SQL Database, and Azure Cosmos DB, along with Microsoft innovations in artificial intelligence, advanced analytics, and big data.

SQL Server 2017

It’s all about choice. Now, you have the flexibility of leveraging SQL Server 2017’s industry-leading performance and security wherever you like—whether that’s on Windows, Linux, or Docker containers. We’re hosting two training sessions this month to help you learn more about the many exciting features of SQL Server 2017.

Industry-leading performance and security with SQL Server 2017
In this webinar, learn more about innovative SQL Server 2017 features that enhance your applications, analytics, and business intelligence (BI) workloads, including:

  • Automated tuning features such as Adaptive Query Processing and Automatic Plan Correction for faster, more consistent performance.
  • Advanced security features such as encryption at rest and in use, dynamic data masking, and row-level security.
  • The ability to store and query graph data along with your existing SQL Server tables.
  • In-database advanced analytics using R and Python machine-learning services.

Running SQL Server on Linux in Azure, AWS, and Google Cloud Platform
In addition to a comprehensive set of features and flexibility of their preferred platform or programming languages, customers want flexibility when it comes to their choice of underlying infrastructure. This includes both the underlying OS as well as the platform. In this in-depth training, we’ll cover:

  • The different ways you can run your SQL Server 2017 on Linux workloads in the cloud. We’ll discuss what our Azure infrastructure enables, but also what other public or private cloud providers are offering.
  • How you can leverage the Azure Infrastructure to enable new SQL Server 2017 on Linux scenarios.
  • Demos of SQL Server 2017 on Linux in Azure, AWS, and the Google Cloud Platform.

Azure Cosmos DB

Engineering experts are leading a seven-part training series on Azure Cosmos DB, complete with interactive Q&As. In addition to a high-level technical deep dive, this series covers a wide array of topics, including:

By the end of this series, you’ll be able to build serverless applications and conduct real-time analytics using Azure Cosmos DB, Azure Functions, and Spark. Attend the whole series to become an Azure Cosmos DB guru, or just register for the sessions that interest you. Learn more.

Artificial Intelligence (AI)

Learn to create the next generation of applications—spanning an intelligent cloud as well as an intelligent edge powered by AI. Microsoft offers a comprehensive set of flexible AI services for any scenario and enterprise-grade AI infrastructure that runs AI workloads anywhere at scale. Modern AI tools designed for developers and data scientists help you create AI solutions easily, with maximum productivity.

Unlock deeper learning with the new Microsoft Cognitive Toolkit
Data is powerful, but navigating it can be slow, unreliable, and overly complex. Join us to learn about the Microsoft Cognitive Toolkit—offering deep-learning capabilities that allow you to enable intelligence within massive datasets. In this session, you’ll learn:

  • What’s new with the Microsoft Cognitive Toolkit.
  • How to maximize the programming languages and algorithms you already use.
  • Cognitive Toolkit features, including support for ONNX, C#/.NET API, and model simplification/compression.

Filter content at scale with Cognitive Services’ Content Moderator
Learn how Azure Cognitive Services’ Content Moderator filters out offensive and unwanted content from text, images, and videos at scale. By combining intelligent machine-assisted technology with an intuitive human review system, Content Moderator enables quick and reliable content scanning. In this training, you’ll learn:

  • Content Moderator platform basics.
  • How to use the machine learning-based APIs
  • How to easily integrate human review tools with just a few lines of code.

Advanced Analytics and Big Data

Data volumes are exploding. Deliver better experiences and make better decisions by analyzing massive amounts of data in real time. By including diverse datasets from the start, you’ll make more informed decisions that are predictive and holistic rather than reactive and disconnected.

Accelerate innovation with Microsoft Azure Databricks
Learn how your organization can accelerate data-driven innovation with Azure Databricks, a fast, easy-to-use, and collaborative Apache Spark-based analytics platform. Designed in collaboration with the creators of Apache Spark, it combines the best of Databricks and Azure to help you accelerate innovation, with one-click set up, streamlined workflows, and an interactive workspace that enables collaboration among data scientists, data engineers, and business analysts. In this session, you’ll learn how to:

  • Use Databricks Notebooks to unify your processes and instantly deploy to production.
  • Launch your new Spark environment with a single click.
  • Integrate effortlessly with a wide variety of data stores.
  • Improve and scale your analytics with a high-performance processing engine optimized for the comprehensive, trusted Azure platform.

from SQL Server Blog http://ift.tt/2FnDPNy

quarta-feira, 21 de fevereiro de 2018

[From Technet] Announcing a new update for mssql-cli: Special Commands

This post is authored by Alan Yu, Program Manager, SQL Server.

We have released our first major update for mssql-cli since our recent Public Preview announcement in December. You can view the public preview announcement here.

mssql-cli is a new and interactive command line query tool for SQL Server. This open source tool works cross-platform and is part of the dbcli community.

Feature highlight: Special commands

In our initial release, mssql-cli brought auto-completion, syntax highlighting, configuration support, and history to your SQL Server command line experience. With v0.10.0, we introduce special commands.

Special commands are meant to make your life easier. They are shortcuts to perform common tasks and queries. All special commands start with a backslash (\), and you can use the built-in IntelliSense to see a list of special commands you can use. You can also learn more by running the following command:

$ mssql-cli>\?

To start off, let’s say you have a common query you run often, but don’t want to type it out each time or scroll through your history. You can “save” your query by running the following special command:

$ mssql-cli>\sn address select * from "Person"."AddressType"

To execute this query, simply run:

$ mssql-cli>\n address

To see this in action, see the gif below:

To learn more about special commands, please check out usage guide, which also includes information about other features that you can use with mssql-cli.

Getting started

In order to install mssql-cli, you must have Python on your machine. Please refer to the installation guide for OS-specific methods to get Python.

mssql-cli is installed via pip. If you know pip, you can install mssql-cli using command.

$ pip install mssql-cli

If you are having installation issues or would like more specific instructions, please see the installation guide.

Once you have mssql-cli installed, connect to your database with the command:

$ mssql-cli -S <server name> -U <user name> -d <database name>

Please try out mssql-cli and submit any feature requests and issues on our Github issues page. You can also keep track of any future updates by following our Twitter @sqldatatools.


from SQL Server Blog http://ift.tt/2sG7HTO

terça-feira, 20 de fevereiro de 2018

[From Technet] What’s new in SSMS 17.5: Data Discovery and Collection

This post is authored by Alan Yu, Program Manager, SQL Server.

We are excited to announce the release of SQL Server Management Studio (SSMS) 17.5!

Download SSMS 17.5 and review the Release Notes to get started.

SSMS 17.5 provides support for almost all feature areas on SQL Server 2008 through the latest SQL Server 2017, which is now generally available.

In addition to enhancements and bug fixes, SSMS 17.5 comes with several exciting new features:

  • Data Discovery and Collection is a new feature for discovering, classifying, labeling and reporting sensitive data in your databases.
  • Query Editor now has support for a SkipsRows option for the Delimited Text External File Format for Azure SQL Data Warehouse.
  • Showplan has enabled the display of estimated plan button for SQL Data Warehouse.

SSMS 17.5 also includes key bug fixes to Showplan, Query Editor, SMO, and Templates, which can be found in the Release Notes.

Data Discovery and Collection

SQL Data Discovery and Classification introduces a new tool built into SQL Server Management Studio (SSMS) for discovering, classifying, labeling and reporting the sensitive data in your databases.

Discovering and classifying your most sensitive data (business, financial, healthcare, PII, etc.) can play a pivotal role in your organizational information protection stature. It can serve as infrastructure for:

  • Helping meet data privacy standards and regulatory compliance requirements, such as GDPR.
  • Controlling access to and hardening the security of databases/columns containing highly sensitive data.

Data Discovery & Classification includes a set of new capabilities, forming a new SQL Information Protection paradigm aimed at protecting the data, not just the database:

  • Discovery and recommendations – The classification engine scans your database and identifies columns containing potentially sensitive data. It then provides you an easy way to review and apply the appropriate classification recommendations, as well as to manually classify columns.
  • Labeling – Sensitivity classification labels can be persistently tagged on columns.
  • Visibility – The database classification state can be viewed in a detailed report that can be printed/exported to be used for compliance and auditing purposes, as well as other needs.

Try it out and let us know what you think! You can message us on our twitter @SQLDataTools.

from SQL Server Blog http://ift.tt/2EWrC5w

quinta-feira, 15 de fevereiro de 2018

[From Technet] The February release of SQL Operations Studio is now available

This post is authored by Alan Yu, Program Manager, SQL Server.

We are excited to announce the February release of SQL Operations Studio is now available.

Download SQL Operations Studio and review the Release Notes to get started.

SQL Operations Studio is a data management tool that enables you to work with SQL Server, Azure SQL DB and SQL DW from Windows, macOS and Linux. To learn more, visit our GitHub.

SQL Operations Studio was announced for Public Preview on November 15th at Connect(), and this February release is the third major update since the announcement. If you missed it, the January release announcement is available here.

The February release includes several major repo updates and feature releases, including:

  • Added Auto-Update Installation feature
  • Added Connection Dialog ‘Database’ Drop-down
  • Added functionality for new query tabs keeping active connection
  • Fixed bugs in SQL Editor and auto-completion

For complete updates, refer to the Release Notes.

Auto-Update Installation

We want to use the February Insiders builds to test the auto-update feature. The 0.26.2 build will be released as an auto-update to 0.26.1 (assuming there are no issues that require publishing a new build to successfully support auto-update).

To discover updates faster, we have added auto-update functionality to ensure you have the latest version of SQL Operations Studio. A notification will pop up on the gear icon on the bottom left as shown in the image above. In addition, you can also check for updates by clicking Help on the toolbar.

Connection Dialog Database Drop-down

The Connection Dialog Database field is now a dynamically populated drop-down list that will contain a list of databases populated from the specified server.

New query tabs keeping active connection

A top voted feature by our users was for new query tabs to keep their active connection. In the February release, users can now click on a server name and press Ctrl + N, which will create a new query tab and retain the active connection instead of having to manually set the connection. This can be seen in the gif below.

Contact us

If you have any feature requests or issues, please submit to our GitHub issues page. For any questions, feel free to comment below or tweet us @SQLOpsStuido.

from SQL Server Blog http://ift.tt/2EKVZvS

Best way to count words in a string using T-SQL

On this recent project, I needed to count words in a string, in order to measure its general "quality", for a Data Quality project.

Sounded simple enough, but this is a massive table with milions of rows so performance is a requirement.

I created I function of my own, using this answer on StackOverBlow: https://stackoverflow.com/questions/15352990/get-word-count-of-a-column-using-sql

CREATE FUNCTION [dbo].[Wordcount] (@InputString VARCHAR(4000)) 
returns INT 
      DECLARE @Index INT 
      DECLARE @Char CHAR(1) 
      DECLARE @PrevChar CHAR(1) 
      DECLARE @WordCount INT 

      SET @Index = 1 
      SET @WordCount = 0 

      WHILE @Index <= Len(@InputString) 
            SET @Char = Substring(@InputString, @Index, 1) 
            SET @PrevChar = CASE 
                              WHEN @Index = 1 THEN ' ' 
                              ELSE Substring(@InputString, @Index - 1, 1) 

            IF @PrevChar = ' ' 
               AND @Char != ' ' 
              SET @WordCount = @WordCount + 1 

            SET @Index = @Index + 1 

      RETURN @WordCount 


Althought it is a correct answer to the problem, it's not performactly good. So after doing some more googling, I found this answer much more simpler: https://stackoverflow.com/questions/41952250/sql-string-counting-words-inside-a-string

VALUES      ('I am not your father.'), 
            ('Where are your brother,sister,mother?'), 
            ('Where are your brother, sister and mother?'), 
            ('Who are     you?'), 

WITH cte 
     AS (SELECT id, 
                Replace(Replace(string, ' ', '><'), '<>', '') string 
         FROM   @t) 
       Len(' ' + string) - Len(Replace(string, '><', ' ')) CountWords 
FROM   cte 

After tweaking it to handle null and empty values, my function looked like this:

CREATE FUNCTION [dbo].[Fn_wordcount] (@InputString VARCHAR(4000)) 
returns INT 
WITH schemabinding 
      DECLARE @WordCount INT = 0; 

      IF @InputString IS NULL 
        RETURN 0 

      SELECT @InputString = Replace(Replace(@InputString, ' ', '><'), '<>', ''); 

      SELECT @WordCount = Len(' ' + @InputString) - Len( 
                          Replace(@InputString, '><', ' ')); 

      SELECT @WordCount = Isnull(@WordCount, 0); 

      RETURN @WordCount 


I'm still observing the performance, the the first run looked ok. I will get back on this post after properly measuring a full load.

Check out my new book about R Language http://www.amazon.com/dp/B00SX6WA06

[From Technet] Sync SQL data in large scale using Azure SQL Data Sync

This post is authored by Xiaochen Wu, Program Manager, SQL Server.  

Azure SQL Data Sync allows users to synchronize data between Azure SQL Databases and SQL Server databases in one-direction or bi-direction. This feature was first introduced in 2012. By that time, people didn’t host a lot of large databases in Azure. Some size limitations were applied when we built the data sync service, including up to 30 databases (five on-premises SQL Server databases) in a single sync group, and up to 500 tables in any database in a sync group.

Today, there are more than two million Azure SQL Databases and the maximum database size is 4TB. But those limitations of data sync are still there. It is mainly because that syncing data is a size of data operation. Without an architectural change, we can’t ensure the service can sustain the heavy load when syncing in a large scale. We are working on some improvements in this area. Some of these limitations will be raised or removed in the future. In this article, we are going to show you how to use data sync to sync data between large number of databases and tables, including some best practices and how to temporarily work around database and table limitations.

Sync data between many databases

Large companies and ISVs use data sync to distribute data from a central master database to many client databases. Some customers have hundreds or even thousands of client databases in the whole topology. Users may hit one of the following issues when trying to sync between many databases:

  1. Hit the 30 databases per sync group limitation.
  2. Hit the five on-premises SQL Server databases per sync group limitation.
  3. Since all member databases will sync with the hub database, there’s significant performance impact to workload running in the hub database.

To work around the 30 databases or five on-premises databases per sync group limitation, we suggest you use a multi-level sync architecture. You can create a sync group to sync your master database with several member databases. And those member databases can become the hub databases of the sub sync groups and sync data to other client databases. According to your business and cost requirement, you can use the databases in the middle layers as client databases or dedicated forwarders.

There are benefits from this multi-level sync architecture even you don’t hit the 30 databases per sync group limitation:

  • You can group clients based on certain attributes (location, brand…) and use different sync schema and sync frequency.
  • You can easily add more clients when your business is growing.
  • The forwarders (member databases in the middle layers) can share the sync overhead from the master database.

To make this multi-level sync topology work in your system, you will need a good balance between how many client databases in a single sync group and how many levels in the overall system. The more databases in a single sync group, the higher impact it will add to the overall performance in the hub database. The more levels you have in your system, the longer it takes to have data changes broadcasted to all clients.

When you are adding more member databases to the system, you need to closely monitor the resource usage in the hub databases. If you see consistent high resource usage, you may consider upgrading your database to a higher SLO. Since the hub database is an Azure SQL database, you can upgrade it easily without downtime.

Sync data between databases with many tables

Currently, data sync can only sync between databases with less than 500 tables. You can work around this limitation by creating multiple sync groups using different database users. For example, you want to sync two databases with 900 tables. First, you need to define two different users in the database where you load the sync schema from. Each user can only see 450 (or any number less than 500) tables in the database. Sync setup requires ALTER DATABASE permission which implies CONTROL permission over all tables so you will need to explicitly DENY the permissions on tables which you don’t want a specific user to see, instead of using GRANT. You can find the exact privilege needed for sync initialization in the best practice guidance. Then you can create two sync groups, one for each user. Each sync group will sync 450 tables between these two databases. Since each user can only see less than 500 tables, you will be able to load the schema and create sync groups! After the sync group is created and initialized, we recommend you follow the best practice guidance to update the user permission and make sure they have the minimum privilege for ongoing sync.

Optimize the sync initialization

After the sync group is created, the first time you trigger the sync, it will create all tracking tables and stored procedures and load all data from source to target database. The initial data loading is a size-of-data operation. Initializing sync between large databases could take hours or even days if it is not set up properly. Here are some tips to optimize the initialization performance:

  1. Data sync will initialize the target tables using bulk insert if the target tables are empty. If you have data on both sides, even if data in source and target databases are identical (data sync won’t know that!), data sync will do a row-by-row comparison and insertion. It could be extremely slow for large tables. To gain the best initialization performance, we recommend you consolidate data in one of your databases and keep the others empty before setting up data sync.
  2. Currently, the data sync local agent is a 32 bits application. It can only use up to 4GB RAM. When you are trying to initialize large databases, especially when trying to initialize multiple sync groups at the same time, it may run out of memory. If you encountered this issue, we recommend you add part of the tables into the sync group first, initialize with those tables, and then add more tables. Repeat this until all tables are added to the sync group.
  3. During initialization, the local agent will load data from the database and store it as temp files in your system temp folder. If you are initializing sync group between large databases, you want to make sure your temp folder has enough space before you start the sync. You can change your temp folder to another drive by set the TEMP and TMP environment variables. You will need to restart the sync service after you update the environment variable. You can also add and initialize tables to the sync group in batch. Make sure the temp folder is cleaned up between each batch.
  4. If you are initializing data from on-premises SQL Server to Azure DB, you can upgrade your Azure DB temporarily before the initialization. You can downgrade the database to the original SLO after the initialization is done. The extra cost will be minimum. If your target database is SQL Server running in a VM, add more resources to the VM will do the same.

Experiment of sync initialization performance

Following is the result of a simple experiment. I created a sync group to sync data from a SQL Server database in Azure VM to an Azure SQL database. The VM and SQL database are in the same Azure region so the impact of network latency could be ignored. It was syncing one table with 11 columns and about 2.1M rows. The total data size is 49.1GB. I did three runs with different source and target database configuration:

In the first run, the target database is S2 (50 DTU), and source database is running in D4S_V3 VM (4 vCPU, 16GB RAM). It takes 50 min to extract data to the temp folder and 471 min to load the data from the temp folder to the target database.

I upgraded the target database to S6 (400 DTU) and the Azure VM to D8S_V3 (8 vCPU, 32GB RAM) for the second run. It reduced the loading time to 98 min! The data extracting surprisingly took longer time in this run. I can’t explain the regression since I didn’t capture the local resource usage during the run. It might be some disk I/O issue. Even though, upgrading the target database to S6 reduced the total initialization time from 521 min to 267 min.

In the third run, I upgraded the target database to S12 (3000 DTU) and used the local SSD as temp folder. It reduced data extract time to 39 min, data loading time to 56 min and the total initialization time to 95 min. It was 5.5 time faster than the first configuration with extra cost of a cup of coffee!


  1. Upgrade the target database (Azure DB) to higher SLO will help to improve the initialization time significantly with manageable extra cost.
  2. Upgrade the source database doesn’t help too much since the data extract is an I/O bound operation and 32bits local agent can only use up to 4GB RAM.
  3. Using attached SSD as temp folder will help on the data extract performance. But the ROI is not as high as upgrading target database. You also need to consider if the temp files can fit into the SSD disk.
Runs Target database SLO (Azure DB) Source database SLO (VM) Total Initialization time Data extract time Data load time
1 S2 D4S_V3 521 min 50 min 471 min
2 S6 D8S_V3 267 min *169 min 98 min
3 S12 D8S_V3, Attached SSD 95 min 39 min 56 min

In this article, we provided some best practices about how to sync data using Azure SQL Data Sync service between many databases and databases with many tables. Please find more information about data sync in the online documentation. More data sync best practice is available at Best Practices for Azure SQL Data Sync.

from SQL Server Blog http://ift.tt/2C0Ke3c

sexta-feira, 9 de fevereiro de 2018


I had to run a update on a massive table from SQL SERVER, but ended up giving up on the task as it was taking too long and keeping from more important tasks.

After trying to kill the process unsuccesfully, I restarted the service and the database came up as recovery after the restart.

SQL SERVER 2008 or superior versions, can handle RECOVERY modes on their own, but to check on the status of the task, you check the SQL SERVER error log by executing this proc on your SQL SERVER:

EXEC sp_readerrorlog;

I know we have to be patient when doing database stuff, but its hard to be assertive with other people breathing down your neck.

Your SQL SERVER will show the evolution of the 3 steps to the recovery as follows:

2018-02-09 11:27:46.540 spid27s      Recovery of database 'dbx' (10) is 23% complete (approximately 2090 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.
2018-02-09 11:28:06.540 spid27s      Recovery of database 'dbx' (10) is 24% complete (approximately 2072 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.
2018-02-09 11:28:26.550 spid27s      Recovery of database 'dbx' (10) is 25% complete (approximately 2040 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.

Check out my new book about R Language http://www.amazon.com/dp/B00SX6WA06

segunda-feira, 5 de fevereiro de 2018


Ever had a hard time deleting rows from a very large table? Try doing it in smaller chuncks in a loop, like this:

INTO   #tmp 
FROM   sys.sysobjects;


FROM   #tmp;

       SELECT 0 
       FROM   #tmp) 
  DELETE TOP (10) 
  FROM   #tmp; 

  PRINT '10 DOWN!'+try_cast(@COUNTX as varchar)+' TO GO!' 


Check out my new book about R Language http://www.amazon.com/dp/B00SX6WA06