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

Daily Coping Tip

Get outside and notice 5 beautiful things

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.

Shared Security

When I worked in a large Operations team, we had various passwords for some systems that we stored in a password vault. This was on a network share that only the administrators had access to read. We used this for keeping a number of passwords for various services and systems that required sysadmins to access.

However, we tried not to use shared accounts whenever possible. We wanted to ensure an audit trail, for both compliance and understanding of what happened in our environments. All sysadmins had two accounts, which allowed them to access most services. We only had a user/password stored for certain systems that needed some sort of separate account, and we did change those passwords regularly.

Our customers didn't always do this. In fact, I regularly found different groups using a single account, always logged in, for which everyone knew the user name and credentials. They never seemed to consider this a problem, despite regular security warnings from administrators.

I was reminded of this story about a water treatment facility in Florida that was hacked. They had a shared account, used by many people, the same password for everyone, and a lack of a firewall. An intruder caused a release of chemicals, but no one was injured.

Firewalls prevent a lot of issues, if they are configured to limit access. In addition, ensuring that each user has different passwords is important. This allows you to turn off access for certain people, which includes former employees. That's basic security, and I'm surprised how often people forget about this, often because they worry about some system breaking.

The tools we have to enforce stronger security have improved over the years, but we need to take advantage of them, and we need to ensure that good, if not best, security practices are followed. It's easy to become lax over time, but remember that there are lots of threats out there, from hackers to malware, to former employees.

Far too many people are attacked, more than are in the news, so don't get complacent. We are all at risk.

Steve Jones - SSC Editor

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

 
 Featured Contents

Minimal Downtime Storage Migration of Large Databases

Dinesh Karunarathna from SQLServerCentral

This article shows how we were able to migrate a TB size database to new storage with minimal downtime.

DBA in training: SQL Server under the hood

Additional Articles from SimpleTalk

In this article of the series, Pamela Mooney explains the architecture of SQL Server under the hood, including some query anti-patterns to avoid.

From the SQL Server Central Blogs - Merging PDFs with Power Automate Desktop

Devin Knight from Devin Knight

In this video we start exploring some of the simple actions that are available within the Power Automate Desktop to create a solution that can

From the SQL Server Central Blogs - Passing JSON arrays between pipelines in Azure Data Factory

Rayis Imayev from Data Adventures

(2021-Feb-15) This post a continuation of my personal experience working with arrays (or simply JSON structures) in Azure Data Factory (ADF). It just happened that the more I work with...

 

 Question of the Day

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

 

The Default LAG

I've got a query that gives me the previous month's sales for a set of data:
SELECT 
       ms.saleyear
     , ms.salemonth
     , ms.currMonthSales
     , LAG (ms.currMonthSales) OVER (ORDER BY
                                         ms.saleyear 
                                       , ms.salemonth) AS prevsales
FROM   dbo.MonthSales AS ms
ORDER BY ms.saleyear DESC, ms.salemonth desc;
However, the value for the last (earliest) month is NULL. What is the easiest and cleanest way to change the query to make this a 0 (zero) for the last row?

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)

Changing the format for a date

I have today's date stored in a variable:

DECLARE @d DATETIME = '2021/02/22'

How can I get this in the format dd.mm.yyyy and returned to the client as a string?

Answer: select CONVERT(datetime, @d, 104)

