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

Re-Evaluating the Cloud

Last year 37 Signals (makers of Basecamp and Hey)  announced they were leaving the cloud. I wrote about the decision, and wondered if they'd look back at this as a great decision or one they'd regret and backtrack to the cloud again. They planned to build their own tooling, buy a bunch of servers, and run their own data center (or rather, rent space in someone's data center).

Recently there was an update, in an FAQ, about how the transition has gone. In short, very well. One of the founders, David Heinemeier Hansson answered several questions about the move and the financial status. They didn't hire more people, so their payroll is the same. They used a service to unpack and rack their servers, so they could just connect to them remotely and not deal with hardware. They built their own redundancy across two data centers where they rent space, and they think they will save well over $7 million in the next 5 years. They had a $3.2million cloud budget per year, so that appears to be halved (3x5=15 - 7 = 8ish) with their move.

There are some answers to various questions, which are likely of interest to many data professionals who might feel pressure to move to the cloud. First, they discuss resource optimization in the cloud. I think that's really hard, to do well and if they gave the details of what they'd done, that might help others decide if 37 Signals did a good job, or if there are things others could do. I think many people might struggle to optimize their usage, especially if you are only responsible for one thing, like the database. In many orgs, once something gets deployed, or developers build a PoC using some cool, new service, it's hard to get rid of it or even change how it's used. I think 37 Signals has some agility here that many orgs struggle to implement.

Rewriting things are cloud-native is the way to go, in my mind, and I do have customers and clients who see applications working better in the cloud. However, I might argue that rewriting applications is hard and expensive, and many companies aren't great at writing software that's efficient, so rewrites are hard. I also think in most organizations, developers struggle to understand what cloud-native means. I think DHH is a little off here, as their developers probably could make better software written for the cloud, but they didn't want to spend the time and/or money.

The points about security and reliability are fair, but I think those are a function of having good people implementing systems. You can get good security and reliability in the cloud or poor implementations of either. The thing about security is that the overall protections that detect some of the DDOS or attacks are better with Azure/AWS, but simple SQL injection or open firewalls are still a problem. Authentication and security are just hard for most people and often people do this poorly in the cloud. To be fair, most people don't do this well on-premises, so at least in the cloud, there are some services/scanners/checks that let you know when you've messed up. Whether you change or implement their suggestions is another whole debate.

Is reliability better? AGs are hard in SQL Server, as are distributed clusters. The cloud services do this better for most people, but maybe not for you. That brings me to his super engineer point. He doesn't think he has super engineers on staff, but I disagree. I think they can pick good people, especially because of their profile, and they don't need a lot of people, so their average engineer is likely better than the average engineer at most organizations. They built some great software, and they've built an amazing framework and tooling to deploy their software. Don't tell me that's even close to the capabilities of the staff at many organizations. We have really good engineers at Redgate, and I think we're above average as well. I don't know how we compare to them, but we haven't had people publicly write and release code that thousands of other developers use. At least not at quite the same high profile level.

The cloud has its place. It can work well and it can be very expensive. This journey is worth sharing with your management if they want to move to the cloud, especially if they want to lift and shift. That might create some flexibility and CapEx/OpEx changes that are worth it, but you ought to debate and question whether that's reality or marketing hype from a vendor. After all, it's not clear if a cloud move is really something that returns an ROI or one that reduces your profitability.

Steve Jones - SSC Editor

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

 
 Featured Contents
SQLServerCentral Article

Adding Cookie Consent to SQL Server Central

Press Release from SQLServerCentral

We are adding a consent form to the SQL Server Central website to allow our cookies.

SQLServerCentral Article

An In-Depth Look at MIXED_PAGE_ALLOCATION and Changes in Page Allocation

Parthprajapati from SQLServerCentral

Learn about the benefits of mixed extent allocation in SQL Server 2016, along with how you can check this setting or enable it in your database.

External Article

Connecting SQL Server to SaaS applications with a Linked Server

Additional Articles from MSSQLTips.com

But nowadays, most services are in the cloud. They're not on your servers; they're somewhere else. With most software-as-a-service (SAAS) offerings, you don't have many options to configure connectivity. What if you need data from one SaaS service into another?

Blog Post

From the SQL Server Central Blogs - Update Azure SQL database and storage account public endpoint firewalls with Data Factory IP ranges

Meagan Longoria from Data Savvy

While a private endpoint and vNets are preferred, sometimes we need to configure Azure SQL Database or Azure Storage to allow use of public endpoints. In that case, an...

Blog Post

From the SQL Server Central Blogs - Build a Terraform Module for Azure SQL

hellosqlkitty from SQLKitty

Building a Terraform module for Azure SQL DB is like packaging your infrastructure magic into a reusable box. It’s the kind of thing that makes your IT life smoother....

Transact-SQL: The Building Blocks to SQL Server Programming eBook by Gregory A. Larsen

Transact-SQL: The Building Blocks to SQL Server Programming by Gregory A. Larsen

Greg Larsen from SQLServerCentral

Transact SQL (TSQL) is the languaged used to query and update data stored in a SQL Server. This book, written by SQL Server Central and Simple Talk author Greg Larsen, will give developers an understanding of the basics of the TSQL language. Programmers will have the building blocks necessary to quickly and easily build applications that use SQL Server.

 

 Question of the Day

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

 

Adding a PK - I

I have this code:
CREATE TABLE SalesTracking
( SalesDate DATETIME
, SalesPersonID INT
, CustomerID INT
, PONumber VARCHAR(80)
, paid bit
)
GO
CREATE CLUSTERED INDEX SalesTrackingCDX ON dbo.SalesTracking (SalesDate)
GO
ALTER TABLE dbo.SalesTracking ADD CONSTRAINT SalesTrackingPK PRIMARY KEY (PONumber)
GO
What happens when I run this on SQL Server 2022?

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)

