Problems displaying this newsletter? View online.
SQL Server Central
Featured Contents
Question of the Day
Redgate SQL Provision
The Voice of the DBA
 

DR Priority

Those of us that act as DBAs or sysadmins for database systems know that DR is a point of emphasis for us. We usually spend plenty of time ensuring backups are working and practicing restores. The automated scripts and processes that people use are some of the most popular and discussed topics on SQLServerCentral.

However, we can't ensure every system is protected at the same level. It's not cost effective to cluster or build AGs with hot standbys, or even warm standbys, for many databases. Often our organization will ensure some are ready to go and others will have to be dealt with if there are issues.

William Durkin noticed recently that O'Reilly hadn't prepared well enough for their learning site. They were affected by the fires and power outages in California and since they host some of their systems in an on-premises data center, there were issues. Certainly we might think they hadn't prepared well for DR, and perhaps this is a fair view of their service, but perhaps they made the decision not to built out an expensive DR environment for a service that can tolerate some downtime.

This week, I wonder how some of you look at the systems you support. Perhaps you are the person that has to make decisions, or perhaps your organization doesn't fund DR well. I'm wondering, how do you decide which systems don't get enough DR support?

Certainly there are inexpensive, perhaps crafty ways that some people might plan for DR. I know I've cobbled together systems from spare parts to use for testing restores, with the idea that the hardware might need to be an emergency DR server for a single system or two in the event of an incident. If you've got ideas on how to be prepared even without organizational support, let us know today.

Steve Jones - SSC Editor

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

Redgate Database Devops
 
 Featured Contents

Best Option for Working with Files in SSIS

Daniel Calbimonte from SQLServerCentral.com

This time we will compare different solutions to copy system folders using different SSIS tasks.

Recover SQL Server Resource Database

Additional Articles from MSSQLTips.com

SQL Server Post Update Failure | Troubleshooting - In this tip we look at the steps to recover missing SQL Server mssqlsystemresource database files that cause SQL Server not to startup. This can occur when an update to SQL Server does not complete successfully therefore making these files absent from the folder where they need to reside.

SQL Clone PowerShell Scripting: The Basics

Additional Articles from Redgate

Phil Factor demonstrates some simple examples of how to use SQL Clone's PowerShell library to pass objects between cmdlets, and simplify common tasks, such as creating and deploying clones from various images. He then documents the objects and cmdlets, and illustrates their inputs and outputs.

From the SQL Server Central Blogs - Power Platform Quick Tips – Episode 01: Fixing Power BI Column Sorting

Devin Knight from Devin Knight

Check out this new series that I’ve started on Power Platform Quick Tips. This series is all about solving problems quick with videos less than

From the SQL Server Central Blogs - Batch Scripting SQLCMD–#SQLNewBlogger

Steve Jones - SSC Editor from The Voice of the DBA

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. I wouldn’t do this anymore, but I ran...

 

 Question of the Day

Today's question (by Steve Jones - SSC Editor):

 

The CTE Insert

I have to insert some data into a table from other sources in the database. My SELECT is complex and includes a CTE to make this easier to read. What type of structure should I write for a CTE with an INSERT? 
-- 1
WITH myTally(n)
INSERT dbo.LottaNumbers (n, somechar) 
AS
(SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT null))
 FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a(n)
)
INSERT dbo.LottaNumbers (n, somechar) 
SELECT n, REPLICATE('a',4500)
FROM myTally

-- 2
INSERT dbo.LottaNumbers (n, somechar) 
WITH myTally(n)
AS
(SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT null))
 FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a(n)
)
SELECT n, REPLICATE('a',4500)
FROM myTally

-- 3
WITH myTally(n)
AS
(SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT null))
 FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a(n)
)
INSERT dbo.LottaNumbers (n, somechar) 
SELECT n, REPLICATE('a',4500)
FROM myTally

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)

Finding Free Space

I have a few files in a filegroup on my SQL Server 2017 database. I want to query the amount of free space in a particular file. Where do I find this information?

Answer: FILEPROPERTY() for the logical filename

