Category Archives: Scripts

Linking Data Sources to SSRS Reports with PowerShell

Imagine the following scenario: You just migrated a bunch of SSRS Reports to a new Reporting Services instance, which has a different folder structure. In this perspective, one of the things that get lost along the way is the linking between Data Sources and Reports.

If you are in the PowerShell world like me you know very well this repository in the Microsoft organization on Github: ReportingServicesTools.

Reporting Services Tools is a module that interacts with Microsoft SQL Server Reporting Services, making it easier several tasks like importing and exporting reports, creating folders, subscriptions, data sources and so on. The module is maintained by Microsoft along with some know PowerShell folks from the community like my friend Claudio Silva(@claudioessilva) from the dbatools team. Read the rest of this entry

Automate your SQL Server Restore Tests with PowerShell, dbatools and PowerBI

Hello everyone,

At work, I got to do some really interesting stuff. What I like is they really give me the freedom to try things out. Ah, and if you wanna join us, we have a position to work with me, posted here: https://grnh.se/4899e2091.

There is a saying, which I don’t exactly remember, is that a backup is only a backup if you test the restore of the same. So, I was working on creating an automated way of restoring the backups of my production databases and give me some sort of reporting around it.

So, I did that work and it is running really fine with the structure that I have. Then, I had the idea to share what I did with the community, mainly because I rely on almost all of my work to dbatools, and without them I would have more trouble/work do, to get the same results.

I shared a screenshot on twitter as you can see below.
image
link: https://twitter.com/marcosfreccia/status/1037031233262374914

I got a few replies from people saying that they would like to see how it is done. So, in this blog post, I’ll explain you all the steps that I had to do, to obtain this result.

Read the rest of this entry

Reporting Services Databases on Availability Groups

In my current job as a DBA, one of my missions is making everything as automated as possible and making sure that when things go wrong(trust me, they will), the applications that are using my environment can smoothly continue to run with less intervention possible.

You will make mistakes which is part of the job, learning from those mistakes is what makes you grow. Today I want to share with you a few tips that will make your experience with SSRS Databases and Availability Groups less painful. Read the rest of this entry

Visualizing the content of encrypted stored procedures

Probably you were a bit curious about it and decided to click here, right? This is a very recurrent question on Microsoft forums, where people who maybe by mistake encrypted all of the stored procedures, or maybe you support a 3rd party application and there is an particular stored procedure killing the performance of your server and you just wanna know what the same is doing?

Independent of what you want to do, let me show you here how can you manage to see the content of an encrypted procedure. Read the rest of this entry

Removing Databases from Availability Groups with Powershell

Those days at work I had to remove a bunch of databases from the Availability Groups and later drop them from the primary and secondary replicas. I know I could do the click, click and drop thing, but why not PowerShell? Read the rest of this entry

Synchronize Files and Folders using Powershell

Hello everyone!

Last week I was helping an Application Admin to Synchronize files and folders between Source and Destination Servers. The challenge was because some folders had really long hierarchies, so you could imagine something like this.

image

I would say that doing this for some files it is really ok, but for my case we are talking about of 10GB of data and around 4K folders in the root level. Basically, this was the solution that I was able to come up.
The script in in the Github link below.
Read the rest of this entry

My Lightining Talk at SQL Saturday Vienna 2018

SQLSat679_Speaking_600x300_02

Hello everyone!

I just wanted to share that I will be presenting a Lightining Talk at SQL Saturday 679 in Vienna, Austria. I will have 10 minutes to present the following session.

Title: My 5 favorite Powershell Commands!

So, if you are planning to be in Vienna for the SQL Saturday, make sure to register yourself, as there are only a few spots available. Also, if you are a DBA, I would say that you really should be attending my Lightining Talk, because the commands that I am gonna show you will save your life.. or at least make it easier.

Also, look at the Event Agenda, as there will be many other sessions in all areas of the Data Platform world.

http://www.sqlsaturday.com/679/EventHome.aspx

See you in Vienna!

SQL Saturday 689 – Prague – Scripts and Slides

Hi Everyone,

First of all, I want to thank everyone at my session at SQL Saturday 689 in Prague this last weekend. It was a tremendous pleasure for me to present and share a bit of what I know with all of you.

I also want to say THANK YOU to the Czech BI & SQL Pass User Group for putting this amazing event to the community, I know how much work and effort has to be done to have such a great event. You treated not only myself but all the speakers so well, so thanks again for having me! You guys are great!

I also want to say THANK YOU to the Sponsors, with all your support the event was just awesome!

As I promised during my presentation, I would write this post to share the link to where you can download most of the resources used by myself during the presentation.

https://github.com/marcosfreccia/sql-saturday-689-prague

I would like to also ask you a bit of your time to fill up the session evaluation. This means a lot to all the speakers. Doing that, it helps all of us speakers to improve our presentation, subjects, demos and etc.. Please, please, please!! Do it! The link is below.

https://goo.gl/6ZTfH6

Thanks again and see you in 2018!

Regards,
Marcos Freccia
Data Platform MVP

Deletando informações em batches –

Olá pessoal,

Quando trabalhamos bases de dados muito grandes e criticas é normal que alguma forma de purge (expurgo) seja feita no banco de dados, é melhor para a aplicação e também para o DBA. Porem uma grande preocupação quando vamos falar sobre deletar uma grande quantidade de dados é não prejudicar nosso arquivo Transaction Log para que o mesmo não estoure o espaço em disco, como também não bloqueie nossa aplicação que esta tentando inserir ou atualizar dados na base. Alem de tudo isso nos preocupamos com performance, pois uma grande operação de delete poderia impactar todo o nosso ambiente.

Pensando nisso criei um script bem simples, mas que pode te ajudar no momento de realizar esse expurgo da base de dados. Read the rest of this entry

Recycle ErrorLog baseado no tamanho do arquivo

Olá pessoal,

Estou aqui para divulgar um script que criei, onde baseado no tamanho do arquivo do ErrorLog realizamos ou não a limpeza do mesmo. O mesmo foi criado em PowerShell e é bastante simples, pois meu conhecimento nele é relativamente baixo. No meu script o tamanho do arquivo foi  setado para 250MB, mas você pode edita-lo para acomodar a sua necessidade.

Não vou colar o script aqui na postagem, mas sim o link para o meu OneDrive onde o mesmo se encontra.

http://1drv.ms/1B02XA0

Para executar o script é bastante simples, bastando apenas criar um job no SQL Server Agent onde o step deverá executar código PowerShell. Você pode consultar essa documentação abaixo para a criação do Job.

https://msdn.microsoft.com/en-us/library/hh213688.aspx

 

Espero que gostem do script, e se alguem não estiver conseguindo realizar o download do arquivo, deixe um comentário que eu poderei ajustar.

 

Abraços,
Marcos Freccia
SQL Server MVP