There's a saying in the DevOps world that we want to treat our servers like cattle, not pets. This really means that we don't want to get attached to any of them. Each is a resource, and we should be able to easily replace it with another. One of my web servers (or DNS, file, print, etc. servers) has an issue? I destroy it and replace it with another. In many cases, this makes perfect sense, though it does require adopting some DevOps approaches like treating our configuration as code and having a way to easily spin up new machines, VMs, or containers.
In the database world, at least the relational world, we've tended to treat our database instances, and really servers, much more like pets. We craft them, raise them, and lavish attention on them to ensure they live strong and long. We hate for them to die, because it's a pain to rebuild a SQL Server. I'm sure more than a few of you don't even want to attempt the process, worrying about forgetting some setting, driver, or other item that was long ago installed on your system. Even in cases where a cluster or other HA technology exists, most people dread having to replace a SQL Server instance.
That shouldn't be the case. We should treat our machines like cattle, and be ready to easily rebuild one of them at any time. We should be ready, at least for DR purposes, to reinstall SQL Server on a new host, with the same settings, restore our databases, users, linked servers, jobs, etc. That means we need to ensure we have all that information ready. While plenty of that is stored inside the various system databases in SQL Server, could you move a single database to a new instance?
Many people would struggle with this, which is not a great situation. Not for the person, not for the organization, and not for our industry as a whole. We often find ourselves ill-prepared to recover from issues when our server experiences catastrophic failures. I would hope we could do better.
As much as I appreciate the idea of DevOps helping software developers produce better software faster, I think it's just as important that operations staff adopt the practices as well. Store all configuration as code, in a version control system, and build processes to automatically apply changes to your instance. Then store that process call or code so that you are prepared to recreate an instance in an instant. If you get that working, add to your process and have a way to check that your instance actually conforms to your desired configuration. PoSh DSC, Chef, Puppet, or some other tool can get you started.
SQL Server has improved across its versions to make it easier for us to build database cattle, and get away from database pets. All you need to do is take advantage of the tools, capabilities, and best practices to do so.
NEW product launch: SQL Clone - live stream this March!
Data Platform MVPs Grant Fritchey and Steve Jones show how to create database copies in seconds using MBs of disk space with Redgate’s NEW database provisioning tool SQL Clone! Tune in to this live stream session on March 29th 2017. Register now.
If you need to run SQL Server in an Azure Virtual Machine, your choice of Azure storage will have a great effect on its performance. If performance is important, you are likely to discover complications and barriers in the storage options when you come to provision the server. If you get it wrong, you could end up with an expensive service. Joshua explains the value of using a lab environment to allow you to make well-informed VM storage decisions when the time comes to provision your production system. More »
Today during the Visual Studio launch event, we announced that we’ve partnered with Redgate to include Redgate Data Tools in Visual Studio 2017. Redgate Data Tools includes three components that extend DevOps practices to SQL Server and Azure SQL databases and increase your productivity while doing database development. More »
In the previous post, I showed how you can get full listings of your execution plan costs. Knowing what the... More »
Question of the Day
Today's Question (by Steve Jones):
I want to secure my Reporting Services report server so that all of my clients can connect with SSL. In order to do this, what cryptographic object do I need?
Think you know the answer? Click here, and find out if you are right.
We keep track of your score to give you bragging rights against your peers.
This question is worth
1 point in this category: Security.
We'd love to give you credit for your own question and answer.
To submit a QOTD, simply log in to the
Yesterday's Question of the Day
(by Uwe Ricken):
You have a database running with READ COMMITTED SNAPSHOT Isolation ON! The database contains a table called [dbo].[CustomerOrderDetails] which has an unique clustered index on the attributes [Order_Id] and [Position].
CREATE TABLE dbo.CustomerOrderDetails
Order_Id INT NOT NULL,
Position TINYINT NOT NULL,
AnyOtherCol CHAR(100) NOT NULL
CREATE UNIQUE CLUSTERED INDEX cuix_CustomerOrderDetails
The table has increasing order numbers from 1 to 10,000! Someone is running the following command in a SSMS window
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET Price = Price * 1.10
WHERE Order_Id = 1001;
In an application a user wants to see the order details from order 1002. What will happen in the application when it fires the following sql statement
SELECT * FROM dbo.CustomerOrderDetails WHERE Order_Id = 1002;
Answer: The application has to wait until the open transaction is closed
The open transaction is using the isolation level SERIALIZABLE which holds a RangeS-S lock on the order with the ID = 1001.
The isolation level SERIALIZABLE overwrites the RCSI for the session and prevents other processes to insert new positions for the order with the ID = 1001.
Therefore the lock has to be extended to the first record of the order with the ID = 1002.
When i was trying to execute the SSIS package through command prompt below:
C:\Users>dtexec /f "C:\Users\Documents\Visual Studio 2015\Projects\SSISPackage\Package.dtsx" /Set "\Package.Variables.Properties";"C:\\AzureDirectory\\Scripts\\" /Set...
Referencing another table throws off totals
- Using Microsoft SQL Server Report Builder 3.0, This SQL query code:
,tblDataPermit.XmlDataField.value('(//*)', 'nvarchar(max)') AS UniformCode
Create Indexes on Computed Columns
- Hi everyone
Hi have a doubt about create indexes on computed columns.
Microsoft provide in its web with comments about this subject:
This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.