Problems displaying this newsletter? View online.
SQL Server Central
Featured Contents
Question of the Day
Redgate Database Devops
The Voice of the DBA
 

Why I Don’t Like Shared Development Databases

Today we have a guest editorial from Kendra Little as Steve is away on his sabbatical.

When working with Redgate customers who are starting to improve their software development pipeline and delivery practices for databases, I find that many folks are using a practice which I consider outdated and harmful for both code quality and release velocity: the shared development database. 

Shared development databases are problematic for code quality for a few reasons. First, a shared development environment limits innovation as well as initial testing: if I am a developer on a team using a shared database, I will personally be quite unlikely to try many experiments. This is because anything I change in that database could impact others; they are using the same schema and the same data. I will also be hesitant to change around test data and indexes much to do any testing for performance – after all, the more changes I make, the more risk that I could accidentally cause issues for someone else. 

There is also the risk that changes I make to a shared development database may be accidentally overwritten or picked up by someone else. This risk is major, because if someone overwrites my changes before I commit and I don’t realize it, I could end up accidentally deploying the wrong changes. Some products like SQL Source Control offer object locking to mitigate this risk, but that introduces other problems: the more objects I need to lock, and the longer I need to lock them, the more I may impact other people on my team who now need to wait to do any changing or testing of changes to those objects. 

There are even more downsides to the shared development database. Troy Hunt has done a fantastic job of documenting these problems in his classic post, “The unnecessary evil of the shared development database,” which he published in 2011.  

Since the publication of his post, the only major changes which have occurred are that SQL Server Developer Edition is now free from Microsoft (instead of available at a low cost), and developers have more options than ever before to use provisioning and storage technologies to create lightweight, writeable copies of even the largest production datasets. Many offerings for this, such as Redgate’s SQL Provision, include tooling to mask / de-identify production data and provide fast on-demand creation of databases for development and test 

While products that help you efficiently manage dedicated databases for development do cost money, slow or poor quality releases can easily cost your organization far more  so I encourage you to make 2020 the year in which you assess if you can provide better development environments for your team, using whatever tech stack is right for your organization. 

Kendra Little

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

Redgate Database Devops
 
  Featured Contents

The Connection to the Primary Replica is not Active

gana20m from SQLServerCentral.com

In this article, I’m going to talk an issue that I found when joining replica or database on secondary replica to availability group.

Do you want the chance to win Redgate goodies and books?

Press Release from Redgate

Visit the Redgate Forums today to enter the competition to win a Redgate goodie bag including a copy of Grant Fritchey’s SQL Server Executions Plans (Third Edition), plus your choice of the classic novel about DevOps, The Phoenix Project, or the follow-up book, The Unicorn Project.

Power BI Advanced Q&A

Additional Articles from MSSQLTips.com

What advanced features are available in the Power BI Q&A functionality? Also, how does the new Q&A visual work?

From the SQL Server Central Blogs - Bring Your Own Key to Azure SQL Database Managed Instance TDE

Bradley Schacht from Bradley Schacht

Last year Azure SQL Database Managed Instance saw the introduction of bring your own key (BYOK) functionality for transparent data encryption (TDE). This functionality has been in the singleton...

From the SQL Server Central Blogs - Changing the owner of a schema removes all direct permissions of objects owned by the schema.

Kenneth.Fisher from SQLStudies

This is one of those things that probably doesn’t happen all that often, but bit me in the … elbow ... Continue reading

 

  Question of the Day

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

 

SQLCMD OS Commands

I am writing a SQLCMD script in SSMS with SQLCMD mode. I want to execute an operating system command to perform an action, like make a directory. How do I format this command in my script?

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

 

 

  Yesterday's Question of the Day (by John Mitchell-245523)

Assignment of a value to a variable

What will be the respective values of j0, j1, j2 and j3 returned by this code?

