SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

Statistics Profile Results

The Voice of the DBA

Practice Those Scripts

I've written lots of scripts that were deployed to production. I've often had another set of eyes look them over, and still, we made mistakes. In fact, a recent Salesforce outage was blamed on a poorly written database script that gave users more rights than they should have gotten. There wasn't an actual outage caused by the script, but since customers might have been able to see data and change from other customers, Salesforce took its own service down to prevent anyone from doing so.

I'm a big fan of DevOps, and certainly including the database in a DevOps process to build a better software development flow. Part of that is ensuring that you can deploy by practicing the act multiple times. In a database world, this would mean that we run a script not just on a development server, but on a QA server, on a staging server, on any other environment we can find to practice and test the deployment. At that point, we should be confident of execution on the production system without issues.

Good in theory, but sometimes you can't easily test scripts in intermediate environments. I think changing security is a place where it can be hard to actually test things, especially if specific accounts are referenced that might not need or have access in that environment. Certainly some data changes might be easily be tested in intermediate environments, especially when these refer to configuration differences, like email or messaging systems.

In this case, I suspect the "access changes" were data changes that updated values in certain tables in the Salesforce application. In that case, why wasn't this tested? A restore of production to a staging environment would allow developers to test their script. It's not multiple executions on intermediate servers, but it is better than nothing.

I'm sure many of you have had the need to execute scripts to change data, alter permissions, or something else in production. Could the same thing that happened to Salesforce happen to you? What precautions do you take, or what would you recommend to prevent this type of issue. Let us know today.

Steve Jones - SSC Editor

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

Featured Contents

Group Managed Service Accounts (gMSAs) in SQL2016

michael.minarzick 34627 from SQLServerCentral.com

Implementing Group Managed Service Accounts (gMSAs) in existing SQL Server instances with AlwaysON

10 steps you can take to be compliant worldwide – free whitepaper

Additional Articles from Redgate

Learn how data protection legislation is changing around the world and the 10 steps database teams can take to ensure compliance and defend against data breaches.

What’s Better, CTEs or Temp Tables?

Additional Articles from Brent Ozar Unlimited Blog

Brent get's asked this question a lot, so here he has set up an example with the Stack Overflow database. His query’s goal is to find the top 5 Locations, and list the users who live in those top 5 Locations, alphabetized by their DisplayName.

From the SQL Server Central Blogs - The Data Platform is Important

Steve Jones - SSC Editor from The Voice of the DBA

I didn’t attend the //build/ conference this year and am slightly disappointed. Only slightly, since I was in these cities in May already and didn’t need another trip: London,...

From the SQL Server Central Blogs - Why Security Through Obscurity Is Bad (Alone)

K. Brian Kelley from Databases – Infrastructure – Security

Security through (by) obscurity is where we try to protect an asset by hiding it. Anyone who has ever played the game Capture the Flag knows that a motivated...

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Statistics Profile Results
How do the results from SET STATISTICS PROFILE appear in SSMS?
Think you know the answer? Click here, and find out if you are right.

Yesterday's Question of the Day (by mkdm)

Service Broker Disconnected Conversations

I created a simple Service Broker implementation in which the initiating procedure does no further processing after sending the message. Learning that this is an example of the "fire-and-forget" anti-pattern, I query sys.conversation_endpoints and find a very large number of conversations in DI (Disconnected Inbound) state. What two simple changes could I make to correct this?

Answer: Use queue activation to create a stored procedure that ensures conversations are ended on both sides., In the BEGIN DIALOG command, specify a conversation lifetime

Explanation: A queue activation stored procedure on the initiator automatically processes messages received by the target service. When an END CONVERSATION message is received, the activation procedure should issue a corresponding END CONVERSATION, ensuring that the conversation has been ended on both sides. Following this, the conversation is put into the CD (Closed) state, and will eventually be deleted. https://sqlperformance.com/2014/03/sql-performance/configuring-service-broker https://docs.microsoft.com/en-us/sql/t-sql/statements/create-queue-transact-sql?view=sql-server-2017 The BEGIN DIALOG command has a LIFETIME clause, allowing you to specify a time after which the conversation is automatically deleted. https://docs.microsoft.com/en-us/sql/t-sql/statements/begin-dialog-conversation-transact-sql?view=sql-server-2017

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 2017 - Administration

