Deploying SQL Server Automatically

I've had to install SQL Server many times over the years. Often it has felt that most of these installs were one-offs, a dev server, a new QA instance, a production server for a brand new application. A few times I've had to recover from disaster, including restoring master, but often, I just installed SQL Server manually because that was quick and guaranteed. I knew what needed to be done for most of the instances.

However, I couldn't be sure. In the past, there were relatively few settings that were ever changed outside of the master database, but today there are more, and perhaps more importantly, the tolerance for making mistakes with any security missteps, is low. I've spent a bit of time learning to do unattended installs, and I've tried systems like FineBuild for installing SQL Server. In any size organization that might want certain standards set up, using one of these systems is important. Perhaps one of the better reasons to do this is to ensure that your dev and test systems are configured the same as production to prevent any silly misconfiguration problems.

This week I saw a post on using Ansible to install SQL Server on Linux, with all the various configurations. While I do think that it's easier to install SQL Server on Linux programmatically, there are a number of items to set and configure. Following along the install, it's interesting how much more this feels like something a developer would do, which is how many modern Operations groups approach installs and upgrades. Everything needs to be done without a human directly involved, often because of scale. There are so many systems we manage, often a mix of VMs, cloud, and local systems, which means that a consistent, programmatic way of installing instances is needed.

That's likely one of the most important skills for a modern system administrator. We need to learn to use tools to get our work done. Whether these are free, open-source, or purchased, using a tool to work at scale is more important today than ever before. Even if you are part of a development group, learning to manage systems with code can help ensure you can scale and grow quickly, and more importantly, handoff this job easily to someone else when you reach that point.

I think there are plenty of homemade scripts and tools that can help here, but almost every time I've seen one of these, it's not very portable to other staff, especially if the author isn't available. Too often these tools take shortcuts or are specifically tailored to the current environment and not the future one. These days, for many functions we tackle there are extremely well-built tools available at no, low, or modest costs. I'd encourage anyone that is handling these tasks to learn about the tools available and pick one. I don't know that I think any of these are necessarily easier to use, but they are all well documented and capable of handling most of your install, update, and configuration chores.

Steve Jones - SSC Editor

Join the debate, and respond to the editorial on the forums

When you are using Flyway, how can you test your database migration script first to make sure it works exactly as you intended before you let Flyway execute it? Phil Factor explains.

During database development, as well as writing SQL, you must also write the tests that prove your SQL code meets all the requirements. The article will help you get started with database testing for your SQL Server databases, by creating and running unit tests against them, as well as static code analysis tests.

Describing a database version control system using an Azure DevOps-hosted Team Foundation Version Control repo and SQL Source Control, and a workaround for authentication problems when connecting to multiple Azure DevOps organizations.

When integrating database changes into a DevOps process, the biggest challenge is to standardize and coordinate the different approaches to application and database development. Discover the key tools & training resources to help you and your team.

Share your favorite Redgate tool – win technical training from Pluralsight! Level up your skills with Redgate’s latest competition. Redgate is giving you the chance to win a 3-month subscription to Pluralsight, the technical skills platform. To enter, visit the Redgate Forums and answer the question ‘What’s your favorite Redgate tool and why?’

Machine Learning (ML) has come from a buzzword that is nice to have in your application to a must-have feature that works and adds value. Data scientists develop ML models in various ML Frameworks like TensorFlow, Scikit-learn, PyTorch, Azure ML, etc. Before ML.NET became available to all developers, adding the ML functionality to .NET applications required knowledge in some ML frameworks to build and train ML models.

In this Q&A with MIT/SMR Connections, Gavin Day, Senior Vice President of Technology at SAS, shares real-life examples of artificial intelligence (AI) at work, discusses picking the right problems...

According to the Federal Trade Commission, consumers reported losing more than $3.3 billion to fraud in 2020, an increase of $1.5 billion since 2019. Contributing to this uptick in...

Click to learn more about author Assaf Egozi. Modern organizations house a growing number of “citizen data analysts.” These individuals hold a wide range of positions in the enterprise, from...

Microsoft Azure has been a leading cloud service provider over the past few years. In this article, we are going to look into an overview of various cognitive services...

Join our headline experiment post-mortem today, July 28, at 1 pm Eastern time!

It’s a SQL Server Instance not a SQL Server. The product is SQL Server. The installed copy is an Instance. ... Continue reading The post Server vs Instance. A rant....

You might be asking why on earth would you want to get a database into an undesirable state, more specifically into a Recovery Pending state.  Well, in my case,...

