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

Featured Script

Function for Getting Interval Dates by days,months,years.

The Voice of the DBA

DevOps and Security

DevOps is a buzzword these days, and like many of the hyped concepts written about, it has a lot of meanings. There is this idea releasing software more often, using automation, having various groups talk to each other, and more, all of which we might see as common sense. However other DevOps ideas, such as releasing more often (with potentially less review), being willing to break applications and fix them quickly, having developers able to release code to live, production environments, these don't seem to be ideas that would enhance security for most organizations.

However, that's not necessarily the case. Security and DevOps practices aren't necessarily mutually exclusive. I ran across a piece from the security perspective, looking at some of the ideas in DevOps that can actually enhance security.

Writing more code, especially around the configuration and infrastructure, allows versioning, auditing, and more that can ensure we have fewer mis-configured systems. Adding some Desired State Configuration (DSR), with some automated testing of this code, could ensure that the changes made don't open up security holes. Or, at least, allow us to determine who made the change and when the issue appeared. These are important for understand security risk.

There are also the ideas of measurement, metrics, and feedback, which are important for ensuring security. After all, anomalous behavior should be investigated, as this could be a sign of intrusion. For databases, it's especially important with the large number of clients that connect to our systems. Adding DevOps style monitoring can allow us to determine if a workload is normal, or perhaps a sign of intentional, accidental, or malicious data query activity.

I enjoyed the piece, and I'd recommend you read it. Plus, whenever I see "snowflake" in an article, I think of Grant and want to read further to see how someone else has used the same analogy he does.

Steve Jones from SQLServerCentral.com

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

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.2MB) podcast or subscribe to the feed at iTunes and LibSyn.

The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music.

Everyday Jones

Follow Steve Jones on Twitter to find links and database related items and announcements.

SQL Prompt v7 now supports Visual Studio 2015 as well as SSMS 2016

It also lets you create your own placeholders in snippets, open an actions list when you select text in a query, use different tab colors, and find more invalid objects. Find out how.

How to track every change to your SQL Server database

See who’s changing your database, alongside affected objects, date, time, and reason for the change with SQL Source Control. Get a full change history in your source control system. Learn more.

Want a SQL comparison tool you can trust?

Save time comparing and deploying SQL Server database schema, create error free deployment scripts in minutes, and fix errors caused by differences between databases. It’s no coincidence 71% of the Fortune 100 companies use Redgate SQL Compare! Try SQL Compare for free.

Featured Contents

Configuring geo-replication for an Azure SQL Database

Arun Sirpal from SQLServerCentral.com

This article will discuss geo-replication for an Azure SQL Database (PaaS) and the steps required for setup via the Azure portal and TSQL. More »

The Plain Simple Password Page Not So Simple Anymore

Additional Articles from SimpleTalk

The requirements for the management of passwords have become far more complex over the past few years in response to the increasing sophistication of security breaches. Nowadays, you need to provide a range of features such as hashing, routine change of passwords, preventiion of repeat passwords, email confirmation, auto-generation of random passwords and password quality checks. Dino Esposito explains. More »

From the SQLServerCentral Blogs - Minimizing Cloud cost

Kenneth Fisher from SQLServerCentral Blogs

The cloud can be expensive. I had this hammered home the other day by a simple mistake. I’m currently working... More »

From the SQLServerCentral Blogs - Azure Cortana Intelligence Suite – with Azure Data Catalog

Rolf Tesmer (Mr. Fox SQL) from SQLServerCentral Blogs

I presented at our local Melbourne SQL Server User Group (SSUG) this week on Azure Cortana Intelligence Suite – and the integration... More »

Question of the Day

Today's Question (by Steve Jones):

I want to build a view that works like this:

 CREATE VIEW SalesPersonTotal (SalesID, TotalSales) AS SELECT soh.SalesPersonID , SUM(sod.LineTotal) FROM Sales.SalesOrderHeader AS soh INNER JOIN Sales.SalesOrderDetail AS sod ON sod.SalesOrderID = soh.SalesOrderID GROUP BY soh.SalesPersonID;

