SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

A Strange Comparison

The Voice of the DBA

Daily Coping Tip

Set hopeful but realistic goals for the week ahead

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.

SQL Server Phone Home

A few versions ago Microsoft added the Customer Experience Improvement Service to the SQL Server platform. This is the CEIP service, and it comes with SQL Server. It is designed to get telemetry from your operation of a SQL Server instance on your premises. If you have Standard or Enterprise, you can turn this off, but if you use Developer or Eval, you cannot. Brent wrote a short description of this service recently, which is a good summary.

When this first came out, there was a lot of concern with regards to data privacy, but I suspect most of this is overblown. Microsoft is bound by the GDPR, and my conversations with employees over the years have convinced me they take this seriously. Not just the legal staff, but many of the developers were surprised by the detail and documentation that they had to provide in order to gather data.

Microsoft documents about what they collect, as well as the access restrictions at Microsoft and the data retention. I don't see anything here that I am too concerned about, but I do think this is a great template that many organizations could use to document what data they capture from customers, how it is used, and how long they keep it. Whether you are required to do this or not now, I suspect more of us will be required to do this over time. You could start doing this now and be prepared early.

This isn't a fun task, and it's very tedious, but if you capture this data and adjust it as you evolve and alter your schema, it's not too bad. If you also include a place to document this, you can then work on this over time, not as one big, long, really annoying project. As data documentation has become more important to many of the customers I deal with at Redgate, having the ability to work on this over time is helpful in any size organization.

Steve Jones - SSC Editor

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

Featured Contents

Better SQL Server Agent Job Failure Monitoring

The xTEN Team from SQLServerCentral

SQL Server Agent has a built-in alerting process for when jobs fail, but the information it provides isn’t very useful. You’re only told which job, what time, who ran it, and which step failed. If you want to see why it failed, you have to review the job history manually. On a busy system with […]

Let’s move: Azure Resource Mover

Additional Articles from SimpleTalk

Moving resources from one location to another in Azure has been possible but not easy to do. In this article, Dennes Torres introduces an exiting new Azure feature: Azure Resource Mover.

Monitoring Amazon RDS with Redgate SQL Monitor

Additional Articles from Redgate

With the launch of SQL Monitor 11, you can now monitor your SQL Servers hosted on Amazon RDS alongside your on-premises and other cloud-based servers, instances, and databases. Join our webinar on October 28 to discover the benefits of monitoring your entire estate; including on-premises and cloud-hosted databases, from a single pane of glass.

From the SQL Server Central Blogs - How To: Create Schema Only Database Copies

gbargsley from GarryBargsley

Have you ever been asked to make a schema only copy of a database? What is your preferred method to complete this request? Below we will discuss how to...

From the SQL Server Central Blogs - Azure DevOps–Reconfiguring an Agent

Steve Jones - SSC Editor from The Voice of the DBA

This post looks at how to re-configure a local Azure DevOps agent when you need to change to a new pool or organization. PAT Expired I got a note...

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	A Strange Comparison
I get this code from a developer: SELECT sd.Date , sd.Customer_Age , sd.Age_Group , sd.State , sd.Product , sd.Order_Quantity , sd.Profit , sd.Cost , sd.Revenue FROM dbo.SalesData AS sd WHERE sd.Profit !< 10000; What does this return?
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)

Copying an Azure SQL Database with PowerShell

I have an Azure SQL Database that I want to copy for a point in time reporting requirement whenever I am asked. Since I need to do this regularly, I'd like to have a script that can do this easily with PowerShell. What is the easiest way to do this?

Answer: Use New-AzSqlDatabaseCopy

Explanation: The restore command will work, but this needs a point in time. The easiest way to do this is with the New-AzSqlDatabaseCopy. Ref: New-AzSqlDatabaseCopy - https://docs.microsoft.com/en-us/powershell/module/az.sql/new-azsqldatabasecopy?view=azps-4.7.0

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

Alwayson Availbility group extended to Azure?.. - Hello, Can databases on-Premise Alwayson Availability group of SQL server 2017 failover to Microsoft Azure SQL database (PaaS)? Or it has to be IaaS? We don't have Azure VMs (IaaS) but only license for Azure SQL databases (PaaS). And How about Basic Availlbity group on Standard Edtion? thanks alexsunny

SQL Server 2017 - Development

