SQLServer Module in PowerShell

SQLServer Module in PowerShell

If you are not already using this modal as a DBA it’s well worth a go.  In it you will find some great ways to administrate your database servers from your desk top.

To install it just open ISE and type:-

Install-Module –Name  SQLServer

Or to get the very latest (at the time of writing)

Install-Module -Name SqlServer -RequiredVersion 21.1.18080

You might find that you don’t have admin permissions to install it for all users, you can get round this by installing just for your user with:-

Install-Module -Name SqlServer -Scope CurrentUser

Also you may get an issue as parts of this modal may exist, so go for the below that will allow overwrite:-

Install-Module -Name SqlServer –AllowClobber

So the whole thing:-

Install-Module -Name SqlServer -RequiredVersion 21.1.18068  -Scope CurrentUser  -AllowClobber

So what can it do?

Well almost anything, have a look for yourself here:-

Get-Command -Module SqlServer -CommandType Cmdlet | Out-GridView

But here are a few of the top picks I think are worth an explore.

Get-SQLErrorLog

Use this to search the SQL error log for set words or strings, can be very useful for looking for stack dumps or log in failures.

New-SQLAvailbilityGroupListner

Does what it says on the tin, there are a number of availability group modules that are worth a look.

Start-SQLInstance , Stop-SQLinstance

Great for controlling SQL from afar.

Remove-SQlLogin.

Again great if you need to remove a login on all servers quickly.

Have a look about, and tell me your favourite commands.

Audit nightmare - show all users with write access to a database

we have all been asked that age old question by an auditor to show all users that have write access to a database.

We all know its not an easy thing to show.

I think this script answers that question:-

select 'below shows users who have explicit insert, update, delete, alter, or control permissions:'

Select P.name As Principal,

       class_desc As PermissionLevel,    permission_name As PermissionGranted,

       ObjectName = Case class When 0 Then DB_NAME()

              When 1 Then OBJECT_SCHEMA_NAME(major_id) + N'.' + OBJECT_NAME(major_id)

              End

From sys.database_permissions As DP

Inner Join sys.database_principals As P On P.principal_id = DP.grantee_principal_id

Where permission_name In ('insert', 'update', 'delete', 'control', 'alter')

And state = 'G'; -- Grant

select 'below shows users who have built-in role membership that allows write operations:'

DECLARE @command varchar(1000) 

SELECT @command = 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'') 

BEGIN USE ? ;

Select ''?'' as databasename,  P1.name As Principal,

       P2.name As DBRole

From sys.database_principals As P1

Inner join sys.database_role_members As RM On RM.member_principal_id = P1.principal_id

Inner join sys.database_principals As P2 On P2.principal_id = RM.role_principal_id

Where P2.name in (''db_owner'', ''db_datawriter'', ''db_ddladmin'')

end'

CREATE TABLE #role (databasename varchar(400), principal varchar(400) , dbrole varchar (400))

INSERT INTO #role 

EXEC sp_MSforeachdb @command

SELECT * FROM #role

DROP table #role

select 'below shows users who have DBO on a database'

CREATE TABLE #rolemember (databasename varchar(400),  dbrole varchar (400))

SELECT @command = 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'') 

BEGIN USE ? ;

Select ''?'' as databasename, SUSER_SNAME(owner_sid)

From sys.databases

Where database_id = DB_ID();

end'

INSERT INTO #rolemember 

EXEC sp_MSforeachdb @command

SELECT * FROM  #rolemember 

DROP TABLE #rolemember 

select 'Below shows users who have role membership at the server level that gives them write access to the database:'

Select P1.name As Principal,

P2.name As ServerRole

From sys.server_principals As P1

Inner join sys.server_role_members As RM On RM.member_principal_id = P1.principal_id

Inner join sys.server_principals As P2 On P2.principal_id = RM.role_principal_id

Where P2.name = 'sysadmin';

select 'Below shows users who have permissions granted at the server level that gives them write access to the database:'

Select P.name As Principal,

class_desc As PermissionLevel,

permission_name As PermissionGranted

From sys.server_permissions As SP

Inner join sys.server_principals As P On P.principal_id = SP.grantee_principal_id

