SQLServerCentral.com Community Update for April 17, 2023

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

Multiple Defaults

The Voice of the DBA

Coming Out of the Cloud

Google has launched a version of their AlloyDB that can be installed on-premises. AlloyDB is a PostgreSQL compatible cloud database, a full-managed PaaS service. However, they are giving away a free developer edition and a paid for commercial license that can be installed where a customer wants to run it. The new product is called AlloyDB Omni.

I like Google's approach here. Obviously Google would prefer lots of their customers would move to the cloud version hosted in GCP (Google Cloud Platform), but they know that's not realistic. Even for customers that want to move to GCP, the customers might need to keep some workloads on-premises for a period time, so Google is giving them an option to modernize their workload with a new datastore that is PostgreSQL compatible, but running on-premises. Presumably this will be compatible with the cloud version and if customers want to, they can just shift to AlloyDB.

This is similar to what Microsoft is doing as Azure SQL versions, both database and Managed Instance, are mostly compatible with SQL Server on-premises. There are easy migration paths, but since the cloud and local versions aren't quite the same, it might not be simple to migrate. There are lots of tools to help, but the biggest problem (in my mind), is having a development environment that mimics what I get in the cloud. I need to be able to work not only offline, but with the unlimited CPU I get on my laptop. I don't want to pay for developers to stand up cloud instances to experiment with code.

There is the SQL development container (running Edge), but that's a container and I find far too many people don't like working with the container versions. They struggle with getting their data set in the container, or keeping it up to date. Plus there's the fact that the local environment seems linked to database projects, which not everyone uses. Especially those of us using Redgate tooling.

I have been surprised in the last five years by how many companies have moved to the cloud. I'm especially surprised how many have performed lift-and-shift migrations to IaaS services after a mandate by management. I'm also not surprised that many customers find they're spending too much, and that both Azure and AWS realize they need to help customers spend less before they lose them.

The cloud can be a good place for workloads, but you need to plan for it, and often you need to modernize apps, make them less chatty, and write better code against your data services. To do that, I think you need a local dev environment and I like the way that Google is providing that with AlloyDB Omni. I wish we also had a switch to get a local SQL Server dev edition to act like Azure SQL DB.

Steve Jones - SSC Editor

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

Featured Contents

Create an Empty Database Using SQL Server Integration Services

Nisarg Upadhyay from SQLServerCentral

Learn how to create a copy of an existing database without any data using Integration Services.

SQL Prompt Safety Net Features for SSMS: SQL History

Additional Articles from Redgate

Mistakes occasionally happen. Occasionally, you make some ill-judged 'refinements' to working code and now just wish you could rewind your tab back in time an hour and forget the whole sorry episode. Now and again, SSMS just conspires against you and crashes unexpectedly, and you lose all your currently open query tabs, some of which you hadn't saved. SQL History offers a useful safety net in the event of any of these unfortunate events.

Use DDL Triggers to Automatically Keep SQL Server Views in Sync

Additional Articles from MSSQLTips.com

As much as we tell people to use SCHEMABINDING and avoid SELECT *, there is still a wide range of reasons people do not. A well-documented problem with SELECT * in views, specifically, is that the system caches the metadata about the view from the time the view was created, not when the view is queried. If the underlying table later changes, the view doesn't reflect the updated schema without refreshing, altering, or recreating the view. Wouldn't it be great if you could stop worrying about that scenario and have the system automatically keep the metadata in sync?

From the SQL Server Central Blogs - Truncating very large transaction log in full recovery mode

SQLPals from Mission: SQL Homeostasis

While I always configure transaction log backups for every database in non-simple recovery model, and to compliment it I also have monitoring in place if transaction log doesn't get...

From the SQL Server Central Blogs - Best way to monitor high worker thread on SQL Server

dharmendra.keshari from SQL Geek

It is common to see high worker count issue on the production server and it can have a range of negative impacts on SQL Server performance and stability, potentially...

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):
	Multiple Defaults
I want to add two columns to one of my tables, but include defaults for them. Can I do this? ALTER TABLE dbo.Test1 ADD CustCount INT DEFAULT 0, CustName VARCHAR(20) DEFAULT 'N/A'
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)

DOP Feedback Compatibilty

If I have a SQL Server 2022 database, what compatibility level needed?

Answer: compat level 160

Explanation: The DOP feedback requires compatibility level 160 in a database. This is the default level for SQL Server 2022. Ref: DOP Feedback - https://learn.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing-feedback?view=sql-server-ver16#degree-of-parallelism-dop-feedback

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

