Como transferir logins para outro servidor SQL ou instância

Com qualquer migração de servidor, o ideal é que você queira que as coisas sejam executadas sem problemas e recriar os logins e senhas do SQL Server do zero não é algo que você realmente queira ou deveria estar fazendo.

Atualmente, estou envolvido em uma substituição completa de servidor para um dos nossos clusters do SQL Server. Nesse ambiente, a autenticação mista está ativada.

No passado, quando eu estava aprendendo sobre a transferência de bancos de dados de um servidor, descobri rapidamente que a restauração de um banco de dados de um servidor para outro não era tão simples quanto eu esperava. Eu me deparei com um obstáculo que eu precisava superar na forma do SID.

Então, primeiro vou falar sobre as maneiras pelas quais você pode transferir logins de um SQL Server para outro e explicar sobre o SID mais abaixo no post.

Formas de transferir logins e senhas SQL entre instâncias do SQL Server

Agora você pode fazer isso da maneira mais fácil ou mais difícil!

Você pode configurar manualmente cópias de seus logins em seu novo servidor com senhas (se as conhecer) ou automatizar essa tarefa com a ajuda de um script T-SQL ou SSIS.

Eu não sei sobre você, mas eu não gosto de manual e eu recomendo que você automatize isso a cada vez.

Usando o T-SQL para transferir logins e senhas entre instâncias do SQL Server

Você pode obter um script da Microsoft aqui . Você executa o script, ele cria um procedimento armazenado chamado sp_help_revlogin no seu SQL Server. Quando você executa o procedimento armazenado, ele gera instruções CREATE LOGIN para todos os logons de seu servidor, incluindo suas senhas e “sids”.

Você pode então pegar esse script, executá-lo no seu novo SQL Server para configurar os logins do servidor lá. Obviamente, tome cuidado para examiná-lo antes de executá-lo, para ter certeza de que ele capturou tudo e para garantir que você não esteja copiando nada que não seja necessário em sua nova instância.

O script configurará os logins em seu novo servidor usando o idioma padrão configurado para esse servidor, portanto, verifique se ele está configurado corretamente primeiro.

Usando a tarefa de transferência de logins no SSIS para copiar logins e senhas entre instâncias do SQL Server

Há uma tarefa de logons de transferência no SSIS que também fará o trabalho. Na imagem em anexo, estou usando a nova versão do SSIS que é fornecido com o SQL Server 2012. A tarefa logins de transferência cai sob a rubrica “outras tarefas” na caixa de ferramentas SSIS e está disponível em versões antigas do SSIS.

Então, você pode ver na captura de tela que a tarefa de logins de transferência é bastante autoexplicativa. O melhor do SSIS é que ele segura a sua mão de várias maneiras e torna as coisas o mais fáceis possíveis.

Você pode criar conexões a partir desta tarefa, inserir os detalhes de login para sua origem e destino, escolher quais logins copiar e configurar a tarefa para se comportar de maneiras diferentes quando o login existir.

Como você pode ver, circulei “CopySids”, então agora é um bom momento para escrever sobre eles em mais detalhes.

Então, o que é um SID?

SID é abreviação de “identificador de segurança” Um SID é um id interno que é atribuído a um login de servidor quando o login é criado.

O SID pode ser visualizado consultando a visualização do sistema sys.server_principals ou você também pode visualizá-lo observando sys.syslogins

1	SELECT principal_id, sid, name

2	FROM sys.server_principals

1	SELECT sid, name

2	FROM sys.syslogins

Cada login do servidor, seja um logon do SQL, login do Windows ou um que tenha sido criado usando um certificado ou uma chave assimétrica, é atribuído a um SID. Então vamos imaginar um cenário de exemplo onde um novo login é criado e por acaso é um login do SQL Server, esse login é chamado “server_login1”.

Em seguida, você cria um login do banco de dados dentro do banco de dados AdventureWorks2012 usando o login do servidor recém-criado. Vamos chamar isso de "db_login1".

