Problems displaying this newsletter? View online.
SQL Server Central
Featured Contents
The Voice of the DBA

No State Based Deployments

This editorial was originally published on Mar 17, 2015. It is being republished as Steve is traveling to SQL in the City - Austin.

I've been studying deployment ideas and technologies for quite some time now, but it's been a focus for the last year or two as my employer is interested in this area. I've been reading books and talking to people, comparing their experiences with my own. I've found that deployments for databases are much more complex than those for other software, but essentially there are two approaches that people choose: migrations and state based migrations based deployments.

Migrations based deployments are based on tracking each change to a database in a separate script and then executing each script, in order, on your production database. This is hard to do, requires discipline, and is what most people find difficult to do. Scripts get quite numerous, ordering is hard, and deployments become scary and largely unsuccessful without a lot of care. The scripts require tweaks and fixes to work efficiently, and you need smart DBAs and developers.

State based deployments are what many people try to use. They look at the state of production, the state of development, run some tool like SQL Compare, or a process, and determine what changes need to be made to production to get it to match development. There are variations, but this is the basis for how many people try to create a deployment process. It seems easier.

The problem is that at some point a state based deployment process won't work. It's actually impossible for a state based approach to work in all situations. Simple actions, like renames, can't be handled by examining two states. In order to properly manage changes, and keep your data intact and safe, you need to understand "what" happened during the process, not just the end result.

In many situations, state based deployments can work, and they can be used successfully, but there is always the possibility that you'll need to customize your state based approach to include some migrations scripting. Keep that in mind, and make sure that any process or tool you choose has that flexibility. If you understand you need to review and modify scripts at times, you should be successful.

Steve Jones - SSC Editor

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

Redgate SQL Change Automation
  Featured Contents

DBA Walkthrough – Adding space on IBM AIX Power Machines

SOLVARIA from SQLServerCentral

Learn how to manage storage/filesystems on IBM AIX - Power machines. Learn from this demo of expanding a filesytem on AIX so that your DBA's get a high level overview of how AIX storage management works.

Introduction to the Cosmos DB Emulator

Additional Articles from Database Journal

Cosmos DB delivers a wide range of advantages over traditional SQL and NoSQL-based data stores, including support for multiple consistency levels, latency and throughput guarantees, policy-based geo-fencing, automatic scaling, and multi-master replication model. However, it is a significant departure from the traditional database management and development approach that most database administrators are familiar with. Discover the offerings that you can use at no cost to gain hands-on experience with Cosmos DB.

From the SQL Server Central Blogs - Access is denied – Using DTCPing utility between two Windows Server 2016

Cláudio Silva from SQLServerCentral

Few days ago a client requested the configuration of MSDTC (Microsoft Distributed Transaction Coordinator). NOTE: If you want to know more about it here is a nice FAQ from...

From the SQL Server Central Blogs - Exporting an .ISPAC from the SSIS Catalog via the SSIS PowerShell Provider

Aaron Nelson from SQLServerCentral

In my last blog post I showed that using the SSIS Provider could be an easier option for deploying an .ISPAC file, vs. the PowerShell script method shown in...


  Question of the Day

Today's question (by Steve Jones - SSC Editor):


The Datatimeoffset Value

It's 5:00 in the afternoon in London during in May. We can represent this time as:
DECLARE @T DATETIMEOFFSET = '2019-05-15 17:00:00 +01:00';
I want to represent this exact same time in Colorado, seven time zones away from London in May. How would I show this time in Colorado in a datetimeoffset variable?

Think you know the answer? Click here, and find out if you are right.




  Yesterday's Question of the Day (by Steve Jones - SSC Editor)

Preventing Access

I have created a role for my junior DBAs with this code:

-- ...

I have granted a number of rights to this role:

GRANT SELECT ON dbo.UserAccessRoles TO JuniorDBA
-- ...

We are having some issues with a developer and want to prevent SallyDev from accessing the dbo.DatabaseSizeInfo table. What is the best way to do this?

Answer: DENY SELECT on dbo.DatabaseSizeInfo FROM SallyDev

