Creating Database Link from AWS RDS to OnPremises

Hi Folks,

It has been a very long time that I don’t write anything in here, but I just wanted to share with you something that you might find useful for doing Oracle migrations. I wanted to create a Database Link from my AWS Oracle RDS Instance to an Oracle sitting on an On-premises environment.

Of course the very first thing you need to have is internal network connectivity between the instances. Take a look on this part of the documentation for more specification. Assuming that you have the connectivity between then, this is the script you need to execute on your Oracle RDS Instance.

create database link <NameOfDBLink>
connect to UserName
identified by “UserNamePassword”
using ‘(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=TheHostAddressOrIp)(PORT=TCPPortHere))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=TheOracleServiceName)))’;

To test that your database link has been successfully created, you can do a simple test by executing the following query.

select sysdate from dual@NameOfDBLink;

Best,
Marcos Freccia

Advertisement

Como restaurar bancos de dados no SQL Server

Olá pessoal, tudo bom com vocês?

Nos artigos anteriores demos inicio a nossa sequencia de vídeos sobre o dbatools, onde realizamos a instalação do Visual Studio Code e consequentemente instalamos o módulo do dbatools. Abordamos também nossos primeiros comandos: Get-DbaLogin e Export-DbaLogin.

Pois bem, aprofundando um pouco mais em todas as áreas do dbatools, vamos agora abordar um tópico bastante importante: A restauração de um banco de dados.

Uma das tarefas do administrador de banco de dados recorrentes é realizar a restauração, ou na linguagem técnica “restore” de um banco, seja a mesma sobrescrever um banco já existente, ou até mesmo restaurar um único banco para várias instancias em servidores diferentes.

Como sempre o módulo do dbatools possui comandos que nos ajudam e muito nessa tarefa. Aconselho vocês a assistirem o vídeo abaixo e aprender um pouco mais sobre esse poderoso módulo para administração de SQL Server.

Como sempre peço por aqui e nos vídeos, fico sempre aguardando os comentários, sugestões, e até mesmo dicas de vídeos que gostariam de ver por aqui.

Se inscrevam também no meu canal do Youtube para sempre receberem as notificações de vídeos novos.

Um grande abraço para vocês e até o próximo vídeo!

Marcos Freccia
Data Platform MVP

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.

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.

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.

The most read posts of June – 2017

Hi guys,

I have decided that I will continue to do this kind of blog post. So you don’t need any other introduction about the subject. So, let me list the top 5 posts of June.

1)  Transaction log cheio? E agora? – 327 views – Posted on 13.06.2011

2) Como visualizar o conteudo de procedures criptografadas –  182 views – Posted on 11.03.2015

3) Configurando Linked Server de SQL Server para Oracle – 175 views – Posted on 15.06.2012

4) SQL Server no Linux – os primeiros passos – 115 views – Posted on 02.01.2017

2) Adding SSISDB to AG for SQL Server 2016 – 97 views – Posted on 28.04.2017

You see it, that’s not a looot of reads, but I’ll try to improve my writing skills and share more with you, because I have been doing some pretty cool stuff at work!

I hope you like this type of post!

Olá pessoal,

Decidi que vou continuar com esse tipo de postagem. Bom, acredito que não preciso fazer mais nenhuma introdução sobre esse assunto, então segue os 5 posts mais acessados do meu blog.

1)  Transaction log cheio? E agora? – 327 views – Posted on 13.06.2011

2) Como visualizar o conteudo de procedures criptografadas –  182 views – Posted on 11.03.2015

3) Configurando Linked Server de SQL Server para Oracle – 175 views – Posted on 15.06.2012

4) SQL Server no Linux – os primeiros passos – 115 views – Posted on 02.01.2017

2) Adding SSISDB to AG for SQL Server 2016 – 97 views – Posted on 28.04.2017

Para falar bem a verdade, o numero de visualizações é bem baixo até, acho que preciso fazer mais propaganda . Está acontecendo bastante coisa legal no trabalho e vou tentar escrever mais conteúdos para todos.

Se vocês gostaram desse tipo de post, deixa um comentário, já é um bom incentivo.

Regards,
Marcos Freccia
Data Platform MVP

How much transaction log, transactions generate?

Hi Everyone,

I wanted to share something that I am doing this week at work. One of the applications that I support is generating a lot of transaction log in the database. Just to have an idea, we run Transaction Log backups every 30 minutes and I have backups over 100GB and sometimes 200GB. The difficult part of investigating what is generating that amount of log is because the databases involved are used by at least 3 different applications and streams. So, I remember that in the past I created a simple script that looks at this information in the database and then I may have more inputs of what is really generating that amount of log.

Changing the Dump Directory Location may SQL Agent to not start

Hey Guys,

I was those days changing a few settings in the environment, where I had this small problem and I would like to share what did I do to fix it. The task in question was actually easy: To change the default directory of the LOG folder, that usually is where the SQL Server Agent logs, ERRORLOG and some other things are stored.

SQL Server no Linux – The first steps

Hi everyone,

I might confess that today was actually the very first time that took some courage and created a Linux machine with SQL Server vNext on it. I was a bit curious to see how it was working and to find out how easy it is to install SQL. My knowledge on Linux is near to zero, so I had first to do some searches (mainly in the SQL Server docs and blog posts) the actual steps that was needed to finally connect to an instance running on Linux. Therefore, I hope that I can help someone that is also struggling on this subject 🙂

In all my tests I am using an Azure Virtual Machine, all the steps here described are for this scenario.

MVP Virtual Conference–Material de apresentação

Olá pessoal,

No inicio do mês de maio de 2015 entre os dias 13 e 14 tivemos o MVP Virtual Conference onde tive a oportunidade de palestrar apresentando sobre o Resource Governor no SQL Server 2014. Antes de mais nada peço desculpas pela demora em disponibilizar o conteúdo para vocês, mas ultimamente tenho estado bastante ocupado.

Bom, como ja ia falando logo abaixo segue os materiais que utilizei para realizar a apresentação, tanto o PPT como também os scripts para caso alguem tenha interesse em estudar mais sobre o assunto.

Link para download dos scripts: http://1drv.ms/1MIPd4f

Para aqueles que assistiram a palestra, gostaria de agradecer pela presença e assim que as gravações estiverem disponiveis para download e assistir, prometo atualizar todos do mesmo.

Obrigado,
Marcos Freccia
SQL Server MVP

E-Book – SQL Server Além do Conceito – Blog post collection

Olá pessoal,

Estou muito feliz em comunicar o lançamento deste e-book na qual eu e mais 14 amigos criamos e publicamos o e-book: SQL Serve Além do conceito – Blog Post Collection.

Vale ressaltar que este trabalho juntou os melhores posts escritos por cada um dos autores afim de criar um guia de aprendizado em SQL Server. Este e-book é gratuito, e sempre será.

Tive a oportunidade de contribuir com 3 posts aqui do meu blog. A partir da página 105 vocês podem prestigiar os conteúdos que escrevi.

Espero que vocês gostem do conteúdo aqui apresentado. Para realizar o download do mesmo, clique na imagem abaixo que você será redirecionado para o mesmo.

Abraços,
Marcos Freccia
SQL Server MVP