Agora veja o SID em sys.server_principals e sys.database_principals em AdventureWorks2012 e o SID será do mesmo valor.

1	SELECT sid

2	FROM sys.server_principals

3	WHERE name = 'server_login1'

4

5	USE AdventureWorks2012

6

GO

7	SELECT sid

8	FROM sys.database_principals

9	WHERE name = 'db_login1'

A menos que você crie seus logins em seu novo servidor usando um método como o sp_help_revlogin ou a tarefa de logons de transferência do SSIS com “CopySids” habilitado, seus logins serão criados com novos SIDs.

Não tenho certeza se é possível que seu novo servidor atribua o mesmo SID, mas sei que é muito improvável. Talvez você tenha mais chances de ganhar o prêmio da loteria 

Então, que problemas isso causa?

Como os SIDs são diferentes, quando você copia / restaura os bancos de dados do servidor antigo, os logins do banco de dados simplesmente não funcionarão, pois ficarão órfãos de seu SID original.

Você pode encontrar esse erro, por exemplo, se tentar adicionar um login de servidor a um banco de dados que acabou de ser copiado.

Erro 15023: o usuário já existe no banco de dados atual

Isso precisará ser corrigido.

Existe um procedimento que você pode executar para fazer isso e, basicamente, leva o login do servidor e o login do banco de dados e os associa ao nome com o mesmo SID.

Este procedimento é chamado de  sp_change_users_login  e eu vou falar sobre isso nos próximos posts.

Gustavo Damatto
MCSD - Microsoft Certified Solutions Developer

linkedin.com/in/damatto

Ribeirão Preto-SP

8 Novas DMVs e DMFs do SQL Server 2017

As exibições de gerenciamento dinâmico (DMVs) e as funções de gerenciamento dinâmico (DMFs) são exibições do sistema e funções do sistema que retornam metadados do estado do sistema. Ao consultar os objetos do sistema relacionados, os administradores de banco de dados podem entender os componentes internos do SQL Server. Ele nos permite monitorar o desempenho da instância do SQL Server e diagnosticar problemas com ela.

O SQL Server 2017 vem com várias exibições de gerenciamento dinâmico novas e aprimoradas e funções de gerenciamento dinâmico que ajudarão os DBAs a monitorar a integridade e o desempenho de instâncias do SQL Server. Alguns DMVs existentes, como sys.dm_os_sys_info e sys.dm_db_file_space_usage , foram aprimorados. Alguns também foram recém-construídos e estão disponíveis apenas para o SQL Server 2017.

Os novos ou aprimorados DMVs / DMFs se enquadram nas três categorias:

• Banco de dados relacionado
  • sys.dm_db_log_stats
  • sys.dm_db_log_info
  • sys.dm_db_stats_histogram
  • sys.dm_db_file_space_usage
  • sys.sys.dm_db_tuning_recommendations
• Transação relacionada
  • sys.dm_tran_version_store_space_usage
• Relacionado ao sistema operacional do SQL Server
• sys.dm_os_host_info
• sys.dm_os_sys_info

sys.dm_db_log_stats

Vamos dar uma olhada neles criando uma nova consulta aqui e digitando select * from . A primeira visão com a qual quero trabalhar é sys.dm_db_log _ stats . Ele requer o número do ID do banco de dados que você quer investigar, então eu vou digitar o número um dentro do parêntese para indicar o banco de dados mestre.

Vamos em frente e executar a declaração. Este DMV retorna informações sobre os arquivos de log de transação. As informações incluem o modelo de recuperação do banco de dados.

Podemos ver que o banco de dados Python2017 está usando o modelo de recuperação FULL. As outras colunas fornecem o tamanho total do log em megabytes. Além disso, podemos ver algumas informações sobre a última vez que o log foi feito backup. Nesse caso, o log de transações do banco de dados nunca foi submetido a backup, portanto, está relatando a partir de 1º de janeiro de 1900.

É tão fácil projetar os dados para o banco de dados inteiro através da junção cruzada com a visualização do sistema sys.databases. A saída derivada fornece informações muito úteis sobre o banco de dados e a estrutura de log. 

