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

Daily Coping Tip

Tell someone about why your favorite music means a lot to you.

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.

What does the Future DBA Do?

The database administrator (DBA) role has always been a strange one to me. Some companies embrace the role, some don't. Some hire DBAs, some expect others to handle those duties. The others could be sysadmins for other systems or developers, or some might even just assume someone is handling backups, index maintenance, and more. Often those latter companies will have issues at some point when they need to recover a system or performance is extremely poor.

As cloud computing has become more popular, there have been quite a few pieces about how the DBAs job is changing. Often the response to these items is one of two things. Either people agree and talk about a completely new way they will need to work and new skills they must acquire, or they are sure nothing will really change.

I think both things are true. I'm not sure that if you are a DBA now that your work in your current role will change that much. Sure, the company might add some new tasks, but often if your company hasn't embraced a lot of change, your job isn't changing.

I just wonder if this is your last job. I hope this is mine, but for many people, they can expect a new employer at some point. If that is likely, then are you positioned for your next job?

I ran across a piece on how the DBA job might change, and I think there are things to think about here. There are five items to look at, and all of them relate to the cloud's influence on DBAs. While I don't know that most, or even lots, of companies will adopt the Database-as-a-Service (DBaaS) or have their DBs be a utilty in the PaaS sense, I do think DBAs should strive to make the database a utility for both ops staff and developers. There is a surprising amount of effort here, and I do hope things like the Spawn project from Redgate change the way we work with databases in the future.

I do think that if you need to seek a new employer, and new opportunities, your general skills will matter. Being an amazing T-SQL guru or incredible AG admin might be highly valued in your current role, but often hiring managers might want to be sure you know something about a wide variety of things, from AI/ML to Power BI to working in the cloud. Your ability to learn, collaborate, and discuss intelligently different aspects of database work will be important.

If you think you might need another job, think about acquiring the skills for your next job today.

Steve Jones - SSC Editor

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

 
  Featured Contents

Postgresql JDBC Tutorial on Linux

Shivayan Mukherjee from SQLServerCentral

PosgtreSQL is a free and general purpose open source object-relational database system that uses and extends the SQL language. Though originally designed to run on UNIX platforms, Postgres is able to run on various platforms, such as macOS, Solaris, Windows, Unix, and Linux. PostgreSQL databases provide enterprise-class database solutions and are used by a wide […]

SQL Change Automation enables a new flexible approach to DevOps

Additional Articles from Redgate

In the latest version of SQL Change Automation, we enable you to be more flexible in your approach to Database DevOps and combine elements of both state- and migrations- based approaches. Learn more about the new workflow.

Making a Difference in Uncertain Times

Additional Articles from SimpleTalk

For some people, it’s not enough to take care of their own households and those of their families and neighbors. They’ve decided it’s important to make a difference in their communities and beyond.

From the SQL Server Central Blogs - Accessing Managed Instance via SSMS

James Serra from James Serra's Blog

It used to be that the only way to use SQL Server Management Studio (SSMS) against Azure SQL Database Managed Instance (SQLMI) was to create a VM on the...

From the SQL Server Central Blogs - How Does The CHOOSE Command Affect Performance?

Grant Fritchey from The Scary DBA

Questions absolutely drive my blog content and I really liked this one: how does the T-SQL CHOOSE command affect performance. On the face of it, I honestly don’t think...

 

  Question of the Day

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

 

The Rounding Calculation

