SQLServerCentral - www.sqlservercentral.com

A community of more than 1,600,000 database professionals and growing

Featured Contents

The Voice of the DBA

Database Cattle

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.

Steve Jones from SQLServerCentral.com

Join the debate, and respond to today's editorial on the forums

ADVERTISEMENT
SQL Clone

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.

Featured Contents

 

How to work with the command line in Azure SQL Data Warehouse

Daniel Calbimonte from SQLServerCentral.com

This time we will work with the sqlcmd to handle our Azure SQL Data Warehouse More »


 

SQL Migrator (Part 2)

Datamate Technology from SQLServerCentral.com

Automated Upgrade\Migration Tool for SQL Server More »


 

Exploring Azure Storage for SQL Server DBAs

Additional Articles from SimpleTalk

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 »


 

Redgate Data Tools in Visual Studio 2017 Enterprise

Additional Articles from SQLServerCentral.com

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 »


 

From the SQLServerCentral Blogs - SQL Browser, what is it good for? Absolutely something!

cjsommer from SQLServerCentral Blogs

Another great teaching opportunity landed in my lap this week. I got an email from a coworker looking for some... More »


 

From the SQLServerCentral Blogs - Determining the Cost Threshold for Parallelism

Grant Fritchey from SQLServerCentral Blogs

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 Contribution Center.

Yesterday's Question of the Day

Yesterday's Question (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
);
GO

CREATE UNIQUE CLUSTERED INDEX cuix_CustomerOrderDetails
ON dbo.CustomerOrderDetails
(
 Order_Id,
 Position
);
GO
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;
GO

BEGIN TRANSACTION;
GO
 UPDATE dbo.CustomerOrderDetails
 SET Price = Price * 1.10
 WHERE Order_Id = 1001;
 GO
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

Explanation:

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.

References

Isolation Level SERIALIZABLE:
click here

RangeS-S locks:
click here

Thank you very much for answering my QOTD.


» Discuss this question and answer on the forums

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2016 : SQL Server 2016 - Administration

Can I just attach to a database? - My work PC had a very small primary drive. My boss was able to get larger drives. This morning my...

SQL Server Restarted Automatically - Hi All, In our production server, suddenly the server got restarted .What will be the reason..Is there any other way to...

SQL Server 2016 DBA Training - Hi people, I've been working as a SQL developer for a number of years, with an increasing amount of DBA work...


SQL Server 2016 : SQL Server 2016 - Development and T-SQL

Encapsulating complex logic without using a scalar UDF - I have business logic for date comparisons that go beyond the typical DATEDIFF functionality.  For example, when determining the number...

Any way of joining data on two unique records, trying to cancel out matches - In MSSQL 2016 I am trying to find charges that cancel each other out. For example: if a group of...

dtexec error - Hi, 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...

T- SQL Query to fetch values -Pattern Matching - Createtable

How to choose between Clustered Columnstore & Non-Clustered Columnstore index - Hi, Would anyone please explain or point me to resources that make it very clear how to choose between the 2...


SQL Server 2014 : Administration - SQL Server 2014

Index Size Difference - Hi Experts, For testing I have created a table with a clustered index selecting all columns and inserted some data . The...


SQL Server 2014 : Development - SQL Server 2014

Whitespace in 3-part name ignored??? - Hi all, I noticed that if I use spaces and/or tabs in a 3-part name OUTSIDE the brackets , they are ignored. For...

Referencing another table throws off totals - Using Microsoft SQL Server Report Builder 3.0, This SQL query code: SELECT   tblDataPermit.Type   ,tblDataPermit.Status   ,tblDataPermit.ApplicationType   ,tblDataPermit.ApplicationDate   ,tblDataPermit.XmlDataField.value('(//*)', 'nvarchar(max)') AS UniformCode   ,tblDataPermit.XmlDataField.v

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: https://msdn.microsoft.com/en-us/library/ms189292(v=sql.120).aspx Days...

How to overcome the identity column "jump 1000" issue - Hey, Since SQL2012 MS changed something with the identity column; after a restart the value jumps with 1000 (for INT identity...


SQL Server 2012 : SQL Server 2012 - T-SQL

How to make one innerjoin to bring all value of all the substrings within a string separated by a comma - Hi,  I have two tables, answers and lookup, In my answer table I have a field named: value, it my contain...


SQL Server 2008 : SQL Server 2008 - General

The database XXXX is not accessible (ObjectExplorer) - Hello Experts, First of all I am not a DBA guy and have only limited knowledge on SQL server DB. :) We...

How to parse this, 'F0-B20170225131636437{A^CM.INIT}' - What is the best way to parse out 'CM.INIT' from 'F0-B20170225131636437{A^CM.INIT}'?

Import problem - Hello, I have 2 servers. I've created a select query to import data from server host to server destination. When...

SSIS job completion - hi i have an ssis job that picks up .csv files and posts them into various tables. when i manually run the...


SQL Server 2008 : T-SQL (SS2K8)

Table Schema with Search - Hi, Below is the my sample table schema for the Product. I will need to create search functionality on these...


Data Warehousing : Integration Services

creating table for data that rejects - I have a nightly import job that imports from a MySQL linked server into my SQL database. Sometimes data comes...

This email has been sent to {user_email}. To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.
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.
This transmission is ©2015 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com