Explanation: The best way to do this is with another role that has DENY SELECT on dbo.DatabaseSizeInfo  and add SallyDev to that role. However, the common way for a one-off permission set here is to just DENY SELECT to SallyDev for this table. The DENY will override the GRANT. If we remove SallyDev from the role, she loses other access, which isn't likely what we want. If we DENY the role, other users lose access. Ref: GRANT - DENY -

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
ssr2 2008 upgrade to SSRS 2017 - Hi , Currently in my company ssrs report server is SSRS 2008R2 and different data sources connected with different sql servers which are sql server 2012, etc. Now I need to upgrade the SSRS 2008 R2 to latest version like  SSRS 2017. So do I need to upgrade current SQL server and SSRS versions for each […]
Old compatibility Database on newer version of SQL server - Hi All, SQL server 2008 databases moved (upgraded) on SQL server server 2017 , but because of application dependency compatibility level is not changed it is same 2008 (100) . So whether Microsoft will support these DB in future or not ? asking this because SQL server 2008 version is going to end of support […]
SQL Server 2016 - Administration
Not able to retrieve data when Listener is failed over to Second Node on AG. - On the Always On Availability Group when the Listener is on Node1 everything is working but when failed to Node 2, getting this error: Attempt to retrieve data for object failed for server 'Listener'. Invalid Urn filter on server level: filter must be empty or server attribute must be equal with the true server name. […]
Memory - Can someone please explain me I have SQL server 2012 with 24 GB of total ram in server Out of which we have set 12 GB set for Minimum Memory and 22 GB to Max memory set. So what does that mean SQL server is using only 10 GB or 22 GB?? What if I […]
collation RDS AWS - Hello everyone , I need to migrate databases to RDS AWS what worries me is that RDS he proposes the collation SQL_Latin1_General_CP1_CI_AS most of my bases are collation  French_CI_AS Does having two different snacks create a problem for me? will there be a method that allows me to test before performing a migration operation thanks
sql server statistics - I'm migrating databases to another server and concerned about statistics. Once a week I run update statistics with full scan. Problem is, this step runs for over a day and I don't want this spilling over when users are utilizing the database. I'm going to do this migration on a Saturday so, the update statistic […]
SQL 2012 - General
Process suddenly failing with permissions error [SOLVED] - We have a .NET process that runs using a particular account.  That account is set up as a SQL login, and the user has execute permissions for the agent_datetime scalar function from the msdb system database. The process has been running fine, but after an automated SQL Server update, we're now seeing this error: "The […]
Issues upgrading from 2008 to 2012 - We are testing an upgrade from 2008 to 2012 users are hitting some issues in testing that are out of my wheelhouse. Wonder if anyone would have any suggestions? Thank you for following up. However, the test for SSIS packages is very difficult, please advise. 1, To be able to do any SSIS test, I have to modify […]
SQL Server 2012 - T-SQL
SQL Date calculation - Hi, I have a requirement on my plate that driving me nuts and below dataset is in a similar fashion to what I have in our database. I would really appreciate if you can point me in the right direction. My requirement is to calculate leaves (excluding weekends) while populating End Date column. Oddly enough, […]
How to subtract time - Hi,   I have a field that is a Time(4) datatype. I need to make a computed field in the table that checks if you subtract 52 secs. from the Time field is 0 then 0 else the time field value. So I have : alter table [DMPCRU].[dbo].[VaspianCalls] add [Hold_Time] as (iif(DATEADD(ss,-52,Duration)<0,0,Duration)) But this doe […]
SQL Server 2008 - General
Is SSRS 2008 R2 can be integrated with Microsoft Dynamics AX 2012 R3 reports - Hi, My requirement is to consolidate SSRS 2008 R2 and Microsoft Dynamics AX 2012 R3 reports. Currently company has reports in these two environments(some reports in SSRS 2008R2 and Microsoft Dynamics AX 2012 R3 Reports). They want to integrate these two environments. Can someone suggest me if this is possible? or else do I need […]
sql server query for worked total and break total report using InOut attendanc - Need sql server query for gating break total, worked total and worked period result
SQL Server Newbies
multiple tables referencing single primary key - Hello - this is such a basic concept but i have never needed to explore it this thoroughly. If i have table A with primary key  - i would have thought i can have table B and C each with foreign keys referencing table A's primary id. Doesn't look like this is possible with a […]
SSRS 2014
Total Field On Monthly Grouped Matrix - I have a matrix in which data for a 10 year period is averaged by month. The expression is on the month in order provide the grouping and the query to the source data sets the 10 year period. I can get the average monthly numbers but I need to sum the total of all […] Test Forum
Looking for large images - Testing some of the image sizing issues. Here is a large image, uploaded from the toolbar.


RSS FeedTwitter

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 This newsletter was sent to you because you signed up at
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.


- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -