Configurando o DTC no SQL Server

Olá Pessoal!

Nessa última semana participei de uma migração de infraestrutura de um sistema de gerenciamento de serviço, basicamente o host de banco de dados (SQL Server 2016) seria alterado para um novo em outra localidade.

O que parecia ser um procedimento padrão, demandou um trabalho adicionado devido ao MSDTC (Microsoft Distributed Transaction Coordinator) que estava configurado no antigo ambiente e era necessário para o funcionamento de um dos módulos da aplicação. Como o antigo ambiente foi absorvido sem documentação, a necessidade de configuração do DTC só foi percebida depois que a aplicação retornou o seguinte erro ao tentar iniciar:

A mensagem informa que a base especificada não suporta transações XA. Ao pesquisar por essa mensagem foi possível fazer a relação com o DTC, afinal existe uma opção para habilitar as transacoes XA dentro das propriedades do DTC.

Reproduzi as mesmas configurações do DTC que existiam no antigo ambiente para novo, mas mesmo assim a aplicação ainda apresentava erro e nenhuma estatística de transação era gerada. As configurações do DTC podem ser vistas acessando o menu de Component Services dentro do Windows Server:

Navegando nos demais sites relacionados a consulta inicial, foi possível ver alguns procedimentos adicionais que deveriam ser executados no SQL Server, além da configuração já feita no DTC no SO. No site da IBM abaixo, por exemplo, é apresentado um passo a passo do que deve ser feito bem simples, pois o suporte das transações XA é feito pelo driver JDBC para SQL Server, permitindo dessa forma o inicio das operações com transações distribuídas necessário para o funcionamento da aplicação.

https://www.ibm.com/support/knowledgecenter/en/SSFTN5_8.5.5/com.ibm.wbpm.imuc.ebpm.doc/topics/db_xa_nd_aix.html

Em determinado ponto do passo a passo apresentado no site da IBM, é feito referencia aos arquivos sqljdbc_xa.dll e xa_install.sql. Esses arquivos podem ser obtidos através do driver JDBC para SQL Server, segue o link abaixo para download:

https://www.microsoft.com/en-us/download/details.aspx?id=55539

Dentro dos arquivos extraídos terá um diretório com o nome “XA”, com uma breve navegação sera possível encontrar os arquivos sqljdbc_xa.dll e xa_install.sql. Os passos a seguir devem feitos conforme informado no site da IBM, são eles:

  • Copy the sqljdbc_xa.dll file from the JDBC unarchived directory to the Binn directory (for a default SQL Server install, the location is C:/Program Files/Microsoft SQL Server/MSSQL10_50.MSSQLSERVER/MSSQL/Binn) of SQL Server computer. If you are using XA transactions with a 32-bit SQL Server, use the sqljdbc_xa.dll file in the x86 folder, even if the SQL Server is installed on a x64 processor. If you are using XA transactions with a 64-bit SQL Server on the x64 processor, use the sqljdbc_xa.dll file in the x64 folder.
  • Run the xa_install.sql database script on SQL Server. For example; from the command prompt, run sqlcmd -i xa_install.sql. This script installs the extended stored procedures that are called by sqljdbc_xa.dll. These extended stored procedures implement distributed transaction and XA support for the Microsoft SQL Server JDBC Driver. You will need to run this script as an administrator of the SQL Server instance. You can ignore errors about unable to drop procedures that don’t exist.
  • Open the SQL Server Management Studio to locate the security folder under the master database. To grant permissions to a specific user to participate in distributed transactions with the JDBC driver, add the user to the SqlJDBCXAUser role in the master database (for example, for a Lombardi user add master database in User mappings and check SqlJDBCXAUser role).

Após concluir os passos acima a aplicação subiu sem problemas e imediatamente as estatísticas de transações começaram a ser carregadas:

Até a próxima!

Anúncios

SQL SATURDAY #618 BRASILIA 2017

Olá Pessoal!

Novamente teremos o SQL Server Saturday em Brasilia e como sempre, palestras de alto nível com os melhores profissionais de todo país.

Salve a data de 19 de Agosto de 2017 e lembrando, todo evento é gratuito. Mais informações na página oficial do evento:

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

Garanta sua inscrição no link abaixo:

https://www.sqlsaturday.com/618/RegisterNow.aspx

Um agradecimento aos colegas que estão atuando como organizadores do evento, além de todos os parceiros envolvidos.

Até a próxima!

Otimizando o PostgreSQL ODBC Driver

Olá Pessoal!