sys.dm_db_log_info

O sys.dm_db log_info também lida com os arquivos de log. Requer o ID do banco de dados para entrada. Esta visão analisa especificamente os arquivos de log virtuais ou VLFs. Estes compõem o log de transações do banco de dados e ter um grande número de VLFs pode afetar negativamente o tempo de inicialização e recuperação do banco de dados. Com essa visão, podemos ver quantos VLFs seu banco de dados tem atualmente, junto com seu tamanho e status. Esta exibição de gerenciamento substitui o comando do console do banco de dados, informações de log do dbcc .

No exemplo a seguir, T-SQL, podemos ver que a saída do DMV é unida a sys.databases, uma visualização do sistema para obter dados consolidados sobre todos os bancos de dados da instância do SQL

sys.dm_db_tuning_recommendations

O novo DMV retorna as informações detalhadas das recomendações de ajuste. Isso requer uma habilitação da opção de ajuste automático (Automatic tuning) . Se o mecanismo de banco de dados identificar o plano que está causando a regressão, uma sugestão será feita para corrigi-lo usando esse dmv.

sys.dm_tran_version_store_space_usage

O sys.dm_tran_version_store_space_usage é usado para controlar o uso do armazenamento de versão por banco de dados em tempdb. Isso é bastante útil no monitoramento proativo do dimensionamento de tempdb no requisito de uso de armazenamento de versão de cada banco de dados. Este DMV retorna o espaço de armazenamento de versão agregada consumido em tempdb por banco de dados. Não possui argumentos que precisem ser fornecidos. Os resultados mostram o databaseID, a contagem de páginas reservadas em tempdb para registros de armazenamento de versão e o espaço total em kilobytes.

Por exemplo, o banco de dados Python2017 é configurado com a opção ALLOW_SNAPSHOT_ISOLATION.

sys.dm_db_stats_histogram

O próximo dmv é sys.dm_db_stats_histogram . Ele requer dois argumentos, object_ID e um stats_ID que obtemos na visualização do sistema sys.stats . Este DMV é semelhante a dbcc show_statistics com o comando histogram.

Vamos dar uma olhada nas estatísticas desse objeto. Os resultados contêm detalhes sobre a frequência de ocorrência de cada valor distinto na tabela. Estes são os valores que o otimizador de consulta usa para ajudar a escolher um plano ao executar uma consulta. 

Essa tabela em particular tinha 10.000 registros com um inteiro único em cada um. As linhas são avaliadas entre os intervalos 0 e 1, 1 e 9.999 e 10.000. Portanto, o histograma mostra que os dados são divididos em três etapas. A primeira etapa inclui os valores entre zero e a chave alta do intervalo de um. O segundo passo inclui os números entre 1 e 9999. Ele mostra que temos 99.997 registros que estão dentro desse intervalo e um registro que corresponde exatamente à chave alta de 9999. Finalmente, o último intervalo é para todos os números entre 9999 e 10000. Não há nenhum dentro desse intervalo, e há apenas um único que corresponde à chave alta.

sys.dm_os_host_info

O sys.dm_os_host_info retorna informações sobre o sistema operacional do host que a instância do servidor está executando. Isso também será executado em máquinas Linux e também identificará a distribuição do Linux. 

sys.dm_os_sys_info

O sys.dm_os_sys_info retorna algumas informações úteis sobre os recursos usados, bem como disponíveis para a instância do SQL Server. As três colunas recém-adicionadas são:

• socket_count
• cores_per_socket
• numa_node_count

sys.dm_db_file_space_usage

O sys.dm_db_file_space_usage é o DMV que retorna informações de uso de espaço do banco de dados.

A coluna recém-adicionada coluna modified_extent_page_count nos dá a flexibilidade para gerenciar backups. A contagem de páginas modificada pode ser usada para rastrear mudanças diferenciais no banco de dados desde o último backup completo para decidir se um backup diferencial é benéfico. A coluna é o número total de páginas modificadas nas extensões alocadas do arquivo desde o último backup completo do banco de dados.

