SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

Migrate databases from SQL Server to SQL Managed Instance

The Voice of the DBA

Daily Coping Tip

Let go of self-criticism and speak kindly to yourself

I also have a thread at SQLServerCentral dealing with coping mechanisms and resources. Feel free to participate.

For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from The Action for Happiness Coping Calendar. My items will be on my blog, feel free to share yours.

Patterns and Potential Problems

I saw a post recently from a developer that needed to refactor and rename a table in a live system. The post describes a pattern for doing so and gives the steps taken, though not the actual code. I like the pattern overall, and I think it can work well in many situations. It's for a PostgreSQL table, so I don't know what restrictions might be different from SQL Server, but this type of pattern can work for SQL Server as well.

It also could be problematic. Using the famous "it depends", there could be issues with this pattern, depending on your workload and how your application is structured. The triggers in use could also be an issue in some environments, as they create an additional load.

The biggest concern I have with this pattern is the copying of the data. Likely this is something that always works on a developer's machine with a few dozen or even hundreds of rows of data. If this is millions, or tens of millions, the copy could end up taking substantial time. There is also the issue of changing data, with data being added or changed in the table, separately from being copied out. Depending on your locking and concurrency schemes, you could miss data.

Or you could just lock the table and cause issues for clients. Both things that might not show up in developer testing. The lesson here is that changes to big tables need to be tested in a big way.

This isn't to say the pattern is bad, but that you should be aware of the potential pitfalls and then develop mitigation strategies. One way to get around the data issue might be choosing a way of copying over new or changed data after the initial data movement, or maybe even a cleanup load later after the new table is online. There are many possible ways to mitigate issues, if you take some time to think about the potential issues and then come up with a solution.

When we are looking to make changes to our system, patterns are important to help us and others adopt the processes that work well. As we find and develop patterns, we need to ensure that we understand the strengths and weaknesses, and choose what's best for us, with mitigation strategies to get around the potential problems. There often isn't a perfect solution, and ensuring you and your team understand the limits of your chosen approach, helps ensure that we deploy code that not only works, but is deployed without causing issues.

Steve Jones - SSC Editor

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

Featured Contents

Stairway to DAX and Power BI - Level 14: DAX CALCULATE() Function: The Basics

Bill Pearson from SQLServerCentral

Business Intelligence Architect, Analysis Services Maestro, eight-year Microsoft Data Platform MVP and author Bill Pearson introduces the DAX CALCULATE() function, discussing its syntax, basic uses and operation. He then provides hands-on exposure to CALCULATE(), focusing largely upon its most basic uses in evaluating an expression in a context that is modified by specified filters.

One Flyway Migration Script for Diverse Database Systems

Additional Articles from Redgate

How to create a single set of SQL migration scripts for Flyway that we can use across multiple database systems, or for all regional variants of a database.

Disable or Enable All SQL Server Agent Jobs

Additional Articles from MSSQLTips.com

In this article we look at a PowerShell script that you can use to centrally enable and disable any SQL Server Agent Job on any instance of SQL Server.

From the SQL Server Central Blogs - Timeseries Analytics Capabilities, and Azure Data Explorer (ADX)

Rolf Tesmer (Mr. Fox SQL) from Mr. Fox SQL

I've had a few recent conversations where customers/partners were encountering scale concerns in existing timeseries database applications hosted outside of Azure, and wished to explore the native services in...

From the SQL Server Central Blogs - CDOT Bar Chart Makeover

Meagan Longoria from Data Savvy

As I was browsing Twitter today, I noticed a tweet from the Colorado Department of Transportation about their anti-DUI campaign. Shown below, it contains a bar chart that appears...

Question of the Day

Today's question (by Evgeny Garaev):
	Migrate databases from SQL Server to SQL Managed Instance
I want to migrate my multi-terabyte database from on premises SQL Server to SQL Managed Instance in Azure. Which technology will help me to achieve this?
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)

Azure Storage Copies

How many copies of my data are created with Locally redundant storage (LRS) in Azure Storage, the cheapest option?

Answer: 3

Explanation: Three copies of your data exist. Ref:

Azure Storage: Blob and Data Lake Storage Gen2 - https://www.sqlservercentral.com/articles/azure-storage-blob-and-data-lake-storage-gen2
Azure Storage Redundancy - https://docs.microsoft.com/en-us/azure/storage/common/storage-redundancy

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

What other useful DBA reports do you suggest? - What other useful DBA reports do you suggest? Maybe something we would run every month or so to check that our environment is configured/running optimally. We have these so far: see screenshot. I also already have (adding soon) certain reports on block leaders and Waits report based on sp_whoisactive, report on 'Worst of the Worst […]

SQL Server 2017 - Development

SSIS - Write table to multiple files - Hi there, everyone. I have an SSIS package where I have to write a large table to smaller csv files. I have created a stored procedure as below that is called by an OleDb Source inside a workflow task. This then needs to write each result to a file. I'm very confused as to how […]

