SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

The Voice of the DBA

Can Azure Stack Reduce Software Development Costs?

There’s a piece, called The Real Value of Azure Stack, that caught my eye recently. This seems a little promotional, as lots of vendors are mentioned in the piece, but the gist of the piece is that one of the largest line items for most enterprise IT shops is software development and maintenance. Part of Microsoft’s strategy to address this is with Azure Stack, a hybrid solution that allows you to build a platform in your data center that can extend to the cloud if you need it.

Software development is hard, and from what I can see now, Azure Stack still doesn’t have a great data story. While one can develop against SQL Server, PostgreSQL, MongoDB, etc., these databases instances used would be the same standalone services that we would use without Azure Stack. The platform does provide some features that smoother the use of SQL Server or MySQL, but it’s a far cry from the Azure SQL Database or Azure MySQL Database offerings or even CosmosDB. I hope to see those on Azure Stack at some point, perhaps with lower performance capabilities and promises.

That doesn’t mean that there aren’t advantages to Azure Stack because there are. The consistent development platform, the use of functions, known VMs or App Services mean that your developers can build and test things inside your data center or in the cloud, knowing that these services will run smoothly when deployed on Azure Stack. I’d expect that we will see container and k8s support at some point as well, which will further help us smooth the process of moving software from one machine to the other.

I don’t know of any customers for Redgate that are using Azure Stack right now, but I am hopeful to meet some and see the system in action. I don’t hear about it often, but every time I do, I get a little excited. I think this is the type of platform that could get more people interested in writing more cloud native software, with all the requirements to build more fault tolerant, resilient and scalable applications.

Of course, moving to Azure Stack is likely most beneficial for new applications. While existing ones might port into an IaaS infrastructure and reduce some sysadmin costs, I doubt that there would be significant savings without a lot of development work that takes advantage of more modern software frameworks. There likely isn’t going to be any software maintenance savings on those old thick client applications or ASP websites.

If any of you are moving to Azure Stack, let me know. I’d be interested in your experiences.

Steve Jones - SSC Editor

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

Featured Contents

Stairway to SQL Server Security Level 5: Schemas and Security

Don Kiely from SQLServerCentral.com

In this stairway level you’ll learn how you can give principals access to groups of objects by assigning permissions on schemas instead of individual tables, code modules, and other objects. You’ll also learn about the benefits of user-schema separation and how it can increase object security, and how using default schemas for users and groups can simplify object access management and security.

SQL Server Database Users to Roles Mapping Report

Additional Articles from MSSQLTips.com

In this tip we look at a SQL Server function that generates a report of all users and their assigned database roles.

5 reasons why backup and restore doesn’t cut it in dev and test

Additional Articles from Redgate

The provisioning process is a critical part of database development. Redgate’s Rebecca Edwards looks at a tool that can remove the pain points of backup and restore and replace them with a more efficient solution can hugely benefit your organization and the quality of life of you and your team.

Free eBook: Troubleshooting SQL Server: A Guide for the Accidental DBA

Press Release from Redgate

Three SQL Server MVPs (Jonathan Kehayias, Ted Krueger and Gail Shaw) provide fascinating insight into the most common SQL Server problems, why they occur, and how they can be diagnosed using tools such as Performance Monitor, Dynamic Management Views and server-side tracing. The focus is on practical solutions for removing root causes of these problems, rather than "papering over the cracks".

From the SQL Server Central Blogs - Can SQL Ids be used on an instance with Windows Authentication only?

Kenneth.Fisher from SQLServerCentral

tl;dr; Yes, but. It’s generally considered more secure to have your SQL Server instance set to Windows Authentication and not … Continue reading

From the SQL Server Central Blogs - Testing as another user–#SQLNewBlogger

Steve Jones - SSC Editor from SQLServerCentral

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. This is one of those things I do…

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	dput()
I have this dataframe in R: > HR.hitters rank players Hr yrs.played 1 1 Barry Bonds 762 22 2 2 Hank Aaron 755 23 3 3 Babe Ruth 714 22 4 4 Alex Rodriguez 696 22 5 5 Willie Mays 660 22 6 6 Ken Griffey, Jr. 630 17 7 7 Albert Pujols 633 22 8 8 Jim Thome 612 22 9 9 Sammy Sosa 609 18 10 10 Frank Robinson 586 21 What does this code do? > dput(HR.hitters)
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)

