Monday, 15 June 2020

Restore Azure SQL Database to different subscription without bacpac

Have you ever faced a scenario doing bacpac for a large database in order to move to another subscription, and waiting endlessly for this process to complete and thought why are't we able to just restore AzSQL Database. Well actually we can and it is well documented in Microsoft too under (Copy a transactionally consistent copy of a database in Azure SQL Database ) the problem is it is not under restore section so on basic google search it becomes bit tricky as the default bacpac option is the one you are going to see.

Summarising the process in the link, you can do this only using SSMS and not through Portal,You have to use a login that has the same name and password as the database owner of the source database on the source server. The login on the target server must also be a member of the dbmanager role, or be the server administrator login. Then execyte the below TSQL(Server name will be just the AZSQL Server name without database.windows.net


-- Execute on the master database of the target server (server2) to start copying from Server1 to Server2
CREATE DATABASE Database2 AS COPY OF server1.Database1;

Hope the article helps!

Tuesday, 17 December 2019

Last login date Power BI

Recently one of my client came up with a requirement of finding out the last login date of a Power BI user and wanted it scheduled as report, The details can be found in Power BI audit log or Azure AD user sign in info. I used the AzureADPreview Module cmdlets in PowerShell to get the required details

Friday, 11 October 2019

PowerShell - Audit / Extract Workspace User Access in Power BI

As part of security exercise I was asked extract all the users and their permissions in ouer Power BI environment, With introduction of PowerShell Module of Power BI and REST API's for Power BI auditing has become as easy as ever using PowerShell and API's.

In order to run the below Powershell script you first need to install MicrosoftPowerBIMgmt module in your powershell environment.

Copyright © 2019 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.

Thursday, 29 August 2019

PowerShell - Create a new Credential in Credential Manager - Interactive

Powershell create a credential in credential manager, The script interactively gets the Username and password and creates a Credential in Credential Manager,


Copyright © 2019 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.

Monday, 10 June 2019

Configure SQL Azure Auto-Failover Group - Azure Portal

In addition to the Geo-Replication feature that Azure has Microsoft has built in an additional awesome feature call Auto-Failover Group to failover Group of Azure databases and all database in Managed Instance, the more import feature is that the endpoint for the primary and the secondary endpoint remains the same, I would not go into more details you can find more details on
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-auto-failover-group 

Let see how it can be configured via Azure Portal,

My environment,

Primary SQL Instance - td-sqltestinstance
Region - UK West
Database Name - AdventureWorks2017

Secondary SQL Instance - td-sqlinstance-dr
Region - UK South


I am going to create a geo-replication first between the primary and the secondary server and allow geo-replication link to be inherited by the failover group.



I am adding UK South as my secondary server location and keeping the secondary Replica as a readable copy as below,


In the below Pic you can see my secondary read-only replica has been added, Primary Server can be used for all RW functions and Secondary read-only replica can be used for all reports.


Now lets move on and create a Failover Group for these 2 databases, on the settings of primary server td-sqltestinstance select Failover Groups and Add a Group


I am going name my Failover group as 'td-sqltestinstace-fo' this failover groups name acts like cluster virtual name / AG Listener Name so we do not need to change the server name setting in the application on failover. Select the database(s) which needs to be in the failover group.



The below pic shows the failover group has been created and the databse added to the failover group, Another important point is to note the RW Listener endpoint and Read-only Listener Endpoint. These 2 name will remain constant even in failover so the application need not change anything in case of failover this is a significant improvement from geo-replication where we have to change the servernames in case of failover.






Now that we have created the Failover group, Let us try manually failing over manually,



As you can see below, UK South has now become primary and UK west is now secondary read only replica but the Listener endpoints remains the same.

Hope the above article help, I am also sharing the Microsoft link for various topologies we can use with the Failover groups its worth a read before designing your DR solution.

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-designing-cloud-solutions-for-disaster-recovery

Thursday, 7 February 2019

PSScript:- Modify database mail Account

The below script was developed to modify existing DBMail account with new details like a new smtp server, authentication details etc, The below script was developed for specific requirement but will give you a template on how to make the changes via Powershell.



Copyright © 2019 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.

Tuesday, 5 February 2019

DBMail - Set mail server login password failed

Recently one of my clients complained that they were not able to modify DB mail setting and it is failing with the following error,

set mail server password failed for Mail Server '<servername>'

An error occured during Service Master Key decryption(Microsoft SQL Server, Error:33094)


This error happen when the service account is not able to decrypt the Service Master key may the server was rebuilt or migrated, or simply the process dencrypting  does not use the latest key. The only way to resolve this issue is by regenerating the Service Master Key.

https://technet.microsoft.com/en-us/library/ms187788.aspx

The REGENERATE phrase regenerates the service master key. When the service master key is regenerated, SQL Server decrypts all the keys that have been encrypted with it, and then encrypts them with the new service master key. This is a resource-intensive operation. You should schedule this operation during a period of low demand, unless the key has been compromised. If any one of the decryptions fail, the whole statement fails.

The FORCE option causes the key regeneration process to continue even if the process cannot retrieve the current master key, or cannot decrypt all the private keys that are encrypted with it. Use FORCE only if regeneration fails and you cannot restore the service master key by using the RESTORE SERVICE MASTER KEY statement.”

Once I ran ALTER SERVICE MASTER KEY FORCE REGENERATE the DBMail started working without any issues.