What does this return? (guess, don't test)
DECLARE @i DECIMAL(10,4) = 3.23;
SELECT @i - ROUND(@i, 0) AS Guess_Dont_Test
 

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 is a measure?

What is an example of a  measure in DAX?

Answer: A calculated result from an expression formula

Explanation: A measure is an aggregation, calculated from an expression formula. These typically calculate values across many rows, not for a single row. Ref: Power BI Desktop Measures - https://docs.microsoft.com/en-us/power-bi/desktop-measures

Discuss this question and answer on the forums

 

Featured Script

Powershell Script - Azure cosmos DB(SQL API)

Dnirmania from SQLServerCentral

Scripts contains 4 following functions. Users can create 3 different script from this for inserting, fetching and deleting a document.
1. Function Generate-MasterKeyAuthorizationSignature - This function is used to create connection to Azure cosmos DB. This function codes were taken from Technet.
2. Function Post-CosmosDb - Insert single document in azure cosmos DB. Same function can be used to perform bulkinsert. This function code was taken from following article. To make it useful i made some modification in it.
(https://www.systemcenterautomation.com/2018/06/cosmos-db-rest-api-powershell/)
3. Function Get-CosmosDocument - Fetch single document from azure cosmos DB. Same function can be used to fetch all documents from Cosmos DB.
4. Function Delete-CosmosDbDocument - Delete single document from cosmos DB. Same function can be used to delete more than one document with some additional codes.

To use any of the function like Post,Get, Delete. you have to comment the other 2 function call. If you are using all 3 functions(Post,Get, Delete), Be cautious while using them.

# add necessary assembly
#
Add-Type -AssemblyName System.Web

# generate authorization key
Function Generate-MasterKeyAuthorizationSignature
{
[CmdletBinding()]
Param
(
[Parameter(Mandatory=$true)][String]$verb,
[Parameter(Mandatory=$true)][String]$resourceLink,
[Parameter(Mandatory=$true)][String]$resourceType,
[Parameter(Mandatory=$true)][String]$dateTime,
[Parameter(Mandatory=$true)][String]$key,
[Parameter(Mandatory=$true)][String]$keyType,
[Parameter(Mandatory=$true)][String]$tokenVersion
)

$hmacSha256 = New-Object System.Security.Cryptography.HMACSHA256
$hmacSha256.Key = [System.Convert]::FromBase64String($key)

$payLoad = "$($verb.ToLowerInvariant())`n$($resourceType.ToLowerInvariant())`n$resourceLink`n$($dateTime.ToLowerInvariant())`n`n"
$hashPayLoad = $hmacSha256.ComputeHash([System.Text.Encoding]::UTF8.GetBytes($payLoad))
$signature = [System.Convert]::ToBase64String($hashPayLoad);

[System.Web.HttpUtility]::UrlEncode("type=$keyType&ver=$tokenVersion&sig=$signature")
}

# Function to insert document in Azure Cosmos DB
Function Post-CosmosDb
{
[CmdletBinding()]
Param
(
[Parameter(Mandatory=$true)][String]$EndPoint,
[Parameter(Mandatory=$true)][String]$DataBaseId,
[Parameter(Mandatory=$true)][String]$CollectionId,
[Parameter(Mandatory=$true)][String]$MasterKey,
[Parameter(Mandatory=$true)][String]$JSON
)

$Verb = "POST"
$ResourceType = "docs";
$ResourceLink = "dbs/$DatabaseId/colls/$CollectionId"
$partitionkey = "[""$(($JSON |ConvertFrom-Json)."Partition Key Name""")""]"

$dateTime = [DateTime]::UtcNow.ToString("r")
$authHeader = Generate-MasterKeyAuthorizationSignature -verb $Verb -resourceLink $ResourceLink -resourceType $ResourceType -key $MasterKey -keyType "master" -tokenVersion "1.0" -dateTime $dateTime
$header = @{authorization=$authHeader;"x-ms-version"="2017-02-22";"x-ms-documentdb-partitionkey"=$partitionkey;"x-ms-date"=$dateTime}
$contentType= "application/json"
$queryUri = "$EndPoint$ResourceLink/docs"

#[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
Invoke-RestMethod -Method $Verb -ContentType $contentType -Uri $queryUri -Headers $header -Body $JSON
#return $result.statuscode

}

# fill the target cosmos database endpoint uri, database id, collection id and masterkey
$CosmosDBEndPoint = "https://XXXXXXX.documents.azure.com:443/"
$DatabaseId = "Database NameID"
$CollectionId = "Collection Name ID"
$MasterKey = "Read write key value"

$document = @" # Values to be inserted in JSON format
{
`"id`": `"$([Guid]::NewGuid().ToString())`",
`"Name`": `"Dinesh Kumar`",
`"Department`": `"IT`"
}
"@

# execute
Post-CosmosDb -EndPoint $CosmosDBEndPoint -DataBaseId $DataBaseId -CollectionId $CollectionId -MasterKey $MasterKey -JSON $document -Verbose -Debug

# Function to fetch the data from single document in JSON Format
Function Get-CosmosDocument
{
[CmdletBinding()]
Param
(
[Parameter(Mandatory=$true)][String]$EndPoint,
[Parameter(Mandatory=$true)][String]$DataBaseId,
[Parameter(Mandatory=$true)][String]$CollectionId,
[Parameter(Mandatory=$true)][String]$MasterKey

)
$Verb = "GET"
$ResourceType = "docs";
$DocumentID = "Document id"
$ResourceLink = "dbs/$DatabaseId/colls/$CollectionId/docs/$DocumentID"

$KeyValue = "Key Value"
$partitionkey = "[""$($KeyValue)""]"
#$partitionkey

$dateTime = [DateTime]::UtcNow.ToString("r")
$authHeader = Generate-MasterKeyAuthorizationSignature -verb $Verb -resourceLink $ResourceLink -resourceType $ResourceType -key $MasterKey -keyType "master" -tokenVersion "1.0" -dateTime $dateTime
$header = @{authorization=$authHeader;"x-ms-version"="2017-02-22";"x-ms-documentdb-partitionkey"=$partitionkey;"x-ms-date"=$dateTime}
$contentType= "application/json"
$queryUri = "$EndPoint$ResourceLink"

Invoke-RestMethod -Method $Verb -Uri $queryUri -Headers $header

}

# fill the target cosmos database endpoint uri, database id, collection id and masterkey
$CosmosDBEndPoint = "https://XXXXXX.documents.azure.com:443/"
$DatabaseId = "Database NameID"
$CollectionId = "Collection name ID"
$MasterKey = "Read Only key value"

# execute
Get-CosmosDocument -EndPoint $CosmosDBEndPoint -DataBaseId $DataBaseId -CollectionId $CollectionId -MasterKey $MasterKey -Verbose

# Function to delete document from Cosmos DB
Function Delete-CosmosDbDocument
{
[CmdletBinding()]
Param
(
[Parameter(Mandatory=$true)][String]$EndPoint,
[Parameter(Mandatory=$true)][String]$DataBaseId,
[Parameter(Mandatory=$true)][String]$CollectionId,
[Parameter(Mandatory=$true)][String]$MasterKey
#[Parameter(Mandatory=$true)][String]$JSON
)

$Verb = "DELETE"
$ResourceType = "docs";
$DocumentID = "Document id whcih needs to be deleted"
$ResourceLink = "dbs/$DatabaseId/colls/$CollectionId/docs/$DocumentID"
$KeyValue = "Partition Key value"
$partitionkey = "[""$($KeyValue)""]"

$dateTime = [DateTime]::UtcNow.ToString("r")
$authHeader = Generate-MasterKeyAuthorizationSignature -verb $Verb -resourceLink $ResourceLink -resourceType $ResourceType -key $MasterKey -keyType "master" -tokenVersion "1.0" -dateTime $dateTime
$header = @{authorization=$authHeader;"x-ms-version"="2015-12-16";"x-ms-documentdb-partitionkey"=$partitionkey;"x-ms-date"=$dateTime}
#$contentType= "application/json"
$queryUri = "$EndPoint$ResourceLink"

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

Invoke-RestMethod -Method $Verb -Uri $queryUri -Headers $header -Verbose
#return $result.statuscode

}

# fill the target cosmos database endpoint uri, database id, collection id and masterkey
$CosmosDBEndPoint = "https://XXXXXXXX.documents.azure.com:443/" #URI
$DatabaseId = "Database Nameid"
$CollectionId = "Collection Name ID"
$MasterKey = "Read Write Key value"

# execute
Delete-CosmosDbDocument -EndPoint $CosmosDBEndPoint -DataBaseId $DataBaseId -CollectionId $CollectionId -MasterKey $MasterKey -Verbose

More »

 

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
Odd Connection / Authentication Errors - Hello, lately we've been experiencing some odd Connection and/or Authentication errors while connecting to our SQL instances.  These issues have been very intermittent and hard to troubleshoot as they are not easy to reproduce.  They have also occurred on a variety of SQL versions from 2014-2017 Symptoms: Occasionally Agent jobs, fail logging in to a […]
SQL Server 2017 - Development
a good book on stored procedures - Could someone recommend a good book on SQL programming on Stored procedures, functions, triggers? I know there a lot of SQL queries books there, but would like particular how to write stored procedures, tricks, examples, logics? more intermediate level or advanced level? Thanks
SQL Server 2016 - Administration
Automating restore of a database that is part of an availability group - Hello experts, I've been tasked with automatically refreshing a dev database from a production backup each day. This task is fairly straightforward for a standalone server, but I am running into some complications when trying to set up the same for a database that has been added to an availability group. Also, in full disclosure, […]
log file size issue - I have a DBs where the log file is 90GB now. It grew to be 90 because the log backup job was paused for couple of hours. So I ran the log backup (which ran successfully and took about 20+ minutes) and then I tried shrinking the log file size to 20480 (because that's what […]
SQL login without a user (after a restore) - Hi Got this weird issue after a server was brought to live due a VM issue. A domain user, let's call it CONTOSO\joe  is not able to do anything on the database "ABC". After checking the database, I see no user. I attempted to create the database user and got an error saying the user […]
Duplicate email being sent from Report Server - Hello! We migrated from SSRS 2012 to PowerBI Report server as well database from SQL 2012 to 2016. SSRS 2012 just sends out one email even though it has invalid email address but Power BI Report server is sending duplicate emails. Max -retries for both server is set to 3 times for every 15 mins. […]
SQL Server 2016 - Development and T-SQL
Data Warehousing Tutorials - I'm looking to learn more about designing and implementing a data warehouse and I've been able to find some really good books and training videos that cover the concepts. However, I'm also looking for demos or tutorials that you can follow along with in order to get hands on experience. Does anyone know of any […]
join - how to get only the first row from the left table - Hello, I started learning T-SQL 4 years ago and this is the first time I come here so...nice to meet to you ! Here is a simple query excecuted in AdventureWorks2017 : SELECT SOH.[SalesOrderID], SOH.[DueDate], SOH.[SalesOrderID] , SOD.[OrderQty] , SOD.[UnitPrice] FROM [Sales].[SalesOrderHeader] SOH LEFT JOIN [Sales].[SalesOrderDetail] SOD ON SOH.[SalesOrderID] = SOD.[SalesOrderID] Result : And […]
Administration - SQL Server 2014
Restore from Prod to Dev - I am looking a way to restore production data to our dev and QA environments for user database.  Instead of manual restores, I would automate using a sql Agent Job so that developers can run on demand. So grant permission to that particular agent job to run manually. The database should not lose user accounts […]
Development - SQL Server 2014
FTI Query over Joined tables is slow - Hi I have a table with a full text index. I have a query which uses CONTAINS to search the table. This works well. I now need to expand the query to also search in a parent table. The two tables are join with a primary/foreign key. The parent table has a full text index […]
SQL Server 2012 - T-SQL
Convert from nvarchar to numeric only if valid number-VAL command like MS Access - Hi Folks, I have been struggling with this and tried several different methods with no success.  All I am trying to do is successfully create a view in which fields that are nvarchar or varchar can be converted to their appropriate field type.  No matter what I do, be it using the isnumeric command or […]
SQL Server 2019 - Development
Date filtering using DATEDIFF - I am attempting to create an episode marker when duration between dates is >= 90 days, filtered by EMID.  Below is the query I ran, with actual vs expected output in the attached file.   There are 2 issues: 1- the DATEDIFF function is supposed to restart with each new EMID (which is the point of […]
correct placement of comment block - Many examples on the web, eg. sqlauthority show comment block before the CREATE AS https://www.mssqltips.com/sqlservertutorial/167/using-comments-in-a-sql-server-stored-procedure/ Is this correct, or is there an argument for placing it after AS? Why? notes: by comment block I mean this thing that we put at the top of a sproc to describe it and track modifications /* -this procedure […]
SQL Azure - Administration
What's different about configuring a Windows cluster in Azure - Trying to setup a test SQL failover instance on Azure. Already setup a DC. Is there a especial step to configure or present the "shared" disks for the cluster?  All the documentation I can find online skips or does not detail the actual steps for the cluster setup, only last steps for SQL. Any help […]
COVID-19 Pandemic
Daily Coping 4 May 2020 - Today’s tip is to send friends a photo of a time you all enjoyed together. My thoughts (and pictures): http://voiceofthedba.com/2020/05/04/daily-coping-4-may-2020/
 

 

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

 

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