Azure Stretch Database, Part I: Getting Going (2019-08-26)

 SQLServerCentral Newsletter for August 26, 2019 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Azure Stretch Database, Part I: Getting Going]( - [4 Pillars of SQL Server Monitoring]( - [Cracking DAX – the EARLIER and RANKX Functions]( - [From the SQL Server Central Blogs - Jenkins in a Container]( - [From the SQL Server Central Blogs - SQL Server Execution Plan Operators]( Question of the Day - [Minimalistic SELECT]( [Redgate SQL Source Control]( The Voice of the DBA  Growing and Changing When I started with SQL Server, I worked on OS/2 and SQL Server 4.2. Eventually I moved on to SQL Server 6.5 at a few places, one of which had heavy workloads. While I liked v6.5 much more than v4.2, there were issues with nested views, and certainly issues with heavy tempdb loads. In fact, at one job, I banned temp tables unless we had no other way to get queries to work. This helped stabilize some of our code, and we essentially had a review group that a developer had to convince to allow temp tables since there were situations where we needed them. I was reminded of this recently after Grant published his [Best Practices and Band Plans]( editorial. There was [this comment](, which noted: " I have talked to some people that state "our best practices is to never use..." because they tried it when it was first introduced in SQL Server 2005 and it didn't work for them so now they won't use it at all. " I've encountered this in my career, and I've been guilty of it. It took me a few versions, and more complex code, to start thinking in terms of temp tables at times. I still try to avoid them, but I'm not afraid of them, and I know SQL Server handles tempdb loads better with each version. They are a tool I reach for sooner, but not immediately. I still try to get a simpler T-SQL query if I can. Many developers and DBAs have plenty of experience with T-SQL, but how often do they update their knowledge? How many people question that the way they first think to solve a particular problem is the best way? The T-SQL language has grown and changed across versions. Some language changes are great, like the OVER() clause for window functions. Some might not be so great (STRING_SPLIT among them) or have limitations that you might not expect. Without questioning the way you write code and periodically looking for new techniques, you might not be aware that the amazing code you wrote a few years ago could be better structured today. There is a balance between relying on your knowledge and trying new things. Certainly most of the time I'd expect a developer to write code based on their experience and what works well, but I do think that you also need to periodically investigate new techniques. Regular learning is part of what we hope to help you with at [SQLServerCentral](, with [articles](, the [Question of the Day](, and [blogs]( in our [daily newsletter](. Maybe you don't read every article, but go through one once every week or two. Whether it's something you do every day or have never tried, you might be surprised what you learn. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums]( [Redgate SQL Provision](   Featured Contents [Azure Stretch Database, Part I: Getting Going]( SQLBlimp from SQLServerCentral With most innovative new technologies, Azure Stretch Database demos make it look completely easy. Here is a step by step to get going, with examples. Part One of a Two-Part series. [4 Pillars of SQL Server Monitoring]( Additional Articles from SimpleTalk Five SQL Server experts have brought their collective years of experience working with and maintaining SQL Server data systems to identify the four strategies required for a successful estate-wide SQL Server monitoring solution. [Cracking DAX – the EARLIER and RANKX Functions]( Additional Articles from SimpleTalk So far in this series, Andy Brown of Wise Owl Training has shed light on functions like CALCULATE, VALUES and FILTER, but it’s only when you understand the idiosyncrasies of the EARLIER function that you can claim to have genuinely cracked DAX. This article gives four examples of the use of this peculiar function, in the process explaining why it has such a misleading name. Using the EARLIER function properly all boils down (as is so often the case with DAX) to understanding row and filter context. The article also shows how to use the RANKX function to sort data into your required order. From the SQL Server Central Blogs - [Jenkins in a Container]( Steve Jones - SSC Editor from The Voice of the DBA One of the things I needed to do recently was get Jenkins running as a demo for a customer. We have some pre-built VMs to do this, but I... From the SQL Server Central Blogs - [SQL Server Execution Plan Operators]( Bert Wagner from Bert Wagner Watch this week’s Execution Plan Operators episode on YouTube. SQL Server Execution Plan Operators When examining a query’s execution plan, certain operators tend to crop up over and over...   Question of the Day Today's question (by Thomas Franz):  Minimalistic SELECT What will be the result of the following Statement: SELECT 1.test; 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) Synonyms for Tables Which of these can I not create a synonym for in SQL Server 2017? Answer: user-defined aggregate function Explanation: A synonym cannot be created for a user-defined aggregate function, but it can be for the others. Ref: Synonyms - [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 [disable CEIP]( - Hi everybody, I just want to know, how to disable the CEIP service. I know this two links: SQL Server 2016 – SQL Server Telemetry CEIP Services Disable or Turn-off SQL Server Telemetry Service But on this microsoft page, they tell me, that the CEIP service had to run, otherwise my support maybe lost: "Removing […] SQL Server 2017 - Development [Convert Seconds to Minutes]( - Guys, is there a way to add a new column to show HH-MM-SS ( as per below example). In excel for example i would simply divide the seconds by 86400 and change the format in the column to hh-ss-mm. IF OBJECT_ID('tempdb..#Log') IS NOT NULL DROP TABLE #Log SELECT * INTO #Log FROM (VALUES ( '10', […] [CAST FOR XML - Illegal Name Character]( - I am trying to write a query that generates an html report to be emailed and one of the columns is a hyperlink to our report server that we can click on from the email and it will open the report in report browser. It needs to pass the row value as a parameter to […] SQL Server 2016 - Administration [Replication Error - TCP Provider: The semaphore timeout period has expired.Â]( - Experts, We are facing an issue with one of the replication subscriber. There were already two subscribers and replication is working fine since long time for both. Recently added one more subscriber and while configuring replication we are getting the below error. TCP Provider: The semaphore timeout period has expired. This error occurs with ONLY […] [Revert a database to a DB Snapshot]( - We are a product based company and using SQL Server as backend. Whenever there is a new version of OUR product released, we will upgrade the product for all our SaaS customers and upgrading the product across all the SaaS Customers have been automated. As a pre-upgrade process, we will take DB Snapshot and if […] SQL Server 2016 - Development and T-SQL [Get the inactive records in sqlserver]( - HI Team, please help to get the expected data. my Data like below: create table #comapny ( companyid int, childname varchar(100), activeid int) insert into #comapny select 1,'aaa',0 union ALL select 1,'bbb',0 union ALL select 1,'ccc',1 UNion ALL select 2,'ddd',0 union ALL select 2,'eee',0 union ALL select 1,'fff',0 select *from #comapny Existing Data: companyid childname […] Administration - SQL Server 2014 [Data Migration]( - Hi , Our new project demands migration of data from Oracle database to multiple SQL Server databases. We are planning to go with SSIS package to achieve this. Is there any better way to achieve this? [DBCC8 File]( - Hi, There is a X._mdf_mssql_dbcc8 file on one of the drives occupying 350GB. I hope it got created due to DBCC CheckDB on database and whether can it be deleted and what information are recorded in it. Will it affect the database if it's been deleted? Any idea how it got created?  Development - SQL Server 2014 [Primary Key and Clustered Index]( - A topic I always get confused with is primary key vs. clustered index. I have an understanding of what each is and/or is not but I struggle with the application of the two. Suppose I have a table with an account number field in which all account numbers are unique. Is it worth having an […] [odd error message in function in a proc]( - I have a cursor which has calls a function (sorry for design this is a 3rd party db). Odd part is there are 10 rows in the cursor select, only every other row gets updated, next row gets error below. This happens no matter what the sort order is, one row works, next row errors, […] SQL Server 2008 - General [Data Migration Assistant errors - dacpac and openquery failures]( - Hi all, I am attempting to use DMA to assess an upgrade from 2008 R2 to Asure SQL on VM. Our database has linked servers and many views that use openquery. I'm receiving a lot of errors running DMA. First is 'Failed to extract dacpac at location 'C''. Second is a load of errors like: […] Reporting Services [Trimming then adding text]( - Hello I have an output in my queries that gives me: xxxxxxx xxxxxxxxx : 123456 (xx) - xxxxxxx... or xxxxxxx xxxxxxxxx : 123456789 (xx) - xxxxxxx... basically text before either a 6 or 9 digit number then text after I've created a column in a table in SSRS that just shows the number, and I used this […] Integration Services [Scalable SSIS Package - Single SSIS package across 50 different projects]( - Hi All, I have a successfully running SSIS package which has been created for a single project. It works over a pipe-delimited file and pulls the data from the source, and loads it directly into the destination table. For each projects, I am using the same job to execute based on a job execution table. […] [How to make in memory SQL transformations within a data flow task]( - Hi all, first of all, I am new to Integration Services. I googled everything so far, but I do not find any answers on my question. So I hope anybody of you can help me out :-). Currently, I am migrating an T-SQL ETL process to SSIS to see the benefits in parallel executions and […] T-SQL [CASE statement headache]( - Hello I have an output in my queries that gives me: xxxxxxx xxxxxxxxx : 123456 (xx) - xxxxxxx... or xxxxxxx xxxxxxxxx : 12345678 (xx) - xxxxxxx... or xxxxxxx xxxxxxxxx : TEST12345678 (xx) - xxxxxxx... basically text before either a 6 or 8 digit number then text after.  Ideally I'd like to be able to CASE this column so […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -