Implementing Stretch Database (2020-07-03)

 SQLServerCentral Newsletter for July 3, 2020 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Implementing Stretch Database]( - [The importance of remote monitoring]( - [Azure Data Factory vs SSIS vs Azure Databricks]( - [From the SQL Server Central Blogs - PASS – Meanderings]( - [From the SQL Server Central Blogs - Open Letter to PASS]( Question of the Day - [Partitioning Heaps]( The Voice of the DBA  Daily Coping Tip Thank a friend for the joy they bring into your life 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. Lessons from Little Green Army Men Today we have a guest editorial from Andy Warren. This editorial was originally published on 19 Feb 2016. It is being re-run as Steve is on holiday. My daughter had an ear infection and sore throat so we went to the local pharmacy/clinic to see a nurse and get some medicine. As we were leaving she asked if she could spend her allowance in the toy section of the store. She browsed and was leaning towards more Pokemon cards when I asked her to consider trying something different (surely we have enough Pokemon cards). She looked again and then asked if she bought the bag of green army men if we could play with them together. Father’s Day came early this year! We went home and start setting up the pieces. She asked where the flag should go, the tank, the barrier wire and the sandbag bunker. That led to a discussion of defense in depth and guarding the flanks, what a shoulder fired rocket was for, how a tank works and how it used to be that tanks had to stop to shoot accurately, which in turn lead to watching a video of a tank firing. All of this while our green men battled. At one my point I had to retreat and explained the bugle calls, Retreat being my favorite and why Taps isn’t just played at funerals. She didn’t notice that the toy soldiers were all men, something I’d like to see changed to coincide with the recent announcement that women will be allowed to serve in all combat roles. I was thinking about that as we played. Should we talk about that? My goal in these kinds of moments is to not disrupt the fun, not make it too much about learning, but try to show her the topic is deeper and richer than she might think at first glance. For this day, military strategy was enough, and for this day it was good to postpone thoughts of my child someday deciding to take on bigger risks. We had fun. She learned a few things and we’ll build on that. The battles? Somehow in spite of my knowledge I lost all of them! Andy Warren [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Implementing Stretch Database]( Deepak Prasad Sahu from SQLServerCentral.com Learn how you can enable the Stretch Database feature in SQL Server 2016. [The importance of remote monitoring]( Additional Articles from Redgate Monitoring should be able to happen no matter where you are located. This year organizations around the globe had to adapt to many new challenges including a distributed workforce and managing their estate when they can’t be in the office. This blog outlines the reasons and benefits of adopting remote monitoring. [Azure Data Factory vs SSIS vs Azure Databricks]( Additional Articles from MSSQLTips.com In this article we cover some things you should consider when determining whether to use Azure Data Factory, SSIS or Azure Databricks. From the SQL Server Central Blogs - [PASS – Meanderings]( Diligentdba 46159 from Mala's Data Blog As most people reading this would know…the PASS organization is in a state of crisis now following the decision to go virtual. We are in a place where we... From the SQL Server Central Blogs - [Open Letter to PASS]( SQLEspresso from SQLEspresso As you know, I spoke to 12 Chapter Leaders and 4 Regional Mentors yesterday. All are very upset with PASS right now and feel slighted, which as a volunteer...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Partitioning Heaps Can I partition a heap table? 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) Querying Configurations I can query various instance setting with sp_configure, but can I get these same settings in a DMV? Answer: Yes, by querying sys.configurations Explanation: The sys.configurations DMV includes all the settings from sp_configure, and more. Ref: sys.configurations - [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 [Replication and always on]( - Previously we used to use transactional replication and that would allow reporting database to be different name. However, with always on AG the database name on secondary cannot be different has to match the source db name and i tested this to ensure. Do you agree the db name cannot be different for secondary replica? [Failover, Patching and Hallengren Backups]( - I am using a replication tools that has been having issues the last couple of patch cycles. I am doing some troubleshooting and I see a few issues.  When patching the AG servers a failover occurred. To patch AG servers, should we reboot the secondary first and then the primary? We never thought of […] SQL Server 2017 - Development [JOIN Causing Duplicate Values]( - Dear Group: I am missing something and not sure what. Below, is the output and query when I do a simple select statement and these numbers are correct. SELECT A.ReportDate, A.Site, FORMAT(A.ReportDate, 'yyy') + '-' + FORMAT(A.ReportDate, 'MM') AS Report_Slicer, A.Pfolio, A.C_Type, SUM(A.Calls) AS Calls, SUM(A.HSec) AS HSec, SUM(A.Accept) AS Accept, SUM(A.Reject) AS Reject, SUM(A.ASec) […] [Incorrect syntax near the keyword 'unique'.]( - Hi All I am trying to create new table on sql server 2014 enterprise edition with constraint and unique non-cluster index and getting "Incorrect syntax near the keyword 'unique'." msg. It runs ok on sql 2017 server.I tried to look into books online but link takes to 2019.here is my script EGIN TRAN BEGIN try […] SQL Server 2016 - Administration [Finding when SQL login switched to disabled status]( - Hello experts, I just fixed an issue for a client where I discovered the SQL login in question was disabled. I forgot (my fault) to check the login data before re-enabling the login, so I think I lost the previous change date for the login. I did try to go back and restore backups of […] Administration - SQL Server 2014 [Strategies for better SQL server Management and Cost Saving]( - Our company has nearly 50 SQL server engines (more than 2/3 are version 2014 or newer) and 250 database instances running, the business applications data in the databases vary greatly in their subjects. Due to lack of expertise and good management strategies, we do not have good knowledge about our SQL server environments, from licensing, […] [error while setting up linked error]( - Hi I get the error Client and Server cannot communicate...they do not posses a common algorithm how to resolve this Regards  Development - SQL Server 2014 [SQL Server Trigger to prevent insertion of duplicate data in Table]( - Hello, I have a company table for some reason I can't create a constraint on table level. I am trying to use the following Trigger on my table to avoid duplicate records. when I try to insert the record I am getting following error. Please advise how to avoid duplicate record insert / Update using […] SQL 2012 - General [How to write dynamic statement with way do it run quickly?]( - I work on SQL server 2012 I face Issue I implement dynamic SQL it very slow so I need to enhance statement below to work quickly . DECLARE @SQL NVARCHAR(MAX) select @SQL =CONCAT(' SELECT * Into #NewTable FROM #SplitNumberAndUnitsFinal PIVOT(max(Value) FOR DKFeatureName IN ('+@Columns+')) AS PVTTable ', N' Select ''Variant ID'' as ''[Variant ID]'',''PART_ID'' as […] [Can't start SQL Server Agent]( - I'm having enormous trouble starting SQL Server Agent. I have one server that is running two instances of SQL Server - one the agent works fine on the other no luck with multiple things I've tried. I simply see the line "SQL Server Agent (Agent XPs disabled) - any clues would be most appreciated! SQL Server 2012 - T-SQL [? on Non-Clustered Indexes]( - Hi Recently we moved to replication of a DB we receive. I noticed it was slow, long story short, the non-clustered indexes were missing, added them back in and queries ran quickly again. My question is , how do I know when and what fields to add a non-clustered index? Any info/links would be appreciated […] SQL Server 2019 - Development [Build a string based on values from other fields]( - I have 10 fields that have either a 1 for true and 0 for false I have an 11th field that I want to create a list in based on which attribute fields have a 1 or true in it I need to read each record and each of the 10 fields in that record. […] [What is the difference between statement and sql_text?]( - Hi All,  I was asked to map all ojects that a specific group of users access in our DBs. I've created an extended Events session and captured all of their batch completed and rpc_starting. When I started to analyze the data, I was surprised to see that rpc_started event gave me sql text and […] SQL Azure - Administration [List of all users for all databases]( - I need to run a query that will return all of the users for all of the databases on a SQL query. I am able to get all of the users for a single database, but I really need all of the users for all databases in a single output result. I thought this would […] COVID-19 Pandemic [Daily Coping 30 Jun 2020]( - Today’s Tip is to create a list of favorite memories you feel grateful for.   [RSS Feed]([Twitter]( 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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -