I don’t know about your experience when it comes to SQL Server Agent jobs but often I receive requests to grant permission so that clients can handle their jobs in an autonomous way.

Any problem with that?

It depends 😄 If you’re not familiarized with the difficulty this can bring, let me share with you a different way to accomplish the task without adding the login to the sysadmin instance role. I’m sure that you don’t want to do that on a production instance. The next possible way is to make the login as the owner of the job and it needs to belong to one of the msdb fixed database roles ! You can take a look at this documentation and see the matrix there for each role.

Continue reading

Dear reader, before continue please open a PowerShell console and run the following command:

Install-Module -Name dbatools

If you are doing this on the date of this blog post, you have just installed dbatools v1.0!

After more than 200 commits, the work of more than 20 contributors and 20 days since the last published version, dbatools v1.0 is live!

To all of you that have contributed direct or indirectly to the module a big thank you!

Continue reading

Few days ago I received a request to restore a dozen of tables because someone have deleted more data than it was supposed. I immediately thought about dbatools for the job!

NOTE: I also thought about SSMS “Import/Export Data”. And this is ok when someone says “it’s to run just once, just now”. When you are in the IT for a while you know that is not only once :-). And what if I need to re-run? Or save it as example and share with my colleagues? Doing this using a PowerShell script makes it much more flexible.

Continue reading

Probably you had the need to script out some objects from a SQL Server instance/database and this is quite easy. You just need to right click on the object (well…not on every single one, try it with an Availability Group :-), no script option available) select “Script XXXX as” and you have it.

But have you realized that this option doesn’t bring all the stuff? Let’s say you are scripting a table and you have a Non-Clustered index or a trigger…using this option some of the objects under the table will not be scripted out. I understand this as each one of this is a different object, which means if you go to each one you can right click and script just that one.

Continue reading

Few days ago a client requested the configuration of MSDTC (Microsoft Distributed Transaction Coordinator).

NOTE: If you want to know more about it here is a nice FAQ from Microsoft blogs - MSDTC Recommendations on SQL Failover Cluster ?

The client has 2 machines: one an application server and one a database server.

Both run on Windows Server 2016 OS and, the database server runs SQL Server 2016 using Availability Groups feature (where their databases resides).

Continue reading