Which Default Schema

I run this code on a SQL Server 2022 instance:

CREATE LOGIN JoeDBA WITH PASSWORD = 'DemoP@sswordTh@t1sLong'
GO
ALTER SERVER ROLE sysadmin ADD MEMBER JoeDBA
GO
USE sandbox
GO
CREATE SCHEMA etl
GO
CREATE TABLE etl.Customer (CustomerID INT, CustomerName VARCHAR(200), LoadDate DATETIME)
CREATE TABLE dbo.Customer (CustomerID INT, CustomerName VARCHAR(200), status tinyint)
GO
INSERT etl.Customer
  (CustomerID, CustomerName, LoadDate)
VALUES
  (1, 'Acme', GETDATE()),
  (2, 'Roadrunner, Inc', GETDATE()),
  (3, 'Coyote Enterprises', GETDATE())
GO
INSERT dbo.Customer SELECT c.CustomerID, c.CustomerName, 1 FROM etl.Customer AS c
go
CREATE USER JoeDBA FOR LOGIN JoeDBA WITH DEFAULT_SCHEMA =[etl]
GO

This all works fine. I then log into the instance as JoeDBA. I change to the sandbox database and run this:

USE sandbox
GO
SELECT * FROM Customer AS c

Which three columns are returned?

Answer: CustomerID, CustomerName, Status