Demo:

Vamos considerar uma estratégia de backup para VLDB

1. Backup completo semanal
2. Backup diferencial diário
3. Backups de log por hora

O backup diferencial é uma coleção de páginas modificadas desde o último backup completo. O DMV sys.dm_db_file_space_usage introduz uma nova coluna chamada modified_extent_page_count que nos fornece detalhes sobre as páginas modificadas desde o último backup completo. O valor dessa coluna é usado como referência para iniciar o processo de backup diferencial.

1. Backup do banco de dados
2. Consultar o  sys.dm_db_file_space_usage  dmv
3. Validar a alteração diferencial, a quantidade de dados alterados desde o último backup completo; vemos uma mudança de 14,55% nos dados
4. Construa a lógica para iniciar um processo de backup diferencial; no nosso caso, se houver uma alteração de 10% ou mais, um backup diferencial será iniciado

Wrapping up

A visão geral das novas exibições de gerenciamento dinâmico e das funções de gerenciamento dinâmico fornecidas com o SQL Server 2017 foi explicada em detalhes. Eles podem ser usados para obter mais informações sobre o status do sistema. Pode ser informativo e útil para os administradores entenderem os metadados pertencentes à instância do SQL Server. Para mim, o sys.dm_db_file_space_usage e o sys.dm_db_tuning_recommendations são algo que eu estava procurando há muito tempo. E quanto a você? Fiquem à vontade para comentar.

Referências

• Como analisar o desempenho do banco de dados do SQL Server usando o T-SQL
• sys.dm_tran_version_store_space_usage
• DBCC SHOW_STATISTICS
• sys.dm_db_file_space_usage
• sys.dm_os_sys_info
• Blog do Mecanismo de Banco de Dados do SQL Server

Gustavo Damatto

MCSD - Microsoft Certified Solutions Developer

linkedin.com/in/damatto

Ribeirão Preto-SP

Numero De Conexões por aplicação - SQL Server

Nesta dica rápida, iremos demonstrar a utilidade da SYSPROCESSES, onde podemos identificar as sessões conectadas na nossa instância do SQL Server e especifico detalhar por aplicações. 

Esta informação é muito útil quando planejamos uma janela de manutenção ou quando queremos identificar se há um aumento no fluxo dados e quantidade de sessões por usuário.

abraços

Gustavo Damatto
MCSD - Microsoft Certified Solutions Developer

linkedin.com/in/damatto

Ribeirão Preto-SP

SQL Server - Listen (acesso) por mais de uma Porta TPC/ip

SQL Server - Como permitir acesso em várias portas TCP no SQL Server?

Durante implementação em um novo cliente, a equipe que prestava serviços responsável pela infraetrutura no cliente perguntou se era possível executar o SQL Server em várias portas TCP. A resposta é sim , é possível.

Para configurar a instancia de Banco de Dados do SQL Server para escutar em uma porta TCP adicional, abra primeiro o SQL Server Configuration Manager. Uma vez aberto, expanda Configuração de Rede do SQL Server e clique em Protocolos para sua instância e, em seguida, expanda Protocolos para e selecione TCP / IP. Depois disso, você pode percorrer todo o caminho e vá para a opção IPAll e clique em propriedades.

Agora, se você tiver um padrão instalado, verá uma porta padrão 1433 listada lá. Se você quiser adicionar qualquer porta adicional aqui, basta adicionar uma nova porta com vírgula por lá. Por exemplo, se você quiser adicionar a porta 1600 juntamente com 1433, poderá especificar portas como 1433,1600.

A imagem a seguir demonstra como você pode configurar o mesmo com o SQL Server Configuration Manager.

Em seguida, você deve obrigatoriamente reiniciar seus serviços do SQL Server para que essa nova porta seja efetivada. Você também pode reiniciar os serviços do SQL Server no SQL Server Configuration Manager.

Certifique-se que o seu firewall permita que o endereço IP e a porta se conectem ao servidor. Como pode ver, é realmente simples configurar o SQL Server para ouvir várias portas TCP.