Paul Randal continues his series on latches, showing how computed columns can really slow down DBCC operations. The post The DBCC_OBJECT_METADATA Latch appeared first on SQLPerformance.com.

In my previous article about SQL Server System Databases, we learned about each system database that comes as part of SQL Server installation. The current article will focus on...

Erika and I are on the road in Iceland, roaming around the countryside, and I’m taking you with us. This week, we’re in Siglufjörður, a beautiful little oceanfront fishing...

Today, we'll look at how to automate SQL Server deployment and configuration on Linux. To automate our deployment, we will use the Ansible system role, which is available here.   Note:...

Instant file initialization can speed up database ...

This article explores locks to prevent unexpected changes in Azure SQL Database resources. Introduction Suppose you manage multiple critical Azure SQL Server resources. As an administrator, you don’t want...

This article explores database DDL triggers for auditing Data Definition Language (DDL) on Azure SQL Database. Database audit is critical and essential for securing your database infrastructure. Azure SQL...

Azure storage can be marked Private to control access. Dennes Torres explains how to query private blob storage with SQL and Azure Synapse.

Erika and I are on the road in Iceland, roaming around the countryside, and I’m taking you with us. This week, we’re in Siglufjörður, a beautiful little oceanfront fishing...

It’s been a while since I last wrote a new blog, and it has been a very long time since I last wrote a fresh new “What Counts For...

After a year of uncertainty, the employment market...

The world of work has changed dramatically over the last year and, at Redgate, we’ve been reflecting on what this might mean for our business, and the ways that...

Some AWS RDS SQL Server limitations

If you are using Internet Explorer 11 (IE 11) to access the AWS Management Console, web-based services such as Amazon Chime or Amazon Honeycode, or other parts of the...

SQL Server is one of the most popular databases used in modern applications. Over the past few decades, it has established itself as a leading database provider that drives...

Complete Guide for Virtual Machine in Google Cloud. 1) Understanding of Compute Instances in GCP 2) Preemptible & Shielded VM Instances 3) Live Migration of VM instances in GCP 4) Compute Engine Dashboard... The...

Fun with PowerShell Arrays

Philip Cooper, VP of Product, Tableau, discusses the trend toward making data analytics tools available to employees throughout the organization, not merely the C-suite.  Among the topics we discussed: ...

Following on from my last blog introducing the series, in this section, we’ll take a first look at Explanatory Data Analysis with basic summary statistics. Getting started with a...

After delaying its iOS privacy labels for months, Google copies the feature for Play.

This post is part of the series I kicked off here. You can read my post about captions here. Let’s talk about slides! Many of us are familiar with...

Until it offers privacy by default, it remains a liability for many of its users.

Last month, we challenged our community to create a visualization based around the color red. That particular color gets plenty of use in visual design, and it has a...

Click to learn more about author Thomas Frisendal. This is a summer special, on the lighter side, but addressing a simply overwhelming issue at times. What Are You Talking...

This article will explain how to use the SSIS lookup transformation and how it differs from the fuzzy lookup. This is a continuation of the SSIS features face-to-face series...

With the launch earlier this year of Intel’s Ice Lake Xeon Scalable platform, the first Intel enterprise platform based on 10nm, we were always wondering to what extent this...

In today’s Intel Accelerated event, the company is driving a stake into the ground regarding where it wants to be by 2025. CEO Pat Gelsinger earlier this year stated...

Undeterred by the failure of the Duo 1, Microsoft doesn't seem to be changing much.

INTERSECT: Returns the rows of left-side table which appear in right-side table. https://dax.guide/intersect/

UNION: Returns the union of the tables whose columns match. https://dax.guide/union/

While Azure has been growing steadily, it faces steep competition for big deals from Amazon.com Inc., the dominant cloud service, and Google, which ranks third in the market but...

SQL Server perfmon counters provide a wealth of information about performance. In this article, Fabiano Amorim shows how current wisdom about one counter, SQL Compilations/sec, can sometimes be misleading.… The...

This is something that comes up time and time again. A developer or member of the service desk comes up to me and says “SQL’s timing out, you see...

Starting SQL: When Do Queries Wait? If you want to check out my more advanced training, follow this link to get 90% off.

Starting SQL: Monitoring Active Queries With sp_WhoIsActive If you want to check out my more advanced training, follow this link to get 90% off.

SQL Server provides information for troubleshooting performance. Ed Pollack demonstrates collecting SQL Server performance monitor data.