Is this a valid view definition?

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: Views.

We'd love to give you credit for your own question and answer.
To submit a QOTD, simply log in to the Contribution Center.

Securing SQL Server - Written by Denny Cherry, a Microsoft MVP for the SQL Server product, a Microsoft Certified Master for SQL Server 2008, and one of the biggest names in SQL Server today, Securing SQL Server, Second Edition explores the potential attack vectors someone can use to break into your SQL Server database as well as how to protect your database from these attacks. In this book, you will learn how to properly secure your database from both internal and external threats using best practices and specific tricks the author uses in his role as an independent consultant while working on some of the largest and most complex SQL Server installations in the world. This edition includes new chapters on Analysis Services, Reporting Services, and Storage Area Network Security.

Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

I have this JSON document:

  {      "firstName": "John",      "lastName": "Smith",      "age": 25,      "address":      {          "streetAddress": "21 2nd Street",          "city": "New York",          "state": "NY",          "postalCode": "10021"      },      "phoneNumber":      [          {            "type": "home",            "number": "212 555-1234"          },          {            "type": "fax",            "number": "646 555-4567"          }      ]  }

If I want to query this document (stored in a variable @d) for the complete address, which path would I include in place of XXX in they query below:

 SELECT * FROM OPENJSON(@d, 'XXX')

Answer: $.address

Explanation:

The path in a JSON document is similar to that in an XML docment. In this case, we use the $. to signify the context item, in this case, the main document. The dot then separates the document from the path, in this case, the address.

Note: JSON documents are case sensitive.

Ref: OPENJSON - https://msdn.microsoft.com/en-us/library/dn921885.aspx

JSON Path Expressions - https://msdn.microsoft.com/en-us/library/mt577087.aspx

» Discuss this question and answer on the forums

Featured Script

Function for Getting Interval Dates by days,months,years.

BHAVIN GALA from SQLServerCentral.com

Pass the values as per below in parameter for getting output.

(@EntityId INT, /*( (Not mandatory) can be pass as null or 0) */

@Frequency VARCHAR(40), /*( From this 1)'Daily', 2)'Monthly', 3)'Yearly')*/

@StartDate DATETIME, /*( Start date of month from where range date starts eg.'01/01/2015' or '01-Jan-2015')*/

@EndDateAvailable BIT, /*( '0' for false and '1' for true)*/

@EndDate DATETIME, /*( First date of last month if @EndDateAvailable is true (till this month range will get set))*/

@RepeatedTimes INT, /*( This is for how many records you want to see from date range)*/

@Intervals INT, /*( As per @Frequency how much distance you want to set eg. in monthly you want date after every 2 month then set '2')*/

@DateType VARCHAR(40), /*( From this 1)'FixedDate', 2)'FixedDay')*/

@FixedMOnth INT, /*( Put month number if required)*/

@FixedDATE INT, /*( If @DateType = 'FixedDate' then set date between 1 to 31 (if @DateType is 'FixedDay' then set null))*/

@FixedDay VARCHAR(40), /*( If @DateType = 'FixedDay' then set day between 'Monday' to 'Sunday' (if @DateType is 'FixedDate' then set null))*/

@DayNo INT, /*( If You want perticular date like 4th sunday of every month then set it as '4' with @DateType = 'FixedDay')*/

@FixedMonthName VARCHAR(40)) /*( This is required when you are using @DayNo functionality and want to find for perticaular month eg. 'January')*/

 --SELECT * FROM funGetIntervalDates( @EntityId, @Frequency, @StartDate, @EndDateAvailable,@EndDate,@RepeatedTimes, @Intervals, @DateType, @FixedMOnth, @FixedDATE,@FixedDay,@DayNo,@FixedMonthName) --Eg. for @RepeatedTimes --> select * from dbo.funGetIntervalDates(null,'Daily','01-Jan-2014',1,'01-Jan-2018',8,1,'FixedDate',4,05,'Monday',2,'January') --(here 8 records btw two dates) --Eg. for @RepeatedTimes --> select * from dbo.funGetIntervalDates(null,'Daily','01-Jan-2014',1,'01-Jan-2018',10,3,'FixedDate',null,null,null,null,null) --(here 10 records with 3 days interval btw two dates) --Eg. for @DayNo --> select * from dbo.funGetIntervalDates(null,'Monthly','01-Jan-2015',1,'01-Jan-2016',10,1,'Fixedday',1,2,'Thursday',3,'January') --(here 10 records with every months 3rd 'Thursday' with week number from January)

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 2014 : Development - SQL Server 2014