Dentre as diversas fontes de dados envolvidas em cargas ETL através do SSIS, conexões com o PostgreSQL são bem comuns. Na maioria das vezes apenas tenho que buscar poucos dados de forma incremental, com alguns tratamentos durante a inserção no SQL Server. O problema é quanto ocorrem as cargas Full e milhões de linhas são lidas em grandes tabelas e o throughput de rede ainda não ajuda.

Bom…inicialmente para efetuarmos uma conexão com o PostgreSQL é necessário instalar o driver ODBC (psqlODBC) para o Windows na máquina que utiliza a solução do Integration Services. Uma vez instado, sua configuração no ODBC do Windows é bem simples, uma vez que, não existam problemas de rota e nenhuma restrição no pg_hba.conf, é possível utilizar o ODBC Connection Manager no Visual Studio para realizar o acesso ao PostgreSQL.

Uma vez que os passos acima tenham sido feitos é possível criar um ODBC Connection Manager no Visual Studio e conectar na instância do PostgreSQL. Conforme comentei no inicio, a medida que a quantidade de dados lidos for aumentando e a query no PostgreSQL for ficando mais complexa, as cargas começaram a ficar mais longas e exigir mais recursos do driver para tentar colocar os registros em memória. Chegou ao ponto de erros relacionados a alocação de memória no Visual Studio ocorrerem ao tentar executar um simples preview no Data Flow Source Component, isso devido as restrições do driver relacionado a memória.

Ao perceber este comportamento, em um primeiro momento pensei nas possibilidades de melhorias que deveriam ser feitas para otimizar a carga, a maior parte delas no lado do PostgreSQL, mas ao mesmo tempo fiquei procurando dicas, inclusive devido aos erros apresentados no Visual Studio durante o preview dos dados. No meio dessa pesquisa encontrei a documentação de configurações especificas do driver ODBC do PostgreSQL, uma delas sendo o “Use Declare/Fetch”.

“If true, the driver automatically uses declare cursor/fetch to handle SELECT statements and keeps 100 rows in a cache. This is mostly a great advantage, especially if you are only interested in reading and not updating. It results in the driver not sucking down lots of memory to buffer the entire result set. If set to false, cursors will not be used and the driver will retrieve the entire result set. For very large tables, this is very inefficient and may use up all the Windows memory/resources. However, it may handle updates better since the tables are not kept open, as they are when using cursors. This was the style of the old podbc32 driver. However, the behavior of the memory allocation is much improved so even when not using cursors, performance should at least be better than the old podbc32.”

Esse simples checkbox caiu como uma luva para o meu cenário, no qual estou fazendo apenas leituras de uma massa grande de dados. Com apenas um clique nessa configuração a consulta que buscava os dados de origem não apresentou mais problemas no preview do componente, além de ter ficado extremamente rápida, o que também se refletiu no processo de carga com uma melhora considerável no desempenho.

Não deixem de fazer o teste, mesmo que não estejam passando por problemas de performance agora, essa é a uma otimização que vale a pena aplicar no seu driver ODBC do PostgreSQL.

Até a próxima!

SQL Server Mobile Report Publisher

Olá Pessoal!

Juntamente com o SQL Server 2016, a Microsoft disponibilizou o novo Reporting Services que está com a cara nova em sua sexta edição. Uma série de novos recursos estão disponíveis e entre eles um que me chamou atenção, o Mobile Report. Para quem não lembra a Microsoft adquiriu ano passado o software Datazen que era especializado exatamente na construção de painéis e KPIs em cima da plataforma do Power BI, basicamente o Reporting Services 2016 incorporou essas funcionalidades em sua nova edição..

Assim que descobri essa funcionalidade dentro do Reporting Services comecei a fazer testes e atualmente tenho diversos painéis publicados em produção. Como gostei bastante desse recurso e sua facilidade de uso, resolvi fazer esse post detalhando os passos iniciais de como usar o Mobile Report.

Basicamente com o Reporting Services Mobile Reports é possível rapidamente criar relatórios otimizados para dispositivos mobile conectando diretamente a diversos tipos de fonte de dados on-premises.

O acesso aos painéis construídos pode ser feito pelo próprio browser em uma página publicada pelo Reporting Service ou pelo aplicativo do Power BI, disponível para inúmeros dispositivos diferentes em suas respectivas lojas virtuais.

No aplicativo do Power BI existe uma opção para conexão ao Reporing Services On-Premises, sendo assim, desde que seu dispositivo esteja conectado na mesma rede que o servidor do Reporting Services, será possível acessar os painéis mobile criados.

Apesar de sua facilidade de uso e construção dos relatórios, existem alguns pré-requisitos que devem ser cumpridos para o uso da ferramenta de criação dos relatórios, assim como, algumas dicas que só se aprende no dia a dia do uso.

