How to test your workload before SQL Migration

Hi Friends,

Today I’ll talk about how to test SQL Workloads before a SQL Migration or to test new features. for this article, let’s suppose that I have a SQL 2012 Instance and I have been asked to switch to SQL 2016, before moving in, I want to test my workloads on SQL 2016 to be sure that I won’t have performance problems with or breaking changes.
The goal is:
– Capture workload from SQL2012
– Replay the workload on SQL2016
– Generate reports with results.

We have some ways to reach the goal, for example:
– Backup from SQL2012 and Restore on SQL2016, change the application connection to SQL2016;
– Use Distributed Replay do simulate workload.
– etc….

The first option we will have downtime for business, since we will point the application just for test and we don’t know what will happen. this can also be dangerous if we have third party integration (we use other application to share or get information, probably we will have data loss to roll back to SQL2012)

The second option is good option, since SQL 2012 we can use Distributed Replay, although this is a command line tool, will just have the replay and you will spend some time to crate the command to capture and replay, we still need to create the report manually.

Now we have a fantastic tool DEA (Database Experimentation Assistant) that is new tool to capture workload form A server and replay on B server. DEA support previous version of SQL Server (2005 and above), so if you want to upgrade a very old instance, you can just use this tool to be a step ahead of possible issues (performance, breaking changes, etc.).

DEA will also provide a very nice report with analysis metric for compatibility errors, degraded queries, query plans, etc., this will allow us to be more confident to do a successful upgrade.

How to use DEA

First step, download the DEA and install it.

The second step, is to setup Distributed Replay, behind the scenes DEA uses Distributed Replay, DEA will provide a user-friendly interface to capture and replay the workloads.

After DR and DEA Setup, we must to have a backup from our Database (initial position), we will restore this backup on new Server, so we will have the same point to replay the workload.

After backup you can start the DEA you should receive a welcome screen like this:

On the left side we have the menu, the first step is to start a workload capture on Source server, in this case SQL2012, so just click on camera icon.

Just click on +New Capture.

At this screen you must enter the Trace Name, Duration (starting for 5 min up to 3 hours), Source SQL Instance, Source Database name, Path to save the trace and a confirmation check box that you made a backup before start the capture (we will restore this backup on SQL2016 before replay, so SQL will compare apples with apples)

You can check the status while SQL is running the trace

After workload capture, we will be able to replay the workload on destination server (SQL2016), to start replaying the workload, click on Play icon on left menu and click over +New Replay:

Now before start replaying the workload, we must restore the backup that we executed before starting the trace (initial position) after restore, you must inform the Replay Name, Distributor Controller Machine, the Path with source trace (the same that we informed on capture trace screen), target SQL Server name, Path to save the trace during the replay (trace on SQL2016) and the check box to inform that we performed a database restore (initial position)

We need to wait for replay, during this phase we can check the status:

In the end, you should have a screen like this:

Now it is time for report, just click on Report icon on left menu and type the name of the server to host the Report and click over connect button:

Click on button +New Analysis Report, if is the first time and you do not have R for Windows and R Interop installed, you will receive a screen informing that you must install it.

After R setup, click over Try Again button and you will receive a screen to setup the Report Analysis, type a name for Report, the source trace file (SQL2012) and Target trace file (SQL2016) and click on Start

Wait for analysis completion

After the analysis, you will receive a dashboard report comparing the Source and Target server, you can click on graphs to drill down into the details, in this case will click on green area.

The detailed report will show all query text and the duration analysis, we also can click on it to see more details.

In this detailed report we will see the performance comparison between the executions, information about compatibility errors (breaking changes) and execution plans.

So that’s all for now, what do you think? Now it is easy to replay workloads before an upgrade isn’t it?

These articles may help you:
Setup Distributed Replay
Distributed Replay Docs
Distributed Replay Troubleshooting
DEA Capture Trace FAQ
DEA Replay FAQ
DEA Report Analysis FAQ
DEA Solution architecture to compare workloads

Thanks

Marcelo Fernandes

Anúncios

SQL 24 HOP – Palestrarei no SQL PASS 24 horas português

Olá Amigos

No próximo dia 14/11 (segunda-feira) iniciaremos mais um ciclo de palestras com diversos profissionais de SQL Server dos países que falam a língua portuguesa.

A minha sessão será no dia 14/11 as 19:00 (Horário de Brasilia) vou falar de SMB 3.0 como alternativa de storage. Corra, ainda da tempo de fazer a inscrição totalmente gratuíta e online! REGISTRE-SE

Veja a grade de palestras aqui.

 

Testando conexão no SQL com arquivo UDL

Arquivos .UDL (Universal Data Link) é uma maneira simples e rápida para testar conexões com o SQL Server ou ainda criar Connections Strings