Explanation: With a sysadmin, the default schema is always dbo. Ref: ALTER USER - https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-user-transact-sql?view=sql-server-ver16

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
How to optimization of a stored proc - Hi All, Need some help on optimizing a stored proc. I see some of the update statements written multiple times. Instead of writing multiple updates , can we re-write in one single statement  which might avoid reading same table multiple times. This stored proc is doing a lot of logical reads almost like 76TB of […]
SQL Server 2017 - Development
Grouping based on chronological periods - Hello, I have a table Customers that contains the current value and another table Customers_History contains the history values. The customers have more that 10 fields AS Bellow a sample of data : CREATE TABLE #CUSTOMERS( ID int primary key identity, First_Name varchar(255), Last_Name varchar(255), Nick_name varchar(255), Email varchar(255), Date_Bithday date, Gender varchar(1), Is_Married int, […]
Null value is eliminated by an aggregate or other SET operation - Should I see this warning when I have ISNULL check at SUM function and changing NULLs to 0?   Thank you
SQL Server 2012 - T-SQL
parse JSON formatted text into M3U Playlist - sorry.. solved..
Parse out filed as date time ??? - Hi   I have a field with a start and end date (sometimes no end date) . How can I parse this out to have two fields start and end dates? Data looks like ~__2019121407025301GMTOFFSET=-18000~__2019121415164202GMTOFFSET=-18000~ desired out is Start Date 12/14/2019 7:02 AM End Date 12/14/2019 3:16 PM   Thanks  
SQL Server 2019 - Administration
SQL 2019 data masking - I have setup data masking on several fields and can see that the data is masked using t-sql.  I need this masking to follow through to report server.  I am not seeing the data masked there.  any ideas / suggestions would be greatly appreciated
SQL Server 2019 - Development
ReportViewerControl.exportReport opening in new Window/Tab - Hello, I am using the ReportViewer.aspx page to display a SSRS Report. When exporting the report to any format(CSV,PDF,Excel, etc.) it uses the ReportViewerControl.exportReport function which has the following code: window.open(this.ExportUrlBase+encodeURIComponent(n),"_blank") What I would like to do is stop a new window/tab from being created, so instead of "_blank", "_self" could be used. Any idea […]
Reporting Services
copy or duplicate a folder in SSRS web portal - Is there a way to copy a folder in SSRS 2019 server web portal to another folder, I want to make a copy of backup for temporary keep. For example I have  a folder called myReports which has 20 reports in it, I want to duplicate the folder and all the reports in it to […]
Analysis Services
SSAS tabular account impersonation - Hello, I'll try my best to summarize the problem and I hope someone can offer me a hand with this. The Enviroment: 2 Domains (let's call them domain_1 and domain_2) Server1 (it is joined to domain_1) This server has SQL Server and SSAS SSAS has a domain account set up on the service Domains have […]
Integration Services
On Error Event handler in For Each Loop Container - I have a For Each Loop Container that iterates through a list of servers in my environment. Usually it is used to gather information from each of the servers. Inevitably there are a few errors. But my error handler does not write the name of the Server at which the error occurred. Here is my […]
Visual studio 2019 hangs when open excel data source - I had Visual studio 2019 installed on windows 10. I have a SSIS package that has been working when I run it in visual studio. But recently when I open the ssis package, and open the excel data source to edit, it freeze there forever, then visual studio shows busy. I have to hard stop […]
SQL Server 2022 - Administration
Scheduling AWS RDS instance resizing - I know that it is possible to use the AWS Scheduler to stop and start RDS instances. For my purposes we just want to resize it e.g. 16 to 4 processors, so it can still work in low usage periods but will not cost so much. Has anyone ever done anything like this? We do […]
SQL Server 2022 - Development
How to query selected multiple rows into multiple colums - How to display datas using query data from multiple specific rows in one table sql into multiple columns? For example, insert red data into column1,  insert blue data into column2,  insert green data into column3,  etc. I've used these code : SELECT um_value FROM wp_um_metadata WHERE um_key='first_name' But the result I only got one column. […]
Column Name Is Different in Power BI Than SQL View - Hi all, Using Snowflake. When I Describe my View the first columns shows as Nielsen Market Name (KUSA Adjusted) VARCHAR (10000) I connected to the View from Power BI Desktop (PBID) However, in PBID, the name of the column is showing as Nielsen Market Name (USA Adjusted) - the "K" has been dropped from the […]
Basic query performance - Hi, I have this pretty basic query that is starting to perform slowly. It returns over 22m records in about 5 minutes. I know its difficult without table defs but any ideas on what I can try to make this return faster?   SELECT [e].[eligibility_id] FROM [dbo].[eligibility] AS [e] -- Table is updated hourly with […]
 

 

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

 

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