A ferramenta utilizada para publicação e construção dos relatórios é a SQL Server Mobile Report Publisher. Faça seu download no seguinte link:

https://www.microsoft.com/en-us/download/details.aspx?id=50400

Uma vez instalado o Mobile Report Publisher, o software pode ser inicializado por dentro do portal de gerência do Reporting Services ou acessando diretamente o próprio software.

Com relação a criação dos painéis e seus layouts, existem documentações oficiais da Microsoft para apoiar nos passos iniciais, mas apenas com a pratica é possível explorar a capacidade ferramenta. Segue o link abaixo com a documentação oficial:

https://docs.microsoft.com/en-us/sql/reporting-services/mobile-reports/create-mobile-reports-with-sql-server-mobile-report-publisher

Uma vez que o seu primeiro report tenha sido criado é possível acessá-lo através do próprio navegador da mesma maneira que os tradicionais relatórios paginados, mas com a diferença da interatividade fornecida para cada gráfico utilizado e o redimensionamento de acordo com tamanho do dispositivo que esta acessando.

Para o acesso via smartphones e tablets é necessário efetuar o download do aplicativo do Power BI em sua respectiva loja, no meu caso eu fiz o donwload na Apple Store e o ícone do aplicativo é este abaixo:

Dentro do aplicativo terá a opção para conexão com as credencias da conta no Power BI ou a do SQL Server Reporting Services. Essa conta deverá ter privilégios dentro do portal de gerência do Reporting Services a partir da raiz.

A conexão ao servidor que hospeda o Reporting Services deve ser feita através de seu endereço e para isso as rotas necessárias de conexão devem existir, assim como, possíveis liberações a nível de firewall.

Uma vez conectado ao servidor o Reporting Services será possível navegar nos diretórios nos quais são exibidos não só os relatórios móveis, mas também os KPIs que também podem ser criados e consumidos pelo aplicativo do Power BI. Não vou cobrir o funcionamento desses indicadores, mas segue um bom conteúdo sobre o assunto para os primeiros passos:

https://www.mssqltips.com/sqlservertip/4450/create-a-basic-kpi-in-sql-server-reporting-services-2016/

https://docs.microsoft.com/en-us/sql/reporting-services/working-with-kpis-in-reporting-services

O relatório criado anteriormente no Mobile Report Publisher é exibido da conforme a imagem abaixo em um smartphone. Ao interagir com cada um dos gráficos é possível observar diferentes comportamentos e inclusive, integrações entre eles. Dentro do aplicativo do Power BI existem algumas amostras dos tipos de relatórios que são possíveis de serem feitos e dento do Mobile Report Publisher alguns outros exemplos bem elaborados também auxiliam para o entendimento da confecção de painéis mais complexos.

Através dessa funcionalidade, agora nativa do Reporting Services, será possível expandir o seu uso e empoderar os seus usuários com todas as capacidades da ferramenta. Apesar dos seus benefícios, ainda há muito a ser melhorado, eu senti falta do funcionamento do refresh automático dos relatórios serem iguais a configuração utilizada nos relatórios paginados. Outro ponto é que, mesmo com uma quantidade razoável de dashboards disponíveis para uso, existe margem para melhorar o quesito da personalização, isso levando em consideração os concorrentes do mercado, como por exemplo, o QlikView.

Até a próxima!

SCRIPT BÁSICO PARA LEVANTAMENTO DE INFORMAÇÕES

Olá Pessoal!

Só passando para deixar dois scripts que costumo utilizar para levantar informações a nível de base das minhas instâncias. Atualmente eu tenho um host que centraliza a execução dessas consultas e busca nos demais ambientes através de Linked Servers, a partir dai é possível carregar esses dados em relatórios no Report Services ou no Excel para manter a documentação em dia.

Seguem:



