SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

The First Day of the Week

The Voice of the DBA

An Upgrade Slog

I saw a blog post from Randolph West recently that asked How do you restore a SQL Server 2000 database in the year 2024? It's a bit of a process, involving an intermediate version and two restores. He also points out the need to run DBCC after the first restore, which is a good idea. I wonder how many people would take the time to do this, or even think about it as an upgrade step?

This was interesting to read as I had a customer ask me about doing this a few months back. They were trying to clean up their database estate and modernize some of their older systems. This was becoming a big project for them, as they had several pre-2017 systems, none of which were in support. Auditors, regulatory authorities, and even business partners see this as a large security risk and get concerned if you're running older software.

I've felt that in most cases, I ought to be able to run a database server for close to a decade. I certainly need to patch it with CUs in that time, but the support lifecycle says that you get mainstream support for 5 years and then extended support (paid) for 5 more. That extended cycle also includes security patches, so ten years seems reasonable.

As a side note, the final support lifecycle for 2014 ends on 9 Jul 2024. That's a decade if you upgraded in the first year of release.

However, many of us have multiple instances, and upgrading those can be a chore. Perhaps you trust that nothing breaks, but I would say for many larger organizations, upgrades are a constant fact of life, and it is important to probably start testing upgrades at five years, knowing it might take 1-2 years to upgrade all instances of a given version. That's if you don't find issues in testing. If you test a 2017->2022 upgrade now and find issues, you might spend time mitigating these, or maybe wait for SQL Server 2025 (my guess) and hope you don't have the same issues. There are also the challenges of in-place vs. side-by-side upgrades, and you might choose one in testing, but decide to change for various reasons.

I still find myself a little nervous about the "evergreen" versions of SQL Server, where Microsoft patches them as needed. I know they try hard not to break any backward compatibility, but if they do, then you're stuck. I prefer to schedule my upgrades and make them a normal part of the DBA job. That being said, don't drag them out for years and years. If you still have SQL Server 2012 or older versions, you're doing something wrong.

Steve Jones - SSC Editor

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

Featured Contents

Meet the new Google AI Assistant Gemini

Daniel Calbimonte from SQLServerCentral

Learn about the new Gemini AI from Google and how this can be used for common tasks, as well summarizing content.

General program for PASS Summit 2024 now available

Additional Articles from PASS

Did you know that the general program for PASS Data Community Summit 2024 (November 4-8) was recently unveiled? This year's lineup includes over 150 different sessions being delivered by over 140 speakers!

This year's program also includes 16 incredible full day pre-cons, taking place on November 4 and 5, plus 6 different learning pathways to choose from. Join PASS Summit on-site for a whole week of connecting, sharing and learning with industry experts. Register before July 16 to secure a 3-day ticket at the early bird rate.

What does SQL NULL mean and how to handle NULL values

Additional Articles from MSSQLTips.com

This tip will help you understand what NULL means and different ways to handle NULL values when working with SQL Server data.

From the SQL Server Central Blogs - What to Know about Power BI Theme Colors

Meagan Longoria from Data Savvy

Power BI reports have a theme that specifies the default colors, fonts, and visual styles. In Power BI Desktop, you can choose to use a built-in theme, start with...

From the SQL Server Central Blogs - System Views in Microsoft Fabric – Query references an object that is not supported in distributed processing mode

Koen Verbeeck from Koen Verbeeck

I have a metadata-driven ELT framework that heavily relies on dynamic SQL to generate SQL statements that load data from views into a respective fact or dimension. Such a...

The Definitive Guide to Azure Data Engineering: Modern ELT, DevOps, and Analytics on the Azure Cloud Platform

Site Owners from SQLServerCentral

Build efficient and scalable batch and real-time data ingestion pipelines, DevOps continuous integration and deployment pipelines, and advanced analytics solutions on the Azure Data Platform. This book teaches you to design and implement robust data engineering solutions using Data Factory, Databricks, Synapse Analytics, Snowflake, Azure SQL database, Stream Analytics, Cosmos database, and Data Lake Storage Gen2.

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	The First Day of the Week
If I run this, what day is the first day of the week? SET DATEFIRST 1
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 is a table?

Of these items, which are tables according to the relational model from Dr. Codd?

CREATE TABLE dbo.Customers_a
(
    CustomerID INT IDENTITY(1, 1) NOT NULL,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    Email NVARCHAR(100) NOT NULL,
);

CREATE TABLE dbo.Customers_b
(
    CustomerID INT IDENTITY(1, 1) PRIMARY KEY,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    Email NVARCHAR(100) NOT NULL,
);

CREATE TABLE dbo.Customers_c
(
    CustomerID INT NULL IDENTITY(1, 1),
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    Email NVARCHAR(100) NOT NULL UNIQUE,
);

CREATE TABLE dbo.Customers_d
(
    CustomerID INT NOT NULL ,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    Email NVARCHAR(100) NOT NULL,
);

Answer: Customers_b only

