SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

Using temporary tables with OUTPUT clause

The Voice of the DBA

VMs are not VMs

This editorial was originally published on May 19, 2015. It is being republished as Steve is at SQL in the City.

I was at VMWare recently. One of the main things that all of the SQL Server professionals that were there tried to emphasize is that SQL Server workloads are not like other workloads. The impact on the various host resources, the stress on the storage systems, these are fundamentally different in a database server. The loads tend to be higher, but not always, however the tolerance for delays tends to be lower than for many other types of applications.

This becomes an issue if you work in an organization that doesn't understand the challenges of database systems. It's entirely possible that your virtualization administators, or your storage administrators don't recognize that the SQL Server might need more resources. Or they don't believe the impact is greater for the organization. To be fair, that might be true, but someone other than the DBA or system administrator should decide if the database is more important than the file server and should be treated differently from an infrastructure perspective.

No matter what level of resources your database server need, it's not going to run like other systems. Typically this means that the density of VMs has to change when a database server is involved. As an example, I know of a system that typically has a 10:1 guest:host ratio for most of their server systems. However for SQL Servers it's 4:1 or lower. The same is true for storage. Aggregate bandwidth doesn't always reflect the ability of a storage system to keep up with database requests. It becomes important that both you and your storage administrators learn to speak the same language and understand what requirements exist for SQL Server VMs.

Virtualization really starts to highlight the advantages of a DevOps environment. DBAs and developers should work closely with the virtualization and storage administrators to learn what each others' requirements are and how each of us can help the other perform their particular job at a higher level. Infrastructure staff can help prepare standard environments and ensure production looks like staging. Developers and DBAs can help a vSphere admin learn a little PowerCLI and programming. That might get them to be more cognizant of the particular requirements of your SQL Server and be more willing to work with you.

Steve Jones - SSC Editor

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

Featured Contents

SQL Server Performance Benchmarking with Patterns

Paul Brewer from SQLServerCentral.com

A SQL Server, SQL Azure and Hekaton performance bench marking application that uses patterns.

Reporting Services Basics: Creating Your First Report

Additional Articles from SimpleTalk

In this article, Kathi Kellenberger demonstrates how to create a project and report using the SSRS Report Wizard. The wizard is fine for getting started, but you’ll soon learn about its many limitations. She also explains how reports fit in projects and solutions.

From the SQL Server Central Blogs - High Security Prices–Always Encrypted 2019 needs Datacenter Edition

Steve Jones - SSC Editor from The Voice of the DBA

I was recently doing some work on Always Encrypted in SQL Server 2019 and needed to set up an HGS server. I went through most of the setup, and...

From the SQL Server Central Blogs - Leadership Part 1: Team building

jphillips 46546 from Another SQL Geek

One of the most challenging aspects of being a good leader is building a team that can support the initiatives you will set forth. Without a team that will...

Question of the Day

Today's question (by Junior Galvão - MVP):
	Using temporary tables with OUTPUT clause
I execute the code block below: -- Create Table MyTable -- Create Table MyTable (ID SmallInt Identity Primary Key Clustered, Numbers Int, CreationDate Date, ManipulationDate Date) Go -- Insert Rows -- Insert Into MyTable(Numbers, CreationDate, ManipulationDate) Values(10, GETDATE(), GETDATE()+1), (20, GETDATE(), GETDATE()+2), (30, GETDATE(), GETDATE()+3), (40, GETDATE(), GETDATE()+4) Go -- Create Temporary Table #MyTempTable -- Create Table #MyTempTable (ID SmallInt Identity Primary Key Clustered, Numbers Int, CreationDate Date, ManipulationDate Date) Go -- Update Rows with Output clause -- Update MyTable Set ManipulationDate=DateAdd(d,5,ManipulationDate) Output Inserted.Numbers, Inserted.CreationDate, Inserted.ManipulationDate Into #MyTempTable Go What will be the returned in SQL Server 2017?
Think you know the answer? Click here, and find out if you are right.

Yesterday's Question of the Day (by mkdm)

Service Broker Undelivered Messages

Service Broker is enabled on my database, but messages are not arriving in my TargetQueue. I have confirmed that the initiator and target services have been configured correctly, and that my queues are enabled. Where could I look for the undelivered messages?

Answer: sys.transmission_queue

Explanation: Service Broker places messages to be sent over the network into sys.transmission_queue. Messages remain in this queue until Service Broker receives notification from the destination server that the message has been received. If the send fails, the transmission_status column contains the reason, usually an error message, for the failure. Ref: sys.transmission_queue - https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-transmission-queue-transact-sql?view=sql-server-2017

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