SELECT 'PRODUCAO' AS 'AMBIENTE', @@SERVERNAME AS SERVER_NAME, @@SERVICENAME AS INSTANCE_NAME,
db.NAME COLLATE SQL_Latin1_General_CP1_CI_AI AS DATABASE_NAME ,
db.STATE_DESC AS DATABASE_STATE,
CASE WHEN db.IS_READ_ONLY = 1 THEN 'READ_ONLY' ELSE 'READ_WRITE' END AS 'IS_READ_ONLY',
CASE WHEN db.COMPATIBILITY_LEVEL = 80 THEN '2000'
WHEN COMPATIBILITY_LEVEL = 90 THEN '2005'
WHEN COMPATIBILITY_LEVEL = 100 THEN '2008 R2'
WHEN COMPATIBILITY_LEVEL = 110 THEN '2012'
WHEN COMPATIBILITY_LEVEL = 120 THEN '2014'
WHEN COMPATIBILITY_LEVEL = 130 THEN '2016'
END AS 'COMPATIBILITY_LEVEL ' ,
db.RECOVERY_MODEL_DESC AS 'RECOVERY_MODEL',
db.PAGE_VERIFY_OPTION_DESC AS 'PAGE_VERIFY',
db.SNAPSHOT_ISOLATION_STATE_DESC AS 'SNAPSHOT_ISOLATION',
CASE WHEN db.IS_READ_COMMITTED_SNAPSHOT_ON = 1 THEN 'SIM' ELSE 'NÃO' END AS 'READ_COMMITTED_SNAPSHOT',
CASE WHEN db.IS_AUTO_CREATE_STATS_ON = 1 THEN 'SIM' ELSE 'NÃO' END AS 'AUTO_CREATE_STATISTICS',
CASE WHEN db.IS_AUTO_UPDATE_STATS_ON = 1 THEN 'SIM' ELSE 'NÃO' END AS 'AUTO_UPDATE_STATISTICS',
(Select distinct
substring(
(
Select distinct ','+cast(local_tcp_port as varchar(20)) AS [text()]
from master.sys.dm_exec_connections where local_net_address is not null and protocol_type = 'TSQL'
For XML PATH ('')
), 2, 1000) AS PORT_NUMBER
from master.sys.dm_exec_connections
where local_net_address is not null and protocol_type = 'TSQL')AS PORT_NUMBER
, fl.physical_name COLLATE SQL_Latin1_General_CP1_CI_AI as Physical_Path,
fl.type_desc as 'FILE_TYPE',
(fl.size / 128) as 'FILE_SIZE_MB',
fl.growth as 'GROWTH_MB_OR_%',
CASE WHEN fl.is_percent_growth = 1 THEN 'SIM' ELSE 'NÃO' END as 'PERCENT_GROWTH',
(fl.max_size / 128) as 'MAX_FILE_SIZE_MB',
suser_sname(owner_sid) as 'DATABASE_OWNER'
FROM master.sys.databases as db
join master.sys.master_files as fl on db.database_id = fl.database_id



select @@SERVERNAME as Server_Name, servicename collate Latin1_General_CI_AI as Service_Name,
startup_type_desc collate Latin1_General_CI_AI as Startup_Type,service_account collate Latin1_General_CI_AI as Service_Account,
last_startup_time as Last_Startup_Time, status_desc collate Latin1_General_CI_AI as Status
from master.sys.dm_server_services

As consultas acima só funcionam a partir da versão do SQL Server 2005.

Até a próxima!

Bug no Database Mail do SQL Server 2016!?

Olá Pessoal!

Passei o inicio do ano sem nenhuma publicação mas estou voltando com uma situação curiosa que passei hoje e vou compartilhar com vocês.

Estava em mais um dia de trabalho, dessa vez criando 4 ambientes com SQL Server 2016. Em uma das etapas do meu checklist estava a configuração do tradicional Database Mail, o qual não esperava problemas, pois o relay de e-mail já estava liberado. Com a conta configurada, foi a vez de fazer o teste rápido de envio de e-mail e foi exatamente ai que me deparei com um problema que nunca tinha passado antes.

Apesar de aparentemente o e-mail de teste ter sido enviado, no Database Mail Log nenhum registro foi gravado e após longos de minutos de espera constatei que realmente o e-mail não iria chegar na minha caixa corporativa. Como sou persistente, fiz o teste de envio diversas vezes, além de reconfigurar o Database Mail, inclusive com outra conta também válida. Após esse esforço sem resultado, comecei a procurar por situações parecidas na internet.

Após um tempo de busca me deparei com alguns relatos avulsos no stackexchange informando que esse problema acontece no SQL Server 2016 devido ao .Net Framework. Após essa dica, consegui filtrar melhor minha pesquisa e encontrei alguns outros casos idênticos ao meu, além da solução.

Acontece o seguinte, como sabemos a partir do SQL Server 2016 não existe mais a dependência do SQL Server com o .Net Framework 3.5 que já existia há algumas versões. O pré-requisito é o .NET Framework 4.6.1 que já é instalado junto do próprio SQL Server.

O caso do Database Mail está diretamente ligado ao .Net 3.5, sendo necessário fazer a sua posterior instalação para que esse problema seja resolvido.

