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.

Tuesday, 8 January 2019

Rename SQL azure Database - PowerShell


Below example is how we can rename an AzureDB using AzureRm module in the below example i am renaming Azure DB VinothPowerBiTest to VinothPowerShellTest,


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

Monday, 30 July 2018

Free Procedure Cache in SQL Azure Database

To free up the procedure cache on the SQL Azure database use the following ALTER DB Statement,

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

This is same as running DBCC FREEPROCCACHE in the on-Premise SQL Instance.

Thursday, 22 February 2018

SQL Azure:Powershell AzureRM Create a new database in a existing server

Below is a simple script with modification from the MSDN sample script below to create a SQL Azure database on an existing resouce group and existing SQL server in oppose to creating a new RG and server in the below example,


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

Tuesday, 28 November 2017

Schedule SQL Jobs Using Azure Automation account

With the migration to SQL Azure happening on a fast pace i get lot of queries from my clients on how to schedule a SQL Agent job on a SQL Azure database In this Blog We will see how to schedule a SQL how to schedule a SQL Job using Azure Automation Account.

Environment:-

I am going to use my Test server 'Vinothtestsqlserver' and Test database 'VinothSampleDatabase' for this example.



Login into to Azure Subscription of the SQL Server and create new Automation Account from Azure MarketPlace



Once the Automation Account is created, Scroll through the left tab and select Runbooks under Process Automation,


First Step is to create a Credential for the SQL connection, Similar to Credential in SQL Agent or Data Sources in SSIS,  Under Shared Resources on you Automation Account Page, Select Credentials and Create a new Credential as below.


I have created a Credential Named BlogTestCredential for this example,



Now Create New Run book-This is similar to creating a new SQL agent Job


I have named the Runbook 'VinothTestRunBook' and Will be using Powershell Workflow. SQL Jobs can be created with either Powershell or Powershell Workflow.


Once the Runbook is created Your Script Editor will open, My aim for the SQL Job is to run 'SELECT * FROM sys.tables' from the VinothSampleDatabase every Hour.

I have used the below Powershell Script to achieve the output, The Script take Servername and Database as Parameter, then Uses BlogTestCredential using Get-Credential cmdlet to connect the SQL database. It then uses SQLConnection and SQLCommand Class to run the SQL.


In the Above Example I have used SQL Adapter to get the output and display, But in case you want to run a Store Proc or run Updates or Deletes use the Appropriate method(eg. ExecuteReader(), ExecuteNonQuery(), ExecuteScalar() etc.. More details on SQLCommand class and method in the below MSDN Link.

https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand%28v=vs.110%29.aspx


Once you Finish Scripting Test the Script by Clicking the Test Pane Button, where you can test the script by specifying the parameters(Server name and DB Name in this case) as below.


Once your testing Successful, Publish your run book by clicking the publish button this is same as creating a SQL Agent Job without a Schedule. 


Now once your Runbook is publish we can create a Schedule, Go back to Runbook Page on the Portal and select Schedules and create a new schedule as below I have created similar to SQL Agent job to run this script every hour,



Once you Create the Schedule Specify the Parameters for the Run book Schedule as below,


That's about it your job is now ready to run, To check the Job history Go to Workbook page in the Portal and click Jobs


Now you can see summary of a completed job, for detailed information click on the summary


And You can see the Jod Run's detailed info, To see the output click the output pane and you can see the job output.



There you go now you have perfectly working SQL Agent Environment in Azure. Powershell Workflow Allows you you run Steps in Parallel and in sequence we will see in detail on future blogs.





Monday, 11 September 2017

Replication - Compatibility issue

I was working on a POC concept for my client to test some replication functionality and got the below error,



"he selected subscriber does not satisfy the minimum version compatibility level"

I was using SQL Server 2008 as a distributor and Publisher and SQL Server 2012 as subscriber to mimic my client environment and i was using Developer edition to carry out my test.  I very well know the version and edition i was using supports replication. Finally after some research i found i was using SQL 2008R2 SSMS to set up my subscriber with was the cause of the mismatch, once i switched to SQL2012 SSMS everything was running fine. After this i did a testing with SQL 2012,SQL 2014 and SQL2016 similarly using publisher a one level below the subscriber and all the version failed with the same error when i use the lower version ssms. Hope the information helps.