tag:blogger.com,1999:blog-24996041303513336692024-03-18T02:17:26.383-07:00SQL Server TechnetVinoth N Manoharanhttp://www.blogger.com/profile/15753172087248156870noreply@blogger.comBlogger105125tag:blogger.com,1999:blog-2499604130351333669.post-15494293371013755112023-02-21T09:41:00.002-08:002023-02-21T09:41:17.983-08:00Powershell to Restore Azure SQL database PITR backup<p>Today there seems to be a bug on Portal resource Manager API not allowing any SQL databases pitr restores from portal and the only way to do it at this point is using scripts, The below script is an example to restore the database on the same server with Feb 20th 3 PM backup</p>
<script src="https://gist.github.com/nmvinoth/3e7079918527f27cc3f0cc70fa0738f5.js"></script><div style="text-align: left;">
<span style="font-size: x-small;"><i>
Copyright © 2023 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.
</i></span></div>Vinoth N Manoharanhttp://www.blogger.com/profile/15753172087248156870noreply@blogger.com0tag:blogger.com,1999:blog-2499604130351333669.post-85200971544876928252023-02-21T09:24:00.002-08:002023-02-21T09:24:17.428-08:00Power BI DAX for Start of Year To End of Prior Month SalesWe had a requirement for Power BI Measure Start of the year To End of Prior Month Sales sum for one of the report which was failed for January as the original Measure was just query Previous Month and going to a negative Data Diff value obviously for January Eg:DATEDIFF(1jan2023,31DEC2022). To avoid this we had to introduce a check on the measure if the month is january then we need SUM of Sales of January rather than previous Month. I used the below logic to solve this issue
<script src="https://gist.github.com/nmvinoth/712cc08667a1084349b1869a37aae06c.js"></script><div style="text-align: left;">
<span style="font-size: x-small;"><i>
Copyright © 2023 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.
</i></span></div>Vinoth N Manoharanhttp://www.blogger.com/profile/15753172087248156870noreply@blogger.com0tag:blogger.com,1999:blog-2499604130351333669.post-46903290933352455212023-02-21T09:12:00.000-08:002023-02-21T09:12:02.278-08:00Bug In Azure API creating Data Factory Default AutoResolveIntegrationRuntime on Managed Virtual NetworkRecently our terraform Infrastructure as code effort to create a datafactory resource with default AutoResolveIntegrationRuntime in Managed Virtual Network failed to create one.<div><br /></div><div>We had <b>managed_virtual_network_enabled</b> flag on terraform and tried to use native azure cli too as a backup but both failed to create the default integration runtime on Managed Virtual Network and created a default public integration runtime. Only work around it was to create a Data Factory using ARM Template. I exported the ARM template and created the below Powershell to create the Data factory. I stored the Template and parameter file on a Fileshare and used it in the powershell to reuse and create a parameter file each time for different DF creation.</div><script src="https://gist.github.com/nmvinoth/6c944af7e0bb1db14bd5f87f91772def.js"></script><div style="text-align: left;">
<span style="font-size: x-small;"><i>
Copyright © 2023 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.
</i></span></div>Vinoth N Manoharanhttp://www.blogger.com/profile/15753172087248156870noreply@blogger.com0tag:blogger.com,1999:blog-2499604130351333669.post-75288219762122966612023-02-21T08:31:00.000-08:002023-02-21T08:31:14.932-08:00Powershell Azure Devops REST API to create/update a variable group based on json variable input<p><span style="font-family: trebuchet;"> I had a requirement to automate creation and updating of Azure devops variable group based on a configuration variable json file proved to me by the application team for CICD process, the below script will look for the variable group if it exists with the project mentioned if not it will create a new variable group, If the variable group exists it will update the variable group with json provided. The idea is to maintain the json file on git for version and see the history of the changes on the variable group variables provided to the pipeline.</span></p><p><script src="https://gist.github.com/nmvinoth/4c30cd2f0c7e4a39f554504f9818e9b6.js"></script> The below is the format of the Variable json passed on to the script</p><script src="https://gist.github.com/nmvinoth/74f03760cdef3ddc0f3b4940670088c6.js"></script><div style="text-align: left;">
<span style="font-size: x-small;"><i>
Copyright © 2023 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.
</i></span></div>Vinoth N Manoharanhttp://www.blogger.com/profile/15753172087248156870noreply@blogger.com0tag:blogger.com,1999:blog-2499604130351333669.post-75068358555274921702022-11-03T12:05:00.004-07:002022-11-03T12:05:37.075-07:00Work with Azure SQL with Python import result to pandas dataframeI am going start a series of blogs working with python on Azure SQL, what we can do and limitations, In this first blog we look look into connecting to a database and importing a sql result to pandas dataframe. In the below example we can see we have imported the data and printed the last 10 rows and I have attached the screen shot of the result. In the future blogs we can se how we can work in pandas with the data.<div><br /></div>
<script src="https://gist.github.com/nmvinoth/64576ba4caba1da4ae9b0c7a38887b38.js"></script><div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEhYcEshXdPkXul_DWkkG7rjMia6CypAy0u-GMTgucUJqpeSAr-MMWG4hZFkP0J3E6bqbWu0qrXWd9b3wyq2VGEwhxZ2gyM8kPcGTggzogFbVf1cU9gXAMRUwuhR_8EdL_xrOShF2evfhKzztEFFtdi-jNMyrZngSxnlQrMyWLT8q5jcztjKqL8bUNva" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="193" data-original-width="394" height="157" src="https://blogger.googleusercontent.com/img/a/AVvXsEhYcEshXdPkXul_DWkkG7rjMia6CypAy0u-GMTgucUJqpeSAr-MMWG4hZFkP0J3E6bqbWu0qrXWd9b3wyq2VGEwhxZ2gyM8kPcGTggzogFbVf1cU9gXAMRUwuhR_8EdL_xrOShF2evfhKzztEFFtdi-jNMyrZngSxnlQrMyWLT8q5jcztjKqL8bUNva" width="320" /></a></div><br /><br /></div>
Vinoth N Manoharanhttp://www.blogger.com/profile/15753172087248156870noreply@blogger.com0tag:blogger.com,1999:blog-2499604130351333669.post-37150733182507529492022-02-09T10:30:00.004-08:002022-02-09T10:30:44.062-08:00Azure Windows VM Disk Usage report for All VM in a Azure Tenant<p><span face="Arial, Tahoma, Helvetica, FreeSans, sans-serif" style="background-color: white; color: #333333; font-size: 14.85px;">I was setting up monitoring for my Azure environment and I had an requirement to email the VM Disk usage of all windows VM's in a tenant to Support team on daily Basis and also sent notification in Slack. I used PowerShell to consolidate the data and Logic apps for the integration for Slack notification.</span></p><p><span face="Arial, Tahoma, Helvetica, FreeSans, sans-serif" style="background-color: white; color: #333333; font-size: 14.85px;"><br /></span></p><p><span face="Arial, Tahoma, Helvetica, FreeSans, sans-serif" style="background-color: white; color: #333333; font-size: 14.85px;">To breakdown the script for better understanding, I have used a credential from credential manager which had RBAC permissions to all subscriptions in the tenant and I used the function in </span><a href="https://gallery.technet.microsoft.com/scriptcenter/Accessing-Windows-7210ae91" style="background-color: white; color: #336699; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 14.85px; text-decoration-line: none;">https://gallery.technet.microsoft.com/scriptcenter/Accessing-Windows-7210ae91</a><span face="Arial, Tahoma, Helvetica, FreeSans, sans-serif" style="background-color: white; color: #333333; font-size: 14.85px;"> for my Azure and SFTP authentication using the credentials already in credential manager. I am using </span><span face="Arial, Tahoma, Helvetica, FreeSans, sans-serif" style="color: #333333;"><span style="font-size: 14.85px;"><a href="https://docs.microsoft.com/en-us/powershell/module/az.compute/invoke-azvmruncommand?view=azps-7.2.0">Invoke-AzVMRunCommand</a> which basically runs a PowerShell script file on the remote server. The below PowerShell script will run C:\temp\GetDisk.ps1 on all the remote servers I am passing. </span></span></p><p><span face="Arial, Tahoma, Helvetica, FreeSans, sans-serif" style="color: #333333;"><span style="font-size: 14.85px;">GetDisk.ps1 runs the below PowerShell commandlet </span></span></p><p><span face="Arial, Tahoma, Helvetica, FreeSans, sans-serif" style="color: #333333;"><span style="font-size: 14.85px;"><b><i>"Get-Volume|where {$_.FileSystemLabel -ne 'System Reserved' -and $_.FileSystem -eq 'NTFS'}|SELECT DriveLetter,Size,SizeRemaining|ConvertTo-Json"</i></b></span></span></p><p><a href="https://docs.microsoft.com/en-us/powershell/module/az.compute/invoke-azvmruncommand?view=azps-7.2.0" style="font-size: 14.85px;">Invoke-AzVMRunCommand</a><span style="color: #333333;"><span style="font-size: 14.85px;"> Will return only as single string irrespective of output format so I have converted into json so I can easily convert to PSObject for processing in my main script.</span></span></p><p><span style="color: #333333;"><span style="font-size: 14.85px;"><br /></span></span></p><p><span style="background-color: white; color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 14.85px;">The script will consolidate result in HTML format and Json format. Json format will be sent as Payload to Webhooks to Logic Apps were you can integrate with Slack or Teams alerts. HTML format will be styled with yellow rows for 10 to 15% free space and Red Rows for usage less than 10% free space as below.</span></p><p></p><h1><span style="mso-fareast-font-family: "Times New Roman";">VM Disk Usage<o:p></o:p></span></h1>
<table border="1" cellpadding="0" cellspacing="0" class="MsoNormalTable" style="border-collapse: collapse; border: none; mso-border-alt: solid black .75pt; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt; mso-yfti-tbllook: 1184;">
<colgroup><col></col><col></col><col></col><col></col><col></col><col></col><col></col><col></col></colgroup>
<tbody><tr style="mso-yfti-firstrow: yes; mso-yfti-irow: 0;">
<td style="background: lightblue; border: solid black 1.0pt; mso-border-alt: solid black .75pt; padding: 3.75pt 3.75pt 3.75pt 3.75pt;">
<p align="center" class="MsoNormal" style="text-align: center;"><b><span style="mso-fareast-font-family: "Times New Roman";">VMName<o:p></o:p></span></b></p>
</td>
<td style="background: lightblue; border-left: none; border: solid black 1.0pt; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; padding: 3.75pt 3.75pt 3.75pt 3.75pt;">
<p align="center" class="MsoNormal" style="text-align: center;"><b><span style="color: black; mso-color-alt: windowtext; mso-fareast-font-family: "Times New Roman";">SubscriptionName</span></b><b><span style="mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></b></p>
</td>
<td style="background: lightblue; border-left: none; border: solid black 1.0pt; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; padding: 3.75pt 3.75pt 3.75pt 3.75pt;">
<p align="center" class="MsoNormal" style="text-align: center;"><b><span style="color: black; mso-color-alt: windowtext; mso-fareast-font-family: "Times New Roman";">RGName</span></b><b><span style="mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></b></p>
</td>
<td style="background: lightblue; border-left: none; border: solid black 1.0pt; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; padding: 3.75pt 3.75pt 3.75pt 3.75pt;">
<p align="center" class="MsoNormal" style="text-align: center;"><b><span style="color: black; mso-color-alt: windowtext; mso-fareast-font-family: "Times New Roman";">DriveLetter</span></b><b><span style="mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></b></p>
</td>
<td style="background: lightblue; border-left: none; border: solid black 1.0pt; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; padding: 3.75pt 3.75pt 3.75pt 3.75pt;">
<p align="center" class="MsoNormal" style="text-align: center;"><b><span style="color: black; mso-color-alt: windowtext; mso-fareast-font-family: "Times New Roman";">Size(GB)</span></b><b><span style="mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></b></p>
</td>
<td style="background: lightblue; border-left: none; border: solid black 1.0pt; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; padding: 3.75pt 3.75pt 3.75pt 3.75pt;">
<p align="center" class="MsoNormal" style="text-align: center;"><b><span style="color: black; mso-color-alt: windowtext; mso-fareast-font-family: "Times New Roman";">SizeRemaining(GB)</span></b><b><span style="mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></b></p>
</td>
<td style="background: lightblue; border-left: none; border: solid black 1.0pt; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; padding: 3.75pt 3.75pt 3.75pt 3.75pt;">
<p align="center" class="MsoNormal" style="text-align: center;"><b><span style="color: black; mso-color-alt: windowtext; mso-fareast-font-family: "Times New Roman";">UsedSpace(GB)</span></b><b><span style="mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></b></p>
</td>
<td style="background: lightblue; border-left: none; border: solid black 1.0pt; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; padding: 3.75pt 3.75pt 3.75pt 3.75pt;">
<p align="center" class="MsoNormal" style="text-align: center;"><b><span style="color: black; mso-color-alt: windowtext; mso-fareast-font-family: "Times New Roman";">FreeSpacePercentage</span></b><b><span style="mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></b></p>
</td>
</tr>
<tr style="mso-yfti-irow: 1;">
<td style="border-top: none; border: solid black 1.0pt; mso-border-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt 3.75pt 3.75pt 3.75pt;">
<p class="MsoNormal"><span style="mso-fareast-font-family: "Times New Roman";">Server1<o:p></o:p></span></p>
</td>
<td style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt 3.75pt 3.75pt 3.75pt;">
<p class="MsoNormal"><span style="mso-fareast-font-family: "Times New Roman";">Sub-1<o:p></o:p></span></p>
</td>
<td style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt 3.75pt 3.75pt 3.75pt;">
<p class="MsoNormal"><span style="mso-fareast-font-family: "Times New Roman";">RG-1<o:p></o:p></span></p>
</td>
<td style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt 3.75pt 3.75pt 3.75pt;">
<p class="MsoNormal"><span style="mso-fareast-font-family: "Times New Roman";">C<o:p></o:p></span></p>
</td>
<td style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt 3.75pt 3.75pt 3.75pt;">
<p class="MsoNormal"><span style="mso-fareast-font-family: "Times New Roman";">512<o:p></o:p></span></p>
</td>
<td style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt 3.75pt 3.75pt 3.75pt;">
<p class="MsoNormal"><span style="mso-fareast-font-family: "Times New Roman";">223<o:p></o:p></span></p>
</td>
<td style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt 3.75pt 3.75pt 3.75pt;">
<p class="MsoNormal"><span style="mso-fareast-font-family: "Times New Roman";">289<o:p></o:p></span></p>
</td>
<td style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt 3.75pt 3.75pt 3.75pt;">
<p class="MsoNormal"><span style="mso-fareast-font-family: "Times New Roman";">43.49978<o:p></o:p></span></p>
</td>
</tr>
<tr style="mso-yfti-irow: 2;">
<td style="border-top: none; border: solid black 1.0pt; mso-border-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt 3.75pt 3.75pt 3.75pt;">
<p class="MsoNormal"><span style="mso-fareast-font-family: "Times New Roman";">Server1<o:p></o:p></span></p>
</td>
<td style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt 3.75pt 3.75pt 3.75pt;">
<p class="MsoNormal"><span style="mso-fareast-font-family: "Times New Roman";">Sub-1<o:p></o:p></span></p>
</td>
<td style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt 3.75pt 3.75pt 3.75pt;">
<p class="MsoNormal"><span style="mso-fareast-font-family: "Times New Roman";">RG-1<o:p></o:p></span></p>
</td>
<td style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt 3.75pt 3.75pt 3.75pt;">
<p class="MsoNormal"><span style="mso-fareast-font-family: "Times New Roman";">D<o:p></o:p></span></p>
</td>
<td style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt 3.75pt 3.75pt 3.75pt;">
<p class="MsoNormal"><span style="mso-fareast-font-family: "Times New Roman";">32<o:p></o:p></span></p>
</td>
<td style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt 3.75pt 3.75pt 3.75pt;">
<p class="MsoNormal"><span style="mso-fareast-font-family: "Times New Roman";">30<o:p></o:p></span></p>
</td>
<td style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt 3.75pt 3.75pt 3.75pt;">
<p class="MsoNormal"><span style="mso-fareast-font-family: "Times New Roman";">2<o:p></o:p></span></p>
</td>
<td style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt 3.75pt 3.75pt 3.75pt;">
<p class="MsoNormal"><span style="mso-fareast-font-family: "Times New Roman";">92.29322<o:p></o:p></span></p>
</td>
</tr>
<tr style="mso-yfti-irow: 3;">
<td style="border-top: none; border: solid black 1.0pt; mso-border-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt 3.75pt 3.75pt 3.75pt;">
<p class="MsoNormal"><span style="mso-fareast-font-family: "Times New Roman";">Server1<o:p></o:p></span></p>
</td>
<td style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt 3.75pt 3.75pt 3.75pt;">
<p class="MsoNormal"><span style="mso-fareast-font-family: "Times New Roman";">Sub-1<o:p></o:p></span></p>
</td>
<td style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt 3.75pt 3.75pt 3.75pt;">
<p class="MsoNormal"><span style="mso-fareast-font-family: "Times New Roman";">RG-1<o:p></o:p></span></p>
</td>
<td style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt 3.75pt 3.75pt 3.75pt;">
<p class="MsoNormal"><span style="mso-fareast-font-family: "Times New Roman";">F<o:p></o:p></span></p>
</td>
<td style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt 3.75pt 3.75pt 3.75pt;">
<p class="MsoNormal"><span style="mso-fareast-font-family: "Times New Roman";">2048<o:p></o:p></span></p>
</td>
<td style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt 3.75pt 3.75pt 3.75pt;">
<p class="MsoNormal"><span style="mso-fareast-font-family: "Times New Roman";">381<o:p></o:p></span></p>
</td>
<td style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt 3.75pt 3.75pt 3.75pt;">
<p class="MsoNormal"><span style="mso-fareast-font-family: "Times New Roman";">1667<o:p></o:p></span></p>
</td>
<td style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt 3.75pt 3.75pt 3.75pt;">
<p class="MsoNormal"><span style="mso-fareast-font-family: "Times New Roman";">18.60302<o:p></o:p></span></p>
</td>
</tr>
<tr style="mso-yfti-irow: 4;">
<td style="background: yellow; border-top: none; border: solid black 1.0pt; mso-border-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt 3.75pt 3.75pt 3.75pt;">
<p class="MsoNormal"><span style="color: black; mso-color-alt: windowtext; mso-fareast-font-family: "Times New Roman";">Server2</span><span style="mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></p>
</td>
<td style="background: yellow; border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt 3.75pt 3.75pt 3.75pt;">
<p class="MsoNormal"><span style="color: black; mso-color-alt: windowtext; mso-fareast-font-family: "Times New Roman";">Sub-2</span><span style="mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></p>
</td>
<td style="background: yellow; border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt 3.75pt 3.75pt 3.75pt;">
<p class="MsoNormal"><span style="color: black; mso-color-alt: windowtext; mso-fareast-font-family: "Times New Roman";">RG-2</span><span style="mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></p>
</td>
<td style="background: yellow; border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt 3.75pt 3.75pt 3.75pt;">
<p class="MsoNormal"><span style="color: black; mso-color-alt: windowtext; mso-fareast-font-family: "Times New Roman";">C</span><span style="mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></p>
</td>
<td style="background: yellow; border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt 3.75pt 3.75pt 3.75pt;">
<p class="MsoNormal"><span style="color: black; mso-color-alt: windowtext; mso-fareast-font-family: "Times New Roman";">127</span><span style="mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></p>
</td>
<td style="background: yellow; border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt 3.75pt 3.75pt 3.75pt;">
<p class="MsoNormal"><span style="color: black; mso-color-alt: windowtext; mso-fareast-font-family: "Times New Roman";">19</span><span style="mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></p>
</td>
<td style="background: yellow; border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt 3.75pt 3.75pt 3.75pt;">
<p class="MsoNormal"><span style="color: black; mso-color-alt: windowtext; mso-fareast-font-family: "Times New Roman";">108</span><span style="mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></p>
</td>
<td style="background: yellow; border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt 3.75pt 3.75pt 3.75pt;">
<p class="MsoNormal"><span style="color: black; mso-color-alt: windowtext; mso-fareast-font-family: "Times New Roman";">14.84652</span><span style="mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></p>
</td>
</tr>
<tr style="mso-yfti-irow: 5; mso-yfti-lastrow: yes;">
<td style="background: red; border-top: none; border: solid black 1.0pt; mso-border-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt 3.75pt 3.75pt 3.75pt;">
<p class="MsoNormal"><span style="color: black; mso-color-alt: windowtext; mso-fareast-font-family: "Times New Roman";">Server2</span><span style="mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></p>
</td>
<td style="background: red; border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt 3.75pt 3.75pt 3.75pt;">
<p class="MsoNormal"><span style="color: black; mso-color-alt: windowtext; mso-fareast-font-family: "Times New Roman";">Sub-2</span><span style="mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></p>
</td>
<td style="background: red; border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt 3.75pt 3.75pt 3.75pt;">
<p class="MsoNormal"><span style="color: black; mso-color-alt: windowtext; mso-fareast-font-family: "Times New Roman";">RG-2</span><span style="mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></p>
</td>
<td style="background: red; border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt 3.75pt 3.75pt 3.75pt;">
<p class="MsoNormal"><span style="color: black; mso-color-alt: windowtext; mso-fareast-font-family: "Times New Roman";">F</span><span style="mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></p>
</td>
<td style="background: red; border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt 3.75pt 3.75pt 3.75pt;">
<p class="MsoNormal"><span style="color: black; mso-color-alt: windowtext; mso-fareast-font-family: "Times New Roman";">512</span><span style="mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></p>
</td>
<td style="background: red; border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt 3.75pt 3.75pt 3.75pt;">
<p class="MsoNormal"><span style="color: black; mso-color-alt: windowtext; mso-fareast-font-family: "Times New Roman";">27</span><span style="mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></p>
</td>
<td style="background: red; border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt 3.75pt 3.75pt 3.75pt;">
<p class="MsoNormal"><span style="color: black; mso-color-alt: windowtext; mso-fareast-font-family: "Times New Roman";">485</span><span style="mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></p>
</td>
<td style="background: red; border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt 3.75pt 3.75pt 3.75pt;">
<p class="MsoNormal"><span style="color: black; mso-color-alt: windowtext; mso-fareast-font-family: "Times New Roman";">5.293138</span><span style="mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></p>
</td>
</tr>
</tbody></table>
<p class="MsoNormal"><o:p> </o:p></p><span style="background-color: white; color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 14.85px;"></span><p></p>
<script src="https://gist.github.com/nmvinoth/bddf3c05ed99e0276da6bf62c9787d90.js"></script>
<div style="text-align: left;">
<span style="font-size: x-small;"><i>
Copyright © 2022 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.
</i></span></div>Vinoth N Manoharanhttp://www.blogger.com/profile/15753172087248156870noreply@blogger.com1tag:blogger.com,1999:blog-2499604130351333669.post-40938156920660498632022-02-09T10:04:00.003-08:002022-02-09T10:21:08.268-08:00Azure File Share Usage Report for all Subscription in your Tenant (Email and Web hooks notification)<p> I was setting up monitoring for my Azure environment and I had an requirement to email the Fileshare usage of all fileshares in a tenant to Support team on daily Basis and also sent notification in Slack. I used PowerShell to consolidate the data and Logic apps for the integration for Slack notification.</p><p>To breakdown the script for better understanding, I have used a credential from credential manager which had RBAC permissions to all subscriptions in the tenant and I used the function in <a href="https://gallery.technet.microsoft.com/scriptcenter/Accessing-Windows-7210ae91">https://gallery.technet.microsoft.com/scriptcenter/Accessing-Windows-7210ae91</a> for my Azure and SFTP authentication using the credentials already in credential manager. I am using Azure Management API's to get the information of the File storage as I found PowerShell Modules had restriction to Storage account opened only for particular subnet outside of the server I am running the script. I have used the method in <a href="https://www.powershellgallery.com/packages/LSECosmos/0.2.1-alpha/Content/Get-AzAccessToken.ps1">https://www.powershellgallery.com/packages/LSECosmos/0.2.1-alpha/Content/Get-AzAccessToken.ps1</a> for API Authentication.</p><p><br /></p><p>The script will consolidate result in HTML format and Json format. Json format will be sent as Payload to Webhooks to Logic Apps were you can integrate with Slack or Teams alerts. HTML format will be styled with yellow rows for 80 to 90% and Red Rows for usage above 90% as below.</p><h1>File Share Usage<o:p></o:p></h1><table border="1" cellpadding="0" cellspacing="0" class="MsoNormalTable" style="border-collapse: collapse; border: none; mso-border-alt: solid black .75pt; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt; mso-yfti-tbllook: 1184;">
<colgroup><col></col><col></col><col></col><col></col><col></col><col></col><col></col></colgroup>
<tbody><tr>
<td style="background: lightblue; border: 1pt solid black; mso-border-alt: solid black .75pt; padding: 3.75pt;">
<p align="center" class="MsoNormal" style="text-align: center;"><b>StorageAccountName<o:p></o:p></b></p>
</td>
<td style="background: lightblue; border-left: none; border: 1pt solid black; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; padding: 3.75pt;">
<p align="center" class="MsoNormal" style="text-align: center;"><b>SubscriptionName</b><b><o:p></o:p></b></p>
</td>
<td style="background: lightblue; border-left: none; border: 1pt solid black; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; padding: 3.75pt;">
<p align="center" class="MsoNormal" style="text-align: center;"><b>ResourceGroupName</b><b><o:p></o:p></b></p>
</td>
<td style="background: lightblue; border-left: none; border: 1pt solid black; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; padding: 3.75pt;">
<p align="center" class="MsoNormal" style="text-align: center;"><b>ShareName</b><b><o:p></o:p></b></p>
</td>
<td style="background: lightblue; border-left: none; border: 1pt solid black; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; padding: 3.75pt;">
<p align="center" class="MsoNormal" style="text-align: center;"><b>TotalCapacity(GB)</b><b><o:p></o:p></b></p>
</td>
<td style="background: lightblue; border-left: none; border: 1pt solid black; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; padding: 3.75pt;">
<p align="center" class="MsoNormal" style="text-align: center;"><b>UsedSpace(GB)</b><b><o:p></o:p></b></p>
</td>
<td style="background: lightblue; border-left: none; border: 1pt solid black; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; padding: 3.75pt;">
<p align="center" class="MsoNormal" style="text-align: center;"><b>PercentageUsed</b><b><o:p></o:p></b></p>
</td>
</tr>
<tr>
<td style="border-top: none; border: 1pt solid black; mso-border-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt;">
<p class="MsoNormal">StgAcctName1<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid black; border-left: none; border-right: 1pt solid black; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt;">
<p class="MsoNormal">Sub-1<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid black; border-left: none; border-right: 1pt solid black; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt;">
<p class="MsoNormal">RG-1<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid black; border-left: none; border-right: 1pt solid black; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt;">
<p class="MsoNormal">FileShare-1<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid black; border-left: none; border-right: 1pt solid black; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt;">
<p class="MsoNormal">1<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid black; border-left: none; border-right: 1pt solid black; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt;">
<p class="MsoNormal">0<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid black; border-left: none; border-right: 1pt solid black; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt;">
<p class="MsoNormal">0<o:p></o:p></p>
</td>
</tr>
<tr>
<td style="border-top: none; border: 1pt solid black; mso-border-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt;">
<p class="MsoNormal">StgAcctName2<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid black; border-left: none; border-right: 1pt solid black; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt;">
<p class="MsoNormal">Sub-1<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid black; border-left: none; border-right: 1pt solid black; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt;">
<p class="MsoNormal">RG-2<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid black; border-left: none; border-right: 1pt solid black; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt;">
<p class="MsoNormal">FileShare-2<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid black; border-left: none; border-right: 1pt solid black; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt;">
<p class="MsoNormal">10<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid black; border-left: none; border-right: 1pt solid black; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt;">
<p class="MsoNormal">0<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid black; border-left: none; border-right: 1pt solid black; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt;">
<p class="MsoNormal">2<o:p></o:p></p>
</td>
</tr>
<tr>
<td style="border-top: none; border: 1pt solid black; mso-border-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt;">
<p class="MsoNormal">StgAcctName3<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid black; border-left: none; border-right: 1pt solid black; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt;">
<p class="MsoNormal">Sub-2<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid black; border-left: none; border-right: 1pt solid black; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt;">
<p class="MsoNormal">RG-3<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid black; border-left: none; border-right: 1pt solid black; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt;">
<p class="MsoNormal">FileShare-3<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid black; border-left: none; border-right: 1pt solid black; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt;">
<p class="MsoNormal">100<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid black; border-left: none; border-right: 1pt solid black; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt;">
<p class="MsoNormal">0<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid black; border-left: none; border-right: 1pt solid black; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt;">
<p class="MsoNormal">0<o:p></o:p></p>
</td>
</tr>
<tr>
<td style="background: red; border-top: none; border: 1pt solid black; mso-border-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt;">
<p class="MsoNormal">StgAcctName4<o:p></o:p></p>
</td>
<td style="background: red; border-bottom: 1pt solid black; border-left: none; border-right: 1pt solid black; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt;">
<p class="MsoNormal">Sub-3<o:p></o:p></p>
</td>
<td style="background: red; border-bottom: 1pt solid black; border-left: none; border-right: 1pt solid black; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt;">
<p class="MsoNormal">RG-4<o:p></o:p></p>
</td>
<td style="background: red; border-bottom: 1pt solid black; border-left: none; border-right: 1pt solid black; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt;">
<p class="MsoNormal">FileShare-4<o:p></o:p></p>
</td>
<td style="background: red; border-bottom: 1pt solid black; border-left: none; border-right: 1pt solid black; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt;">
<p class="MsoNormal">1024<o:p></o:p></p>
</td>
<td style="background: red; border-bottom: 1pt solid black; border-left: none; border-right: 1pt solid black; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt;">
<p class="MsoNormal">926<o:p></o:p></p>
</td>
<td style="background: red; border-bottom: 1pt solid black; border-left: none; border-right: 1pt solid black; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt;">
<p class="MsoNormal">90<o:p></o:p></p>
</td>
</tr>
<tr>
<td style="border-top: none; border: 1pt solid black; mso-border-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt;">
<p class="MsoNormal">StgAcctName5<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid black; border-left: none; border-right: 1pt solid black; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt;">
<p class="MsoNormal">Sub-4<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid black; border-left: none; border-right: 1pt solid black; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt;">
<p class="MsoNormal">RG-5<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid black; border-left: none; border-right: 1pt solid black; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt;">
<p class="MsoNormal">FileShare-5<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid black; border-left: none; border-right: 1pt solid black; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt;">
<p class="MsoNormal">250<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid black; border-left: none; border-right: 1pt solid black; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt;">
<p class="MsoNormal">91<o:p></o:p></p>
</td>
<td style="border-bottom: 1pt solid black; border-left: none; border-right: 1pt solid black; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt;">
<p class="MsoNormal">37<o:p></o:p></p>
</td>
</tr>
<tr>
<td style="background: yellow; border-top: none; border: 1pt solid black; mso-border-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt;">
<p class="MsoNormal">StgAcctName6<o:p></o:p></p>
</td>
<td style="background: yellow; border-bottom: 1pt solid black; border-left: none; border-right: 1pt solid black; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt;">
<p class="MsoNormal">Sub-4<o:p></o:p></p>
</td>
<td style="background: yellow; border-bottom: 1pt solid black; border-left: none; border-right: 1pt solid black; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt;">
<p class="MsoNormal">RG-5<o:p></o:p></p>
</td>
<td style="background: yellow; border-bottom: 1pt solid black; border-left: none; border-right: 1pt solid black; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt;">
<p class="MsoNormal">FileShare-6<o:p></o:p></p>
</td>
<td style="background: yellow; border-bottom: 1pt solid black; border-left: none; border-right: 1pt solid black; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt;">
<p class="MsoNormal">1024<o:p></o:p></p>
</td>
<td style="background: yellow; border-bottom: 1pt solid black; border-left: none; border-right: 1pt solid black; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt;">
<p class="MsoNormal">845<o:p></o:p></p>
</td>
<td style="background: yellow; border-bottom: 1pt solid black; border-left: none; border-right: 1pt solid black; border-top: none; mso-border-alt: solid black .75pt; mso-border-left-alt: solid black .75pt; mso-border-top-alt: solid black .75pt; padding: 3.75pt;">
<p class="MsoNormal">83<o:p></o:p></p>
</td>
</tr>
</tbody></table><p>
</p><p class="MsoNormal"><o:p> </o:p></p>
<script src="https://gist.github.com/nmvinoth/18afd184daae8157197b8f834f858113.js"></script>
<div style="text-align: left;">
<span style="font-size: x-small;"><i>
Copyright © 2022 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.
</i></span></div>
Vinoth N Manoharanhttp://www.blogger.com/profile/15753172087248156870noreply@blogger.com3tag:blogger.com,1999:blog-2499604130351333669.post-62658358851197022112021-06-02T07:09:00.002-07:002021-06-02T07:09:42.411-07:00<h1 style="text-align: left;">Recover Deleted Azure Database using LTR backups</h1><div><br /></div><div>I have had many queries in past where there was scenario where PROD Azure DB has to be restored which mean a new DB is created in Azure DB and users were not sure if they will loose the LTR backups of the old databases if deleted and kept paying for an unused DB.</div><div><br /></div><div>Most of the confusion comes when you delete the database in Azure option to access LTR backups of the deleted DB though portal goes away which makes the users think the backups are also deleted permanently. This is not true even though you have deleted the DB the LTR backups are always retained according to your retention policy. It is only not accessible though Poral, You can use Powershell or AzureCLI to access it.</div><div><br /></div><div>use the below Powershell it will return all the LTR backups on your location,</div><div><br /></div><div><div><b><i>Get-AzSqlDatabaseLongTermRetentionBackup -Location <yourAzDClocation></i></b></div><div><br /></div><div><a href="https://docs.microsoft.com/en-us/powershell/module/az.sql/get-azsqldatabaselongtermretentionbackup?view=azps-6.0.0">https://docs.microsoft.com/en-us/powershell/module/az.sql/get-azsqldatabaselongtermretentionbackup?view=azps-6.0.0</a></div><div><br /></div><div><br /></div><div>You can use the filer at SQL instance level, DB level and Resource group level too. So don't be afraid of losing your LTR backup when you delete the DB.</div><div><br /></div><div>But PiTR backups is a different story, by default the backup can be recovered within 7 days(unless you have set PiTR backups to one day) from deleted DB folder in portal or using Powershell ( <a href="https://docs.microsoft.com/en-us/azure/azure-sql/database/recovery-using-backups#deleted-database-restore">https://docs.microsoft.com/en-us/azure/azure-sql/database/recovery-using-backups#deleted-database-restore</a> ) After which you will loose the DB if there are not LTR backups so be careful before deleting Azure databases with no LTR backups.</div><div><br /></div></div>Vinoth N Manoharanhttp://www.blogger.com/profile/15753172087248156870noreply@blogger.com0tag:blogger.com,1999:blog-2499604130351333669.post-55028465986409978542020-12-30T03:53:00.002-08:002022-02-09T10:09:47.644-08:00Powershell - Set Azure SQL Firewall for all Azure SQL Service Tag IP in a Region<p> With one of my client we had to create a external data source between 2 Azure DB's but we were not happy to set Allow Azure services option to enable connectivity for 2 Azure DB's and open up Firewall to DB from all Azure resources so we went through the option of setting Allow Azure Services Off and whitelisting SQL ServiceTag IP's for Azure region where our DB's were hosted, Below is the script i created to accomplish the task and WestEurope is the region I used...</p><p><br /></p><p><script src="https://gist.github.com/nmvinoth/5f1801f5149c3307818fe77546ce0819.js"></script></p>
<div style="text-align: left;">
<span style="font-size: x-small;"><i>
Copyright © 2020 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.
</i></span></div>
Vinoth N Manoharanhttp://www.blogger.com/profile/15753172087248156870noreply@blogger.com13tag:blogger.com,1999:blog-2499604130351333669.post-1196851406663293212020-06-15T11:16:00.002-07:002020-06-15T11:30:58.177-07:00Restore Azure SQL Database to different subscription without bacpac<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: inherit;">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 </span>(<a href="https://docs.microsoft.com/en-us/azure/azure-sql/database/database-copy?tabs=azure-powershell" target="_blank">Copy a transactionally consistent copy of a database in Azure SQL Database</a> ) 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.<br />
<div>
<span style="font-size: small;"><span style="color: #171717; font-family: inherit;"><span style="font-weight: normal;"><br /></span></span></span></div>
<div>
<span style="font-family: inherit;"><span style="color: #171717;">Summarising</span><span style="font-size: small;"><span style="color: #171717;"> the process in the link, you can do this only using SSMS and not through Portal,You have to u</span></span><span style="background-color: white; color: #171717;">se 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 </span><code style="border-radius: 3px; box-sizing: inherit; color: #171717; direction: ltr; outline-color: inherit; overflow-wrap: break-word; padding: 0.1em 0.2em;">dbmanager</code><span style="background-color: white;"><span style="color: #171717;"> role, or be the server administrator login. Then execyte the below TSQL(Server name will be just the AZSQL </span><span style="color: #171717;">Server name</span><span style="color: #171717;"> without </span></span><span style="background-color: white; color: #323130; white-space: nowrap;">database.windows.net</span></span></div>
<div>
<span style="background-color: white; color: #171717;"><span style="font-family: inherit;"><br /></span></span></div>
<div>
<span style="font-size: small;"><span style="font-family: inherit;"></span></span><br />
<div>
<div>
<span style="font-size: small;"><span style="font-family: inherit;"><span style="color: #38761d;">-- Execute on the master database of the target server (server2) to start copying from Server1 to Server2</span></span></span></div>
<div>
<span style="font-size: small;"><span style="font-family: inherit;"><span style="color: blue;">CREATE DATABASE</span><span style="color: #171717;"> Database2 </span><span style="color: blue;">AS COPY OF</span><span style="color: #171717;"> server1.Database1;</span></span></span></div>
</div>
<span style="font-size: small;"><span style="font-family: inherit;">
<div style="color: #171717;">
<br /></div>
<div style="color: #171717;">
Hope the article helps!</div>
</span></span></div>
</div>
Vinoth N Manoharanhttp://www.blogger.com/profile/15753172087248156870noreply@blogger.com1tag:blogger.com,1999:blog-2499604130351333669.post-27799869109514705102019-12-17T08:04:00.002-08:002022-02-09T10:09:14.208-08:00Last login date Power BI<div dir="ltr" style="text-align: left;" trbidi="on">
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
<script src="https://gist.github.com/nmvinoth/d3f0c34697c7e6da888c8f14530fd377.js"></script>
<div style="text-align: left;">
<span style="font-size: x-small;"><i>
Copyright © 2019 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.
</i></span></div>
</div>
Vinoth N Manoharanhttp://www.blogger.com/profile/15753172087248156870noreply@blogger.com0tag:blogger.com,1999:blog-2499604130351333669.post-33736280319160482572019-10-11T04:28:00.001-07:002019-10-11T04:29:53.035-07:00PowerShell - Audit / Extract Workspace User Access in Power BI<div dir="ltr" style="text-align: left;" trbidi="on">
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.<br />
<br />
In order to run the below Powershell script you first need to install <b>MicrosoftPowerBIMgmt </b>module in your powershell environment.<br />
<br />
<script src="https://gist.github.com/nmvinoth/74a15d050695dfa0eb768324029a468a.js"></script>
<div style="text-align: left;">
<span style="font-size: x-small;"><i>
Copyright © 2019 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.
</i></span></div>
</div>
Vinoth N Manoharanhttp://www.blogger.com/profile/15753172087248156870noreply@blogger.com0tag:blogger.com,1999:blog-2499604130351333669.post-56579675772820935722019-08-29T05:48:00.001-07:002019-10-11T04:52:01.808-07:00PowerShell - Create a new Credential in Credential Manager - Interactive<div dir="ltr" style="text-align: left;" trbidi="on">
Powershell create a credential in credential manager, The script interactively gets the Username and password and creates a Credential in Credential Manager,<br />
<div>
<br /></div>
<div>
<br />
<script src="https://gist.github.com/nmvinoth/9360197c88db73abf80ca53159259f29.js"></script>
</div>
<span style="font-size: xx-small;"><i>Copyright © 2019 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.
</i></span></div>
Vinoth N Manoharanhttp://www.blogger.com/profile/15753172087248156870noreply@blogger.com3tag:blogger.com,1999:blog-2499604130351333669.post-84909196763998605962019-06-10T05:49:00.003-07:002019-06-10T05:49:50.439-07:00Configure SQL Azure Auto-Failover Group - Azure Portal<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: Arial, Helvetica, sans-serif;">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</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><a href="https://docs.microsoft.com/en-us/azure/sql-database/sql-database-auto-failover-group">https://docs.microsoft.com/en-us/azure/sql-database/sql-database-auto-failover-group</a> </span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Let see how it can be configured via Azure Portal,</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">My environment,</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Primary SQL Instance - td-sqltestinstance</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">Region - UK West</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">Database Name - <span style="background-color: white; white-space: nowrap;">AdventureWorks2017</span></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Secondary SQL Instance - td-sqlinstance-dr</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">Region - UK South</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj0_ZK8w_jT3YSzucGioKtq1W3RWjt_NH7iWu1xsCQh3ahUeuctpY0a7xeVYjU87oJyT7WrMEMcWFWs-AMTKLxDdrIkylYuZ2ttfn3FGKz5e0SOxXvBFdjuVocLSPPwAILWgqwmAhGLGBs/s1600/1.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="110" data-original-width="1069" height="64" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj0_ZK8w_jT3YSzucGioKtq1W3RWjt_NH7iWu1xsCQh3ahUeuctpY0a7xeVYjU87oJyT7WrMEMcWFWs-AMTKLxDdrIkylYuZ2ttfn3FGKz5e0SOxXvBFdjuVocLSPPwAILWgqwmAhGLGBs/s640/1.JPG" width="640" /></a></div>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">I am going to create a geo-replication first between the primary and the secondary server and allow </span><span style="background-color: white;"><span style="font-family: Arial, Helvetica, sans-serif;">geo-replication link to be inherited by the failover group.</span></span><br />
<span style="background-color: white;"><span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></span>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhVjUkJbEihAGVi1di52wKGraWQuR1sphfIickYX_lKfu3VaqGXo-pjOjmehywGs1kZ0L0suDGCqAUmJlm8LatxhH2b7QUODMk9y6SD0LWcjEd74oJXt4TAfHsXGq-0yZysAXlpCvZsao8/s1600/2.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="908" data-original-width="1600" height="362" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhVjUkJbEihAGVi1di52wKGraWQuR1sphfIickYX_lKfu3VaqGXo-pjOjmehywGs1kZ0L0suDGCqAUmJlm8LatxhH2b7QUODMk9y6SD0LWcjEd74oJXt4TAfHsXGq-0yZysAXlpCvZsao8/s640/2.JPG" width="640" /></a></div>
<span style="background-color: white;"><span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></span>
<span style="background-color: white;"><span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></span>
<span style="background-color: white;"><span style="font-family: Arial, Helvetica, sans-serif;">I am adding UK South as my secondary server location and keeping the secondary Replica as a readable copy as below,</span></span><br />
<span style="background-color: white;"><span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></span>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgt7Ka-1FaW57vxtqtnh614GMvbnDGclWjbliT2zVrKd3Nfvxm9H3_wjgrc6N4mRvBFCN6vvKyavMRsxl9EYJnCgUN3ztPh560qVqujybX2PqlHUIUmrOvtb9lH1ZD7ts9g6io9_SXQnGs/s1600/3.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="554" data-original-width="473" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgt7Ka-1FaW57vxtqtnh614GMvbnDGclWjbliT2zVrKd3Nfvxm9H3_wjgrc6N4mRvBFCN6vvKyavMRsxl9EYJnCgUN3ztPh560qVqujybX2PqlHUIUmrOvtb9lH1ZD7ts9g6io9_SXQnGs/s640/3.JPG" width="546" /></a></div>
<span style="background-color: white;"><span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></span>
<span style="background-color: white;"><span style="font-family: Arial, Helvetica, sans-serif;">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.</span></span><br />
<span style="background-color: white;"><span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></span>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgcTZCBhHrSL-D7OmDfFj3lAzDvLL7HEkmot9rBybyaPCpXIM5UXNvIHuYUglEbJoL-an0PhaD24FpemWRPBkhcKjzYu8_ltDKeB7Q-LxJt-CCDIH8KPAgqclEiEZyOIjVP8Od6ReR6I4I/s1600/4.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="762" data-original-width="1600" height="304" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgcTZCBhHrSL-D7OmDfFj3lAzDvLL7HEkmot9rBybyaPCpXIM5UXNvIHuYUglEbJoL-an0PhaD24FpemWRPBkhcKjzYu8_ltDKeB7Q-LxJt-CCDIH8KPAgqclEiEZyOIjVP8Od6ReR6I4I/s640/4.JPG" width="640" /></a></div>
<span style="background-color: white;"><span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></span>
<div class="separator" style="clear: both; text-align: left;">
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</div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh6ocFlmRywRPA7GEsjDdF312QqJPsuxxBgIRu9SY7_7Az2L0NJyfVv8TJWx7oGnZsr6XmVAJwBl120sfTa4nmIwGvSHqzvZByrU6XF-xI7w-t_LNbDP727nLCcXcf2YR1JH2u84TmOupc/s1600/5.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="558" data-original-width="1589" height="224" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh6ocFlmRywRPA7GEsjDdF312QqJPsuxxBgIRu9SY7_7Az2L0NJyfVv8TJWx7oGnZsr6XmVAJwBl120sfTa4nmIwGvSHqzvZByrU6XF-xI7w-t_LNbDP727nLCcXcf2YR1JH2u84TmOupc/s640/5.JPG" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
I am going name my Failover group as '<b><i>td-sqltestinstace-fo</i></b>' 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.</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi0sK0qXhk1oj5sXogx33yX-yXXBmTm0JePMEN48Dfgg_zQmj-S-EyrF20UiU-TLt8BQhF397JVww40bwJcMZ0H4vol8HdTrhAT9oUK7K-uba19z4-pLzMVdbLiuAEYZbjM5d3eYGwhaa4/s1600/6.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="506" data-original-width="1145" height="282" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi0sK0qXhk1oj5sXogx33yX-yXXBmTm0JePMEN48Dfgg_zQmj-S-EyrF20UiU-TLt8BQhF397JVww40bwJcMZ0H4vol8HdTrhAT9oUK7K-uba19z4-pLzMVdbLiuAEYZbjM5d3eYGwhaa4/s640/6.JPG" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<br />
<div class="separator" style="clear: both; text-align: left;">
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.</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgKa5qOJVB9GqS1Oef-q-f2Yestn6P7lzNL9SbfAUfPBzCCnK86PzlIlY1c4GjnpTXPKCGtKCH7HObhD5vAn48rOJJUQzdaR28Lj-xJmpZzICYzVpSx2kHkpuRWkxkVTd7TDv9sGRanbdk/s1600/7.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em; text-align: center;"><img border="0" data-original-height="169" data-original-width="1345" height="80" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgKa5qOJVB9GqS1Oef-q-f2Yestn6P7lzNL9SbfAUfPBzCCnK86PzlIlY1c4GjnpTXPKCGtKCH7HObhD5vAn48rOJJUQzdaR28Lj-xJmpZzICYzVpSx2kHkpuRWkxkVTd7TDv9sGRanbdk/s640/7.JPG" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi4qGBs1htHcYotxNYavJJKhaAJl-Gqv5RZsu7zLNH769RvWT-Kc_QHzXo_KiDDaXBtS_uPf1ULUe2aHWJUIuZq3nv4pPmBSeZp1NwLt3RDoCppW_hYoYtIFuvi43zTuU8js6VVsbLEn5A/s1600/8.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="907" data-original-width="1436" height="404" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi4qGBs1htHcYotxNYavJJKhaAJl-Gqv5RZsu7zLNH769RvWT-Kc_QHzXo_KiDDaXBtS_uPf1ULUe2aHWJUIuZq3nv4pPmBSeZp1NwLt3RDoCppW_hYoYtIFuvi43zTuU8js6VVsbLEn5A/s640/8.JPG" width="640" /></a></div>
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgcmerFfABtpcdKGt7eH4SgyWYBcBr4Er1j7k2VaE7HEpPun1KC-VrQRko9O3FCaLMhhSoe63GU6PivNdsx0LSkdjCrej-QwmhpVaDZXXQd4zSlJNP9KLFZGymgX2wHlB4M0pa1gkn7Ht8/s1600/9.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="375" data-original-width="918" height="260" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgcmerFfABtpcdKGt7eH4SgyWYBcBr4Er1j7k2VaE7HEpPun1KC-VrQRko9O3FCaLMhhSoe63GU6PivNdsx0LSkdjCrej-QwmhpVaDZXXQd4zSlJNP9KLFZGymgX2wHlB4M0pa1gkn7Ht8/s640/9.JPG" width="640" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
Now that we have created the Failover group, Let us try manually failing over manually,</div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiREjEMDanlmMGihUc0lx01zQ1Bo3JKCJEogS24r25TdtwYD4e1-rPGv7HAuEdK0YA-aIuK6fXsP6Xg1hLpXdOHKuZtfcv_kyib2fNnFJKsBRmbTFm47BgmFW9DI9HzPK0OlpOlRi5IbK0/s1600/10_1.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="208" data-original-width="1366" height="96" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiREjEMDanlmMGihUc0lx01zQ1Bo3JKCJEogS24r25TdtwYD4e1-rPGv7HAuEdK0YA-aIuK6fXsP6Xg1hLpXdOHKuZtfcv_kyib2fNnFJKsBRmbTFm47BgmFW9DI9HzPK0OlpOlRi5IbK0/s640/10_1.JPG" width="640" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEie5Ktu67t02pqYHU4v6BGLAOeGWqvOi5m4thqJNkek5KauYz0DHLQP1GfDtPI4aNroHpBGFwXucMLGa4jYVKiPObx8JnQIP9w3CC9qStY1Cm9p7sgvMOfL_u0Cer1YzSUjO0kAOtwj5kA/s1600/10.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="918" data-original-width="1574" height="372" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEie5Ktu67t02pqYHU4v6BGLAOeGWqvOi5m4thqJNkek5KauYz0DHLQP1GfDtPI4aNroHpBGFwXucMLGa4jYVKiPObx8JnQIP9w3CC9qStY1Cm9p7sgvMOfL_u0Cer1YzSUjO0kAOtwj5kA/s640/10.JPG" width="640" /></a></div>
<br />
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.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgF40Wt_M6In3s7UuIAYuJbNVH9zf8AVuP09WeEJc0lRfP459evYCpnbEuUG0rEsl_ZKsIv-9NfTCPsdIJZznprDp5svb8eRUTqHJvDaaMsA8upXN6S4DYvhiOXjsXCF2ZEbpX7AGkS0hs/s1600/11.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="903" data-original-width="1512" height="382" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgF40Wt_M6In3s7UuIAYuJbNVH9zf8AVuP09WeEJc0lRfP459evYCpnbEuUG0rEsl_ZKsIv-9NfTCPsdIJZznprDp5svb8eRUTqHJvDaaMsA8upXN6S4DYvhiOXjsXCF2ZEbpX7AGkS0hs/s640/11.JPG" width="640" /></a></div>
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.<br />
<br />
<a href="https://docs.microsoft.com/en-us/azure/sql-database/sql-database-designing-cloud-solutions-for-disaster-recovery">https://docs.microsoft.com/en-us/azure/sql-database/sql-database-designing-cloud-solutions-for-disaster-recovery</a></div>
Vinoth N Manoharanhttp://www.blogger.com/profile/15753172087248156870noreply@blogger.com0tag:blogger.com,1999:blog-2499604130351333669.post-22635468608688083012019-02-07T03:56:00.001-08:002019-02-07T03:57:06.929-08:00PSScript:- Modify database mail Account<div dir="ltr" style="text-align: left;" trbidi="on">
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.<br />
<br />
<script src="https://gist.github.com/nmvinoth/5d6ac4ae3642eed81063944c3453ca62.js"></script>
<br />
<br />
<i style="text-align: center;"><span style="font-family: "georgia" , "times new roman" , serif; font-size: xx-small;">Copyright © 2019 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.</span></i></div>
Vinoth N Manoharanhttp://www.blogger.com/profile/15753172087248156870noreply@blogger.com1tag:blogger.com,1999:blog-2499604130351333669.post-29106946692174441822019-02-05T03:02:00.000-08:002019-02-05T03:02:00.243-08:00DBMail - Set mail server login password failed<div dir="ltr" style="text-align: left;" trbidi="on">
Recently one of my clients complained that they were not able to modify DB mail setting and it is failing with the following error,<br />
<br />
<i><span style="color: red;">set mail server password failed for Mail Server '<servername>'</span></i><br />
<i><span style="color: red;"><br /></span></i>
<i><span style="color: red;">An error occured during Service Master Key decryption(Microsoft SQL Server, Error:33094)</span></i><br />
<i><span style="color: red;"><br /></span></i>
<i><span style="color: red;"><br /></span></i>
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.<br />
<br />
<a href="https://technet.microsoft.com/en-us/library/ms187788.aspx" style="background-color: white; color: #33aaff; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 14.85px;">https://technet.microsoft.com/en-us/library/ms187788.aspx</a><br />
<br />
<div style="background-color: white; color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 14.85px; line-height: 13.5pt; margin: 0cm 0cm 0.0001pt;">
<span style="font-family: "Segoe UI", sans-serif; font-size: 10pt;"><i><span style="color: blue;">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.<o:p></o:p></span></i></span></div>
<div style="background-color: white; color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 14.85px; line-height: 13.5pt; margin: 0cm;">
<br /></div>
<div style="background-color: white; color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 14.85px; line-height: 13.5pt; margin: 0cm 0cm 0.0001pt;">
<i><span style="color: blue;"><span style="font-family: "Segoe UI", sans-serif; font-size: 10pt;">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</span><span class="apple-converted-space"><span style="font-family: "Segoe UI", sans-serif; font-size: 10pt;"> </span></span><span style="font-family: "Segoe UI", sans-serif; font-size: 10pt;"><a href="https://technet.microsoft.com/en-us/library/ms187972.aspx" style="color: #6699cc; text-decoration-line: none;">RESTORE SERVICE MASTER KEY</a></span><span class="apple-converted-space"><span style="font-family: "Segoe UI", sans-serif; font-size: 10pt;"> </span></span></span></i><span style="font-family: "Segoe UI", sans-serif; font-size: 10pt;"><i><span style="color: blue;">statement.”</span></i><span style="color: #2a2a2a;"><o:p></o:p></span></span></div>
<div style="background-color: white; color: #333333; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 14.85px; line-height: 13.5pt; margin: 0cm 0cm 0.0001pt;">
<span style="font-family: "Segoe UI", sans-serif; font-size: 10pt;"><i><span style="color: blue;"><br /></span></i></span></div>
<div style="background-color: white; color: #333333; line-height: 13.5pt; margin: 0cm 0cm 0.0001pt;">
<span style="font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 14.85px;">Once I ran </span><span style="color: blue; font-family: Consolas; font-size: 9.5pt;"><b><i>ALTER</i></b></span><span style="font-family: Consolas; font-size: 9.5pt;"><b><i> <span style="color: blue;">SERVICE</span> <span style="color: blue;">MASTER</span> <span style="color: blue;">KEY</span> <span style="color: blue;">FORCE</span> REGENERATE</i></b> </span><span style="font-family: inherit;">the DBMail started working without any issues.</span></div>
<div>
<span style="font-family: "Segoe UI", sans-serif; font-size: 10pt;"><i><span style="color: blue;"><br /></span></i></span></div>
</div>
Vinoth N Manoharanhttp://www.blogger.com/profile/15753172087248156870noreply@blogger.com11tag:blogger.com,1999:blog-2499604130351333669.post-28776682630498798742019-01-08T03:22:00.000-08:002019-01-08T03:25:08.881-08:00Rename SQL azure Database - PowerShell<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
Below example is how we can rename an AzureDB using AzureRm module in the below example i am renaming Azure DB VinothPowerBiTest to VinothPowerShellTest,<br />
<br />
<script src="https://gist.github.com/nmvinoth/85637bd1bc13d313fa6e00bc83a37ba7.js"></script>
<br />
<div>
<div style="text-align: center;">
<i><span style="font-family: "georgia" , "times new roman" , serif; font-size: xx-small;">Copyright © 2019 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.
</span></i></div>
</div>
</div>
Vinoth N Manoharanhttp://www.blogger.com/profile/15753172087248156870noreply@blogger.com0tag:blogger.com,1999:blog-2499604130351333669.post-4989825032077661672018-07-30T05:44:00.000-07:002018-07-30T05:44:37.126-07:00Free Procedure Cache in SQL Azure Database<div dir="ltr" style="text-align: left;" trbidi="on">
To free up the procedure cache on the SQL Azure database use the following ALTER DB Statement,<br />
<br />
<span style="background-color: white; box-sizing: border-box; color: blue; font-family: "Segoe UI", Tahoma, Arial, "Helvetica Neue", Helvetica, sans-serif; font-size: 14px;">ALTER DATABASE</span><span style="background-color: white; color: #333333; font-family: "Segoe UI", Tahoma, Arial, "Helvetica Neue", Helvetica, sans-serif; font-size: 14px;"> SCOPED CONFIGURATION </span><span style="background-color: white; box-sizing: border-box; color: blue; font-family: "Segoe UI", Tahoma, Arial, "Helvetica Neue", Helvetica, sans-serif; font-size: 14px;">CLEAR </span><span style="background-color: white; color: #333333; font-family: "Segoe UI", Tahoma, Arial, "Helvetica Neue", Helvetica, sans-serif; font-size: 14px;">PROCEDURE_CACHE;</span><br />
<br />
This is same as running DBCC FREEPROCCACHE in the on-Premise SQL Instance.</div>
Vinoth N Manoharanhttp://www.blogger.com/profile/15753172087248156870noreply@blogger.com0tag:blogger.com,1999:blog-2499604130351333669.post-30545992499299717862018-02-22T06:36:00.001-08:002018-02-22T06:51:11.884-08:00SQL Azure:Powershell AzureRM Create a new database in a existing server<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="MsoNormal">
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,<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<br />
<script src="https://gist.github.com/nmvinoth/165aa0e68560224c6a7766105034ced6.js"></script>
<div>
Copyright © 2018 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.</div>
</div>
Vinoth N Manoharanhttp://www.blogger.com/profile/15753172087248156870noreply@blogger.com0tag:blogger.com,1999:blog-2499604130351333669.post-4390666363879576612017-11-28T08:53:00.002-08:002018-02-22T06:53:17.504-08:00Schedule SQL Jobs Using Azure Automation account<div dir="ltr" style="text-align: left;" trbidi="on">
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.<br />
<br />
Environment:-<br />
<br />
I am going to use my Test server 'Vinothtestsqlserver' and Test database 'VinothSampleDatabase' for this example.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhEPDRWMnRgx98egCuK3CJDKn7WT5ThOQEtuRi9CxRMMsZoUYGN-SqqubbnM7HZ4FZ_Im8XfUSnaIKXxtCOmDvxPuSrD9Iha_Jwp6acJ9ZoO-ieoNM7MOBVACZdFsfdkqf4RKIMjiReV84/s1600/Capture.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="453" data-original-width="1099" height="262" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhEPDRWMnRgx98egCuK3CJDKn7WT5ThOQEtuRi9CxRMMsZoUYGN-SqqubbnM7HZ4FZ_Im8XfUSnaIKXxtCOmDvxPuSrD9Iha_Jwp6acJ9ZoO-ieoNM7MOBVACZdFsfdkqf4RKIMjiReV84/s640/Capture.JPG" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
Login into to Azure Subscription of the SQL Server and create new Automation Account from Azure MarketPlace</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEizjflOSGZQbKCcvtoraFmr_S5d6SdwLz98nyxJjuAOFhxJCjVhg3pQ1J7z-MEL8_HydvyAdHq0wxcq9uFFi0ToPH3UFHocsjMR8JOxH_24Uy7yleai1cDQ8pbRqdGPEPwwOGs6cPRx_bE/s1600/Capture1.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="642" data-original-width="805" height="510" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEizjflOSGZQbKCcvtoraFmr_S5d6SdwLz98nyxJjuAOFhxJCjVhg3pQ1J7z-MEL8_HydvyAdHq0wxcq9uFFi0ToPH3UFHocsjMR8JOxH_24Uy7yleai1cDQ8pbRqdGPEPwwOGs6cPRx_bE/s640/Capture1.JPG" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiOF1497zNcajGMQ2WRhtefJIuqFA0RKsrk0FYADCqBsoLRJBrnQKYvFg9T76TZjKFNxiF7mDd2GTR8_nhLKf05PsSAlt1qDiI7icJJ9K67gj03y43L7UjtZ61uSfFTiLg3EKNHLZ2CtyM/s1600/Capture2.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="621" data-original-width="533" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiOF1497zNcajGMQ2WRhtefJIuqFA0RKsrk0FYADCqBsoLRJBrnQKYvFg9T76TZjKFNxiF7mDd2GTR8_nhLKf05PsSAlt1qDiI7icJJ9K67gj03y43L7UjtZ61uSfFTiLg3EKNHLZ2CtyM/s640/Capture2.JPG" width="548" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
Once the Automation Account is created, Scroll through the left tab and select Runbooks under Process Automation,</div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjYC4GT-l-VfZ3YOnjnKTNuZqXBFa7kiQWyhqe7jffbXZI7X0ORZUfeMLmpxrBX5zAdQnkFhjY0xh-TCTKfc9CmUd-MFfDuO_G0xULJ6L3bFhBFCxULzSLxFPjQ1_lUv4cHt8A-MhlcZO4/s1600/Capture3.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="626" data-original-width="1047" height="382" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjYC4GT-l-VfZ3YOnjnKTNuZqXBFa7kiQWyhqe7jffbXZI7X0ORZUfeMLmpxrBX5zAdQnkFhjY0xh-TCTKfc9CmUd-MFfDuO_G0xULJ6L3bFhBFCxULzSLxFPjQ1_lUv4cHt8A-MhlcZO4/s640/Capture3.JPG" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
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.</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhuf-lGwzYQqh6kA9bpR8svsGNl6K-ha3P-DRguXf0J4c5g59FituqSAzLX_bSH7ijU8_Yvkr2q6qhdz86MQxMTR0ugFWmrkVMCnuK7n4tGQwL89Y1M5P-RWnOOTLoPS8y36dgdGR-K_oU/s1600/Capture7.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="601" data-original-width="771" height="498" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhuf-lGwzYQqh6kA9bpR8svsGNl6K-ha3P-DRguXf0J4c5g59FituqSAzLX_bSH7ijU8_Yvkr2q6qhdz86MQxMTR0ugFWmrkVMCnuK7n4tGQwL89Y1M5P-RWnOOTLoPS8y36dgdGR-K_oU/s640/Capture7.JPG" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
I have created a Credential Named BlogTestCredential for this example,</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiNDBHkdoFwiPCa3t_sHOyql0P2etiZOJHwUGhIJQMrOir_CE-6KOVVNlOM8oNNOd-KBCibXUjSM2O3T_8opn7BmmsX8wrxNGrry0weIeij0rURsOfB80-mSHdiP5F6u-uF51OHsn3Ek8U/s1600/Capture8.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="643" data-original-width="565" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiNDBHkdoFwiPCa3t_sHOyql0P2etiZOJHwUGhIJQMrOir_CE-6KOVVNlOM8oNNOd-KBCibXUjSM2O3T_8opn7BmmsX8wrxNGrry0weIeij0rURsOfB80-mSHdiP5F6u-uF51OHsn3Ek8U/s640/Capture8.JPG" width="562" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
Now Create New Run book-This is similar to creating a new SQL agent Job</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj12fWmAX2i8NYJX5-8FgaXx-cKbz3p-eiD9kLxMIERoAB0OrD4T1GmZxtPdn_6bHwYEHep9pl5q4zvNe4KYIylws-IBWWzRG3iLPnD4Qhz9LZCoTSErfI-tgTqYKEt07mGfhqK3TTzS0w/s1600/Capture4.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="355" data-original-width="640" height="354" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj12fWmAX2i8NYJX5-8FgaXx-cKbz3p-eiD9kLxMIERoAB0OrD4T1GmZxtPdn_6bHwYEHep9pl5q4zvNe4KYIylws-IBWWzRG3iLPnD4Qhz9LZCoTSErfI-tgTqYKEt07mGfhqK3TTzS0w/s640/Capture4.JPG" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
I have named the Runbook 'VinothTestRunBook' and Will be using Powershell Workflow. SQL Jobs can be created with either Powershell or Powershell Workflow.</div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjeNv6rYRGgUuFiNbT5FvzcCSC9gCy_iDteWntTxlL14PoVDSWbnA_NfZasSDpipXalD40BZnMxJNUF6GLu8tprLtWjvSXP0didtD5CN4KJg4sCfN2MmUoJbAqUKJrNhso68HcMwbX_Lms/s1600/Capture5.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="357" data-original-width="673" height="338" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjeNv6rYRGgUuFiNbT5FvzcCSC9gCy_iDteWntTxlL14PoVDSWbnA_NfZasSDpipXalD40BZnMxJNUF6GLu8tprLtWjvSXP0didtD5CN4KJg4sCfN2MmUoJbAqUKJrNhso68HcMwbX_Lms/s640/Capture5.JPG" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
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.</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
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.</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<script src="https://gist.github.com/nmvinoth/0da038a8ab4511619e04d20429c35e6d.js"></script>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
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.</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand%28v=vs.110%29.aspx</div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjK8DsheFlAsSWhmqyvX5GYjJZ0EGCqvz0Th48zDVpw9wF0A_PrUf5iqXmtfKPjvD1XRbhJ352h9IJNn5GZnP36P1L50RC2HTuzlU4jpLiEIuT9ZD_qRBQU3MxWCZTtt3swcvC9xlbta0k/s1600/Capture15.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="532" data-original-width="1105" height="308" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjK8DsheFlAsSWhmqyvX5GYjJZ0EGCqvz0Th48zDVpw9wF0A_PrUf5iqXmtfKPjvD1XRbhJ352h9IJNn5GZnP36P1L50RC2HTuzlU4jpLiEIuT9ZD_qRBQU3MxWCZTtt3swcvC9xlbta0k/s640/Capture15.JPG" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
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.</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgNjI3SgA4o2y6t3JpgUB2z4VfcE7-__YIk_Vsosl09AwNLIbLZJ0PIU3jqHzlovzpAP_-A5Q8zaafthFLYaAnZeTuf57DLVqh005Z9UslTU3ypsfWA9ueF6e85O8zln0VsqylciSEZz4c/s1600/Capture9.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="596" data-original-width="1329" height="286" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgNjI3SgA4o2y6t3JpgUB2z4VfcE7-__YIk_Vsosl09AwNLIbLZJ0PIU3jqHzlovzpAP_-A5Q8zaafthFLYaAnZeTuf57DLVqh005Z9UslTU3ypsfWA9ueF6e85O8zln0VsqylciSEZz4c/s640/Capture9.JPG" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
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. </div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiv6o0wj_c0DS7YDg_s34aEXEA0vtb2p65kfK_1DR_S4ltb-a8fq_OEW0dhxwXjMN1SzL-pAypIWTLb40dIC13INJSIKqglmUFKQJ5RusGgdqJxPC-oo_6lxSwS7THMaUgjAibjS553Nmg/s1600/Capture10.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="468" data-original-width="812" height="368" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiv6o0wj_c0DS7YDg_s34aEXEA0vtb2p65kfK_1DR_S4ltb-a8fq_OEW0dhxwXjMN1SzL-pAypIWTLb40dIC13INJSIKqglmUFKQJ5RusGgdqJxPC-oo_6lxSwS7THMaUgjAibjS553Nmg/s640/Capture10.JPG" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
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,</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjjlKqAf9bIxss7_MyxMaQ64TxDtzT-_XTam0l894yc7_HV8Gjqv991YPLxLxkS66igS8FyK5NGibvAuP1M8Db95foq_3zmVQkALVkOSpo8a1sO0TCo15m4GrCWxMG1IQ9UnYrXfBIMURo/s1600/Capture11.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="554" data-original-width="637" height="556" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjjlKqAf9bIxss7_MyxMaQ64TxDtzT-_XTam0l894yc7_HV8Gjqv991YPLxLxkS66igS8FyK5NGibvAuP1M8Db95foq_3zmVQkALVkOSpo8a1sO0TCo15m4GrCWxMG1IQ9UnYrXfBIMURo/s640/Capture11.JPG" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh24vthJzDAuuvvUMWPsV5dzfIlNOXGR7yWYlmX50pfBa30kGvUClwypMJxg6JAj-d0vJl3OjiQfuQVg-kfirvDGsDAfvsUYFFDfYqJ7MPI4V9Il5hE1uOzpUF2UJj6hjYhMv_rfgIUeiU/s1600/Capture12.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="628" data-original-width="1191" height="336" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh24vthJzDAuuvvUMWPsV5dzfIlNOXGR7yWYlmX50pfBa30kGvUClwypMJxg6JAj-d0vJl3OjiQfuQVg-kfirvDGsDAfvsUYFFDfYqJ7MPI4V9Il5hE1uOzpUF2UJj6hjYhMv_rfgIUeiU/s640/Capture12.JPG" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
Once you Create the Schedule Specify the Parameters for the Run book Schedule as below,</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjhBHNwiTga7HLuwbVaUmFh-BB1C-p_9MwgH7LyLF1jh-Zo1cq631T3_FlA6yWoDFzU3Mi1kzbXZXsZUIIhf-2b-NTKhbrNdo3Sqxc4iOCdcDfx2HTJtldtADhc28UI4XLfZF_KfAS-Aew/s1600/Capture13.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="429" data-original-width="887" height="308" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjhBHNwiTga7HLuwbVaUmFh-BB1C-p_9MwgH7LyLF1jh-Zo1cq631T3_FlA6yWoDFzU3Mi1kzbXZXsZUIIhf-2b-NTKhbrNdo3Sqxc4iOCdcDfx2HTJtldtADhc28UI4XLfZF_KfAS-Aew/s640/Capture13.JPG" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
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</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjtk-g2VLSurz6l-LX9QRlcyeg4WKwoo4qMhLJnQYCi9zS_ciYS7M4U1H4LgoIvmWG5m4nvkhBXsfw1A8foQvfnX8Gr7TxFoJF3qHGry6KqOG07CQRC1JMiNZr8zYcTQhBR4dlkvFO25ZA/s1600/Capture16.JPG" imageanchor="1"><img border="0" height="338" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjtk-g2VLSurz6l-LX9QRlcyeg4WKwoo4qMhLJnQYCi9zS_ciYS7M4U1H4LgoIvmWG5m4nvkhBXsfw1A8foQvfnX8Gr7TxFoJF3qHGry6KqOG07CQRC1JMiNZr8zYcTQhBR4dlkvFO25ZA/s640/Capture16.JPG" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
Now you can see summary of a completed job, for detailed information click on the summary</div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj0OtOFYMkBUD4srLzCexJop0TVFMZJeNVZPsqSy9DqLPgpdrSYbLvtvhE1-ijiOnFWjXHn6oRJSimnSmK2b7mHurm2oLD0dceIuvC6MWdXp_bhkvBDfyCrEcuaPtKszDSFqnUDeuCwZzY/s1600/Capture14.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="259" data-original-width="604" height="274" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj0OtOFYMkBUD4srLzCexJop0TVFMZJeNVZPsqSy9DqLPgpdrSYbLvtvhE1-ijiOnFWjXHn6oRJSimnSmK2b7mHurm2oLD0dceIuvC6MWdXp_bhkvBDfyCrEcuaPtKszDSFqnUDeuCwZzY/s640/Capture14.JPG" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiBffc1zl0-vsbvoByhvBVbv_ZveWqKj48wYkcFnnFulr0eqW1vmzTEX7q97ve__mnnYG2Ns7M4lVXH0GcQgNtYWCYJYO4uSetvFjI17bFvnMnG8zl7MD3tPuZricbp_5mtODTZNc5u4h0/s1600/Capture17.JPG" imageanchor="1"><img border="0" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiBffc1zl0-vsbvoByhvBVbv_ZveWqKj48wYkcFnnFulr0eqW1vmzTEX7q97ve__mnnYG2Ns7M4lVXH0GcQgNtYWCYJYO4uSetvFjI17bFvnMnG8zl7MD3tPuZricbp_5mtODTZNc5u4h0/s640/Capture17.JPG" width="634" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
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.</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiyVzN3QHJC1loosz6xnDm5BbA__VMjPAzLEQfV0l2cakYFv5szbVxFiBwHjxdkvXgrH8l08akYY0O9kuhVNEJu_bwxgrCt69nC0rT9_ZxOPuCSKybDTyZr55OPDNoBAbUqSMqhZcjnUGc/s1600/Capture18.JPG" imageanchor="1"><img border="0" height="376" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiyVzN3QHJC1loosz6xnDm5BbA__VMjPAzLEQfV0l2cakYFv5szbVxFiBwHjxdkvXgrH8l08akYY0O9kuhVNEJu_bwxgrCt69nC0rT9_ZxOPuCSKybDTyZr55OPDNoBAbUqSMqhZcjnUGc/s640/Capture18.JPG" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
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.</div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
</div>
Vinoth N Manoharanhttp://www.blogger.com/profile/15753172087248156870noreply@blogger.com0tag:blogger.com,1999:blog-2499604130351333669.post-1053525050909702072017-09-11T08:30:00.000-07:002017-09-11T08:30:34.416-07:00Replication - Compatibility issue<div dir="ltr" style="text-align: left;" trbidi="on">
I was working on a POC concept for my client to test some replication functionality and got the below error,<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj1YESee3KAzzCmfijhpgz6w2psnxCcBG9WvFrvLxoTXIgMR7LPLMQcx87VPi9k9JSXM0nqFq0h9icS-cMlvjAUsKjovw3rj3oIeVYO_9zWfGraFwo2BsCO9SGNXHGznsLtL_CN3BcZee4/s1600/rep.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="128" data-original-width="607" height="134" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj1YESee3KAzzCmfijhpgz6w2psnxCcBG9WvFrvLxoTXIgMR7LPLMQcx87VPi9k9JSXM0nqFq0h9icS-cMlvjAUsKjovw3rj3oIeVYO_9zWfGraFwo2BsCO9SGNXHGznsLtL_CN3BcZee4/s640/rep.JPG" width="640" /></a></div>
<br />
<br />
"he selected subscriber does not satisfy the minimum version compatibility level"<br />
<br />
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.</div>
Vinoth N Manoharanhttp://www.blogger.com/profile/15753172087248156870noreply@blogger.com0tag:blogger.com,1999:blog-2499604130351333669.post-13029978186855814532017-06-27T02:36:00.000-07:002017-06-27T02:36:55.098-07:00Run SSMS As User in Different Domain<div dir="ltr" style="text-align: left;" trbidi="on">
Every come across the trouble of having to login to a jump server in just to access SSMS since the server is in different domain. There is a way to by pass it and access SSMS using your second domain login from your desktop by using Runas /NetOnly option irrespective of domain trust.<br />
<br />
Use the below command in the Command Prompt:-<br />
<br />
<span style="font-family: "Times New Roman", serif;"><b><i><span style="color: blue;">runas
/netonly /user:<domain\username> "<SSMSbasepath>\ssms.exe"</span></i></b></span><br />
<br />
SSMS base path is the folder where your SSMS.exe is located.<br />
<br />
More Info <a href="https://technet.microsoft.com/en-gb/library/bb490994.aspx">https://technet.microsoft.com/en-gb/library/bb490994.aspx</a><br />
<br />
<br />
<br />
<br /></div>
Vinoth N Manoharanhttp://www.blogger.com/profile/15753172087248156870noreply@blogger.com0tag:blogger.com,1999:blog-2499604130351333669.post-86815889449925858902017-05-30T08:14:00.003-07:002017-05-30T08:14:39.046-07:00Find SSAS instance Port Number <div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: left;">
<span style="font-family: Arial, Helvetica, sans-serif;">When you have more than one instance of SSAS installed in your Server you might end up in a situation to find the port numbers for each instance to set the firewall settings on the server. As you might all know the default Port for SSAS is TCP 2383, </span></div>
<div style="text-align: left;">
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></div>
<div style="text-align: left;">
<span style="font-family: Arial, Helvetica, sans-serif;">To find the port number of the other instances search for the <span style="background-color: white;">msmdredir.ini file on your ProgramFiles(X86)-></span><span style="background-color: white;">Microsoft SQL Server folder, most probably the file resides in the (</span><span style="background-color: white; font-weight: 600;">%ProgramFiles(X86)%\Microsoft SQL Server\90\Shared\ASConfig\msmdredir.ini)</span><span style="background-color: white;">location, Look in both 64 bit and 32 bit Program Files</span><span style="background-color: white;"> folder but more likely to be in 32 bit folder even for 64 bit installations.</span></span></div>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">If you have more than 1 Instance you will have entry in the ini file like below, If you change the port number to 0 it would take dynamic ports else you can assign a static port to the instance.</span><br />
<span style="background-color: white; font-weight: 600;"><span style="color: blue; font-family: Arial, Helvetica, sans-serif;"><br /></span></span>
<span style="color: blue; font-family: Arial, Helvetica, sans-serif;"><span style="font-weight: 600;"><span class="Apple-tab-span" style="white-space: pre;"> </span><Instances></span></span><br />
<span style="color: blue; font-family: Arial, Helvetica, sans-serif;"><span style="font-weight: 600;"><span class="Apple-tab-span" style="white-space: pre;"> </span><Instance></span></span><br />
<span style="color: blue; font-family: Arial, Helvetica, sans-serif;"><span style="font-weight: 600;"><span class="Apple-tab-span" style="white-space: pre;"> </span><Name>InstanceName</Name></span></span><br />
<span style="color: blue; font-family: Arial, Helvetica, sans-serif;"><span style="font-weight: 600;"><span class="Apple-tab-span" style="white-space: pre;"> </span><Port>64931</Port></span></span><br />
<span style="color: blue; font-family: Arial, Helvetica, sans-serif;"><span style="font-weight: 600;"><span class="Apple-tab-span" style="white-space: pre;"> </span><PortIPv6>64931</PortIPv6></span></span><br />
<span style="color: blue; font-family: Arial, Helvetica, sans-serif;"><span style="font-weight: 600;"><span class="Apple-tab-span" style="white-space: pre;"> </span></Instance></span></span><br />
<span style="color: blue; font-family: Arial, Helvetica, sans-serif;"><span style="background-color: white; font-weight: 600;"></span></span><br />
<span style="color: blue; font-family: Arial, Helvetica, sans-serif;"><span style="font-weight: 600;"><span class="Apple-tab-span" style="white-space: pre;"> </span></Instances></span></span><br />
<span style="color: blue; font-family: Arial, Helvetica, sans-serif;"><span style="font-weight: 600;"><br /></span></span>
<span style="font-family: Arial, Helvetica, sans-serif;">The Above exercise will hold good only for Non-Clustered SSAS instance, Clustered SSAS instance always uses TCP Port 2383 with the corresponding SSAS Cluster Resouce IP.</span></div>
Vinoth N Manoharanhttp://www.blogger.com/profile/15753172087248156870noreply@blogger.com0tag:blogger.com,1999:blog-2499604130351333669.post-10332697636189776332017-03-23T07:53:00.002-07:002017-03-23T07:54:59.332-07:00SQL Azure database - dbmanager and loginmanager role<div dir="ltr" style="text-align: left;" trbidi="on">
Recently I was working for a client to install a new application with a backend on SQL Azure (Paas), The application was an inbuilt application which creates a database during application and does not have an option of using existing database, They did not want to use the Server Admin Login to accomplish this task. Unlike traditional SQL Server you can configure only one server Admin in the Azure Portal for SQL Azure Database as below.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhrZpeXjukJMSmwIklTukR1R7uP2iipnfMxgDNEuL4juuD_aoVqH43bL_Zc02a4KgLjDPMC52JEkczl3hyCgzdye5w5TZXxK8NiCkC_HoNSx65xsoPoXgChHtrK2ixcB3sA_hW6X117hDE/s1600/Blog.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="342" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhrZpeXjukJMSmwIklTukR1R7uP2iipnfMxgDNEuL4juuD_aoVqH43bL_Zc02a4KgLjDPMC52JEkczl3hyCgzdye5w5TZXxK8NiCkC_HoNSx65xsoPoXgChHtrK2ixcB3sA_hW6X117hDE/s640/Blog.JPG" width="640" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<br />
And Since we do not have a server level entity in Azure we do not have any server level roles to provide a user with dbcreator. There alternate way to accomplish this according to MSDN( <a href="https://docs.microsoft.com/en-us/azure/sql-database/sql-database-manage-logins">https://docs.microsoft.com/en-us/azure/sql-database/sql-database-manage-logins</a> ) there are two 2 new roles in the master database for SQL Server (<b><span style="font-size: large;">dbmanager and loginmanager</span></b>)<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhOGgmMlsmrEjPNAwXW5DxAoadJuLyW3Lsbn5IVG2HHXRDtuJUSf3PIxncxWn_XOD8MpN3jRJ9qrlv8t3S2GnxB_o5yrFBnrIW-Xx7rJwm4UN54exsqkJEfVsMnrj5lKLvkfU6pl4DdEI4/s1600/Blog.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhOGgmMlsmrEjPNAwXW5DxAoadJuLyW3Lsbn5IVG2HHXRDtuJUSf3PIxncxWn_XOD8MpN3jRJ9qrlv8t3S2GnxB_o5yrFBnrIW-Xx7rJwm4UN54exsqkJEfVsMnrj5lKLvkfU6pl4DdEI4/s640/Blog.JPG" width="474" /></a></div>
<br />
'dbmanager' allows users to create databases in Azure environment and loginmanager as the name suggests allows you to create new login without being a serveradmin. All you need to do is to create a login and corresponding user in master and grant it dbmanager role for the user to get dbcreator rights on SQL Azure, once we did this we were able to install the application without any issues and the new DB was created successfully.<br />
<br /></div>
Vinoth N Manoharanhttp://www.blogger.com/profile/15753172087248156870noreply@blogger.com71tag:blogger.com,1999:blog-2499604130351333669.post-52152232402382327482017-02-21T06:29:00.002-08:002017-02-21T06:29:38.885-08:00Quick Tips:- Add SQL User/Rolemember to all Databases in SQL Server<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt; text-align: left;">
<span style="font-family: Arial, Helvetica, sans-serif;">Most of times when you have dedicated SQL Server for an Application
clients come up with a request to add a user to all databases in the SQL Server
with certain permission, while its east to do it via SSMS for if the system has
only couple of databases but imagine your SQL instance have hundreds of
databases then scripting is the way to go, I have assumed the login name is <b><span style="color: #4472c4;">‘Test’</span></b><span style="color: #4472c4;"> </span>client has request <b><span style="color: #4472c4;">db_datareader and db_datawriter</span></b><span style="color: #4472c4;"> </span>permission for our
example blog,<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt; text-align: left;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt; text-align: left;">
<span style="font-family: Arial, Helvetica, sans-serif;">There are 2 ways I am going to accomplish this one using TSQL and
another using Powershell.</span><span style="font-family: Times New Roman, serif; font-size: 13.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm;">
<span style="font-family: "Times New Roman", serif; font-size: 13.5pt;"><br /></span></div>
<div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;">
<b><u><span style="font-family: "Times New Roman",serif; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB;">TSQL:-</span></u></b><span style="font-family: "Times New Roman",serif; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm;">
<span style="background: white; color: green; font-family: Consolas; font-size: 9.5pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB;">--Add
User</span><span style="font-family: "Times New Roman", serif; font-size: 13.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm;">
<span style="background: white; color: blue; font-family: Consolas; font-size: 9.5pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB;">EXEC</span><span style="background: white; font-family: Consolas; font-size: 9.5pt;"> </span><span style="background: white; color: maroon; font-family: Consolas; font-size: 9.5pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB;">sp_MSforeachdb</span><span style="background: white; color: blue; font-family: Consolas; font-size: 9.5pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB;"> </span><span style="background: white; color: red; font-family: Consolas; font-size: 9.5pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB;">'USE ?;CREATE USER Test
FOR LOGIN Test'</span><span style="font-family: "Times New Roman", serif; font-size: 13.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm;">
<span style="background: white; color: blue; font-family: Consolas; font-size: 9.5pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB;">GO</span><span style="font-family: "Times New Roman", serif; font-size: 13.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm;">
<span style="background: white; color: green; font-family: Consolas; font-size: 9.5pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB;">--Add
Role member</span><span style="font-family: "Times New Roman", serif; font-size: 13.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm;">
<span style="background: white; color: blue; font-family: Consolas; font-size: 9.5pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB;">EXEC</span><span style="background: white; font-family: Consolas; font-size: 9.5pt;"> </span><span style="background: white; color: maroon; font-family: Consolas; font-size: 9.5pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB;">sp_MSforeachdb</span><span style="background: white; color: blue; font-family: Consolas; font-size: 9.5pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB;"> </span><span style="background: white; color: red; font-family: Consolas; font-size: 9.5pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB;">'USE ?;ALTER ROLE
db_datareader ADD MEMBER Test'</span><span style="font-family: "Times New Roman", serif; font-size: 13.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm;">
<span style="background: white; color: blue; font-family: Consolas; font-size: 9.5pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB;">GO</span><span style="font-family: "Times New Roman", serif; font-size: 13.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm;">
<span style="background: white; color: green; font-family: Consolas; font-size: 9.5pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB;">--Add
Role member</span><span style="font-family: "Times New Roman", serif; font-size: 13.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm;">
<span style="background: white; color: blue; font-family: Consolas; font-size: 9.5pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB;">EXEC</span><span style="background: white; font-family: Consolas; font-size: 9.5pt;"> </span><span style="background: white; color: maroon; font-family: Consolas; font-size: 9.5pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB;">sp_MSforeachdb</span><span style="background: white; color: blue; font-family: Consolas; font-size: 9.5pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB;"> </span><span style="background: white; color: red; font-family: Consolas; font-size: 9.5pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB;">'USE ?;ALTER ROLE
db_datawriter ADD MEMBER Test'</span><span style="font-family: "Times New Roman", serif; font-size: 13.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm;">
<br /></div>
<div style="line-height: normal; text-align: left;">
<b><u><span style="font-family: "Times New Roman",serif; font-size: 12.0pt; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB;">Powershell:-</span></u></b></div>
<div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;">
<span style="color: cadetblue; font-family: "Courier New"; font-size: 10pt;">Clear-Host</span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<b><span style="color: cadetblue; font-family: "Courier New"; font-size: 10.0pt;">Import-Module</span></b><span style="font-family: "Courier New"; font-size: 10pt;"> </span><span style="color: maroon; font-family: "Courier New"; font-size: 10.0pt;">SQLPS</span><span style="font-family: "Courier New"; font-size: 10pt;"> </span><i><span style="color: cadetblue; font-family: "Courier New"; font-size: 10.0pt;">-DisableNameChecking</span></i><span style="font-family: "Courier New"; font-size: 10pt;"> </span><i><span style="color: cadetblue; font-family: "Courier New"; font-size: 10.0pt;">-ErrorAction</span></i><span style="font-family: "Courier New"; font-size: 10pt;"> </span><span style="color: maroon; font-family: "Courier New"; font-size: 10.0pt;">SilentlyContinue</span><span style="font-family: "Courier New"; font-size: 10pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: purple; font-family: "Courier New"; font-size: 10.0pt;">$srv</span><span style="font-family: "Courier New"; font-size: 10pt;">=</span><b><span style="color: cadetblue; font-family: "Courier New"; font-size: 10.0pt;">New-Object</span></b><span style="font-family: "Courier New"; font-size: 10pt;"> </span><span style="color: maroon; font-family: "Courier New"; font-size: 10.0pt;">"Microsoft.SqlServer.Management.Smo.Server"</span><span style="font-family: "Courier New"; font-size: 10pt;"> </span><span style="color: maroon; font-family: "Courier New"; font-size: 10.0pt;">"1MKBG12"</span><span style="font-family: "Courier New"; font-size: 10pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: purple; font-family: "Courier New"; font-size: 10.0pt;">$dbs</span><span style="font-family: "Courier New"; font-size: 10pt;"> </span><span style="color: red; font-family: "Courier New"; font-size: 10.0pt;">=</span><span style="font-family: "Courier New"; font-size: 10pt;"> </span><span style="color: purple; font-family: "Courier New"; font-size: 10.0pt;">$srv</span><span style="font-family: "Courier New"; font-size: 10pt;">.</span><span style="color: saddlebrown; font-family: "Courier New"; font-size: 10.0pt;">databases</span><span style="font-family: "Courier New"; font-size: 10pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: purple; font-family: "Courier New"; font-size: 10.0pt;">$loginname</span><span style="font-family: "Courier New"; font-size: 10pt;"> </span><span style="color: red; font-family: "Courier New"; font-size: 10.0pt;">=</span><span style="font-family: "Courier New"; font-size: 10pt;"> </span><span style="color: maroon; font-family: "Courier New"; font-size: 10.0pt;">'Test'</span><span style="font-family: "Courier New"; font-size: 10pt;"> </span><span style="color: green; font-family: "Courier New"; font-size: 10.0pt;">##########Provide
Login name to check#############<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: purple; font-family: "Courier New"; font-size: 10.0pt;">$chkloginflag</span><span style="font-family: "Courier New"; font-size: 10pt;"> </span><span style="color: red; font-family: "Courier New"; font-size: 10.0pt;">=</span><span style="font-family: "Courier New"; font-size: 10pt;"> </span><span style="color: purple; font-family: "Courier New"; font-size: 10.0pt;">$null</span><span style="font-family: "Courier New"; font-size: 10pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: purple; font-family: "Courier New"; font-size: 10.0pt;">$chkloginflag</span><span style="font-family: "Courier New"; font-size: 10pt;"> </span><span style="color: red; font-family: "Courier New"; font-size: 10.0pt;">=</span><span style="font-family: "Courier New"; font-size: 10pt;"> </span><span style="color: purple; font-family: "Courier New"; font-size: 10.0pt;">$srv</span><span style="font-family: "Courier New"; font-size: 10pt;">.Logins|</span><b><span style="color: cadetblue; font-family: "Courier New"; font-size: 10.0pt;">where</span></b><span style="font-family: "Courier New"; font-size: 10pt;"> {</span><span style="color: purple; font-family: "Courier New"; font-size: 10.0pt;">$_</span><span style="font-family: "Courier New"; font-size: 10pt;">.name </span><span style="color: red; font-family: "Courier New"; font-size: 10.0pt;">-eq</span><span style="font-family: "Courier New"; font-size: 10pt;"> </span><span style="color: maroon; font-family: "Courier New"; font-size: 10.0pt;">'Test3'</span><span style="font-family: "Courier New"; font-size: 10pt;">}<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: "Courier New"; font-size: 10.0pt;">if</span><span style="font-family: "Courier New"; font-size: 10pt;">(</span><span style="color: purple; font-family: "Courier New"; font-size: 10.0pt;">$chkloginflag</span><span style="font-family: "Courier New"; font-size: 10pt;"> </span><span style="color: red; font-family: "Courier New"; font-size: 10.0pt;">-eq</span><span style="font-family: "Courier New"; font-size: 10pt;"> </span><span style="color: purple; font-family: "Courier New"; font-size: 10.0pt;">$null</span><span style="font-family: "Courier New"; font-size: 10pt;"> </span><span style="color: red; font-family: "Courier New"; font-size: 10.0pt;">-or</span><span style="font-family: "Courier New"; font-size: 10pt;"> </span><span style="color: purple; font-family: "Courier New"; font-size: 10.0pt;">$chkloginflag</span><span style="font-family: "Courier New"; font-size: 10pt;"> </span><span style="color: red; font-family: "Courier New"; font-size: 10.0pt;">-eq</span><span style="font-family: "Courier New"; font-size: 10pt;"> </span><span style="color: maroon; font-family: "Courier New"; font-size: 10.0pt;">''</span><span style="font-family: "Courier New"; font-size: 10pt;">)<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10pt;">{ <o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10pt;"> </span><span style="color: purple; font-family: "Courier New"; font-size: 10.0pt;">$login</span><span style="font-family: "Courier New"; font-size: 10pt;"> </span><span style="color: red; font-family: "Courier New"; font-size: 10.0pt;">=</span><span style="font-family: "Courier New"; font-size: 10pt;"> </span><b><span style="color: cadetblue; font-family: "Courier New"; font-size: 10.0pt;">New-Object</span></b><span style="font-family: "Courier New"; font-size: 10pt;"> (</span><span style="color: maroon; font-family: "Courier New"; font-size: 10.0pt;">'Microsoft.SqlServer.Management.Smo.Login'</span><span style="font-family: "Courier New"; font-size: 10pt;">) (</span><span style="color: purple; font-family: "Courier New"; font-size: 10.0pt;">$srv</span><span style="font-family: "Courier New"; font-size: 10pt;">, </span><span style="color: purple; font-family: "Courier New"; font-size: 10.0pt;">$loginname</span><span style="font-family: "Courier New"; font-size: 10pt;">)<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10pt;"> </span><span style="color: purple; font-family: "Courier New"; font-size: 10.0pt;">$login</span><span style="font-family: "Courier New"; font-size: 10pt;">.</span><span style="color: saddlebrown; font-family: "Courier New"; font-size: 10.0pt;">LoginType</span><span style="font-family: "Courier New"; font-size: 10pt;"> </span><span style="color: red; font-family: "Courier New"; font-size: 10.0pt;">=</span><span style="font-family: "Courier New"; font-size: 10pt;"> </span><span style="color: maroon; font-family: "Courier New"; font-size: 10.0pt;">'SqlLogin'</span><span style="font-family: "Courier New"; font-size: 10pt;"> </span><span style="color: green; font-family: "Courier New"; font-size: 10.0pt;">#https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.logintype.aspx<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10pt;"> </span><span style="color: purple; font-family: "Courier New"; font-size: 10.0pt;">$login</span><span style="font-family: "Courier New"; font-size: 10pt;">.</span><span style="color: saddlebrown; font-family: "Courier New"; font-size: 10.0pt;">PasswordExpirationEnabled</span><span style="font-family: "Courier New"; font-size: 10pt;"> </span><span style="color: red; font-family: "Courier New"; font-size: 10.0pt;">=</span><span style="font-family: "Courier New"; font-size: 10pt;"> </span><span style="color: purple; font-family: "Courier New"; font-size: 10.0pt;">$false</span><span style="font-family: "Courier New"; font-size: 10pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10pt;"> </span><span style="color: purple; font-family: "Courier New"; font-size: 10.0pt;">$Login</span><span style="font-family: "Courier New"; font-size: 10pt;">.</span><span style="color: saddlebrown; font-family: "Courier New"; font-size: 10.0pt;">PasswordPolicyEnforced</span><span style="font-family: "Courier New"; font-size: 10pt;"> </span><span style="color: red; font-family: "Courier New"; font-size: 10.0pt;">=</span><span style="font-family: "Courier New"; font-size: 10pt;"> </span><span style="color: purple; font-family: "Courier New"; font-size: 10.0pt;">$false</span><span style="font-family: "Courier New"; font-size: 10pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10pt;"> </span><span style="color: purple; font-family: "Courier New"; font-size: 10.0pt;">$login</span><span style="font-family: "Courier New"; font-size: 10pt;">.</span><span style="color: saddlebrown; font-family: "Courier New"; font-size: 10.0pt;">Create</span><span style="font-family: "Courier New"; font-size: 10pt;">(</span><span style="color: maroon; font-family: "Courier New"; font-size: 10.0pt;">"test"</span><span style="font-family: "Courier New"; font-size: 10pt;">)<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10pt;"> </span><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt;">forEach</span><span style="font-family: "Courier New"; font-size: 10pt;">(</span><span style="color: purple; font-family: "Courier New"; font-size: 10.0pt;">$db</span><span style="font-family: "Courier New"; font-size: 10pt;"> </span><span style="color: blue; font-family: "Courier New"; font-size: 10.0pt;">in</span><span style="font-family: "Courier New"; font-size: 10pt;"> </span><span style="color: purple; font-family: "Courier New"; font-size: 10.0pt;">$dbs</span><span style="font-family: "Courier New"; font-size: 10pt;">)<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10pt;"> {<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10pt;"> </span><span style="color: purple; font-family: "Courier New"; font-size: 10.0pt;">$usr</span><span style="font-family: "Courier New"; font-size: 10pt;"> </span><span style="color: red; font-family: "Courier New"; font-size: 10.0pt;">=</span><span style="font-family: "Courier New"; font-size: 10pt;"> </span><b><span style="color: cadetblue; font-family: "Courier New"; font-size: 10.0pt;">New-Object</span></b><span style="font-family: "Courier New"; font-size: 10pt;"> (</span><span style="color: maroon; font-family: "Courier New"; font-size: 10.0pt;">'Microsoft.SqlServer.Management.Smo.User'</span><span style="font-family: "Courier New"; font-size: 10pt;">) (</span><span style="color: purple; font-family: "Courier New"; font-size: 10.0pt;">$db</span><span style="font-family: "Courier New"; font-size: 10pt;">, </span><span style="color: purple; font-family: "Courier New"; font-size: 10.0pt;">$loginname</span><span style="font-family: "Courier New"; font-size: 10pt;">)<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10pt;"> </span><span style="color: purple; font-family: "Courier New"; font-size: 10.0pt;">$usr</span><span style="font-family: "Courier New"; font-size: 10pt;">.</span><span style="color: saddlebrown; font-family: "Courier New"; font-size: 10.0pt;">create</span><span style="font-family: "Courier New"; font-size: 10pt;">()<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10pt;"> </span><span style="color: purple; font-family: "Courier New"; font-size: 10.0pt;">$usr</span><span style="font-family: "Courier New"; font-size: 10pt;">.</span><span style="color: saddlebrown; font-family: "Courier New"; font-size: 10.0pt;">AddToRole</span><span style="font-family: "Courier New"; font-size: 10pt;">(</span><span style="color: maroon; font-family: "Courier New"; font-size: 10.0pt;">"db_datareader"</span><span style="font-family: "Courier New"; font-size: 10pt;">)<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10pt;"> </span><span style="color: purple; font-family: "Courier New"; font-size: 10.0pt;">$usr</span><span style="font-family: "Courier New"; font-size: 10pt;">.</span><span style="color: saddlebrown; font-family: "Courier New"; font-size: 10.0pt;">AddToRole</span><span style="font-family: "Courier New"; font-size: 10pt;">(</span><span style="color: maroon; font-family: "Courier New"; font-size: 10.0pt;">"db_datawriter"</span><span style="font-family: "Courier New"; font-size: 10pt;">)<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10pt;"> }<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10pt;">}<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<br /></div>
<div style="line-height: normal; margin-bottom: 0.0001pt; text-align: left;">
<span style="font-family: Arial, Helvetica, sans-serif;">The above PowerShell script also
creates a Login if it does not exist then creates the SQL user in all databases
with reader and writer roles. I have written a simple script assuming the Login
to be SQL Authentication. You can tweak the $Login object to create Windows
User/Group Login. </span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; mso-layout-grid-align: none; text-autospace: none;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm;">
<br /></div>
<br />
<div class="MsoNormal">
<br /></div>
</div>
Vinoth N Manoharanhttp://www.blogger.com/profile/15753172087248156870noreply@blogger.com0