SQL Query Help - display records - Need help with Query. CREATE TABLE #tblTemp (IName varchar(50), ICategory varchar(20), IValues varchar(10)) insert into #tblTemp values ('JSmith','Bar1','1QT') insert into #tblTemp values ('JSmith','Bar1','2QT') insert into #tblTemp values ('JSmith','Bar1','3QT') insert into #tblTemp values ('JSmith','Bar2','1QT') insert into #tblTemp values ('JSmith','Bar2','2QT') insert into #tblTemp values ('JSmith','Bar2','3QT') insert into #tblTemp values ('JSmith','Bar3','1QT') insert into #tblTemp values ('JSmith','Bar3','2QT') insert into […]

Stored Procedures and Entity Framework not returning columns - I have a developer using Entity Framework and the metadata isn't returning for some stored procedures. Specifically ones that use Temp Tables. I've seen some EF articles about using SET FMTONLY OFF to get the metadata but I would prefer not to touch this. Are there any examples/methods anyone has come across to resolve this […]

Merge statement with partial part of the table. - Hi, I have a very big table and I need to do a merge. if the primary key and the record source match then I need to update one column, otherwise I need to insert. I really dont need to do the whole table, but only the part when the data is bigger than a […]

SQL Server 2016 - Administration

SSRS 2016 giving 500 error - Hello! I am getting this intermittent issue when browsing the report, this error goes away once I refresh the browser. This seems to be happening when accessing report through the load balancer (https://) only Sys.WebForms.PageRequestManagerServerErrorException: An unknown error occurred while processing the request on the server. The status code returned from the server was: 500 […]

Backup taking too long - SQL Server 2016 - Backup is taking too long to complete all of a sudden, this is the wait_type and time shown for the backup. wait_type :ASYNC_IO_COMPLETION wait_time : 53127671 This DB server has more than 10 CPU and 96 GB RAM, SQL is using max memory of 92 GB.

SQL Server 2016 - Development and T-SQL

Determining whether a parameter has been established. - I have a ssrs report with 6 parameters 4 or which are cascading and one parameter (pTimeZoneID) that drives the "Time Zone" in a dynamic OLE DB connection string. Need to set the Default value in the pTimeZoneID to a static "Time Zone" in order to use the data source to populate the 4 cascading […]

Administration - SQL Server 2014

Need to create a job for dynamic execution on another server - Hi all, I need to create a job to dynamically script out indexes and some other objects from Server A and apply them on Server B. They are linked through a Linked Server. But how to construct the job? I know how to build dynamic code, but how to save its output, and how to […]

sp runs in 1 second sometimes and sometimes it takes forever - sp runs in 1 second sometimes and sometimes it takes forever and returns null even though there is data thought it was parameter sniffing and started using option recompile..but still runs the same. What could be the reason for this behavior? Thanks!

SQL 2012 - General

SQL Server DR Test - Hi all I am pretty new with SQL Server and I have some doubt about doing DR tests with SQL Server. I have around 25 database running in SQL Server 2012 Standard Edition with Log Shipping setup in another Data Center. I have to do a simulation DR test, breaks Data Center communication, […]

SQL Server 2012 - T-SQL

deleted duplicate post - deleted duplicate post

SQL Server 2019 - Administration

Failover Clustering IP's - For a 2 node active/passive cluster on Physical servers with shared storage. I am trying to setup Failover clustering for two nodes. I am looking for the required IP address needed. So below is what I came up: I need total of 3 IP addresses along with the virtual server names 1 IP address for […]

SQL Server 2019 - Development

query - Hi i have 3 table that need to find out which id missing two tables TABLES: Main,table1 and table2 compare with Main-table against the table1 and table2 which id is missing that two table show

Joining 2 select results into a single row - select( select name,enabled, case when enabled = 1 then 'active' else 'notactive' end as JobStatus from msdb.dbo.sysjobs where name='JobA'), (SELECT CASE WHEN EXISTS ( SELECT name FROM msdb.dbo.sysjobs WHERE name = 'JobA' ) THEN 'Exists' ELSE 'Does not Exists' END as 'JobAPresentAbsent') Error:Only one expression can be specified in the select list when the subquery […]

General

Keeping a record of Scripts run - Hello I have a real issue with the scripts that are being run on our systems - database. Vendors pass various scripts through the Apps team so I have no problems with their legitimacy, but I have a real problem in keeping track of what was run, when, by who (which DBA). More often these […]

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.