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

Daily Coping Tip

Write down three things you can look forward to in the next month

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.

Things I've Learned About the Cloud

I do a little cloud work, but mostly I end up working with customers that are trying to build and migrate their systems to the cloud. The transition to cloud first took place quicker than I expected, though certainly the pandemic of the last 3 years has continued to accelerate the moves by many organizations. While not all systems move, for better or worse, many organizations consider the cloud for everything and then decide to accept or reject the notion for individual cases. That's the cloud first world I see.

At a recent event, I had numerous people telling me their were mandated to move to the cloud in a lift-and-shift fashion, and many of the technologists weren't happy. That's a conversation for another day, but listening to them and others has taught me a few things. Some of these are experiences I've learned, and some are things from others, nuggets gained from hard experiences.

First, lift-and-shift appears to be a way to get started in the cloud. Some people find this to be an end-goal when they can get systems running without ever worrying about hardware or facilities. While costs can be higher, flexibility and tax considerations can make this worth the effort. Others see this as a first step to moving towards Platform-as-a-Service (PaaS) products and rewriting software, which is a good way to approach the process, but that leads me to the second nugget.

Everything takes longer to get settled in the cloud than you expect. Not that the cloud is slower to build and use resources. The opposite is certainly true as provisioning anything is fast, and upgrades (or downgrades) are incredibly flexible and quick. However, making the move, getting systems running to your satisfaction, decommissioning old systems, and more take longer. It seems most life-and-shift projects end up taking much longer than expected, and not all of this is the massive data transfers needed to move data assets. Often it's humans that can slow the process, whether through debate on decisions or just getting comfortable with cloud resources through testing. Things take longer.

Maybe the last big thing that I've seen affect many companies is that we have to put better software development techniques and infrastructure configuration in place. The connection to resources is more complex and tenuous than it is on a LAN. We need to better architect software, really architect it as we were told to do so early in our careers with better error handling and retry logic. The cloud is very secure when you set it up right, but you no longer have the castle with high walls that you grew used to in a data center. Instead, you have a series of people and systems that are disparately connected and you need to ensure you protect each one along with the connections between them. Plus there are new tools to learn and the habits of using command line interfaces to build.

I love the cloud. In many ways, I prefer it over building and managing things on premise. Not for everything, and not for things where I might lose productivity with slow connections. I certainly don't want developer laptops or workstations in the cloud, but having other systems there can remove a lot of the hassles of managing assets that we purchase, track, upgrade, etc. I don't know I'd ever want to buy another physical server in a company I owned, but I also know that at some scales, it might still make sense.

The other thing I've learned about the cloud is there are a lot of unknown unknowns, as well as plenty of known unknowns. Tackling the cloud require a staff that wants to grow and learn with the platform, as well as one that knows when to use the resources they know best and don't add complexity or novelty just because someone wants to try something new. The cloud is worth considering, especially as it seems physical offices and data centers are becoming rarer and rarer in many organizations.

Steve Jones - SSC Editor

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

 
 Featured Contents
SQLServerCentral Article

Database Deployment with Terraform - Modules

wagner crivelini from SQLServerCentral

Deploy resources in Azure using reusable code with Terraform modules.

External Article

Rethinking SQL Explicit Transactions

Additional Articles from MSSQLTips.com

This article looks at a simple example of using explicit transactions when modifying data in SQL Server tables.

Technical Article

Post-migration Validation and Optimization Guide

Steve Jones - SSC Editor from SQLServerCentral

SQL Server post migration step is very crucial for reconciling any data accuracy and completeness, as well as uncover performance issues with the workload.

Blog Post

From the SQL Server Central Blogs - Scary Scalar Functions - Part Four: Your Environment

Zikato from StraightforwardSQL

Scary Scalar Functions series overview

Part One: Parallelism
Part Two: Performance
Part Three: The Cure
Part Four: Your Environment

Foreword
In the previous posts, we have learned why Scalar Functions (UDFs) are bad for parallelism...

Blog Post

From the SQL Server Central Blogs - Viewing errors with extended events

Kenneth.Fisher from SQLStudies

A little while back I came across this post: Identifying failed queries with extended events. It has a script to ... Continue reading

 

 Question of the Day

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

 

The Percentile Comparison

Both PERCENTILE_CONT and PERCENTILE_DISC produce a percentile value from a set of data. One interpolates the result and one picks a value from the dataset. If I have this table:
CREATE TABLE PercentileCont
( PCID      INT NOT NULL IDENTITY(1, 1)
, Category  VARCHAR(20)
, SomeValue INT);
go

INSERT PercentileCont
  (SomeValue, Category)
VALUES
  (10, 'Finance')
, (20, 'Finance')
, (30, 'Finance')
, (40, 'Finance')
, (50, 'Finance')
, (60, 'Finance')
, (70, 'Finance')
, (80, 'Finance')
, (90, 'Finance')
, (100, 'Finance')
GO
What happens with this query?
SELECT
  category
