PowerShell Script | Backup Azure SQL MI DB to Blob

#Install-Module dbatools
#Install-Module -Name Az -AllowClobber -Scope AllUsers


param(
[string]$server = ".", 
[string] $Database ,
[string] $subscription,
[string] $ResourceGroupName,
[string] $StorageAccount,
[string] $container,
[string] $BackupfileName = "1"
)
#Get-AzSubscription | Format-Table
#Set-AzContext "Rizwan_Internal_1000"
#$subscription = 'Rizwan_Internal_1000'
Select-AzSubscription -Subscription $subscription
Get-AzContext
#$server = "tcp:sql1243.public.b955f902d673.database.windows.net,3342"
#$Database = "Pubs"
$server_Login = (Get-Credential)

#Setting up backup file name
if ($BackupfileName -ne "1")
{
    $BackupfileName = $BackupfileName
}
else {
    $dt = Get-Date -Format yyyyMMdd_HHmmss
    $BackupfileName = $Database + "_$dt" + ".bak"
}

$BlobStorageURL = "https://$StorageAccount.blob.core.windows.net/$container"
Write-Host $BlobStorageURL, $Server, $Database, $subscription

$StorageAccount = Get-AzStorageAccount -ResourceGroupName $ResourceGroupName -Name $StorageAccount
$ctx = $storageAccount.Context


#checking if credential already exist or need be created
$SASCredential = Get-DbaCredential -SqlInstance $server -SqlCredential $server_Login 
$SQLMI = Connect-DbaInstance -SqlInstance $server -SqlCredential $server_Login

$sas = (New-AzStorageAccountSASToken -Service Blob -ResourceType Object -Permission "rwdl" -Context $ctx).TrimStart('?')
$serverCred = New-DbaCredential -SqlInstance $SQLMI `
                       -Name $BlobStorageURL `
                       -Identity "SHARED ACCESS SIGNATURE" `
                       -SecurePassword (ConvertTo-SecureString $sas -AsPlainText -Force) `
                       -Force
