Search Results

Hardening Sentry One for Security

If you have an environment where you need to lock down Sentry One as much as possible, then this article should help. It is well known that the Sentry One service account needs to be a member of the Local Administrators group in each server it monitors, and also a member of the sysadmin role for each SQL Server instance. At the moment this is still a requirement, but if you can live without the Windows metrics, then you could run Sentry One in Limited Mode which will only gather SQL Server specific metrics. Some of our clients run in

The problem with TDE and the challenge of T

I recently gave a SQL Supper talk as part of the Microsoft Future Decoded evening community events, and I made the point of not being impressed by Transparent Data Encryption (TDE), be it SQL Server, Azure SQL Database or Cosmos Db. I would like to explain why. The problem of TDE I have worked with data and storage engines for some time and therefore TDE seems straight-forward to me. I think a good overview of TDE for SQL Server, Azure SQL Database and Azure SQL Data Warehouse is given here , and I think a similarly good overview of TDE

Fix For Using Azure Active Directory and DacFX

Hello! As part of an SSDT project we have a contained user that authenticates against an Azure Active Directory group (read more on the CREATE USER page). However the account we are executing deployments with is the SQL Admin account on the Azure SQL Instance. And so we get this error - The executed script: CREATE USER [myUser] FOR EXTERNAL PROVIDER; ' Reason: '' At line:94 char:13 + Throw $toThrow + ~~~~~~~~~~~~~~ + CategoryInfo : OperationStopped: (Deployment fail... ' Reason: '':String) [], RuntimeException + FullyQualifiedErrorId : Deployment failed: 'Could not deploy package. Error SQL72014: .Net SqlClient Data Provider: Msg 33159,

SSDT 16.5 Released

Hello! Recently the SQL Tools Team released a new version of both SQL Server Data Tools ( SSDT ) and SQL Server Management Studio (SSMS.)  There’s a range of bug fixes, but two new features that I am particualrly interested in. Firstly, SQLPAckage.exe and the DacFx API can now generate deployment report, deployment script and publish to a datbase all in one action. Neat! This is useful because it’s important to keep track of exactly what has changed on a database. Of course there’s nothing stopping you right now from creating executin these options in three separate actions, but there

Do Azure SQL Database External Tables have a place in a micro-service?

I was recently in discussions on using External Tables to link Azure SQL Databases across micro-service boundaries. This has led to some challenging discussions with a client and unexpected opinions internally here at . My simple view of a micro-service is of a data store fronted by code, which is in turn behind an API or message subscriber. Importantly only this code accesses the store. I have arrived at this opinion though many (often heated) discussions with developers implementing services, and though working with teams breaking large services into micro-services to clarify ownership and responsibility, remove dependencies and simplify

How to move a replication subscriber to a new server with no downtime to the publisher?

In a recent data centre migration for a client we had a problem where we needed to move a subscriber to a new data centre without incurring any downtime to the publisher or loss of data after the subscription migration. The application was sending hundreds of transactions per second to the publisher. An additional complication was an upgrade to SQL Server 2016 from SQL Server 2008 R2 on the subscriber. The first phase of the migration was to move the subscriber to a new server in a different domain, but without incurring any downtime to the publishing application. How to

Assist Deploy Is Available on GitHub

Hello! For some time now I have been working on automating SSIS deployments, and earlier this week I published my efforts on GitHub . But before I get into the what/how, let’s focus on the why and let me catch you up on how I got here… The task to take an ispac and deploy in and of itself is quite a straightforward process as there are multiple ways to do this . For those of you who want the abridged version of the linked post, the choices are as follows: Integration Services Deploy Wizard SSIS Catalog T-SQL API PowerShell

Migrating SSIS Packages to SSIS Azure

Hello! In case you missed the announcement (and there were a lot of announcements during MSIgnite), SQL Server Integration Services is in Public Preview on Azure! I’ve written about it elsewhere in greater depth , but here are the headlines: It makes use of SSIS Scale Out , which was released as part of SQL Server 2017 . Although it is based on SSIS Scale Out, you can’t actually configure SSIS Scale Out to run on the instance. If this confuses you then read my in-depth post. SSISDB is installed in either SQL Azure or on a Managed Instance. You