Explanation: The relational model defines a table as having columns and rows where data is stored, but with the idea that each row can be identified uniquely. A primary key is the way we do this and the ANSI standard assumes this is NOT NULL. I don't have a reference fro SQL-92, but this notes SQL-89 requiring this: https://docs.openlinksw.com/virtuoso/pkeycons/ Kevin Kline's SQL in a NutShell also notes this: https://www.oreilly.com/library/view/sql-in-a/9780596155322/ch02s03s03.html

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

side effects of giving permissions to run traces - Hi All, I would like to know the downsides of giving permissions to run traces in lower environments (DEV/QA/UAT). Our DB team has only two members: one supports PST hours, and the other supports IST hours. The PST resource is on leave for a month, so I have to cover both time zones as needed. […]

Select permission denied despite group having db_datareader - I am at a loss and could use a second pair of eyes. We have a Server with database A and database B, a windows security group (MyDom\MyGroupName) which is created with dbo schema as its default in both databases, and a service account in this group. MyDom\MyGroupName has db_datareader in both databases. There are […]

SQL Server 2017 - Development

Split fields sql 2017 - I am trying to split fields in sql 2017. The column holds the following. I am pulling the first 50 characters and now I need to split those into 2 fields. This is a test name1 3006 […]

SQL Server 2016 - Administration

connection does not open from SSMS - Dear Friends, Would be grateful if anybody can advise why one of my servers, doesnt allow a connection anymore from SSMS on my terminal. The IP address is the unchanged. SQL server port was changed I remember, that i am anyways providing in with the IP as IP:portnumber..Thanks in advace.

How to improve index rebuild for very large table. - My online index rebuild job for a 200G table with 40mil rows took me up to 30hrs to complete, this is a once off activity done since we have an allocated downtime for server maintenance. I would want to know, is there any way for me to do it faster? I had an idea of […]

SQL 2012 - General

SQL code needed for the WHERE clause.. - I have a table with data appending on a daily basis. We plan to keep the set of data in this table for one full year and delete everything after August 1st of every year. I am stuck with the query logic to do this. The situations are like this: If today's date is August […]

SQL Server 2019 - Administration

Database growth report by full back file and send by email in graph and or html - Hello Gurus, How I am in need of all databases growth in 10 instance and send the report by an email. What's the best way to accomplish such task. If anyone has scripts or point me to a website that would be very helpful. thanks in advance.

Backuips are failing - After CU 27 update in sql 2019 backups are failing and all mainnaytainence jobs are also faling . we did not find any error on sql logs , pls assist on ho wto resolve this Message Executed as user: NT Service\SQLSERVERAGENT. Microsoft (R) SQL Server Execute Package Utility Version 15.0.4375.4 for 64-bit Copyright (C) […]

Query Store with AOAG stuck on waiting - Hey Folks, I have a database on SQL 2019 CU24 in an AOAG with 1 primary and 2 secondary. I have enabled Query Store but it will not open and just shows Waiting on the primary. I have QS setup on standalone SQL 2016 databases and they seem to be working fine. Any help is […]

SQL Server 2019 - Development

Web API Procedure will not run in SQL Server Agent Job - I have a stored procedure that pulls JSON data from a US government web API. The procedure executes fine manually but fails with the error below when executed from a job. I've checked permissions of the SQL Agent and ensured it has permission to execute sp_OACreate and sp_OAMethod procdures. Extremely grateful for any help or […]

Logic similar to EXIST to make all values the same if it meets a criteria - Hello everyone, I have a question. Please let me know if someone has any ideas. I need a logic with maybe EXIST(?) to check separately if for one Chain there is a field in REPLENTYPE with a value ‘SINGLE’ – I need all fields for this UPC at a specific Masterchainname to be ‘REPLENISHABLE’ for […]

Reporting Services

SSRS Header not Keeping with report - Hi all, I have an SSRS report issue is when printing to PDF. i have a subreport that works fine, but when there is ONLY enough space for the header, it will stay at the bottom then the rest of the subreport continues on the next page. here is a screenshot of the subreport, mainly […]

SQLServerCentral.com Website Issues

Weird redirect after reporting post - I just tried reporting this post: https://www.sqlservercentral.com/forums/topic/fuhrerschein-umtauschen-49-176-44678273-motorbike-fuhrerschein-2 After clicking the 'Report post' button, I was redirected to this post: https://www.sqlservercentral.com/forums/topic/null If I then return to the original spammy post, there is no indication that the report function worked.

SQL Server 2022 - Administration

SQL Server 2022 Std edition eval version - Hi We are planning to upgrade our SQL Server 2014 Ent edition to 2022 Standard and i want to test Basic Availability Groups future. Is there a free evaluation version for SQL Server 2022 Standard edition? At least i could not find one Thank You

SQL Server 2022 - Development

how to bulk insert a file on a azure sql server managed instance - Don't know if this is the right place for this topic. I have an internal azure sql server managed instance and am trying to read or write a file to the file system. As i understand, there is no file system on a sql server managed instance. Is there a way to add a file […]

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.