1- Criamos um arquivo teste.TXT e vamos renomear para teste.UDL (o ícone mudará)

imageimage

2- Execute o arquivo teste.udl e clique sobre a aba Providers, como você pode observar existem diversos providers, mas para esta artigo em específico vou usar o Native Client do SQL 2008 (10.0), após selecionar o provider clique sobre o botão Next.

image

3- Na aba Connection você deve informar o nome do seu servidor SQL no campo 1 no campo 2 você deve informar o modo de autenticação (SQL ou Windows) se o usuário estiver correto e com permissões no SQL server no campo 3 você conseguirá selecionar o database

image

4- Agora para testar basta clicar sobre o botão Test Connection, se o usuário estiver correto e com as devidas permissões no SQL você deverá receber a mensagem abaixo:

image

5- Caso queira criar a Connection String, basta clicar em OK na mensagem de Test Connection e em OK nas propriedades do arquivo UDL e em seguida abrir o arquivo teste.UDL no notepad e copiar a Connection String e utilizar em seu aplicativo, deverá ser algo +/- assim:

[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLNCLI10.1;Persist Security Info=False;User ID=teste;Password=123456;Initial Catalog=master;Data Source=C3PO

Failed to open loopback connection

Amigos, recentemente passei por um problema muito interessante, ao executar a procedure SP_READERRORLOG recebia o erro abaixo:

Msg 22004, Level 16, State 1, Line 0
Failed to open loopback connection. Please see event log for more information.
Msg 22004, Level 16, State 1, Line 0
error log location not found

Este erro ocorreu em um ambiente clusterizado com Windows 2008 R2 e SQL Server 2008.
Publiquei um artigo no Technet Wiki com detalhes sobre o erro e como solucionar o problema:

http://social.technet.microsoft.com/wiki/contents/articles/7063.solucionando-problemas-error-failed-to-open-loopback-connection-ao-executar-sp-readerrorlog-pt-br.aspx

No transaction is active

Amigos, recentemente passei por um problema bem chato com transações distribuidas em ambiente geograficamente distribuídos, a mensagem é a seguinte:

OLE DB provider “SQLNCLI10” for linked server “<LinkedServer>” returned message “No transaction is active.”.

Msg 7391, Level 16, State 2, Line 4
The operation could not be performed because OLE DB provider “SQLNCLI10” for linked server “<LinkedServer>” was unable to begin a distributed transaction.

Escrevi um pequeno artigo que mostra como identificar e como resolver, espero que ajude caso alguém tenha o mesmo problema:

http://www.mcdbabrasil.com.br/modules.php?name=News&file=article&sid=611

Concactenando valores na mesma coluna

Bom dia galera, tem sido recorrente no Forum Technet threads solicitando ajuda para agrupar valores na mesma coluna e também a pedido do Thiago Alencar que queria ver outra alternativa para concatenar valores sem usar CTE onde o código fica mais enxuto.

Bom, vamos ao cenário, temos os seguintes dados na tabela:

image

E desejo retornar os dados para a aplicação agurapados da seguinte forma:

image

Vamos agrupar todos os personagens por tipo separando-os com uma “,”
Inicialmente vamos criar a variável table @Personagens que usaremos neste exemplo

DECLARE @Personagem TABLE (dsTipo varchar(20),
nmPersonagem VARCHAR(50))
 
INSERT INTO @Personagem VALUES(‘Jedi’,‘YODA’)
INSERT INTO @Personagem VALUES(‘Jedi’,‘Obi-Wan Kenobi’)
INSERT INTO @Personagem VALUES(‘Jedi’,‘Qui-Gon Jinn’)
INSERT INTO @Personagem VALUES(‘Jedi’,‘R2-D2’)
INSERT INTO @Personagem VALUES(‘Jedi’,‘C-3PO’)

A idéia é simples, vamos converter o resultado do nosso select em XML com o FOR XML e usar a função STUFF para substituir as tags XML para “,”.

SELECT a.dsTipo,
    STUFF((SELECT ‘, ‘ + b.nmPersonagem AS “text()”
          FROM @Personagem b
         WHERE a.dsTipo = b.dsTipo
          FOR XML PATH()),1,3,) AS nmPersonagem
FROM @Personagem AS a
GROUP BY a.dsTipo

Existem outras maneiras para se atingir o objetivo, no artigo de um grande amigo Thiago Alencar ele usa CTE.

 

 

Technorati Tags: ,,,,,,,
Windows Live Tags: Concactenando,Forum,Technet,STUFF,SELECT,valores,coluna,agrupar
WordPress Tags: Concactenando,Forum,Technet,STUFF,SELECT,valores,coluna,agrupar