Overall Guidance and Suggestion of environment - First off thanks for your time and much appreciated in all suggestions or ideas. The environment i have is totally different than any i have ever been a part of and hoping that i can get some assistance. It will go against pretty much everything theory based and pretty much shooting from the hip. okay..here […]

SQL Server 2016 - Administration

Automate copy and restore of all PROD databases to a separate server - Hi, We are using ola hallengren backup script to backup databases backups. Now, I'm planning to automate copying those weekly full backups to a separate server and restore them. Can you please advise if there are any automated methods or scripts available to achieve this? Thanks, -jdc

SQL Server 2016 - Development and T-SQL

Trigger to update/Insert record - I have a table that resides on 2 different Servers with the same database name. I want to create a trigger that updates db2.table2 if db1.table1 has a record inserted or updated and visa versa, if db1.table1 gets updated or inserted then update db2.table2. The table has the same structure. How can i do this […]

T-SQL - Ways to get customized rows? - We have a table where we keep rows with vanilla settings for products, and customized rows per product per customer as they see fit to override. When a customer does not override, the vanilla rows with vanilla settings are selected but when customized rows exists for products and customers, the customized rows must be returned […]

Select today's date minus 6 months - I am pretty new to SQL Server. I have created date variables in Access but need to transfer them over to SQL. The syntax is different so I need a little push in the right direction. I am doing a query that selects a date between 6 months from today and 1 month from today […]

Administration - SQL Server 2014

Index fragmentation - hello to everyone , i notice in database tha some indexes the total fragmentation is more than 50%,60%... is this bad for the database performance.is there a way to reduse this percent, may be with rebuild index task?

Development - SQL Server 2014

Declared datetime Parameter not working correctly in sql 2014 - We have a Data base we migrated to 2014 from 2008, everything is working fine except we have a stored proc that has the following it just hangs, but if I replace the parameter in the select statement with getdate() it works. or if i change the compatibility mode to 2008. when I am debug […]

SQL 2012 - General

Plan to migrate to new SQL server... - I've been tasked with moving our production SQL databases to a new server, and in order to minimize the impact on applications we've decided to try renaming the new server to be the same as the old server. So the plan I've come up with is as follows: Set up log shipping to the new […]

SQL Server 2008 - General

SQL Server Agent - Scheduled Jobs Log - Hello, Is there a SQL log somewhere that tracks date and changes made to a Scheduled Job? What I am trying to is determine when a specific Stored Procedure has been commented out within a Scheduled Job Step. My guess is that the logs will not be this detailed, but even Modification Dates would be […]

Reporting Services

Change Shared Data Source Programatically? - We have a shared datasource which mostly will point at a report db, but occasionally will have to switch to live data, is there a way to programmatically change the db its pointed at. Maybe as a step on failure/success of sql agent job? Thanks in advance. (I know I can do this […]

How to create/edit RDLC SSRS Reports - I'm relly hoping someone here has had to work with the RDLC version of SSRS reports because Ive had no luck on the MSDN forums with this. Its easy and I mean EASY to find everything about creating rdl SSRS report files but when it comes to the rdlc variant its like trying to find […]

ssrs 2012 performance issue - I have an ssrs 2012 report that takes along time to run. I would like to look at the execution plan to see what I can do to speed up the main part of a report. Can you tell me how I can look at the execution plan in the SSRS report and/or running the […]

SSRS 2016

How do I get SSRS to connect to an older version of MySQL? - Three weeks ago I was tasked with working on a SSRS report that to run against a MySQL database. Until that time I'd never done anything with MySQL, so that's a learning curve as well. The only way I know of connecting to MySQL is with an ODBC connection. (As an aside, if there's another […]

Powershell

Multiple Invoke-SQLCmd don't produce output - Hi, I am running below 3 Inovke-SQLCmd satatements on a powershell window. I am expecting and output from all 3, but only the first one gives me the data. Below is what I get as output. The commands give me output when running one at a time but not when all 3 are executed together. […]

Integration Services

Tabular Model - Newbie Question - Hi All, I am looking after my first tabular model and was wondering if it is correct that you can only create DAX measures within the measure grid in model.bin file via Visual Studio. As the previous multi dimensional model I worked on used MDX for the measures. Thanks

This email has been sent to {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.