Um Grande abraços sucesso

Gustavo Damatto

MCSD - Microsoft Certified Solutions Developer



linkedin.com/in/damatto

Ribeirão Preto-SP

Procurando uma string em um job (ou step) - SQL Server

Boa tarde pessoal!

Hoje iremos compartilhar de um pequeno, mas bem funcional script para vocês desenvolvedores, que utilizam de jobs (SQL Server Agente) um busca completa por strings dentro do catálogo do banco de dados para encontrar uma determinada string no título do job ou na linha de comando de algum step. 

-- Procura por uma string dentro dos Jobs

SELECT 

NAME NOME_JOB, STEP_NAME ,COMMAND CODIGO, LAST_RUN_DATE

FROM

MSDB.DBO.SYSJOBS A

JOIN MSDB.DBO.SYSJOBSTEPS B ON A.JOB_ID = B.JOB_ID

WHERE

COMMAND LIKE '%BACKUP%'

ORDER BY

NAME

Estes são os resultados da pesquisa e jobs que contem a respectiva

string 'Backup'

Bom é isso aí pessoal,
Bom uso! 

  

Gustavo Damatto
MCSD - Microsoft Certified Solutions Developer

linkedin.com/in/damatto

Ribeirão Preto-SP

Collations vs. Performance No SQL-SERVER

Recentemente me deparo com um fato aparentemente pouco conhecido sobre o SQL Server 

- especificamente os Collations do SQL Server e como eles afetam o desempenho.

Durante uma atualização de normalização para seu banco de dados, 

um de nossos clientes teve problemas de desempenho estranhos e inconsistentes

e não conseguiu encontrar uma solução por um bom tempo.

Eles tiveram duas perguntas:

1) A consulta original (não normalizada): varreu 2,5 milhões de linhas e foi executada por 3 segundos.

2) A nova consulta (normalizada): varreu 10 milhões de linhas semelhantes e foi executada por 50 segundos.

Cada consulta foi executada em uma tabela diferente, mas os dados nas tabelas eram idênticos 

(a diferença era devido à normalização). A coluna que procuramos foi varchar (255) em ambas as tabelas.

Por um tempo, não conseguimos entender a razão da enorme diferença de desempenho.

Algo simplesmente não parecia certo.

Durante minha investigação notei uma intrigante diferença nos planos de execução, 

e isso é na primeira consulta (a rápida) que uma conversão implícita foi realizada para a coluna varchar,

mas tal conversão não ocorreu na consulta lenta. No começo eu não prestei atenção porque, 

como DBA, eu fui treinado para pensar que o desempenho de HURT de conversões implícitas ou explícitas, 

e não o contrário, então isso não poderia ter sido o motivo.

Mas eventualmente (e com um pequeno empurrão) Eu dei uma olhada para descobrir 

por que essa conversão estava acontecendo em primeiro lugar.

Descobrimos que o Collation da coluna varchar era diferente entre as duas tabelas.

A primeira tabela tinha um Collation Latin SQL, mas a segunda tabela tinha um agrupamento ANSI 

(ou Windows Collation, se você preferir chamá-la assim).

Nós tentamos mudar o Collation da segunda tabela para o Collation SQL também - e pronto!

Nossa consulta agora executada por 5 segundos - 10 vezes mais rápido!

Procurei materiais sobre esse problema e não consegui encontrar muita coisa além desse (aparentemente importante) artigo Microsoft: http://support.microsoft.com/kb/322112

Notas:

As regras de classificação Unicode são muito mais complexas do que as regras para uma ordem de classificação SQL não-Unicode. Quando o SQL Server compara dados Unicode, os caracteres recebem um peso modificado dinamicamente com base na localidade do agrupamento. Os dados também são modificados por configurações de estilo de comparação, como largura, acento ou sensibilidade do Kana. As rotinas de classificação Unicode suportam comportamentos de ordenação mais inteligentes, como a ordenação de palavras.

[…]

