Database Snapshot Use Case: Service Migration (SQLServerCentral 12/11/2018)

[SQLServerCentral - www.sqlservercentral.com]( A community of more than 1,600,000 database professionals and growing Featured Contents - [Database Snapshot Use Case: Service Migration]( - [Adopting Compliant Database DevOps at PASS]( - [SQL Server 2019 Community Technical Preview (CTP) 2.1 is Available Now]( - [Forcing a Plan That Has a Plan Guide]( (From the SQLServerCentral Blogs) - [Email Notifications in Azure Data Factory: Failure is not an option]( (From the SQLServerCentral Blogs) The Voice of the DBA I may need to re-think the use of table variables Today we have a guest editorial as Steve is out of the office. I am not sure how many of you use table variables. I would presume many of you that have used them and got burned by them in the end. At first look, a table variable seems like a great way to use a sub set of data that you may need to join to or update in a SQL process. The initial testing goes well and everything seems to perform great. Then the code goes to production, the table variable ends up with way more data than you expected and the performance issues begin. If you aren’t familiar with table variables, they are similar to temp tables, except you don’t need to create and drop them like temp tables. You use a declare statement to define the table variable. There are some downsides to table variables. You cannot create non-clustered indexes, or create constraints, or default column values on them. You also cannot create statistics on them. These detractions aside, they can still be useful in the right situation. I don’t know how you are, but once I have had a bad experience with something I tend to shy away from it in the future. It has come to the point for me that, I won’t use a table variable unless I am extremely certain that the total number of rows in the table variable will only be a few hundred at most. Of course, this self-imposed limitation also limits their usefulness. I recently came across an article by Brent Ozar, discussing how table variables have changed in SQL server 2019. If you didn’t get a chance to read it, I would recommend it, you can see it [here]( He discusses how the current cardinality estimation for table variables is way off. In his example he has over one million rows in the table variable, but the query plan thinks there is just one row. You can see how that might cause some performance issues. He then shows the same example in SQL Server 2019 and the row estimation still isn’t perfect, but it is much better and so is the performance. So now I have a bit of a dilemma. After all of these years, having bad experiences with table variables, I may need to re-think using them in appropriate situations. Of course, there is still the issue of when will my company actually be up and running on SQL Server 2019, but that is a different discussion all together. At some point, I will be using SQL Server 2019 and table variables will be more or less fixed. I guess I will consider using them, it will just feel a bit strange, putting this tool back in the tool belt. How about you? Have you been burnt by bad performance of table variables? When you get up on SQL Server 2019 will you consider using them again? Ben Kubicek from [SQLServerCentral.com]( Join the debate, and [respond to today's editorial on the forums]( ADVERTISEMENT [GDPR](www.red-gate.com/gdpr?utm_source=ssc&utm_medium=pubemailad&utm_content=gdpr-ready&utm_campaign=GDPR&utm_term=20181211-slot1) How to make your SQL Server development GDPR ready Redgate’s SQL Data Privacy Suite helps you to build a data catalog of your SQL Server estate, and put in processes to protect and monitor personal information. [Find out more about how our tools help with GDPR compliance](www.red-gate.com/gdpr?utm_source=ssc&utm_medium=pubemailad&utm_content=gdpr-ready&utm_campaign=GDPR&utm_term=20181211-slot1) [RGUni]( Redgate University Self-paced online training courses with easy to follow classes on getting started, exploring advanced features, and making the most of Redgate products. Learn the fundamentals, best practices, and top tips from the experts - Redgate’s Microsoft Data Platform MVPs and engineers. [Start Learning]( Featured Contents  [] [Database Snapshot Use Case: Service Migration]( Kenneth Igiri from [SQLServerCentral.com]() In this article, we show how we used Database Snapshots as a rollbackup plan for a database migration from one data centre to another. Database Snapshots proved to be the best route since we sould not afford the time a backup/restore approach would take.[More »]( ---------------------------------------------------------------  [] [Adopting Compliant Database DevOps at PASS]( To help meet the requirements of the GDPR, PASS teamed up with Redgate to implement Compliant Database DevOps. This benchmark process has streamlined PASS’ database development pipeline, and strengthened their ability to uphold data privacy regulations such as the GDPR. [More »]( ---------------------------------------------------------------  [] [SQL Server 2019 Community Technical Preview (CTP) 2.1 is Available Now]( Additional Articles from [Database Journal]() Microsoft announced the release or CTP 2.1 for SQL Server 2019. This is the second CTP released for SQL Server 2019, and it has lots of new exciting features, like " UDF inlining". Read on to find out more When something goes wrong with your data transformation process in Azure Data Factory, the last thing you expect to happen...[More »]( Question of the Day Today's Question (by Kendra.Little): What does the "max degree of parallelism" setting configure in SQL Server? 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: Administration. We'd love to give you credit for your own question and answer. To submit a QOTD, simply log in to the [Contribution Center](. ADVERTISEMENT [Pro Power BI Desktop]( This book shows how to deliver eye-catching Business Intelligence with Microsoft Power BI Desktop. You can now take data from virtually any source and use it to produce stunning dashboards and compelling reports that will seize your audience’s attention. Slice and dice the data with remarkable ease then add metrics and KPIs to project the insights that create your competitive advantage. Yesterday's Question of the Day Yesterday's Question (by Steve Jones): I have this code: DECLARE @s sysname = 'audit.OrderLineItem'; SELECT OBJECT_ID(@s); SELECT PARSENAME(@s, 2); The first SELECT returns NULL in my database. What does the second one return? Answer: audit Explanation: The PARSENAME() function does not check for valid objects. Therefore, as long as the string (or sysname) is a name that can be parsed, the function returns a value. In this case, a 2 indicates schema, which is "audit". Ref: Parsename() - [click here]( --------------------------------------------------------------- [» 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]( : [SQL Server 2017 - Administration]( [scale out existing repica(s)]( - Hello, Is there a good inexpensive way to scale out an existing primary replica on SQL Server 2016 Enterprise ( in... --------------------------------------------------------------- [SQL Server 2017]( : [SQL Server 2017 - Development]( [Entity-Attribute-Value is evil ? how to avoid...]( - Hi all consider the following scenario:I have a FILE table with known attributes (size, creation date, ...)The user needs to add... --------------------------------------------------------------- [SQL Server 2016]( : [SQL Server 2016 - Administration]( [DBCC CheckDB in Maintenance plan]( - I inherited a maintenance plan on a 2012 server that has Database Integrity Check as the first step of many.... [LATENT BLOCKING]( - Ok I have a 2016 ent edition 128 gig of RAM 8 core. Have a 3rd party oltp app that... [Logical Drives on a Solid State Platter]( - In the olden days we meticulously separated the system files from data files from log files and from tempdb. So... [AG Listener Names Unavailable for 10-15 Minutes After a Failover]( - I inherited a two node AG on SQL Server 2016 Standard Edition. There are 5 AGs on it, one for... --------------------------------------------------------------- [SQL Server 2014]( : [Administration - SQL Server 2014]( [Issues with SQL backups due to Nodes returning multiple IPs]( - We have a four node cluster (SQL Server 2014 on windows 12) and we recently setup heart beat network on... [Is Mirror upto date]( - Hi there, Is there a script that will tell you how up to date (or not) a Mirror is with the... --------------------------------------------------------------- [SQL Server 2014]( : [Development - SQL Server 2014]( [predicting load on server for report query]( - What questions do I need to ask the business when preparing an table and query against which a report query... [Case when inside of partition by?]( - Hi, I have the following situation: CREATE TABLE #TAB1( ID INT ,MY_STATE VARCHAR(10) ,EVENT_TS DATETIME ) INSERT INTO #TAB1(ID, MY_STATE, EVENT_TS)      VALUES (1, 'STATE_1', GETDATE())             ... --------------------------------------------------------------- [SQL Server 2012]( : [SQL 2012 - General]( [Case statement in where condtiions]( - is it possible to use CASE statement or any other option in below where conditions should be execute when the variables(@LAN,@SUBFORMAT,@CASNUM)... [Left Join with View taking more time in SQL Server]( - The below query is taking twenty seconds to display 53,000 records. This query has five left joins with views. But... --------------------------------------------------------------- [SQL Server 2012]( : [SQL Server 2012 - T-SQL]( [Issues with filling in the blanks from a calendar table.]( - I have an employee activity table that is difficult to determine how much time is actualy spent on an activity... --------------------------------------------------------------- [Cloud Computing]( : [SQL Azure - Development]( [SSIS from Premise to Azure connection login timeout]( - I setup a nightly job to load data from a database on premise to Azure. The SSIS runs successfully intermittently, but sometime it... --------------------------------------------------------------- [Reporting Services]( : [Reporting Services 2005 Administration]( [RS Scripter for SQL 2012]( - Hi Guys, I am trying to find a version of RS Scripter that would enable to import all reports from... --------------------------------------------------------------- [Reporting Services]( : [SSRS 2012]( [How to create a calculated field if the copay type ="Percent"]( - Here are screen shots of my report. I need to divide the Required field by the Count field in order... [How to subtotal a matrix]( - Here's a screen shot of my report? I need to subtotal the values in CopayValue. When I add a row... --------------------------------------------------------------- [Reporting Services]( : [SSRS 2016]( [My subreport only shows 1 record, even when there are more. Why?]( - I've got a main report, which is a detail report. And a smaller report, that's used by the main report... --------------------------------------------------------------- [Data Warehousing]( : [Integration Services]( [Error Message Capture - "OLE DB Destination" - Insert error]( - Dear Forum, I am wanting to capture an error from an "OLE DB Destination". i.e. to a database table or to a flat... [Visual Studio New Script Task always Errors - Scripts contained in the package have compilation errors ......]( - All the info of what I have is below at the bottom. Basically VS 2015 pro. I have had several... 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](. 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](. --------------------------------------------------------------- This transmission is ©2018 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. Contact: webmaster@sqlservercentral.com