Third blog in the series on Transform a local into a global Power BI solution. After covering the general differences between small scale and enterprise solutions and last week all about content sharing, it is time to elaborate on a new topic.

Have you tried measuring performance of Row-Level ...

This article continues to build on my COVID Case Fatality Rate report for Australia. Something happened with the global source data this week – in short, it was wrong....

As is almost always the case when building Power BI reports, the first version is just the start of the journey, not the final destination. This article is a...

In this video Devin continues building on a previo...

Using Smart Narratives and you'd like to add some ...

When working with Power Query, you have probably already realized that every expression you write returns a value of a specific type. Usually this will be a primitive type like text, number, or date.

Large models in Power BI Premium have now a larger...

A quick note for anyone like me who spends too much time looking at the JSON exports from Performance Analyzer in Power BI Desktop: you may have noticed an...

The step-by-step process below walks through connecting to data housed in Azure Blob Storage from Power BI using a SAS token. There are many ways to grab your data... The...

You need to dig into old blogs I wrote before to fully understand how interesting this new extension is. On Saving Money with Log Analytics I mentioned how important log...

I’m slowing the First Responder Kit update frequency down to once every 2 months. I know from firsthand experience working with clients that folks just can’t patch quickly enough,...

Running SQL Server on Azure VM comes with many benefits such as  Lower the cost to run SQL Server on Azure with Azure-only pricing offers.  Easily bring your SQL Server to the cloud and enjoy...

I am pleased to announce the availability today of Amazon Route 53 Application Recovery Controller, a Amazon Route 53 set of capabilities that continuously monitors an application’s ability to...

As always the code can be found here in our github...

Why? Simply because I haven’t attempted the Fizz...

TLDR: Pass the output of the isoreg function to as.stepfun to make an isotonic regression model into a black box object that takes in uncalibrated predictions and outputs calibrated...

I learned this from one of my co-workers.  A quick tip for formatting SSRS reports. When you are in the SSRS report editor, and you want to move the...

I’m thrilled to have recently released a video with Microsoft’s Data Exposed on Channel 9 where I discuss setting up Linux storage appropriate for on-demand disk expansion for your... The...

U.S. companies that provide critical services or have high-value trade secrets should be required to improve their cybersecurity and report hacking attacks to the federal government, national security officials...

This is important: Monsignor Jeffrey Burrill was general secretary of the US Conference of Catholic Bishops (USCCB), effectively the highest-ranking priest in the US who is not a bishop, before...

The remote management software company will not di...

A vast majority of DevOps practitioners say contai...

progress Did some adhoc work in this repo (the hugo repo that contains this blog) testing out Mage, which is a Go based Make alternative. Generated dynamic target directory for hugo...

In this article, we will learn how to calculate and add a subtotal in SQL queries. Introduction A subtotal is a figure that shows the sum of similar sets...

This post is a follow up to my previous post on the performance impacts of the AT TIME ZONE implementation in SQL Server based on some observations and comments...

“Extended properties allow you to add custom properties to database objects”, so says the official Microsoft documentation. However, very few DBAs make use of them, if at all. This...

If you’ve been following along with the previous...

The Common Table Expression aka CTE in SQL Server provides a temporary result set in T-SQL. You can refer to it within a SQL Select, SQL Insert, SQL Delete,...

I start a new series on identifying and removing problematic NOLOCK hints from update and delete statements.

After my CTE presentation at a while back I was asked many questions, and received several great suggestions from people.   One question was how does the performance compare...

In the last few years, JSON has positioned itself as a standard data exchange format between services, although XML is still widely used. In the SQL Server 2016, Microsoft...

Apple is again making a run at the Enterprise, and Forrester has released a report indicating companies could save around $300 a year in support by moving to Macs. ...

The biggest benefit of testing the Windows 11 OS b...

A comprehensive analysis shows that warmer tempera...

Hybridization offers decent fuel economy improvement and a lot more power.

Decision comes after shaking issue with SLS rocket...

Following on from my last post after creating AKS, I now want to work with SQL server. First step, load up Azure cloud shell. Run the following commands Here... The...

I’ve been running a proof of concept for SQL Ser...

Limits in Containers Docker gives you the ability to control a container’s access to CPU, Memory, and network and disk IO using resource constraints, sometimes called Limits. You define... The...

I’ve been running a proof of concept for SQL Server on Kubernetes over the last year or so (ok, probably longer than that…hey, I’m a busy guy ?? )... The...