Speeding up Query - Hello, I am new to sql and learning my way around writing views for our company. Any tips and tricks to make this query a little faster or streamlined? SELECT TOP (100) PERCENT ord.orderid, ord.lt_orderid AS LTOrderID, ord.datetimecreated, ord.BookedByDT AS DateTimeBooked, us.firstname + ' ' + us.lastname AS BookedByStaffer, ord.VoidDT AS DateTimeVoided, ord.CancelledByWho, ord.voidreason AS […]

Need help sorting based on 2 different column DateTime - I've this table and data as following, CreatedDate CANNOT NULL. LatestRepliedDate can NULL CREATE TABLE [dbo].[IncidentMasterSimulation]( [Id] [int] NOT NULL, [IncidentDate] [datetime] NOT NULL, [IncidentDateDisplay] [nvarchar](100) NOT NULL, [HowLong] [nvarchar](100) NOT NULL, [IncidentNo] [nvarchar](100) NOT NULL, [ProjectName] [nvarchar](100) NOT NULL, [Categories] [nvarchar](100) NOT NULL, [PriorityLevel] [nvarchar](100) NOT NULL, [IncidentDescription] [nvarchar](100) NOT NULL, [CreatedBy] [nvarchar](100) NOT […]

SQL Server 2016 - Administration

Resource Monitor - Performance Issues - Hi. Last Weekend I have migrate a server with SQL Server 2016 ENT to another machine, and since then, I have facing frequently slow performance issues, the server become unresponsive sometimes. And always I see this message on Windows Event Viewer when peformance degrades. Resource Monitor (0x2290) Worker 0x0000031D8EE88160 appears to be non-yielding on Node […]

How to get a SQL/CSV list of SCOM alerts and rules - Hello experts, I've been tasked with tuning SCOM alerts for our database environment. Getting the alerts in emails is a little overwhelming and hard to manage. I don't see a way to export a list from the SCOM console, where I have to click through alerts one by one. Does anyone know how I can […]

SQL Server 2016 - Development and T-SQL

Dictionary Lookup - Hi I have hundred million ID, NAME pairs stored in SQL Server as a table that is accessed by C# code, which has a responsibility to lookup NAMEs corresponding to million IDs at a time. C# code has these million IDs stored in an array, before invoking SQL module. Columns of the SQL table: […]

Administration - SQL Server 2014

Reclaim disk space after truncate - Hello, We have truncated a huge table (50+ million) rows in sql server which was created for testing purpose. However the disk space is not released back. Any pointers on how to reclaim the disk space after truncating without shrinking database?

Development - SQL Server 2014

how to get quantity from yesterday 6pm till today 6pm - Hi, I am looking to create a case statement that gets different quantities. For example, I would like to see how many orders were created between 6pm yesterday till 6pm today, how much created between 3pm yesterday till 3pm today, and how much was dispatched between 3pm yesterday till 3pm today. I created this case […]

SQL 2012 - General

SQL 2012 Service Pack 4 - Cant get TLS 1.2 to work - Hi all, I'm pulling my hair out here trying to get tls 1.2 connections to work from our webserver to the database server. Our 2012 server is patched to service pack 4 so it should be 1.2 enabled. The SQL server native client which is used for the connection is at version 11.0.7462.06 (I don't […]

Database Integrity Issues - To avoid a lot of contention during peak business hours we separated our integrity checks into weekday and a weekend run. During the week we do a PHYSICAL only and on weekends when activity is lesser, we run a full blown integrity check. While it appears to be inconsistent, sometimes we get errors for a […]

Rounding problem (?) using DATEDIFF (minutes) - I have a query that is using DATEDIFF to calculate the number of minutes, of course as an integer, but I may need the extra precision of showing this as a decimal (?). Also I'm looking for a way of summing the time based on "EmpID" (using CTE?): SELECT T1.[EmpID], Convert(Datetime,T1.[Time]) AS [Time], MIN(Convert(Datetime,T2.[Time])) AS […]

SQL Server 2019 - Administration

SQL Server 2019 Distributed Availability Groups - Hello, I am attempting to create a SQL Server Distributed Availability Group in my Azure EC3 environment. When I attempt to alter my Distributed AG on AG2 using the below command, I get an error as shown below the command. ALTER AVAILABILITY GROUP [WOASQLDISTAG]JOINAVAILABILITY GROUP ON'WAOAG1Hfv96xWgv' WITH(LISTENER_URL = 'TCP://WAOAG121jAavqBc.WAOAG1.com:5022',AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,FAILOVER_MODE = MANUAL,SEEDING_MODE = MANUAL),'WAOAG2cJ22S7mQO' […]

SQL Server 2019 - Development

How to split Xml columns or Json into records - I have to import every days from 1000 to 100000 records. Bulk insert create one record with the complete xml file. its great an very quick. Can we create directly with a bulk insert one record per order, and one column for the xml ot he order. Or how can we do after bulkinsert, to […]

Reporting Services

Toggle Item Showing First Row of each group and not collapsing all - If I don't use the Toggle Item and just set detail to Hidden = True, all detail rows are hidden. If I then add a Toggle Item of Textbox that's in the group header, it works, however, it always now shows the first row in each group and the + sign then will show all […]

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.