Currently Browsing: Azure

Azure / PowerShell / Deploy Azure SQL database DR environment (PaaS) using PowerShell

Posted by in Azure, Powershell on Jan 21st, 2019 | 0 comments
Azure / PowerShell / Deploy Azure SQL database DR environment (PaaS) using PowerShell
Script Download: The script with usage example is available for download from https://gallery.technet.microsoft.com/Deploy-Azure-SQL-DR-47dfa7f7 Summary: Use PowerShell to create cloud DR environment using Azure SQL Databases (PaaS) and send Email Report with the connection string details   Description: Use PowerShell to deploy cloud service with Azure SQL Database (PaaS) with good resilience to outages (GEO Replications, Failover Groups) PowerShell: Connect to Azure Account, Use existing or create new Resource Group, Create primary Azure Logical SQL Server, Set an admin login and password for...
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 just to users databases   USING SQL Server Management...
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 your Azure Portal.   Open a new support request  ...
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 Azure so that we can manage Azure classic resources from...
read more

Powershell / Use PowerShell to Backup all user databases to Azure Storage

Posted by in Azure, Powershell, SQL Server on Nov 17th, 2017 | 0 comments
Powershell / Use PowerShell to Backup all user databases to Azure Storage
Script Download: The script with usage example is available for download from https://gallery.technet.microsoft.com/Use-PowerShell-to-Backup-3bb0a397 Summary: Backup all user database from specified SQL Server instance to the buffer location, and then copy from the buffer location to the azure storage, and create log file with results. Description: This PowerShell script can be invoked remotely from another PC trough the command line, with PowerShell or executed remotely through task scheduler adding parameters – instance name, backup destination path, excluded databases list, azure storage...
read more

Azure / SQL Server / Azure DB / Add Logins, Users, Permissions

Posted by in Azure, SQL Server on Oct 30th, 2017 | 0 comments
Azure / SQL Server / Azure DB / Add Logins, Users, Permissions
After we created our SQL Server and Azure DB now we need to connect to our Azure DB using SSMS. First of all, we need to add firewall rule to be able to access our Azure SQL Server from our location. Click on yours Azure SQL Server on Azure portal, and choose Firewall / Virtual Networks.   Then just add your firewall rule and IP Address, or IP range if you want. After firewall rules have been added, users can connect to a Azure SQL Database using administrator accounts, or as an database owner or database users (If they already exist). First you need to login to the SQL Database using...
read more

Azure / SQL Server / Create DB on Azure

Posted by in Azure, SQL Server on Oct 30th, 2017 | 0 comments
Azure / SQL Server / Create DB on Azure
First of all, we need to login to Azure Portal and to create Logical SQL Server on Azure   Next, we need to put the name of the server, as well as server admin login account for this Azure Logical SQL Server. After that, we can create a new one or to use existing resource group.   When we finish this part of configuration we can start with creating Azure SQL Server Database. We need to define database name, and subscription we want to use for this SQL Database   After that, we need to select source of our database (blank, sample, or restore from backup)   Than we need to choose...
read more

Azure / SQL Server / Cannot open user default database (Login failed)

Posted by in Azure, SQL Server on Oct 26th, 2017 | 6 comments
Azure / SQL Server / Cannot open user default database (Login failed)
You want to create a new login on Azure Logical SQL Server using your Azure Admin account. To do that you must be connected to the master database on SQL Azure with the administrative login. CREATE LOGIN [azure_db_user] WITH PASSWORD=N'password' GO   After that, you need to create new DB user for that login (db_owner in this example). To be able to do that you must be connected as an admin to the database where you want to create that user. CREATE USER azure_db_user FOR LOGIN azure_db_user GO EXEC sp_addrolemember N'db_owner', N'azure_db_user' GO   Now, if you try to connect to that db using...
read more

Powershell / Backup SQL Server DBs to Azure Storage with Powershell script

Posted by in Azure, Powershell, SQL Server on Oct 4th, 2017 | 5 comments
Powershell / Backup SQL Server DBs to Azure Storage with Powershell script
With this simple script, you can backup all databases to Azure daily, you can hardcode the parameters values or you can use task manager to pass them to the PS script. You need to have Azure PowerShell modules installed on your machine (how to). First off all, let’s try just to connect to your Azure Storage Account You can use azure credential-publishsettings file to do that, or putting the variables with access key, subscription and storage account name as in this example: #Variables param ( [string]$AzureKey= "AzureStorageAccessKey", [string]$AzureSubs= "YourSubscription", [string]$AzureStor=...
read more

Next Entries »