Archives du mot-clé Powershell

Extending SQL Server monitoring with Raspberry PI and Lametric

Mis en avant

Publié le par
Répondre

First blog of this new year 2021 and I will start with a fancy and How-To Geek topic

In my last blog post, I discussed about monitoring and how it should help to address quickly a situation that is going degrading. Alerts are probably the first way to raise your attention and, in my case, they are often in the form of emails in a dedicated folder. That remains a good thing, at least if you’re not focusing too long in other daily tasks or projects. In work office, I know I would probably better focus on new alerts but as I said previously, telework changed definitely the game.

Lire la suite →

AAD user creation on behalf AAD Service Principal with Azure SQL DB

Mis en avant

Publié le par
2

An interesting improvement was announced by the SQL AAD team on Monday 27th July 2020 and concerns the support for Azure AD user creation on behalf of Azure AD Applications for Azure SQL as mentioned to this Microsoft blog post.

Lire la suite →

dbachecks and AlwaysOn availability group checks

Mis en avant

Publié le par
1

When I started my DBA position in my new company, I was looking for a tool that was able to check periodically the SQL Server database environments for several reasons. First, as DBA one of my main concern is about maintaining and keeping the different mssql environments well-configured against an initial standard. It is also worth noting I’m not the only person to interact with databases and anyone in my team, which is member of sysadmin server role as well, is able to change any server-level configuration settings at any moment. In this case, chances are that having environments shifting from our initial standard over the time and my team and I need to keep confident by checking periodically the current mssql environment configurations, be alerting if configuration drifts exist and obviously fix it as faster as possible.

Lire la suite →

Database maintenance thoughts with Azure SQL databases

Mis en avant

Publié le par
1

As DBA, your priority is to ensure your data are consistent, safely backed up and you get steady performance of your database. In on-prem environments, these tasks are generally performed through scheduled jobs including backups, check integrity and index / statistics maintenance tasks.

But moving databases to the cloud in Azure (and others) tells a different story. Indeed, even if the same concern and tasks remain, some of them are under the responsibility of the Cloud provider and some other ones not. If you’re working with Azure SQL databases – like me – some questions raise very quickly on this topic and it was my motivation to write this write-up. I would like to share with you some new experiences by digging into the different maintenance items. If you have a different story to tell, please feel free to comment and to share your own experience!

Lire la suite →

Powershell : scripter la création d’alias SQL Server

Mis en avant

Publié le par
Répondre

J’ai eu récemment à définir une procédure d’installation de serveurs liés SQL Server pour une application. Cette procédure doit être bien entendu testée en environnement de qualité et en production.  De plus chez mon client, un 2ème environnement de qualité est prévu en parallèle pour installer l’application concernée. Le principal souci ici est que les noms de serveurs liés vont changés et que cela risque d’avoir un impact au niveau du code de l’application. Nous ne pouvions pas nous permettre de mettre à jour l’ensemble du code TSQL applicatif à chaque changement d’environnement. Pour répondre à cette problématique nous avons choisi d’utiliser des alias SQL Server. L’utilisation des alias est beaucoup plus flexible avec les serveurs liés. Il suffit de changer ces noms sans avoir un impact sur le code.

Mais revenons à notre problématique initiale : comment scripter la création des alias SQL Server ? Il n’existe pas de procédure système T-SQL qui permette de faire cela. La solution ici est d’utiliser les classes WMI correspondantes (ici j’utilise un SQL Server 2008). Comme nous sommes à l’air du PowerShell voici le script utilisé permettant la création d’alias sous SQL Server :

param
(
$server=$(throw "Mandatory parameter -instance_name_sot not supplied")
)


# Example script to create an alias
$alias = ([wmiclass] '\\.\root\Microsoft\SqlServer\ComputerManagement10:SqlServerAlias').CreateInstance()
$alias.AliasName = 'SERVER_ALIAS'
$alias.ConnectionString = '1433' #connection specific parameters depending on the protocol
$alias.ProtocolName = 'tcp'
$alias.ServerName = $server
$alias.Put() | Out-Null;

# List existing aliases
Get-WmiObject -Namespace 'root\Microsoft\SqlServer\ComputerManagement10' -Class 'SqlServerAlias' |
    Format-Table -Property 'AliasName', 'ServerName', 'ProtocolName', 'ConnectionString'

 

… à utiliser de la manière suivante :

 & '.\deployalias' –server servername

 

image

 

Vous pouvez tout à fait modifier ce script et le rendre plus paramétrable (nom de l’alias, no de port etc …). Encore une chose, en fonction du besoin vous allez sans doute devoir installer les alias pour les 2 types d’architecteur x86 et x64. Pour se faire il suffit de lancer le script avec Windows PowerShell (x64) et Windows PowerShell (x86).

 

Bon déploiement

David BARBARIN (Mikedavem)
MVP SQL Server