Esse caso foi reportado a Microsoft no meio do ano passado e eles finalizaram a thread na época disponibilizando um Cumulative Update para correção. O curioso é que esse mesmo problema foi apresentado para mim hoje e estou com o SQL Server 2016 SP1 na Build (13.0.4411.0), ou seja, com a atualização mais recente disponibilizada no inicio do ano de 2017.

https://connect.microsoft.com/SQLServer/feedback/details/2900323/sql-server-2016-database-mail-doesnt-work-without-net-3-5

Após a instalação do .Net 3.5 toda fila de e-mail de testes foi enviada de uma vez, resolvendo meu problema. Use a consulta abaixo para verificar as mensagens enviadas pelo Database Mail.

SELECT *
FROM sysmail_allitems

Vou tentar reabrir a thread sobre esse problema no Connect, mas até lá vamos aguardar por uma nova atualização ser disponibilizada com a correção.

Até a próxima!

SQL Server

Feedback Provas Betas – 70-761 / 70-764 / 70-767

Olá Pessoal!

Essa semana fiz três provas de certificação Betas do SQL Server 2016. A Microsoft tinha anunciado a liberação de uma série de provas Betas de SQL e Windows a cerca de 2 meses, mas claro que com uma quantidade limitada de vouchers, sendo assim, só consegui pegar as seguintes:

70-761 – Querying Data with Transact-SQL
70-764 – Administering a SQL Database Infrastructure
70-767 – Implementing a SQL Data Warehouse

Após cansativas horas de provas, decidi passar um feedback sobre essas novas provas que irão valer a partir do próximo ano.

70-767 – Implementing a SQL Data Warehouse

Dentre as 3 provas essa foi a que eu achei mais difícil, principalmente por ter alguns casos de uso com bastante informação a ser lida e relida, sendo assim, conclui todas as 60 questões com quase o limite de tempo. Praticamente metade da prova cobriu o uso do SSIS, com questões sobre a Toolbox envolvendo o Control Flow, File System Task, Script Task e XML Task. Já sobre o Data Flow, detalhes específicos de uso do Conditional Split, Merge Join, Merge, Union All, Fuzzy Grouping e CDC Splitter. Tiveram também algumas questões envolvendo dados externos, que no caso seriam informações provenientes do Microsoft Azure, sejam em máquinas virtuais com instâncias Data Warehouse, Azure Blob Storage ou até o Hdinsight com o Hadoop. A publicação de dados on premises como fonte de destino na nuvem também foi uma questão apresentada.

A outra parte da prova ficou dividida entre dois temas, primeiro algumas questões sobre os recursos do Master Data Services, Master Data Management e Data Quality Services, focando inclusive em configurações a serem feitas neles. Em seguida diversas questões similares abordando a criação de índices para otimização de consultas a relatórios, particionamento e compressão para gestão de armazenamento, inclusive com uma questão específica para instâncias no Azure.

Por último ainda teve umas questões sobre o Deployment Model, Integration Services Catalog e implementação de tabelas de fatos/dimensões.

70-764 – Administering a SQL Database Infrastructure

Foram 56 questões bem divididas entre todas as habilidades que deveriam ser testadas, boa parte das questões seguia o estilo de manter as mesmas múltiplas escolhas de resposta, mudando pouco o cabeçalho da questão, esse modelo foi aplicado para escolhas de soluções de alta disponibilidade e estratégias de backup. Os casos de uso apresentados estavam bem claros e foram empregados em algumas questões que necessitavam de um maior contexto. Assim como esperado, muitas questões envolviam o uso do Azure SQL Database, como por exemplo, gerenciamento de backup e Stretch Database.

As questão pontuais focaram em aplicações de índices, estatísticas e configuração de novos recursos, como Query Store, Dynamic Data Masking. O Always On não ficou de fora e foi bem abordado em questões com foco em sua aplicabilidade. Por último ainda teve uma questão sobre as permissões necessárias para o uso do Database Mail.

70-761 – Querying Data with Transact-SQL

Essa prova teve 59 questões e de certa forma, perguntas bem similares. O modelo de manter as mesmas respostas, alterando apenas a pergunta sutilmente foi utilizado bastante. Como já era empregado na antiga prova do mesmo assunto, questões para escrita de Queries foram aplicadas, tiveram cenários para avaliar o emprego dos Joins, CTE, agregações, tratamento de erros e até Pivot Table. As demais questões focaram em cobrir os demais assuntos já esperados, como Functions, Stored Procedures, DML, DDL e temas novos, como a Temporal Tables.

Para mais informações sobre as novas trilhas de certificação para o SQL Server 2016, veja o link abaixo:

https://www.microsoft.com/pt-br/learning/sql-certification.aspx

Até a próxima!

SQL Server