Always on etat des réplicas

 SELECT ag.name AS ag_name, ar.replica_server_name AS ag_replica_server, dr_state.database_id as database_id,
is_ag_replica_local = CASE
WHEN ar_state.is_local = 1 THEN N'LOCAL'
ELSE 'REMOTE'
END ,
ag_replica_role = CASE
WHEN ar_state.role_desc IS NULL THEN N'DISCONNECTED'
ELSE ar_state.role_desc
END,
dr_state.last_hardened_lsn, dr_state.last_hardened_time,
CASE WHEN EXISTS (SELECT dr_state_.last_hardened_lsn
FROM (( sys.availability_groups AS ag_ JOIN sys.availability_replicas AS ar_ ON ag_.group_id = ar_.group_id )
JOIN sys.dm_hadr_availability_replica_states AS ar_state_ ON ar_.replica_id = ar_state_.replica_id)
JOIN sys.dm_hadr_database_replica_states dr_state_ on ag_.group_id = dr_state_.group_id and dr_state_.replica_id = ar_state_.replica_id
WHERE ar_state_.role_desc = 'PRIMARY' AND dr_state.last_hardened_lsn = dr_state_.last_hardened_lsn)
THEN 0 ELSE
datediff(s,last_hardened_time, getdate())
END as 'seconds behind primary'
FROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id )
JOIN sys.dm_hadr_availability_replica_states AS ar_state ON ar.replica_id = ar_state.replica_id)
JOIN sys.dm_hadr_database_replica_states dr_state on ag.group_id = dr_state.group_id and dr_state.replica_id = ar_state.replica_id;

Dynamic Data Masking

Les fonctions de masking

– Default
Full masking according to the data types of the designated fields.

(FUNCTION = 'default()')

– Email
Masking method which exposes the first letter of an email address and the constant suffix « .com », in the form of an email address.

(FUNCTION = 'email()')

– Random
A random masking function for use on any numeric type to mask the original value with a random value within a specified range.

(FUNCTION = 'random([start range], [end range])')

– Custom String
Masking method which exposes the first and last letters and adds a custom padding string in the middle. prefix,[padding],suffix
Note: If the original value is too short to complete the entire mask, part of the prefix or suffix will not be exposed.

(FUNCTION = 'partial(prefix,[padding],suffix)') NULL

Créer MASK

CREATE TABLE Membership
(MemberID int IDENTITY PRIMARY KEY,
FirstName varchar(100) MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)') NULL,
LastName varchar(100) NOT NULL,
Phone# varchar(12) MASKED WITH (FUNCTION = 'default()') NULL,
Email varchar(100) MASKED WITH (FUNCTION = 'email()') NULL);

Ajouter un mask

ALTER TABLE dbo.PersonPhone
ALTER COLUMN PhoneNumber ADD MASKED WITH (FUNCTION = 'default()');
ALTER TABLE dbo.EmailAddress
ALTER COLUMN EmailAddress ADD MASKED WITH (FUNCTION = 'email()');
ALTER TABLE dbo.Employee
ALTER COLUMN BirthDate ADD MASKED WITH (FUNCTION = 'default()');

Identifier les colonnes masquées

SELECT c.name, tbl.name as table_name, c.is_masked, c.masking_function
FROM sys.masked_columns AS c
JOIN sys.tables AS tbl
ON c.[object_id] = tbl.[object_id]
WHERE is_masked = 1;

Démasquer

GRANT UNMASK TO TestUser;
-- Removing the UNMASK permission
REVOKE UNMASK TO TestUser;

Supprimer le masque

ALTER TABLE Membership
ALTER COLUMN LastName DROP MASKED;

Lister les droits sur les bases

CREATE TABLE TempUserRole (
    ServerName VARCHAR(50),
    DatabaseName VARCHAR(50),
    DatabaseRoleName VARCHAR(50),
    DatabaseUserName VARCHAR(50)
)

exec sp_MSforeachdb
'INSERT INTO TempUserRole (ServerName,DatabaseName,DatabaseRoleName,DatabaseUserName)
 SELECT @@SERVERNAME AS ServerName,
        '
'?'' AS DatabaseName,
        DP1.name AS DatabaseRoleName,  
        isnull (DP2.name, '
'No members'') AS DatabaseUserName      
 FROM [?].sys.database_role_members AS DRM  
 RIGHT OUTER JOIN [?].sys.database_principals AS DP1  
   ON DRM.role_principal_id = DP1.principal_id  
 LEFT OUTER JOIN [?].sys.database_principals AS DP2  
   ON DRM.member_principal_id = DP2.principal_id  
WHERE DP1.type = '
'R''
ORDER BY DP1.name'


Select *
From TempUserRole
-- Si utilisateur spécifique décommenter ligne suivante et choisir utilisateur
WHERE DatabaseUserName = 'EUROBAT\GR-USER-BI-DSI'

DROP TABLE TempUserRole;

Zipper le contenu d’un dossier en powershell

Le code suivant permet de générer un zip de tous les dossiers contenus dans un dossier racine.

function backupMe ($sourceFolder, $destinationZip) {
   [Reflection.Assembly]::LoadWithPartialName( "System.IO.Compression.FileSystem" )
   [System.IO.Compression.ZipFile]::CreateFromDirectory($sourceFolder, $destinationZip)
}

$dossierRacine = "C:\log"

$TAB_A_Compresser = Get-ChildItem $dossierRacine | Where-Object { $_.PSIsContainer } | Select-Object  Fullname

Foreach ($item in $TAB_A_Compresser) {  
    $sourceFolder = $item.fullname
    $destinationZip = $sourceFolder + ".zip"
    backupMe $sourceFolder $destinationZip
}