• Se você estiver armazenando e manipulando seus dados usando tipos de dados não-Unicode (char, varchar, text) e estiver usando um collation SQL, as comparações de sequência serão executadas com uma ordem de classificação SQL não-Unicode .

• Se você estiver armazenando e manipulando seus dados usando tipos de dados não-Unicode (char, varchar, text) e estiver usando um collation do Windows, as comparações de sequência serão executadas com as regras de classificação Unicode .

E Isso pode fazer com que determinadas operações que são extraordinariamente dependentes do desempenho da classificação de sequências demorem mais e usem mais CPU do que uma operação semelhante executada com um collation SQL.

• Se você estiver usando tipos de dados Unicode (nchar, nvarchar, ntext), não haverá diferença no comportamento de classificação para o SQL e os collations do Windows. Ambos usarão regras de classificação Unicode.

Em suma, os Windows collation têm um desempenho tão lento quanto os tipos de dados Unicode, 

e os SQL Collation executam muito mais rapidamente.

No entanto, a ordem de classificação deve ser levada em consideração porque pode alterar os resultados.

Embora no caso deste cliente, a primeira tabela era a maneira default de qualquer maneira,

então estamos realmente “consertando” a diferença na ordem de classificação (caso ela existisse).

Posteriormente, também testamos o desempenho de um agrupamento binário e concluímos que

ele era ainda mais rápido em consultas que exigiam comparação e agrupamento, 

mas funcionava pior em consultas que exigiam classificação. Isso faz sentido,

já que os agrupamentos binários devem ser analisados ​​textualmente antes de serem classificados 

- uma operação mais pesada do que fazer o mesmo para collations SQL 

- ao contrário de uma operação de comparação que pode ser tão rápida quanto fazer XOR de dois valores.

Conclusão

SQL ou Windows Collations parecem ter outra importancia que precisamos tomar ao projetar um banco de dados

E analisar seu desempenho - mas cuidado! A alteração do collation pode alterar a ordem de classificação do seu texto,

portanto, se o seu aplicativo depender de uma determinada forma de classificação, será necessário garantir que 

o seu collation corresponda a melhor performance de classificação.

O mesmo artigo microsoft mencionado acima afirma que alterar índices ou como uma consulta é gravada

fará uma diferença maior do que alterar collations, mas é surpreendente a diferença enorme no desempenho

que um collation causou neste exemplo, e tentamos reescrever a consulta várias vezes e alterando os índices,

mas nada fez uma grande diferença, como o Collation.

Encontrou um cenário semelhante?  Problemas semelhantes (ou até mais interessantes - diferentes)?

Conte-nos sobre isso nos comentários!

Ref: Eitan Blumin 

Gustavo Damatto
MCSD - Microsoft Certified Solutions Developerlinkedin.com/in/damatto

Ribeirão Preto-SP

SQL SERVER - Comparação de desempenho IN vs OR

Esta são algumas daquelas perguntas que nunca envelhecem e algumas perguntas e eu acredito que,

estaremos repetindo por muitos anos no futuro. Outros dia recebi esta pergunta durante meu trabalho.

Questão era sobre Comparação de Desempenho IN vs OR

Embora pessoalmente, eu já tenha respondido a essa pergunta diversas vezes, aqui no blog podemos detalhar melhor.

Pergunta: Qual é a consulta executada mais rapida? 

1. A consulta com o operador IN
2. A consulta com o operador OR

Bem, a resposta é ambos são iguais . Sim, essa é a verdade.

Sim, isso está correto, o mecanismo otimizador do SQL Server mapeia internamente automaticamente todos os valores especificados no operador IN para o operador OR. Como o SQL Server converte IN para OU automaticamente, essas são as principais razões para ambos terem desempenho idêntico.

No entanto, se você examinar cuidadosamente o Operador de Filtro no plano de execução da consulta em que utilizei a condição IN, poderá ver que o plano de execução do SQL Server converteu todos os valores do operador IN para OR automaticamente.  

Deixe um comentário e deixe-me saber se você gostaria de ver qualquer comparação de desempenho semelhante.

referências Pinal Dave.