Where permission_name In ('control server', 'alter any database')

And state = 'G'; -- Grant

Show all users in all databases with role name, user type and enabled status

DECLARE @command varchar(1000)

create table #results (

 dbname nvarchar(400), rolename nvarchar(400) , UserType nvarchar(400), create_date datetime, modify_date datetime , authentication_type_desc nvarchar(400), is_disabled bit


)


SELECT @command = ' use [?];
SELECT ''?'' as dbname, d.name , d.type_desc, d.create_date, d.modify_date , d.authentication_type_desc , s.is_disabled
FROM sys.database_principals d
INNER JOIN sys.server_principals s ON s.sid = d.sid
LEFT outer JOIN sys.database_role_members r ON r.member_principal_id = d.principal_id

'
--select @command

insert into #results
EXEC sp_MSforeachdb @command

select * from #results

drop table #results

GDPR, cloud and the DBA

It seams that the world is waking to the power of data.  Since the birth of the database just about every organisation has been storing data away on all aspects of its business.  This data has traditionally been used in a relational method, i.e we pull that data only when we need to look further at it.

However it seams we are now heading for a digital revolution.  For the first time we are starting to see this data being correctly mined, and used to its full value.  Its no wonder then that governments are trying to control the data we hold.

We are sat right on the edge of a time when computers can make complex decisions on how we might behave, how we spend, our honesty and our habits. Its an exciting time, but we are still yet to find a balance between allowing computers to help make our lives easier and allowing them to decisions they should not.

What we as DBAs need to ensure is that data is anonymized for compliance, but still has the ability to be mined.  This means losing personal and identifiable data, but not losing the dates meaning.  So for example, where we scramble a date of birth, we change it to within the same age bracket.  Should we change an address we keep the anonymized address in the same region. 

The shape shifting DBA

Introduction

About every 2 years a technology comes along that the rest of IT believes will wipe out the DBA.  We have seen MySQL, NoSQL, Big data and block chain.  All these have their place, but none replace the RDBMS.  And all need some kind of maintenance. 

The fact is there is no one toll that does it all, there will always need to be a mix of technologies working together to get the right data for the organisation.  But the DBA must move with the times.

Almost from the outset of the use of databases, the relation modal was adopted, and has largely remained that way for 50 years.  This put the data in the hands of the DBA, the developer and BI to serve the data.

That’s all changing, data now is being understood as the commodity it is, the heart of most companies.

What does a DBA do?

The day to day role of the on premises DBA for a RDBMS traditionally fits into these categories. 

Category	DBA responsibility
Application	Designing schema, access patterns, locking strategy, SQL development, and tuning.
	Deploying change scripts.
	Optimizing application and end-user queries (reactive tuning)
	Archiving data
	Generating needed ad hoc reports by querying from the database
	Proactive performance tuning
Access	Enrolling users and maintaining system security
	Controlling user access to the database
	Locking down host access
	Securing database privileged credentials
Database	Parameter configuration and tuning
	Cache management
	Job scheduling
Monitoring	Monitoring performance metrics, response times, and request rates
	Alerting
	Object access
	Logs
Platform	Ensuring compliance with database vendor license agreement
	Allocating system storage and planning future storage requirements for the database system
	Installing and upgrading the database software
	Performing data backups
	Patching the software that powers your database
	Troubleshooting DB errors and potentially contacting vendors for technical support
HA / DR	Ensuring that database are highly available in a system fail.
	Ensuring that databases are available in a disaster that affects entire systems.
	Monitoring performance of performance of DR / HA
	Ensuring that users and jobs are available at DR

So what’s changing? 

The role of the DBA will no longer just sit within the database management system.  The DBA will be required to understand the flow of data though the organisation.  The DBA will need to be involved with the database design from POC to production.

In the past the DBA has only concerned himself with the data that sits within the boundaries of the RDBMS, However as the need for data grows, data no longer remains within these boundaries. 

Data can now be seen being moved to big data stores, Data manipulation tools like PowerBI, data can be replicated to 100s of servers around the world, quires should be able to retrieve this data in seconds.

Why is it changing?