, SomeValue
, PERCENTILE_CONT (.5) within GROUP(ORDER BY somevalue) OVER (partition BY category) EightyPercent_Cont
, PERCENTILE_DISC (.5) within GROUP(ORDER BY somevalue) OVER (partition BY category) EightyPercent_Disc
FROM PercentileCont
GO

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)

What's the CEIP?

What is the CEIP service in SQL Server?

Answer: The configure usage and diagnostic data collection for SQL Server service

Explanation: This is the configure usage and diagnostic data collection service that collects information about how you use SQL Server. None of this information is collected:

  • Any values from inside user tables
  • Any logon credentials or other authentication information
  • Personal information

Ref: Configure usage and diagnostic data collection for SQL Server (CEIP) - https://docs.microsoft.com/en-us/sql/sql-server/usage-and-diagnostic-data-configuration-for-sql-server?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
SQL Server Windows NT - 64 bit" in Task Manager: CPU high -   Hello ,   The SQL Server Windows NT - 64 bit" in Task Manager: CPU high  56,961 MB , the overall server CPU is now 95 % kindly advise the solution.
subscriber part of ALWAYSON - Hi. Publisher stop sending transaction to AOAG primary replica whenever AG failover.  It seems push publisher does not re-directed to subscription database while AG failover. Replication monitor found error messages as below. please suggest me how resolve this issues? Command attempted: if COLUMNPROPERTY( OBJECT_ID('MSreplication_subscriptions'),'distribution_agent','AllowsNull') <> 1 BEGIN alter table MSreplication_subscriptions alter column distribution_agent sysname null […]
SQL Server 2016 - Administration
Shrink database issue - In my database, I have few temp tables that total up to 50GB in size. I already removed the data from the table. When I set the shrinkdatabase in my maintenance plan, it runs successfully but doesn't shrink the database. I tried dbcc shrinkfile(1,truncateonly) and got the same result. However, from sql management studio when […]
SQL Server 2016 - Development and T-SQL
Grant user permissions to a view but the base tables live in another database - I have two databases on the same server.  I have a service account I want to have access to SELECT off some view in the "Staging" database.  The view calls on tables in our Finance database.  If I run the SELECT as myself or other "normal" users it works fine because those accounts have access […]
The ORDER BY clause is invalid in views - Hi, I'm trying to run the below SQL code but I'm getting this error: An error occurred while checking the query syntax. Errors: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified. I'm still learning SQL so any […]
SQL 2012 - General
SQL Server Licensing Question - I'm in the process of replacing a sql server 2012 with 2019. How do CALs work if pieces of the database are publicly accessible? How do you purchase CALs for that? Or do you just only buy the core model in that case? We only have 2 actual employees who will be working with the […]
SQL Server 2019 - Administration
Change tracking - Date a row changed - We have change tracking enabled on one of our databases and I am looking for a query where I could get the timestamp/date on when a row changed.  Any help is greatly appreciated.  Thanks a lot.
Data replication tools and review - Hello expertise, Could you please suggest best tool for database replication? data replication on transactional basis and using for filtering option like DELETE transaction not replicate to target database due to data should maintain long term in target database instance. Thanks.    
SQL Server 2019 - Development
How to run a query multiple times with different values in date - I'm very new to the sql and am wondering what is the easiest way to run the same query multiple times with different dates (ideally is the last day of each month). Can I create a loop so that I don't need to manually change the value in Date? Values in date are 31Jan2022, 28Feb2022, […]
Migration of simple SPs from 120 to 150 COMPATIBILITY_LEVEL issue. - Migration of simple SPs from 120 to 150 COMPATIBILITY_LEVEL issue. Execution time for the first time run of any SP is close to 3 min. After the first execution, it takes ms to repeated execution. The execution plan is identical in 120 and 150. Recompile did not help. SPs are sitting on wait: PAGEIOLATCH_SH for […]
Referencing a SQLCMD variable in a Post Deployment script using SqlPackage - Ive got a Database project running in JetBrains Rider. The project publishes a DACPAC to create or update a target database. Now I have a set of reference data scripts which run (in Create mode) and these are reference through the Script.PostDeployment which looks like this: :r .\LoadDeviceConnectionType.sql :r .\LoadUser.sql :r .\LoadConversionType.sql :r .\LoadChannelType.sql :r […]
SQL Azure - Administration
encrypt the database in azure - How can we encrypt the database in Azure. Research story.   Thank you
Amazon AWS and other cloud vendors
Version Controlling Lambdas - I know there are lambda versions. But I want to know if this can be done using GitHub, can we integrate it with CodePipeline and create something like a centralised repo for all our Python lambdas and then update those functions with every push.
Reporting Services
SSRS Reporting - Anyone can give me a sample program on how to pass parameters from SSRS RDL Report to ASP.net Core or C#? I have a project with multiple users in multiple groups. The idea is for users to generate reports only from their group and they cannot generate reports from other groups. Like when a user […]
How to show currency symbol in column header - In our reports we display the currency ($) symbol in the column header for example Amount ($) or Points Value ($) etc. If we were to "globalize" our reports, how can we pick up the currency symbol from the Culture UI or the registry (for example S-1-5-18 Local system)? I tried this but it did […]
 

 

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

 

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