Collation issue - We have to migrate some databases to a new server. Originally the old server is using server collation Latin1_General_CI_AS. and hence the databases created on that using the same collation Latin1_General_CI_AS. Now we want to new server to be collation: SQL_Latin1_General_CP1_CI_AS, since it will future host some additional vendor product. Is it going to be […]

SQL Server 2017 - Development

Unable to move log file to non-default location - I am managing a migration project for several thousand databases across dozens of SQL servers. These db imports generate very large log files for each database. Once the import is complete, the log file size needed for each db is very small. In order to avoid needing very large log file drives on each server, only […]

Order of a task path - Hi There, I'm trying to work out the best way to work out a workflow to a particular set of tasks I have in the database. Basically in table1 I have the name of the task and the id. In table2 I have the id and the previousid in a table. What I am trying […]

SQL Server 2016 - Administration

Error adding a monitoring server to a log shipping configuration - I am setting up log shipping for a database and have hit an error at the "monitor server" stage. I used the GUI to create the T-SQL that needs to be executed and it generated: EXEC msdb.dbo.sp_processlogshippingmonitorsecondary @mode = 1 ,@secondary_server = N'SECONDARY' ,@secondary_database = N'DBNAME' ,@secondary_id = N'' ,@primary_server = N'PRIMARY' ,@primary_database = N'DBNAME' […]

SQL Server 2016 - Development and T-SQL

What's the Best Way to Pass Table Driven Sensitive Info in SSIS? - Hi, I have a requirement to loop through several SFTP sites, grab files, and then perform various actions on them in SSIS. All SFTP connection information will live in a table in a SQL Server 2016 database. I've got the overall structure of the SSIS package working well. However, I'm now at the point where […]

Stored Procedures - Hi, I created a stored procedure that creates a temporary table, fills it with data, and runs a select and returns the data from the one column in temporary table and then drops the temporary table. How can I grab the results from the stored procedure and use it in a query. I was to […]

Administration - SQL Server 2014

Database last used - Is there a way to find without having access to database when the database was last used? My understanding is Without having SQL access it won’t be possible to get when the database was last used or updated right?

Development - SQL Server 2014

Performance of left outer join - I'm looking for a general answer. In my experience, the performance of inserting data into a table from the results of a LEFT OUTER JOIN query is significantly worse than running two queries - one to populate the destination table followed by update of the destination table using INNER JOIN. I thought SQL Server was […]

SQL 2012 - General

How to Trace ASYNC_NETWORK_IO - We have just converted a SQL Server box from a physical server to a vm. I noticed a significant slowness when connect to Object Explorer in Management Studio. My trace shows the duration is over 2 seconds. The normal duration to connect to other servers are around 200 milliseconds. The SQL profiler trace show there […]

SQL Server 2008 - General

sp_runwebtask error after migration from SQL Server 2000 - Unfortunately I am required to migrate and old database from SQL Server version 2000 up to 2017. The first step I have taken is to migrate to 2008. Apparently everything is good, except for one table, on insert will throw an error could not find stored procedure sp_runwebtask. The offending table was created using good […]

View crashes SQL Studio - Hi all, I have a query which is working fine. However, when I attempt to put it into a view SQL always crashes. There is no error message when I run the view other than the crash window: SSMS - SQL Server Management has stopped working. However, if I try save the view, the error […]

SQL Azure - Administration

Backup retention upto 10 years in PAAS - Hi Experts, Do I have to pay additional amount to retain my PAAS SQL database backup for 10 years in Azure? Thanks Brijesh

Azure elastic database jobs - Hi, I am trying to use this approach to automate some tasks: https://docs.microsoft.com/en-us/azure/sql-database/elastic-jobs-tsql It mentions: "The credential needs appropriate permissions, on the databases specified by the target group, to successfully execute the script. " CREATE MASTER KEY ENCRYPTION BY PASSWORD='password'; CREATE DATABASE SCOPED CREDENTIAL myjobcred WITH IDENTITY = 'jobcred', SECRET = 'password'; GO CREATE DATABASE SCOPED […]

SSRS 2016

Using a partial lookup of a value to conditionally set the color of a textbox - Hi there I have an SSRS 2016 report which set the colour of the text in a cell. The look up expression uses a dataset called 'StatusColour' which is defined as follows: SELECT 'Available' AS [Status], 'Black' AS [Colour] UNION SELECT 'Allocated' AS [Status], 'Blue' AS [Colour] UNION SELECT 'Complete' AS [Status], 'Green' AS [Colour] UNION SELECT 'InProgress' AS [Status], 'Blue' AS [Colour] […]

General

if/then or case return? - Hi folks. I get the if/then and case keywords for conditionally setting field values, what I don't get is how to get a return variable. So I say if var1=1 then var2=1 But I have no idea what's been done, how do I return that var2 has been set to 1 or left alone? […]

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.