So in a world that is quickly requiring data at rapid rates it’s Ironic that the traditional custodian of the data is feeling left behind, in many cases they are the only people that have full access to the data and are often the only people who know where all the data sits. 

The power good data can bring is now being fully understood as BI, data analysis’s, Data scientists, developers and even the business are getting a better understating of the data held and requiring ever growing access to it.  This is also reflected in the agile development style that’s becoming so popular.  The DBA can no longer fully control data.

So are DBAs redundant in the new world

Far from it, a recent career study showed that 70% of DBAs are 45 years or older and 20% are within 10 years of retirement.  We could well be facing a DBA shortage in coming years. 

What are the roles of a DAB in the future?

The DBA now should not be thought of as the owner of the container that the data sits in.  But the owner of the flow of data though the systems, regardless of where they sit.

A lot of the same roles exist in the new world, but there is also a great deal of other tasks that should naturally sit with DBAs.

The below shows the tasks in a world with these new technologies including cloud.

Category	DBA responsibility
Cost and Vendor Management	Keeping data relevant in a world where data size = cost
	Vendor management
	Understanding of tools used by vendor and how they benefit
	Data flow from vendor to premises
Performance	Ensuring that data access sis fast and reliable
	Ensuring data being pulled is from the cloud is not affecting network performance
	Building data pipelines
Security	Have the right people got access
	Monitoring users access
	Ensuring access is revoked / evoked
	Monitor internal and external threats
Application	Designing schema, access patterns, locking strategy, SQL development, and tuning.
	Deploying change scripts.
	Optimizing application and end-user queries (reactive tuning)
	Archiving data
	Generating needed ad hoc reports by querying from the database
	Proactive performance tuning
Database Monitoring	Parameter configuration and tuning
	Cache management
	Job scheduling
	Monitoring performance metrics, response times, and request rates
Platform	Ensuring compliance with database vendor license agreement
	Allocating system storage and planning future storage requirements for the database system
	Installing and upgrading the database software
	Performing data backups
	Patching the software that powers your database
	Troubleshooting DB errors and potentially contacting vendors for technical support
Fault Tolerance and DR
	Ensuring that database are highly available in a system fail.
	Ensuring that the vendors DR/HA work for the organization
	Monitoring performance of performance of DR / HA
	DR testing
	Understanding what applications require access to cloud data

Conclusion

The DBAs still very much have a place, but have to learn to adapt to the changing world of data, how data is used and by who.

Pause all AGs

Doing some work at DR, want to make sure you dont mess with your AGS?

select DISTINCT name, 'ALTER DATABASE ['+name+'] SET HADR suspend;', 'ALTER DATABASE ['+name+'] SET HADR Resume;'
from sys.dm_hadr_database_replica_states rs
join sys.databases sd on rs.database_id = sd.database_id
join sys.dm_hadr_availability_replica_cluster_states rcs on rs.replica_id = rcs.replica_id
order by 2,1

Then run the output.

European Union GDPR compliance for the DBA

So today I attended a conference on GDPR, I will write more about it in the future.  

But in a nutshell this affects any company that wants to hold data on EU citizens, so basically any one.  Oh and it stays even after brexit.  

I wont in this post go into full details about how it works,  But it has some interesting points that may prove to be impossible to meet as a DBA.

Right To Be Forgotten

One of these is the right to be forgotten, this seams simple enough, just delete them from the database.  But no.... this includes all backups, archives and BI data.  How can a DBA be expected to remove data from an old backup, especially if that backup is on tape.

Data Portability

Another is Data portability, this is similar to the current DPA in that a data subject can request data on them, but differs in that it states that a "structured, commonly used and machine-readable format" must be used. My guess is CSV would cover this.

Retention of data 

Retention of data is also mentioned like the right to be forgotten data must be erased after a set period of time, no real time limits seam to be mention, but its suggested this is after the data subjects data is not useful.  This again could be a mine field to manage.  

Data Access 

This is an interesting one, the suggestion is that data held about the subject should be essay accessible, again hard work, would this then need to be viewed over the web?  If so that would but the data at more risk.

Its all interesting reading.   Watch this space for updates.