CREATE TABLE #John (j int);
INSERT INTO #John (j) VALUES (1);
DECLARE @j int;
SELECT @j AS j0;
SELECT @j = j FROM #John;
SELECT @j AS j1;
DELETE FROM #John;
SELECT @j = j FROM #John;
SELECT @j AS j2;
SET @j = (SELECT j FROM #John);
SELECT @j as j3;

Answer: NULL, 1, 1, NULL

Explanation: Reason: when assigning variable values with SELECT, an empty result set preserves the existing value of the variable.  When SET is used, an empty result set resets the value to NULL.  I found several blog posts that talk about this behaviour, but it doesn't appear to be officially documented by Microsoft, who only mention what happens with SELECT. Reference: SELECT for Local Variables - https://docs.microsoft.com/en-us/sql/t-sql/language-elements/select-local-variable-transact-sql?view=sql-server-ver15#remarks

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
Space issue on a table - All,   I am working on implementing table partitioning on a existing table. The table has clustered columnstore index. I have to drop the index and recreate it to use the partition scheme The table size before dropping the index is 200 GB and after dropping the index the table size is 4 TB Do […]
SQL Server 2017 - Development
Insert New Rows in sql based on difference between 2 dates - Hi,   I want to create a new records based on the difference between Start and End dates when StartDate is not NULL . The New value created can be first of every month . I need this field for reporting purpose to get a count of employees my month .   CREATE TABLE #MonthsYearBetwenDates […]
View runs slower - I have the following query SELECT DateADD(day,9-DATEPART(dw,cdata .call_datetime ),cast(cdata .call_datetime as date) ) as effective_date , A.EmployeeID AS employee_id , COUNT(cdata .Extension) AS metric_value --, ISNULL(MS.metric_id,'') AS metric_id FROM ( SELECT EmployeeID FROM ad.it_activedirectorydata WHERE [Enabled] = 'True' ) A INNER JOIN [vw_exampl] cdata ON A.EmployeeID =cdata .employeeID INNNER JOIN tbl2 ON A.employyeid=tbl2.employeeid GROUP BY […]
SQL Server 2016 - Development and T-SQL
Update parent row from child row that has partial data. - On the same table I have table rows that are broken from the import and went to the next row. I need to bring it back to the parent row. For example ID 1  Col3  supposed to have the value 'GHI' but the 'I' went to the 2nd row or ID 2 Col1 cell. Same […]
Parallel Load thousands csv Files - Hi, what is the fastest way to load thousands of csv files with the same structure into an OnPrem SQL DB (staging) - day by day? A parallelization would be ideal. These files have the customer number in the FileName (SSIS, Bulk Insert, Stored Proc., Data Factory, partitioning ...): Thanks Regards Nicole
Administration - SQL Server 2014
AWS instance storage - We are planning to use one of AWS instance for sql server and wanted to know if the instance storage drives can be encrypted for storing PCI and PII data. Coudn't find info on AWS documents. https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/InstanceStorage.html  
Replication question - Im an Oracle DBA by trade and been tasked with setting up transaction replication in SQL Server 2014 from one host to another. I set up a test DB, configured a publication, set up a subscriber DB, set up subscriptions and transactions are replicating.  All good to here. In oracle we have dataguard which allows […]
Software to send Report - Hi Experts, Currently we are using SQL Server and Database Mail to send reports and with new projects and requirements it seems like the Report sending will increase . Is there an alternative for sending report than sending from SQL Server\Agent?
SQL Server 2012 - T-SQL
Recursive CTE - Batches - Hi Folks I'm trying to group rows into batch and adding batch to query to fetch data from system. I know there is lot of questions why it is required. I'm trying fetch data from Oracle SQL developer.  The issue is last row of batch is repeating in next batch. I added query for better […]
XQuery - Use column value to display correct node - Hi All First time with XQuery and despite trying to read as many guides as I can, I'm hitting a bit of a mental block. We have a form design application that stores templates and details of the questions that relate to each template in relational tables. The attributes for the template and questions are […]
Fastest way to load a CSV file in SQL Server Table - What is the fastest way to load a file in SQL Server Table ? I have large CSV Files ( 3-4 GB ) which I load through BULK INSERT. I find this slow compared to BCP. I am unable to load it using BCP as the data types are not supported ( INT,VARCHAR, etc) Please […]
SQL Server 2019 - Administration
OLE DB 0x80004005 connection error trying to run SQL HA Maintenance Plans - Hi, I'm getting a connection error when I try to run even the most basic plan, fails with scheduled or manual runs. The user is both a local-admin on all 3 nodes, and a SQL SA member. All engine processes and agent processes run-as that same AD account too. Everything works great (failovers etc), just […]
COHESITY for Backup/Restore? - Any one have any experience with using Cohesity as the backup/restore platform instead of doing native SQL backups? With TSM, we always did a SQL backup, and then swept that up to tape. In our new backup system based on Cohesity, my backup admins want to backup up the database and log files directly from […]
SSDT
VS 2019 - Schema Compare - Ignore Fill Factor Not Working - Got a strange one with VS2019 and a DB Schema Compare. When comparing two tables on ServerA to ServerB, it is throwing up a difference for the fill factor being 80/90 which is right. However in my options I have "Ignore Fill Factor" enabled so I shouldn't be seeing these differences. As we testing different […]
Integration Services
SSIS compoenents - For SSIS to pickup any dll componenet where do we need to deploy?  some dlls should not go to GAC. in that case what is default localtion for SSIS to pick up?
 

 

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

 

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