Azure / SQL Server / Azure SQL Database Disaster Recovery / Orphaned Users / Different SIDs

Posted by in Powershell, SQL Server, SQL Tips and Tricks on Dec 5th, 2018 | 0 comments
Azure / SQL Server / Azure SQL Database Disaster Recovery / Orphaned Users / Different SIDs
If you are using Azure SQL Database with geo-replication or auto failover group and if you are using logins and users rather than contained users you can end up with the following error when you try to connect to the secondary/failover database after failover occured.   If you already created a login on the failover server using SQL command -- ====================================================================================== -- Create SQL Login template for Azure SQL Database and Azure SQL Data Warehouse Database --...
read more

Azure / SQL Server / Rename an Azure SQL database

Posted by in Azure, SQL Tips and Tricks on Nov 16th, 2018 | 0 comments
Azure / SQL Server / Rename an Azure SQL database
We had one customer who was moving his Azure SQL Database from Elastic Pool to Standalone Database. At the same time, he wanted to change database name as well. Azure database was part of an existing failover group (two elastic pools) and geo-replicated to the new future Azure SQL Server. Changing the database name is not allowed if the database is part of the replication   If the database is not part of replication we can use two different approaches to rename it, using: SQL Server Management Studio or Transact-SQL Note: This refers...
read more

Azure / SQL Server / This location is not available for subscription

Posted by in Azure, Powershell, SQL Tips and Tricks on Oct 22nd, 2018 | 5 comments
Azure / SQL Server / This location is not available for subscription
During deployment of Azure SQL Server in Canada East location (new Azure Subscription) I got the following error: Using Azure Portal   Using Powershell   At the same time if I list all available azure location for the current subscription I can see that Canada East is there Get-AzureRmLocation | select displayname   The reason is that Canada East region is not activated for my new Azure Subscription. To resolve this issue just contact the Azure support to have this enabled for you. You can do this for free using support page on...
read more

SQL Server / ALTER INDEX / CREATE INDEX Progress

Posted by in SQL Monitoring, SQL Server, SQL Tips and Tricks on Oct 12th, 2018 | 1 comment
SQL Server / ALTER INDEX / CREATE INDEX Progress
Creating a new and altering existing indexes on the big tables can be time-consuming. There is a simple way to see the progress of the CREATE / ALTER INDEX command. Since SQL Server 2014 we can use sys.dm_exec_query_profiles DMV to monitor real-time query progress. sys.dm_exec_query_profiles (SQL SERVER 2014 and above) “Monitors real-time query progress while the query is in execution. For example, use this DMV to determine which part of the query is running slow. Join this DMV with other system DMVs using the columns identified in the...
read more

PowerShell / SQL Server / ConnectionContext.StatementTimeout

Posted by in Powershell, SQL Server on Sep 30th, 2018 | 0 comments
PowerShell / SQL Server / ConnectionContext.StatementTimeout
Recently I had an issue with a database backup PowerShell script one of our customer using. The script was working fine until trying to do a backup of the large database. During backup attempts script starts failing with the following error: Exception calling "SqlBackup" with "1" argument(s): "Backup failed for Server 'ServerName'. " At line:67 char:1 + $dbBackup.SqlBackup($s) + ~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : FailedOperationException Exception calling...
read more

Microsoft SQL Server 2019 Updates / BETA (15.0.1000.34) / RTM (15.0.2000.5)

Posted by in Updates on Sep 25th, 2018 | 0 comments
Microsoft SQL Server 2019 Updates / BETA (15.0.1000.34) / RTM (15.0.2000.5)
SQL SERVER 2019 Update Version: MSSQL 2019 RTM CU-16 SECURITY UPDATE, Build: 15.0.4236.7, KB: KB5014353, Release Date: June 2022, Download: https://support.microsoft.com/en-us/topic/kb5014353-description-of-the-security-update-for-sql-server-2019-cu16-june-14-2022-f0afe659-bd19-4c87-a417-a4c67a47e644; Update Version: MSSQL 2019 RTM CU-16, Build: 15.0.4223.1, KB: KB5011644, Release Date: April 2022, Download: https://support.microsoft.com/en-us/topic/kb5011644-cumulative-update-16-for-sql-server-2019-74377be1-4340-4445-93a7-ff843d346896;...
read more

PowerShell / SQL Server Backup / Cannot find type Microsoft.SqlServer.Management.Smo.Backup

Posted by in SQL Server on Aug 14th, 2018 | 0 comments
PowerShell / SQL Server Backup / Cannot find type Microsoft.SqlServer.Management.Smo.Backup
In one of my previous posts, I show you how to use PowerShell to manage your SQL Server database backups and how to install the SQL Server PowerShell module. To be able to manage SQL Server Database Backups you will need to load Microsoft.SqlServer.SmoExtended assembly. If not you can end up with the following error. PS>TerminatingError(New-Object): “Cannot find type [Microsoft.SqlServer.Management.Smo.Backup]: verify that the assembly containing this type is loaded.” new-object : Cannot find type...
read more

PowerShell / SQL Server / Cannot find type Microsoft.SqlServer.Management.Smo.Server

Posted by in Powershell, SQL Server on Aug 8th, 2018 | 7 comments
PowerShell / SQL Server / Cannot find type Microsoft.SqlServer.Management.Smo.Server
Some users complained that they cannot use my PowerShell script published on TechNet https://gallery.technet.microsoft.com/Use-PowerShell-to-Backup-3bb0a397 It is a PowerShell script to Backup all user databases to Azure Storage. They experienced the following error: 8/14/2018 11:18 AM – Setting Folders PS>TerminatingError(New-Object): “Cannot find type [Microsoft.SqlServer.Management.Smo.Server]: verify that the assembly containing this type is loaded.” new-object : Cannot find type...
read more

SQL Server / Missing Indexes Query / DMV / Email Alert

Posted by in SQL Server on Jul 27th, 2018 | 2 comments
SQL Server / Missing Indexes Query / DMV / Email Alert
This is an example of missing index query (DMV) of the database within email alert, we already had examples how you can use sp_send_dbmail stored procedure from msdb database to send backup size email daily,  long-running queries report and how to send missing indexes (Cached Plans) report via email. This is just another use case of sp_send_dbmail stored procedure. I used following arguments to achieve this @profile_name (name of an existing Database Mail profile, type sysname, default NULL), If you are not sure about your profile account use...
read more

Powershell / Azure Automation / Delete older Containers from Azure Storage Account

Posted by in Azure, Powershell on Jul 4th, 2018 | 0 comments
Powershell / Azure Automation / Delete older Containers from Azure Storage Account
This will be a simple example of how you can delete older containers in the Azure storage account. I will use Azure automation to scan storage containers and delete those matching the certain date pattern. First, you need to choose an existing one or create new automation account to run your Runbooks   You should put the script within your Runbooks under the process automation. You will have some useful tutorial Runbooks as well.   We will use connection name with information needed to authenticate with...
read more

« Previous Entries