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

I’m speaking at SQL Saturday Lviv 2018

SQLSaturday #753 - Lviv 2018 Hello Everyone!

In the end of this month, I will be in Lviv, Ukraine for the SQL Saturday 753 or SQL Saturday Lviv!

There will be 18 sessions with a variety of topics for you to choose 🙂 If you live in Lviv or nearby you should definetely check it out.

Honestly, I am really excited to be in Lviv, I never expected that one day I would be in Ukraine and also going there for speaking, it is a double excitement!

I will be speaking about some 10 alerts that you shoul have it running in your SQL Server. It is a very interesting topic in those days where everything is “automated”, right? If you are attending the event, make sure to join me at 12:50PM for the session: 10 alerts that you should have it running in your SQL Server.

If you follow me here or in my twitter @marcosfreccia and you are going to attend the event, make sure to stop by and say hello!!

See you!

Marcos Freccia
Data Platform MVP

[SCRIPT] – Listing Database Users and Roles

I am a fan of not reinventing the wheel. What that means? Well, If I need to do something, which usually involves creating a script, I try first to do a quick search on Google and see if someone already had this idea.

Looking at the title of the article you might say: Oh, that’s easily done with dbatools, which in fact it is true. However, my use case this time was a bit different than the conventional, because I want to expose that dynamically in my Python/Django Internal tool that exposes SQL Server through API’s, so the need for a TSQL script is imminent.

The goal result that I need is:

 

User Name User Type Create Date Roles
User001 SQL_USER 2018-09-10 14:00 db_datareader
User002 WINDOWS_USER 2018-01-01 13:00 db_datareader;db_datawriter;db_ddladmin

  Read the rest of this entry

I’m speaking at SQL Saturday Gothenburg 2018

SQLSaturday #775 - Gothenburg 2018 Hello Everyone!

Next weekend on 15th of September, I will be in Gothenburg for the SQL Saturday 775 or SQL Saturday Gothenburg!

There will be more than 15 sessions with all kinds of flavours, so you experiment a bit of everything 🙂 If you are in the area of Gothenburg I am sure you don’t wanna miss the event!

For me it will be a pleasure to be for my first time in Sweden and have the honor to speak there. I’m really excited! 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

Changing the Job Owner, doesn’t change the Schedule Owner

One of the capabilities that we do offer to our developers is the ability for themselves to create SQL Server Agent Jobs in our DEV/TEST Environments, so they perform some of their work without the DBA to be involved.

To fullfil this requirement, we grant them the role SQLAgentUserRole, which pretty much does the job. They can create and schedule jobs and execute them. However, latelly one of our DEV’s changed his team and I had to transfer his SQL Agent Jobs to another DEV.

It turns out that when the DEV tried to edit the existing schedule he got the following message: The specified @schedule_id (‘ScheduleIDHere’) does not exist. Read the rest of this entry

A Python script for Re-tweeting tweets

On the attempt to always learn something new, I was playing around with Python, the Python-Twitter Package and the Schedule package.

Pretty much I wanted to create a small script where I could every X minutes to connect on twitter and re-tweet some tweets that were using an specific hashtag.

This was what I came up with. Read the rest of this entry

Free Microsoft Flow + Twitter + SQL Server

Those days I was playing around with Microsoft Flow and have created some automation for a few things.

While playing with it the thing that I liked was the possibility to host workflows in a free account. So, if you have a live account you can pretty much use a ton of connectors and create your own workflows. You should check it out the plan offers in this link below and understand how you can use it.

image
https://emea.flow.microsoft.com/en-us/pricing/  Read the rest of this entry

Microsoft MVP Conference – #mvpconf Quer ganhar um ingresso?

Olá pessoal,
Nos dias 6 e 7 de Abril, acontecerá na UNIP Tatuapé São Paulo o maior evento presencial de experts Microsoft do Brasil –
www.mvpconf.com.br, vou tentar enumerar as 5 razões de qualificar como o maior evento de expects e impulsionar sua curiosidade para realizar sua inscrição agora: Read the rest of this entry