Creating Statistics

When I use CREATE STATISTICS with multiple columns, what data is used for the histogram?

Answer: Only the first column

Explanation: Only data from the first column is used to create the histogram. Ref: CREATE STATISTICS - https://docs.microsoft.com/en-us/sql/t-sql/statements/create-statistics-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.

Finding duplicated values in multiple columns (Not the one we all know of) - This isn’t specifically related to SQL Server 2017 but more a general T-SQL question. I couldn’t find the right place for this question. You may though this is another “duplicated” question that can be solved with Group By and Count > 1 but it’s not. I have been googling for days and pulling my hair […]

Today's newsletter email had misdirected links (4/22) - Today’s email newsletter features an article titled “Service Broker Part 2: Why Service Broker”. If you click on that link, you’ll be taken to, instead, a 2012 article by Tony Davis on the subject of “Data Compression Double Take”. The link has the underlying address of… https://www.sqlservercentral.com/?post_type=ssc_article&p=7507

Express edition DB crossing limit - We have an express edition database and performing bulk import into a table. Table details when fully imported:- number of rows – 143,073 ReservedKB – 66136, Data – 66136, IndexKB – 43352, UnusedKB – 317648 Data file details when fully populated: File size – 10273 MB, Used space – 1348.56 MB, Free Space – 8924.44 […]

SSIS strips periods off column names in Flat File Connection Manager - This question is related to my previous question but is different in focus, so I’m creating a new question. I have a CSV file that has only column names in it, no data. I’ve created a Flat File Connection Manager in SSIS using Visual Studio 2012. I’ve checked **Column Names in the first data row** […]

How can I identify gaps in coverage dates - I need to look through 200,000 IDs with multiple dates and identify just the ID’s that have a gap. My first step was going to be to pull all the dates together to see gaps, but all I did was get the Min and Max and ignored the gaps. How can I identify something that […]

Not Receiving Daily Newsletter - I have not received a daily newsletter since 4/5/19. I asked our support staff if the newsletter email might be getting bounced and they said the last one that came into our system was on 4/5/19. Thanks, Nancy

Adding images - This should be animated

How to test Asynchronous Failover - Hi All, I have a working SQL 2016 Enterprise AAG at our primary datacenter using Synchronous mode. We are using a file share witness in another datacenter. I created a new SQL 2016 Enterprise instance in Azure for DR purposes. The AAG at the primary DC is still being tested so is not in production […]

SSRS not displaying image from AWS S3 bucket - Hi, I am creating a report which needs to show an image from AWS S3 bucket. I am using image source as “External” and Image URL as use this image option. I could see the image in the design view but not in the preview. I am not getting any error while previewing the report. […]

How to write MERGE statement in sql server - Hi Sir, How to write MERGE statement when using more then two source tables joining with the TARGET table. Sample SQL DDL and insert statement is as below, CREATE TABLE m_exp (m_exp_key INT, m_dea_key INT, m_del_key INT, m_date_key INT ) INSERT INTO m_exp VALUES (10,13,2,201901); INSERT INTO m_exp VALUES (10,13,2,201902); INSERT INTO m_exp VALUES (10,13,2,201903); […]

How to calculate DTUs or eDTUs for my PAAS SQL database - Hi Expert, Can anyone guide me the way to calculate DTU for my SQL PAAS database. I’m really stuck here. Thanks Brijesh

Error when creating a database diagram - When I create a new database diagram, I get this error: What does this mean and how can I resolve this?

SQL Hung - Dear All, My production sql server was hung on a specific day for about 2-3 hours. Can some one help how can i do the post mortem report and what has caused that issue. Thanks in Advance!

Error when exporting Excel file - I am trying to export an Excel file and I get this error: How do I resolve this? I think this is related to MS Access but I’m exporting to an Excel file (I’m specifying Excel in the Destination field).

Boolean expression not working - I have the following code: SELECT A, B, A = B FROM (SELECT ((.1 + .2) * 10) as A, (1.0 + 2.0) as B) as tbl1 which returns this: Shouldn’t the 3rd column be named A = B instead of just A? More importantly instead of having a value of 3.0 in the 3rd […]

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.