Explanation: Convert will allow you to do this. The 104 format is dd.mm.yyyy. Ref: Cast and Convert - https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15

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
Extended events query plan - All, Firstly apologises as I'm sure I'm missing the obvious but I've searched and I can't work it out. I have setup extended events and included both the plan_handle and query_hash actions. I'm retrieving the results with the following: set dateformat dmy SELECT n.value('(action[@name="query_hash"]/value)[1]', 'nvarchar(128)') as query_hash, n.value('(action[@name="plan_handle"]/value)[1]', 'nvarchar(max)') as plan_handle from ( select cast(event_data […]
SQL Server 2017 - Development
Group By with SELECT MAX - Is there a tutorial for this?  And can someone help me with what is wrong? This woks fine if I remove the MAX and GROUP BY.  I don't understand what I need to group by. ERROR: Msg 8120, Level 16, State 1, Line 27 Column 'Documents.DocumentID' is invalid in the select list because it is […]
SQL Server 2016 - Administration
Migrate from AWS SQL Server RDS to EC2 - Hello, I could not find any documentation on how to migrate SQL server RDS to an EC2 instance. I have a requirement to migrate a large DB to ec2. Can anyone help me with the steps to migrate the DB with low downtime, please? Thanks in advance
Dynamic Data Masking and Adhoc queries. - This is the first time we are implementing this, Data Masking that became available in SQL Server 2016. I am not sure that this is a best Data Masking solution though. Because documentation says that a non-privileged user can run an ad-hoc query, using some functions like CAST, and data becomes visible. Is my understanding […]
SQL Server 2016 - Development and T-SQL
Returning single row multiple values - I am wanting to return only one row for continuous employment, what is the indcator is there WORKTYPE, RELATIONSHIPSTATUS and BEGINDATE. The bolded line is the BEGINDATE I want for Employee 100043, Employee 105640 has ENDDATE of 9999-12-31 which will be GETDATE,  The attach image is of the results as they should be returned. --===== […]
DelimitedSplit8K License Question - This may be a question specifically for Jeff Moden, but does anyone know what license governs the DelimitedSplit8K function? Tally OH! An Improved SQL 8K “CSV Splitter” Function I ask because I may want to adapt it to fit some code I'm writing, and I just want to make sure I stay within the lines. […]
Question related to architecture - Hello Fellow DBA friends, Currently I am working on a project where a system has to be migrated to our system. I just want to know whether I should go with creating a new DB schema for its objects in current database or should I create a new database altogether ? What are the specific […]
Administration - SQL Server 2014
Transaction Log Shipping Location Change - It has been a while since I have done anything with log shipping and wondered if someone can provide me with some assistance. So basically the old file server is being decommissioned where the Log shipping writes to.  We have a new file server and as such, I would like a simple way to modify […]
Who modified my Database? - Hi, we have an issue where entire cubes are sometimes deleted from our SSAS database.  I am assuming a human has done this, but I cant find any way of determining who did it or when it has happened.  Does anyone know where (if at all) Database schema modifications would be logged?  Thanks!
SQL Server 2019 - Administration
Subscription owner and multiple subscriptions - We are using reporting service 2019 Enterprise. There is an issue with running the subscription the other people created. For example two user are both content manager role. And the first user created the subscription, and she becomes the owner of the subscription automatically.  She runs the report with no problem. But when the second […]
SQL Server 2019 - Development
Custom Header Row in Query Result - Don't laugh too hard. For 36 months I've been sending a monthly file to a vendor that requires a custom header on Row 1 of the flat text file. I've been opening the text file in Notepad++ and manually setting the header. I want to level up and stop doing this. There are only three […]
Add column to existent table with default costraint value based on condition - Add column to existent table with default value based on condition Hello, i would to create a new column with default value, but i would know if its possible to speficy dfault value upon a value on another column, example i would write something like: ALTER TABLE [MyTable1] ADD NewRange bit NOT NULL default (IF […]
Vague job scheduler support for SQL Server on Linux - I’m having some trouble figuring out the kind of support for Linux that the SQL Server 2019 job scheduler has. It’s clear that the job scheduling procedure API, and SQL Server Agent have both been ported to Linux and appear to work. But there are a limited number of kinds of job steps supported. These “kinds” of […]
Powershell
double check if file exists in S3 bucket directory - Hello, I did google this, and not sure if I am doing it right or maybe just missing one minor thing, I am just trying to verify if the file exists in S3, if so, great, if not, then... I will figure something out for now I have the following code: $DBList = Invoke-SQLQuery […]
The Future of Community
We need to move beyond SQL Server... - I think we should have a discussion about topic. First, the future is more data. SQL Server is just one place it is put. We need reflect the needs of our customers and prospective clients. How do we get there? We do SQL Server now and add others annually. Postgres, MySQL, Oracle and Vertica have […]
 

 

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

 

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