Explanation: The FILEPROPERTY() function has a "SpaceUsed" parameter that can be used to return the number of allocated pages in the file. This data is also in the sys.dm_db_file_space_usage DMV. Ref: FILEPROPERTY() - https://docs.microsoft.com/en-us/sql/t-sql/functions/fileproperty-transact-sql?view=sql-server-ver15

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 - Development
Update - W. Average Query - I need help with UPDATE Query. I have this data in tables and want to update weighted average in table tblData3 - (TValue * BPrice) .... / SUM(TValue) create table tblData1 ( IDate date, PID varchar(20), SID varchar(20), TValue float ) insert into tblData1 values ('10/15/2019','4567','ABC',4567.34) insert into tblData1 values ('10/15/2019','4567','ABC',5678.34) insert into tblData1 values […]
SQL Server 2016 - Administration
Suspended session - Hi, Application service is going to hung state and when we check the database, there is a connection in suspended state and it is running forever until we restart the service. And the wait event is ASYNC_NETWORK_IO. The query running is very small select query, fetching couple of hundred rows. and there is no information […]
XP_send mail fail - We migrated from sql 2005 to sql 2016  recently in one of the job we are getting error Executed as user: ' 'Failed to initialize sqlcmd library with error number -2147467259. [SQLSTATE 42000] (Error 22050). The step failed. SQL agent has sysadmin and database reader role access on msdb how to solve the issue   […]
Minimum memory - Hi All , Should we adjust the minimum memory per query (in KB)  in SQL ? The default is 1024 KB and I plan to change it into 2048 KB , Would it help boosting SQL performance ? Also if  change it into 2048 KB , should I change Minimum server memory (in MB) into […]
Monitor / Alert PII fields - Best 3rd party tool? - I have been tasked with finding a new tool for Database Activity Monitoring.  We have previously used Mcafee DAM tool but that is ending and we are frantically searching for a replacement.   If anyone has any experience recently with a 3rd party Data Monitoring / Activity Monitoring tool please let me know.   Thanks […]
Trigger store procedure with a file in SQL Server - Hi, Will receive a JSON file from an application on to D drive D:\tmp Whenever I receive that JSON file, a stored procedure has to execute  taking that JSON file as input file? How do I automate it?
Is it possible get from Query Store historical ~ CPU % usage by each query ? - Is it possible get from Query Store historical ~ CPU % usage by each query in 1 hr range? Many places share code  where "avg cpu utilization " or max cpu utilization" extracted from query store (https://github.com/MicrosoftDocs/azure-docs/blob/master/articles/sql-database/sql-database-monitoring-with-dmvs.md) -- Top 15 CPU consuming queries by query hash -- note that a query hash can have many […]
missing storage space - Hi I have a particular drive on one of my sql servers, it's gotten very full - there are no log files involved, this is purely data within the filegroup (primary) there are only 2 tables (everything else is in other filegroups) dtproperties sysdiagrams These are tiny - when I do a dbcc shrinkfile, I […]
Administration - SQL Server 2014
How does DBCC CHECKDB use tempdb? - I would like to know the working principle of DBCC regarding the tempdb. I want to know why it uses tempdb for what purpose. I didn't find any advanced article about that so maybe someone can shortly explain or at least share links for the article about that.
SQL 2012 - General
Analysis services - I have disk space is getting filled up with Analysis OLAP and log location where it has extensions of .db Does it mean they are analysis services databases? I am planning to delete older dbs on analysis services do you see any issues? Please advise?
SQL Server 2012 - T-SQL
Need help with a Query - Hi, Below I have code that takes in 3 parameters. Now, sometimes, as you can imagine one of these can have a value, or only two, or all three may be populated. I am trying to find a way to write the code below so that this will work, but so far it has not. […]
T-SQL (SS2K8)
Running Totals by Date and Account - I'm trying to develop T-SQL (SQLServer 2008 R2) logic for calculating running totals for all accounts each day. The database columns available are Date, AccountNo and Amount. I need a report to list dates in the first column and each accounts running total in succeeding columns to the right. I would like all dates listed in column […]
SQL Azure - Administration
Does blocking has affect on High DTU utilization ? - Hi, Does blocking has affect on High DTU utilization ?  (some people advocate that Spike in DTU might be caused by high number of blocking processes , or long term blocking) DTU definition select convert(decimal(18, 0),AVG([avg_DTU_percent])) from (SELECT (SELECT Max(v) FROM (VALUES (avg_cpu_percent), (avg_data_io_percent), (avg_log_write_percent)) AS value(v)) AS [avg_DTU_percent] FROM sys.dm_db_resource_stats WHERE end_time>=DATEADD(MINUTE, -10, GETUTCDATE())) […]
Reporting Services
SSRS Enterprise vs Standard Edition - I need to install SSRS for report development and deployment. I don't work with it myself and I haven't had much luck trying to find the information I need. My boss would like me to install SSRS 2019 Enterprise Edition. It looks like there is just one download for SSRS 2019. Here are the questions […]
Integration Services
Flat file source to destination table is getting only top 1 record into table - I have a package where i have flat file source with 4 columns and the source file will be in this format 123456|20130701|AWD|WS1035575|20170201|VCDF|MA1064844|20110001|NHS|AS19474754|20160401|EFV7|LK First column holds -123456 Second column holds -20130701 Third column holds -AWD Fourth column holds -WS it repeats …...   before for each loop i have a script task where the files […]
 

 

RSS FeedTwitter

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

 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -