Press "Enter" to skip to content

Month: June 2018

Understanding Transactions In SQL Server

Published 2018-06-25 by Kevin Feasel

Jeanne Combrinck explains the purpose and nature of transactions in SQL Server:

Distributed Transactions:
You can use Distributed transactions which specifies the start of a SQL distributed transaction managed by Microsoft Distributed Transaction Coordinator (MS DTC). This is only applicable to SQL Server and no Azure. Transaction-level snapshot isolation does not support distributed transactions.

Arguments:
transaction_name
Is a user-defined transaction name used to track the distributed transaction within MS DTC utilities. transaction_name must conform to the rules for identifiers and must be <= 32 characters.

@tran_name_variable
Is the name of a user-defined variable containing a transaction name used to track the distributed transaction within MS DTC utilities. The variable must be declared with a char, varchar, nchar, or nvarchar data type.

Jeanne also includes the important comment that nested transactions don’t really work the way you’d expect them to.  I probably could have ended that last sentence early with, “don’t really work.”

Comments closed

Granting Non-Admin Users Access To Run ML Services

Published 2018-06-25 by Kevin Feasel

Niels Berglund walks through the rights needed for a non-administrative user to execute an external script using SQL Server Machine Learning Services:

Oops, something did go wrong, as it turns out that if you try to grant permissions on extended stored procedures, which SPEES is, you need to do it from the master database. Cool, let us switch to master and do it there. Well, if you try to do that – then you get another error: the user does not exist in master, sigh!

At this stage you have a couple of options:

  • Add the login for the user to the sysadmin role, or the user to the db_owner role in the actual database. No do not do that, I am only kidding! Do.Not.Do.That!

  • Create the user in master and grant the permission. That would work.

  • Grant the permission to public.

Check it out, as there are two parts to the process.

Comments closed

Column-Level Security In Azure SQL Data Warehouse

Published 2018-06-25 by Kevin Feasel

Kavitha Jonnakuti announces a new feature for Azure SQL Data Warehouse:

Access to the table columns can be controlled based on the user’s execution context or their group membership with the standard GRANT T-SQL statement. To secure your data, you simply define a security policy via the GRANT statement to your table columns. For example, if you would like to limit access to PII data in your customers table, you can simply GRANT SELECT permissions on specific columns to the ContractEmp role:

GRANT SELECT ON dbo.Customers (CustomerId, FirstName, LastName) TO ContractEmp;

This capability is available now in all Azure regions with no additional charge.

This has been in regular SQL Server for a long time, so it’s good to see it make its way into Azure SQL Data Warehouse, and in a manner which doesn’t involve creating user-defined functions for predicates like Row-Level Security.

Comments closed

Updating Stats With Ola’s Scripts

Published 2018-06-25 by Kevin Feasel

Erin Stellato shows us a smarter way to update statistics using Ola Hallengren’s index optimization script:

With this option, if no rows have changed, the statistic will not be updated.  If one or more rows have changed, the statistic will be updated.

Since the release of SP1 for 2012, this has been my only challenge with Ola’s scripts.  In SQL Server 2008R2 SP2 and SQL Server 2012 SP1 they introduced the sys.dm_db_stats_properties DMV, which tracks modifications for each statistic.  I have written custom scripts to use this information to determine if stats should be updated, which I’ve talked about here.  Jonathan has also modified Ola’s script for a few of our customers to look at sys.dm_db_stats_properties to determine if enough data had changed to update stats, and a long time ago we had emailed Ola to ask if he could include an option to set a threshold.  Good news, that option now exists!

Keeping statistics up to date is a nice way of quietly improving performance in a system.

Comments closed

Thoughts On Spending Money In The Cloud

Published 2018-06-25 by Kevin Feasel

Andy Leonard has a few thoughts on spending money once you’ve migrated to the cloud:

In a previous consulting life, a customer contacted us and asked for an evaluation of their architecture. They were attempting to scale the business and encountering… obstacles. A team looked over their software and database designs and recommended a rewrite of their custom code. We supplied a proposal (including an expensive estimate) to deliver the re-architecture, redesign, and rewriting of their code.

They felt the price was too high.

We understood, so we countered with a proposal to coach their team to deliver the same result. This would cost less, the outcome would be the same, and their team would grok the solution (because their team would build the solution). The catch? It would take longer.

They felt this solution would take too long.