write-host "Credential recreated"
<#
if ($SASCredential.Name -eq $BlobStorageURL)
{
     
    write-host "Credential already exists, skipping this step"
}
Else {
    $sas = (New-AzStorageAccountSASToken -Service Blob -ResourceType Object -Permission "rwdl" -Context $ctx).TrimStart('?')
    $serverCred = New-DbaCredential -SqlInstance $SQLMI `
                           -Name $BlobStorageURL `
                           -Identity "SHARED ACCESS SIGNATURE" `
                           -SecurePassword (ConvertTo-SecureString $sas -AsPlainText -Force) `
                           -Force
                           
    write-host "Credential does NOT and it has been created"
}
#>
Backup-DbaDatabase -SqlInstance $SQLMI -Database $Database `
    -AzureBaseUrl $BlobStorageURL `
    -BackupFileName $BackupfileName `
    -Type Full -Checksum -CopyOnly -CompressBackup -Verbose
x

Powershell Script to Refresh Azure SQL DB from one server to another within same subscription

This script will allow you to refresh Azure SQL DB from one server to another within the same subscription. It renames target server before restoring and also matches service level of target database.

<#
Azure PowerShell Module Installation is required
if (Get-Module -Name AzureRM -ListAvailable) {
    Write-Warning -Message 'Az module not installed. Having both the AzureRM and Az modules installed at the same time is not supported.'
} else {
    Install-Module -Name Az -AllowClobber -Scope CurrentUser
}
https://docs.microsoft.com/en-us/powershell/azure/install-az-ps?view=azps-4.1.0
#>

#Connect to Azure if not already connected
Connect-AzAccount
#Get information which subscriptin you are connected in
Get-AzContext
#Set subscription in which source and target SQL are located
Get-AzSubscription
Set-AzContext '<Set Subscription>'
#Variables
$S_ResourceGroup = '<Source Resource Group>'
$S_Server = '<Source Server>'
$Database = '<DB to be refreshed>'


$T_ResourceGroup = '<Target Resource Group>'
$T_Server = '<Target Server>'
$T_OriginalName = '<Target DB Original Name>'
$dt = Get-Date -Format yyyyMMdd
$T_DBOriginalRename = $T_OriginalName +'_' +$dt





#Get Target DB Service Level
$T_DB = Get-AzSqlDatabase -ResourceGroupName $T_ResourceGroup -ServerName $T_Server -DatabaseName $T_OriginalName
#Rename TargetDatabse to be refreshed
Set-AzSqlDatabase -ResourceGroupName $T_ResourceGroup -ServerName $T_Server -DatabaseName $T_OriginalName -NewName $T_DBOriginalRename
#Start copy of the sourceDB to TargetDB
New-AzSqlDatabaseCopy -ResourceGroupName $S_ResourceGroup -ServerName $S_Server -DatabaseName $Database -CopyResourceGroupName $T_ResourceGroup -CopyServerName $T_Server -CopyDatabaseName $Database
Get-AzSqlDatabase -ResourceGroupName $T_ResourceGroup -ServerName $T_Server -DatabaseName $Database
Start-Sleep -Seconds 10
#Set Restored DB Service Tier
Set-AzSqlDatabase -ResourceGroupName $T_ResourceGroup -ServerName $T_Server -DatabaseName $Database -Edition $T_DB.Edition -RequestedServiceObjectiveName $T_DB.CurrentServiceObjectiveName
<#
REsources
https://docs.microsoft.com/en-us/powershell/module/az.sql/set-azsqldatabase?view=azps-4.1.0
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-copy?tabs=azure-powershell
https://docs.microsoft.com/en-us/azure/sql-database/scripts/sql-database-copy-database-to-new-server-powershell
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-single-databases-manage#powershell-manage-sql-database-servers-and-single-databases
Get-AzSqlServerServiceObjective -Location 'EastUS2' | Out-GridView
#>

SQL 2012 Memory and Sizing Considerations when setting up Max Memory

SQL Memory and Sizing Considerations

I am getting info about this article from https://support.microsoft.com/en-us/kb/2663912

I had recently interviewed for Microsoft SQL PFE position and failed miserably when they asked me about Max Memory (that is why I am not working there yet)

1. What does it do?
2. When you configure it, does SQL Engine utilize all of it

After answering the questions, the technical interviewers were nice enough to explain why I was wrong. I decided to learn more on it and found the article in the above link. 

One thing to remember before we get started that MAX MEMORY behavior was changed starting SQL 2012 and beyond. All versions including SQL 2008 R2 and prior handled memory differently.

Summary:

MS SQL Engine manages automatic and dynamic memory requirements based on current memory needs of the internal SQL components and workload. There are options to fine tune specific behaviors to meet needs of SQL. One the option we are discussing is MIN MEMORY and MAX MEMORY.

Info:

With SQL 2012, new changes were introduced to include specific types of memory allocations. Let me explain some of these terms

Types of Memory Allocations:

1. Single-page Allocations: Memory allocations requests less than or equal to 8K are referred to Single-page Allocations used by SQL Server processes. 
2. Multi-page Allocations: These are the allocations that request more than 8K pages. What could cause them, one of the examples I found was an example of workload that has stored procedures with large number of parameters and then invoked via large ad-hoc batches. You can read more about it here
3. CLR allocations: These allocations include the SQL CLR heaps and its global allocations that are created during CLR initialization.
4. Thread Stacks Memory: Memory used for thread stacks within SQL Server Process (Max worker thread * thread stack size). Thread stack size is 512K in 32 bit SQL Server, 904K in WOW mode, and 2 MB in 64 Bit.  
5. Direct Allocations from Windows: Direct windows allocations made by Non-SQL Server DLL’s ([These include windows heap usage and direct virtual allocations made by modules loaded into SQL Server process. Examples: allocations from extended stored procedure DLL’s, objects created using OLE Automation procedures (sp_OA calls), allocations from linked server providers loaded in SQL Server process)

SQL 2005, SQL 2008, SQL 2008 R2:

Type of memory allocation	Supported
Single-page allocations	YES
Multi-page allocations	NO
CLR allocations	NO
Thread stacks memory	NO
Direct allocations from Windows	NO

SQL 2012, SQL 2014, SQL 2016:

Starting with SQL 2012, Single-page and Multi-page memory allocation are included in Any Size Page Allocator.  You can read more about it here

Type of memory allocation	Supported
Single-page allocations	Yes - As part of Any size Page Allocator
Multi-page allocations	Yes - As part of Any size Page Allocator
CLR allocations	YES
Thread stacks memory	NO
Direct allocations from Windows	NO

Hopefully this will help you understand Max and Min memory for SQL server. If you like to learn more in detail, I have included links within this article.

Thanks!

How to convert INT to Date and Date to INT - SQL

How to convert INT to Date and Date to INT

 I recently switched from DBA to Database Analyst working on Data Warehouse on SQL 2012.

One of the few challenges I run into is to convert date to INT for matchup in SSIS packages. I am writing this blog so I can reference it whenever I am in doubt.

Just remember that INT and DATE Functions cannot be converted directly. They need to converted o VARCHAR or CHAR.

Here is the info for reference

https://msdn.microsoft.com/en-us/library/ms187928.aspx



Converting DATE to INT

Declare @DateConvert DATE = '12/10/2104'

SELECT CAST(CONVERT(VARCHAR(8), @DateConvert, 112) AS INT) 

Converting INT to DATE

Declare @INTConvert INT = 20141012

SELECT CAST(CONVERT(VARCHAR(8), @INTConvert, 112) AS DATE) 

In the statements above

• VARCHAR or CHAR can be used. I like using varchar but that my personal preference
• 112 - That is the data type format for ISO Standard. If you prefer to use US or other format, reference the Microsoft article and it should help you.

Hopefully this will be helpful.

Have a great day

SQL DB stuck in Single User Mode

I recently ran into an issue with SQL 2012 database. One of the automated process changed database into a single user mode and then failed. Luckily this database was only a QA database so bringing it backup was not a priority. I tried to bring it back by

   1:  ALTER DATABASE [DB_Name] SET MULTI_USER;

   2:  GO

I would get an error

Msg 1205, Level 13, State 68, Server XXXXXXXXXXX, Line 1
Transaction (Process ID XX) was deadlocked on lock resources with another process
and has been chosen as the deadlock victim. Rerun the transaction.
Msg 5069, Level 16, State 1, Server XXXXXXXXXXX, Line 1
ALTER DATABASE statement failed.
I ran sp_whoisactive with @show_sleeping_spids = 2 but couldn’t see which SPID was connected to the database. Then researched on the internet and found that I can trying to use sp_who2 and find the spids. There were too many results to go through. I found this neat script

use master
GO

select 
    d.name, 
    d.dbid, 
    spid, 
    login_time, 
    nt_domain, 
    nt_username, 
    loginame
from sysprocesses p 
    inner join sysdatabases d 
        on p.dbid = d.dbid
where d.name = '[DB_Name]'
GO

This scripts picks up all the sessions connected to the database. I tried to kill one of the process and got

Msg 6107, Level 14, State 1, Line 1
Only user processes can be killed.

So no solution till I ran across this article by Pearl Knows on SQL Server Central http://goo.gl/z7EXUT. Great article.

I was able to use the article to get out of this jam.

I used High Deadlock Priority to get connection.

USE [master]
SET DEADLOCK_PRIORITY HIGH
ALTER DATABASE [DB_Name] SET MULTI_USER WITH NO_WAIT
ALTER DATABASE [DB_Name] SET MULTI_USER WITH ROLLBACK IMMEDIATE

Hopefully this will help you get out of a jam.

Thanks!

SSAS Port setup and fixing issues

 

Recently installed SSAS 2008 R2 on a server. I couldn’t connect to it from SSMS via a separate computer. Tried to figure but couldn’t resolve it. Then I realized that I haven’t set the port and maybe that might be the issues.

I launched SSMS on the box where SSAS is installed.

• Connected to it
• Right click and select properties
• Check Show Advanced (All) Properties in the bottom
• Scroll down to PORT and change the Value.
• Click OK

 

 

 

But in IT nothing is that simple. I was supposed to enter 2393 but ended up entering 2382. Don’t ask why it happened but it happened. So right after you change the port, it needs to be restarted

I restarted the service and it would not come online. I looked in the event log and found this error

-
System-

Provider

[ Name]
MSOLAP$P1

-
EventID
0

[ Qualifiers]
49441

Level
2

Task
289

Keywords
0x80000000000000

-
TimeCreated

[ SystemTime]
2014-02-13T19:25:41.000000000Z

EventRecordID
41775

Channel
Application

Security

-
EventData

The following system error occurred: Only one usage of each socket address (protocol/network address/port) is normally permitted. The following system error occurred: Only one usage of each socket address (protocol/network address/port) is normally permitted.

 

 

So basically 2382 is port where SQL listens for connection request so that is why the service was not starting. I looked online and after much research, I found that file “msmdsrv.ini” file. If open the file, change the value to 0 (dynamic) or the port you want to assign which in this case was 2393. Once I changed the file saved it, the server restarted.

 

I hope this is helpful

Finding SQL Primary and Secondary Replica in SQL 2012 AG Groups

 

Whenever I try to find out which is primary or secondary in SQL 2012 AG groups, I find it out through SSMS by connecting to AG Group and then opening Availability Groups. This is a tedious process sometimes and I wanted to find much better process. I posted a question on Twitter and got great info from Allan Hirt @SQLHA. He suggested two links which give great information on using DMV. Here are the links

• http://t.co/pmRal95p4l
• http://t.co/Nu8LF3xAf1

Here is another script compiled by one of my co-worker (Tim) which displays the following

• Availability Group Listener
• Availability Group
• State
• IP Address
• Databases within AG groups
• Replicas
• Primary Replica

Hope this helps

   1:  --

   2:  -- This script provides a 1 line view by AG indicating the basic AG summary information 

   3:  --

   4:  SELECT AvailabilityGroupListener, AvailabilityGroup, state_desc, ip_address, Databases, Replicas, primary_replica

   5:  FROM  (

   6:  SELECT   distinct qo.*,qi.*

   7:  FROM    (select distinct ag.name as [AvailabilityGroup], ag.group_id,agl.dns_name as [AvailabilityGroupListener],agl.port,aglip.ip_address,aglip.state_desc, ags.primary_replica

   8:  from sys.availability_group_listeners agl join sys.availability_group_listener_ip_addresses aglip on agl.listener_id=aglip.listener_id

   9:  join sys.availability_groups ag on ag.group_id=agl.group_id join sys.availability_databases_cluster adc on adc.group_id=ag.group_id

  10:  join sys.dm_hadr_availability_group_states ags on ag.group_id=ags.group_id ) qo

  11:  CROSS APPLY

  12:          (

  13:          SELECT  CASE ROW_NUMBER() OVER(ORDER BY database_name) WHEN 1 THEN '' ELSE ', ' END + qi.database_name

  14:          FROM    (select ag.name as [AvailabilityGroup],ag.group_id,agl.dns_name as [AvailabilityGroupListener],agl.port,aglip.ip_address,aglip.state_desc ,adc.database_name from sys.availability_group_listeners agl join sys.availability_group_listener_ip_addresses aglip on agl.listener_id=aglip.listener_id

  15:  join sys.availability_groups ag on ag.group_id=agl.group_id join sys.availability_databases_cluster adc on adc.group_id=ag.group_id) qi

  16:          WHERE   qi.AvailabilityGroup = qo.AvailabilityGroup

  17:          ORDER BY database_name  FOR XML PATH ('')

  18:          ) qi(Databases) join sys.availability_replicas ar on ar.group_id=qo.group_id

  19:                

  20:  ) Fo

  21:  CROSS APPLY

  22:          (

  23:          SELECT  CASE ROW_NUMBER() OVER(ORDER BY replica_server_name) WHEN 1 THEN '' ELSE ', ' END + Fi.replica_server_name

  24:          FROM    (

  25:  SELECT   qo.*,qi.*,ar.replica_server_name

  26:  FROM    (select distinct ag.name as [AvailabilityGroup], ag.group_id,agl.dns_name as [AvailabilityGroupListener],agl.port,aglip.ip_address,aglip.state_desc from sys.availability_group_listeners agl join sys.availability_group_listener_ip_addresses aglip on agl.listener_id=aglip.listener_id

  27:  join sys.availability_groups ag on ag.group_id=agl.group_id join sys.availability_databases_cluster adc on adc.group_id=ag.group_id) qo

  28:  CROSS APPLY

  29:          (

  30:          SELECT  CASE ROW_NUMBER() OVER(ORDER BY database_name) WHEN 1 THEN '' ELSE ', ' END + qi.database_name

  31:          FROM    (select ag.name as [AvailabilityGroup],ag.group_id,agl.dns_name as [AvailabilityGroupListener],agl.port,aglip.ip_address,aglip.state_desc ,adc.database_name from sys.availability_group_listeners agl join sys.availability_group_listener_ip_addresses aglip on agl.listener_id=aglip.listener_id

  32:  join sys.availability_groups ag on ag.group_id=agl.group_id join sys.availability_databases_cluster adc on adc.group_id=ag.group_id

  33:         ) qi

  34:          WHERE   qi.AvailabilityGroup = qo.AvailabilityGroup

  35:          ORDER BY database_name  FOR XML PATH ('')

  36:          ) qi(Databases) join sys.availability_replicas ar on ar.group_id=qo.group_id

  37:  ) Fi

  38:  WHERE   Fi.AvailabilityGroup = FO.AvailabilityGroup

  39:  ORDER BY replica_server_name  FOR XML PATH ('')

  40:  ) Fi(Replicas)