Can not Truncate the database because “Secondary has no log was added” - I got an error when trying to truncate the database: "Database can not shrink until all secondaries have moved past the point where the log was added". When I check the status of the secondary on the primary machine (Always On High Availability Dashboard), it has heath: good and Synchronization state: Synchronizing. But the status […]

Blocking query - Hi All, Seeing blocking on production server. There is one particular sql statement which is causing the blocking all the time. Please guide and on how to resolve or minimize the blocking for this statement. I always see this as a head blocker. These sql statement is executed quite a lot. Why it is holding […]

SQL Server 2016 - Development and T-SQL

Hierarchy related calculation - Hello all, I have a hierarchy of businesses. example can be created as follows: create table #temp (LeafLvl varchar(20), L1 varchar(20), L2 varchar(20), L3 varchar(20), L4 varchar(20), L5 varchar(20)) insert into #temp (leafLvl, L1, L2, L3, L4, L5) select 'L2-1', 'Top', NULL, NULL, NULL, NULL UNION ALL select 'L3-1', 'Top', 'L2-1', NULL, NULL, NULL UNION […]

SQL Server 2019 - Administration

if then else issue - I'm trying to use the if then else to check if a member exist in a server role. if it does, do X, else do Y. but not getting the desire outcome. BEGIN DECLARE @member nvarchar(50); USE [master] SELECT @member = members.name FROM sys.server_role_members AS server_role_members INNER JOIN sys.server_principals AS roles ON server_role_members.role_principal_id = roles.principal_id […]

database schema and permissions - Hi - trying to find a t-sql to list all db schemas and their permissions. unable to find anything helpful on google. thanks

SQL Server 2019 - Development

Find the records based on group by Student ID - Data looks like below Status 1 is Active and 2 is Clean For one Student ID we may have one active, one clean status; I want to find the records only with Clean status for a student ID. Student Id Student_Status 12345 […]

problem of slowness in my requests - hello, I am having a major performance issue , with this part of code which is still blocked on my server , Attached is the part of the code that is causing the problem. DELETE R FROM #RESULTS_DEM AS R INNER JOIN dbo.Dem_trans AS DEM ON DEM.NUM_DEM = R.NUM_DEM WHERE ( NOT EXISTS ( SELECT […]

Joining CTEs Causing Performance Issues - Hi everyone I am not sure why the query is taking so long to run. If I run each CTE separately then it runs in less than 2 seconds. However, when I join them they take forever to run. I stop the query after about 10 minutes. I am pretty sure I am joining them […]

Need to create a .xml file in specific format using SQL Query. - I have a table of data that I need to create a .xml file from in a specific format. CREATE TABLE [dbo].[XML_TABLE]( [ProductID] [nvarchar](25) NULL, [Name] [nvarchar](25) NULL, [ParentID] [nvarchar](51) NULL, [AttributeType] [nvarchar](10) NULL, [AttributeID] [nvarchar](255) NULL, [AttributeValue] [nvarchar](4000) NULL ) ON [PRIMARY] GO Here are some values to insert: INSERT INTO WORK.DBO.XML_TABLE([ProductID], [Name], [ParentID], […]

SQL Azure - Development

Copy DB from Prod to Test !! - Good morning. I would like your advice. I work on Azure Sql. We have 3 environments (Dev, Test and Prod) Each environment has a specific tenant. Every month, I would like to copy the database from Production to the Test environment. What solution do you recommend to perform this task? Regards

SSRS 2014

Open linked report in new tab with multiple parameters (javascript) - I have a report that links to another report on the server, while the way I have it currently works the users are requesting that it opens in a new window. The report being linked to has two multi select parameters ="javascript:void(window.open('http://east-rpdb01/reports/report/Testing/Profitability%20Report%20All%20Clients&FiscalYear=" & Fields!FiscalYear.Value &"'))" I am close to getting this but not sure […]

Anything that is NOT about SQL!

Need information on past SQL Summits, please... - I'm endeavoring to convince my employer to send me to SQL Summit this year, but I'm going to need to "sell" that it would be worthwhile for them to do this. And sell it not just to my supervisor, but to his supervisor and to her superior. Now, I fully expect that there's going to […]

SQL Server 2022 - Development

The Nook Spa - The Nook Spa is a safe and healing environment. offering therapeutic massage services, body treatments, facials, anti-aging treatments, enhancements and infrared sauna. Services are provided by licensed estheticians and professional massage therapists in Dallas, TX. for more details click here:Professional Thai Massage Therapists Dallas TxThai Massage Services Dallas Tx

What are some SQL commands that data scientists should be familiar with ? - Hello As an individual who is new to the field of data science, I am keen to gain insights into the fundamental SQL commands that data scientists should possess knowledge of. I would appreciate your guidance on this matter.

This email has been sent to newsletter@newslettercollector.com. 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.