Andy has some great thoughts on the subject.  One area where I’d push further is to say that the best way to take advantage of cloud services is not the best way to take advantage of on-prem services, so even if you have a well-architected on-prem solution, it might not be ideal for running in AWS or Azure.

Comments closed

Solving A Problem In TensorFlow Using SoftMax

Published 2018-06-22 by Kevin Feasel

Kiran Gutha gives us a fairly simple solution to the MNIST digit data set using the SoftMax algorithm:

In this tutorial, we will train a machine learning model for predicting numbers in pictures. Our goal is not to design a world-class complex model (although we will give you the source code to implement first-rate predictive models later). Rather, this tutorial is to introduce how to use TensorFlow. So, we start here with a very simple mathematical model called Softmax Regression.

The implementation code for this tutorial is short, and the really interesting content is only contained in three lines of code. However, it is very important to understand the design ideas contained in these codes: the basic concepts of TensorFlow workflow and machine learning. Therefore, this tutorial will explain in detail the implementation of these codes.

This is about as easy as it gets with neural networks, but easy doesn’t mean wrong.

Comments closed

Executing SSIS From Azure Data Factory

Published 2018-06-22 by Kevin Feasel

Andy Leonard shows us how to execute an SSIS package from Azure Data Factory:

The good people who work on Azure Data Factory recently added an Execute SSIS Package activity. It’s pretty cool. Let’s tinker with it some, shall we?

First, you will need to create an Azure Data Factory SSIS Integration Runtime. If you don’t know how, that’s ok – I’ve written a post titled Lift and Shift SSIS Part 0: Creating the ADF Integration Runtime that describes one way to set up ADFIR.

Read on for an example.

Comments closed

Automatic Seeding In Availability Groups

Published 2018-06-22 by Kevin Feasel

Frank Gill explains one method of building out data in an Availability Group, automatic seeding:

Microsoft released Availability Groups (AG) as a feature in SQL Server 2012. Prior to SQL Server 2016, there were two methods of adding a database to a new AG replica.

  1. You could provide the Add Database to Availability Group wizard a file share accessible by the primary and secondary replicas.  SQL Server would run FULL and LOG backups of each database to the share and use them to restore the database(s) to each replica.
  2. You could manually run a FULL and LOG backup of each database, copy the backup files to each replica, and restore the databases WITH NORECOVERY.

With SQL Server 2016. Microsoft has provided a third option, Automatic Seeding.  With Automatic Seeding, you specify the databases and the replicas and SQL Server will begin transferring data to each replica.  The duration of the seeding process depends on the size of the database and the network bandwidth available between primary and secondary replica.

Automatic seeding isn’t perfect, but it’s quite useful.

Comments closed

Scripting Maintenance Mode Tasks

Published 2018-06-22 by Kevin Feasel

Jamie Wick shares some hard-earned knowledge regarding scripting out maintenance tasks using Powershell:

Given that we have several hundred servers (and growing), this process is taking an increasing amount of time each month. Over the years we’ve implemented various automated patching systems (WSUS, IBM BigFix, etc.) and they’ve worked reasonably well for managing the Download & Install step. The pain point lately has become the first two steps (snapshots and maintenance mode). Both processes are simple to complete using the VCenter web-based user interface and SCOM console. The problem is the volume of button clicks it takes to complete the process for ALL of the servers. Using the standard (web) user interfaces, over an hour of the monthly maintenance window can be lost to just getting the snapshots and maintenance mode tasks completed. Extrapolate that out over a year and we’re looking at over 1.5 DAYS of work-time lost to getting the servers ready to START applying updates. That’s not a statistic we want to publish to senior management. So, how to fix (or minimize) the problem? The answer to which is: Script It.

Let’s take a look at how to use PowerShell to automate the snapshot and maintenance mode tasks.

Read on for sample scripts.

Comments closed

Resetting SSMS Window Layout

Published 2018-06-22 by Kevin Feasel

Wayne Sheffield clues us in on a nice Management Studio feature:

SSMS is a wonderful tool. You can drag Windows around, grouped with others, split, docked, undocked, hidden… it seems endless what you can do with them. You can even change what columns you see. Invariably, with all of this customization, things go wonky. I’ve even seen windows opened up on invisible monitors. Sometimes you can’t find the windows that you need. Conversely, windows that you don’t need are open all over the place. Sometimes, you just need to reset everything and start over. Short of reinstalling SSMS, how do you do this? You just reset the window layout.

It’s one of those options which stares you in the face but you can easily miss.

Comments closed