treatment imbuements routinely close by - treatment imbuements routinely close by a wide edge most by far of that is going to modify. Our condition Revitasence...

Simple CLR request - I'm trying to create what I believe should be a very simple Table Valued Function CLR. Here's a T-SQL version...

SQL Server 2012 : SQL 2012 - General

The family has been billed as much as - [url=https://www.shapeways.com/shops/copa-america-final-2016-live-streaming-argentina-vs-chile-online-telecast]Copa America Final 2016 Live Streaming[/url]

dutch assen moto - [url=https://www.shapeways.com/shops/dutch-grand-prix-2016-live-streaming-motogp-assen-telecast]Dutch Grand Prix 2016 Live Streaming[/url]

LS DR Testing on Always On setup - Hi All, I need your experience and advice for below test. I have setup like Always on setup with on...

SQL Server 2012 : SQL Server 2012 - T-SQL

Sql Duplicate Values - Hu gurus, I am inserting values through xml. The following is the stored procedure i am using. i do not...

SQL Server 2008 : SQL Server 2008 - General

physical events such as scoring - [url=https://www.shapeways.com/shops/copa-america-2016-final-live-stream-argentina-vs-chile-soccer-26-06-2016]Copa America 2016 Final Live Streaming[/url]

bougies dallumage moteurs essence - [url=http://www.drawdeck.com/jobs/argentina-vs-chile-live-stre-amfinal-onlinepreview-06272016-0]Argentina vs Chile Live Streaming[/url]

competition returned to being held every - [url=https://www.shapeways.com/shops/argentina-vs-chile-li-ve-str-eaming-copa-america-final-2016-time-tv]Argentina vs Chile Live Streaming[/url]

Même les hôtels les plus - [url=https://norvegcivilalap.hu/hu/node/18420]Kohler Grand Prix 2016 Live Streaming[/url]

remarkably similar to modern football - [url=https://norvegcivilalap.hu/hu/node/17998]Kohler Grand Prix 2016 Live Streaming[/url]

SQL Server 2008 : T-SQL (SS2K8)

Generate n random int subsets - Howdy SSC, I'm trying to generate some realistic test data to do some load testing. The app I'm testing takes a...

Need algorithm for Complex looping logic - Dear All, Hope all are good. I am Sql developer but i was in support ,now in developement, i am...

SQL Calendar Table based on Start and End Date Help - Hello Everyone, I need some help in setting up a Calendar Table or an Staging Table that I can use for...

SQL Server 2008 : SQL Server 2008 High Availability

Clustering with problem database - We have an existing SQL cluster (two-node active/passive) that's been in place for a few years and has been very...

SQL Server 2008 : SQL Server 2008 Performance Tuning

Rebuilding Indexes - I am running SQL Server Express and we have to perform the rebuilding of indexes manually via query and verify...

Reporting Services : SSRS 2016

Main Report not picking up Sub Report - I have a report that I deployed to the HOME section of my SSRS 2016 portal. This report uses a...

Data Warehousing : Analysis Services

How to propagate relationships for calculated members in SSAS? - I am stuck in a situation which I believe should have a ready solution because it looks like a common...

SQL Server 2005 : SQL Server 2005 Integration Services

Rename file - prefix with file date - I receive three versions of a file each day, which I then import. But the file names are such that...

SQL Server 7,2000 : General

Existing Meta Data Services packages should be moved to SQL Server storage - In preparation for upgrading to SQL Server 2005, I run the Upgrade Advisor and it gave me the warning: Existing...

This email has been sent to newsletter@newslettercollector.com. 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.