Archive

Posts Tagged ‘SQL Server 2008’

Trabalhando com Credenciais e Proxy Account no SQL Server

Este post é dedicado à mulher mais importante da minha vida, que fez ser o que sou hoje com muita luta e dedicação minha mãe, Francisca de Alencar.

Após a dedicatória, vamos falar sobre SQL.

Bom dia. Galera, blz? Hoje vamos falar de alguns recursos que ganharam muita força a partir do SQL Server 2005. Segurança, no SQL Server foram incluídas diversas features, como por exemplo: TDE, Certificados, Credenciais, Proxy Account e etc.

No post , falamos de como podemos utilizar certificados para atribuir permissões especificas, sem ter que aumentar o nível de acesso de nossos logins. Nesse post vamos falar de como utilizar credenciais e proxy account. Um dos problemas que tínhamos no SQL Server 2000 era que quando era necessário executar o comando xp_cmdsheel, o login que disparava a chamada do comando precisava ser membro do server role sysadmin para poder executar com sucesso. A partir do SQL Server 2005, temos como permitir esse comportamento, sem que o login seja membro do sysadmin utilizando proxy account.

Imagine o cenário: No nosso ambiente temos diversos pacotes SSIS de integrações, porém, o login que os executa é um login do tipo SQL Server authentication que não pode fazer parte do server role sysadmin. Um dos motivos é que a publicação do pacote é feita através de file system. Thiago existe outra maneira mais segura de fazer isso? Claro, que sim meu querido blog, mas, no momento foi essa solução implementada.

Obs: Com o file system, é aberto uma brecha de segurança, pois, o login que tiver acesso a pasta onde os arquivos serão publicados, podem abrir o webconfig do pacote.

É triste dizer, mas, vi muitos DBAs que tiveram esse problema e colocaram o login dentro do server role sysadmin para “resolver” o problema. A solução que encontrei sem ter que dar a permissão “motherfucker” para o login que executa o pacote será explicada a seguir.

Vamos criar o login do tipo SQL Server dentro do server role bulkadmin.

/****** Object:  Login [PkgSSIS]    Script Date: 09/19/2012 20:00:58 ******/

IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N’PkgSSIS’)

DROP LOGIN [PkgSSIS]

GO

 

/* For security reasons the login is created disabled and with a random password. */

/****** Object:  Login [PkgSSIS]    Script Date: 09/19/2012 20:00:58 ******/

CREATE

            LOGIN [PkgSSIS] WITH PASSWORD=N’pkgssis’,

                  DEFAULT_DATABASE=[master],

                  DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

 EXEC sys.sp_addsrvrolemember @loginame = N’PkgSSIS’, @rolename = N’bulkadmin’

GO

 ALTER LOGIN [PkgSSIS] ENABLE

GO

Vamos atribuir permissão para a base de dados utilizadas nos post anteriores e para o msdb. No banco dbPermissoes o login terá que ficar dentro do database role DR_EXEC. Dentro do msdb o login ficará dentro do database role SQLAgentUserRole e SQLAgentReaderRole.

dbPermissoes:

Atribuição ao Role:

Msdb e atribuição aos roles:

 Por enquanto até aqui sem muitas surpresas e complicações. Agora queremos que esse login tenha permissão de executar pacotes, quando tentamos executar pacotes sem ser membro do server role sysadmin, a mensagem é retornada:

Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy accoutnt.

Primeiro temos que criar uma credencial, conforme script:

–Cria a credencial utilizando a conta de dominio SQLSVC e coloque o password da conta.

USE [master]

GO

CREATE CREDENTIAL [SSISCredencial] WITH IDENTITY = N’Dominio\SQLSvc’, SECRET = N’PasswordAqui’

GO

Vai ficar da seguinte forma:

Agora iremos criar um proxy account , atribuindo a essa credencial.

USE [msdb]

GO

 /****** Object:  ProxyAccount [SSISProxy]    Script Date: 09/18/2012 13:31:46 ******/

IF  EXISTS (SELECT name FROM msdb.dbo.sysproxies WHERE name = N’SSISProxy’)

begin

      EXEC msdb.dbo.sp_delete_proxy @proxy_name=N’SSISProxy’

end

GO

 USE [msdb]

GO

 /****** Object:  ProxyAccount [SSISProxy]    Script Date: 09/18/2012 13:31:46 ******/

EXEC msdb.dbo.sp_add_proxy @proxy_name=N’SSISProxy’,@credential_name=N’SSISCredencial’,

            @enabled=1

GO

EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’SSISProxy’, @subsystem_id=3 — SubSystem: CMDExec

GO

EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’SSISProxy’, @subsystem_id=11 — SubSystem: SSIS

GO

EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N’SSISProxy’, @login_name=N’PkgSSIS’

GO

O subsystem de Id 3 e 11 são os subsystem para utilizar CmdExec e SSIS..No modo gráfico é possível verificar que eles estão “ticados”:

Clique na gui “Principals” e verifique que o login PkgSSIS está marcado como SQL Login:

Tudo isso, foi feito no script anterior. Isso está sendo mostrado para que o leitor consiga identificar o que está sendo criado e onde fica.

Calma galera! Já estamos quase lá. Agora vamos ver onde essas informações devem ser atribuídas no Job. Neste meu caso o job já foi criado, apenas colocarei os prints aonde tive que modificar.

O owner do job, que executará será o login: Pkgs:

No steps podemos ver que o job é um pacote SSIS. Temos apenas que mudar quem executa o nosso job que não será mais a conta do SQL Server Agent e sim no Proxy.

Agora iremos executar o job e “Eureca”.

O que tem dentro do pacote? Apenas uma procedure que executa um comando update sem Where dentro de uma proc. A procedure é chamado dentro de um “Execute SQL Task” do SSIS. Não existe nada de funcionalidade, pois, o post é apenas pra mostrar que é possível executar um pacote sem ter que dar permissões “absurdos” para um login especifico.

Espero que tenha ajudado.

Abs

Configurando o Operador no SQL Server

Mais uma vez, bom dia galera. Hoje esse será um post rápido, pois, ta muito tarde(Post Agendado..rs.) e está na hora de ir para a cama. Este post de hoje é dedicado ao um homem que sempre me espelhei e sempre me deus bons conselhos, meu Pai, José Adão Carlos.

Em um ambiente de banco de dados corporativo, o DBA sempre deve ter em mente uma coisa: Como automatizar meu trabalho, para que sempre possa agir com pró-atividade? A resposta é meio simples,  automatize e sempre seja avisado dos problemas antes de eles começarem aparecer. Na prática não é tão fácil, mas, tente manter isso em mente.

No post anterior eu falei sobre database email e como configurá-lo. Mas, até ai beleza, o que faço com isso? Vamos automatizar nossas rotinas!?

Imagine que você criou um job de backup, e que ele deve ser feito toda  noite. Esse job é um job critico, e no caso dele falhar você precisa ser informado. Criaremos um operador, que trará a lista dos emails que devem ser informados. Além do meu e-mail adicionarei o pessoal do suporte que fica 24×7, assim, a cobertura da solução é maior.  Com o Database e-mail configurado e o operador criador, apenas temos que mudar uma opção no JOB em questão. Let’s go.

Expanda o item SQL Server Agent e clique com o botão direito sobre Operator e em seguida em New Operator.

Aparecerá a seguinte tela:
Name: Nome do Operador.
Email name:  Lista de emails que irão receber a notificação (os emails devem ser separados por ; )

Preencha os dados e clique em OK.

Notificação

Após estes procedimentos as notificações devem ser adicionadas nos Jobs.

Clique em um job com o botão direito vá em propriedades. Em notification coloquem as seguintes configurações:

Espero ter ajudado….

Abs e até o próximo post

O que é o DTA?

Amigos da comunidade, blz? Esse post é dedicado a um outro amigo de trabalho. O DBA Felipe Melo , conhecimento SQL Geek em replicação, tunning e T-SQL. Sempre trocamos uma “figurinha” nos desafios profissionais, afinal, sempre é bom ter uma outra opinião. A certeza é: Nunca estamos totalmente certos! Mão na massa!?

O Database Engine Tuning Advisor é uma das “novas” features do SQL Server, ele analisa arquivos de carga de trabalho e propõe alterações no banco de dados, a fim de melhorar seu desempenho geral.

Para as alterações propostas, o Tuning Advisor também mostra o impacto que causará cada modificação.

Entre as suas capacidades, estão:

  • Query Optimazer, para propor índices e visões indexadas;
  • Recomendação de particões;
  • Análise de impacto das recomendações;
  • Fornecimento de informações sobre o número de consultas e o número de índices.

Opções de ajuste:

  • Quais objetos o Tuning Advisor poderá recomendar;
  • Quais partições analisará;
  • Quais estruturas serão mantidas no banco de dados;
  • Espaço máximo para recomendações;
  • Número máximo de columas por índice.

Criando analises com o DTA

Para iniciar o DTA, vá em StartAll / Programs / Microsoft SQL Server 2008 / Performance Tools / DataBase Engine Tunning Advisor.

Quando o programa for iniciado, clique em File/New Connection.

Aparecerá a tela de conexão para qual servidor você deseja criar a análise

Obs: Para o exemplo será usado um ambiente de testes.

O servidor será mostrado no canto superior direito da tela, clique com o botão direito e escolha a opção New Session.

A seguinte tela será mostrada:

Clique na guia Tunning Options e será mostrado a seguinte tela:

Physical Design Structure(PDS) to use in database: Esta opção avalia o que a Engine do DTA irá analisar referente a objetos de design na estrutura do banco(índex,índex views e etc). A opção de Índex views é desabilitada no SQL Server STD.

Partitioning strategy to employ: A opção de particionamento, verifica se existem objetos que podem ser particionados e avalia o particionamento que já existente. Disponível somente na versão Enterprise do SQL Server.

Physical Design Structure(PDS) to keep in database: Avalia a estrutura fisica do banco, abalia se existem índices clustereds e nonclustereds devem ser deletados(ele não deleta os indices), ou se existe algo divergente na no modelo como um todo. O padrão é Keep All existing PDS.

Voltando a guia General, aonde:

  • Session Name: Nome da Sessão
  • WorkLoad: A origem de ondes virão os dados que serão analisados(no caso do profiler o mesmo pode ser um arquivo ou uma tabela)
  • DataBase for WorkLoad Analysis: Aonde serão gaurdadas as analises temporarias.
  • Select DataBase and tables to tune: Lista de banco de dados e de tabelas que serão parte da analise. (sempre coloque os bancos de dados que [*] foram filtrados no profiler)

Coloque o nome no profiler e escolha o caminho aonde está o arquivo de trace do profiler. Ao clicar no Radio Button File, clique no binóculo a esquerda e será aberta a tela do Windows. Escolha o profiler que deseja analisar.

Clique em abrir.

Caso o trace tenha sido guardado em uma tabela, marque o Radio Button Table, clique no binóculo a esquerda e será aberta a tela de conexão:
Escolha qual banco, schema e tabelas estão os profilers que foram armazeandos.

Obs:  O login qual abriu a sessão no DTA deve ter acesso as tabelas que estão os traces, caso contrário, uma mensagem de erro será retornada.

 

O preenchimento deve ficar parecido com a tela abaixo:

 

Clique no botão Start Analysis. Após clicar em Star Analysis, uma tela de progresso será exibida.

 

Na guia recomendações, é apresenta a porcentagem de melhoria da análise.

 

No menu Actions aparecerá a opção de aplicar as recomendações ou savá-las.
Clique em Save Recommendations

Será gerado um arquivo .sql com as recomendações.

Espero Ter ajudado

 

Atualizando a Edição do SQL Server 2008

Caros, bom dia.

Este post é dedicado ao meu amigo Lucas Souza, um DBA com quem venho aprendendo muito. Está sempre me acompanhando nos processos aqui na empresa e me instruindo.

Hoje vamos falar sobre um assunto bastante simples, porém, foi a primeira vez que tive que realizar o procedimento, atualizar a edição do SQL Server. Hoje temos aqui na empresa o ambiente de desenvolvimento, produção, testes e homologação. Junto com a equipe começamos uma iniciativa de padronizar a instalação do SQL Server, isso inclui Edição utilizada, service pack e cumulative updates. Após baixar o SP2 da versão do SQL Server 2008 R2, comecei a aplicar a instalação no ambiente de desenvolvimento, testes e homologação. Quando me deparei com um ambiente de homologação que está com a versão Standard do produto. E agora? Preciso reinstalar o SQL Serve novamente? Claro que não…

Vamos ao passo-a-passo

Inicialmente você deve copiar o instalador do SQL Server para a máquina qual deseja atualizar. De um duplo clique no setup.exe a seguinte tela será apresentada:

Clique em Yes e aparecerá o SQL Server Installation Center. No lado esquerdo clique em Maintenance.

Clique no Edition Upgrade, após isso, apenas devemos seguir os passos. Vamos fazer juntos?

O SQL Server irá fazer uma verificação, se todos os itens verificados passarem por essa verificação, prossiga. Caso algum item falhar, recomendo que seja ajustadoo item em questão e o processo seja refeito.

É altamente recomendado que todos os itens sejam aprovados no check que o SQL Server faz, só assim podemos garantir que não teremos problema futuramente.

Clique em OK.

Esse processo é bem parecido com o processo de instalação, após o OK confirmado na tela acima, aparecerá a opção da chave do produto, Clique em Next:

Marque o checkbox de “Aceito os termos” e clique em next:

Na próxima tela, podemos perceber que o instalador traz a versão atual do produto e para qual instancia será feito o “upgrade”.

Clique em next.O SQL Server fará uma verificação antes de lhe dar a opção de Update.

Clique em next. Você verá um sumario bem importante, que diz para qual versão está sendo modificado o SQL Server, qual ação está sendo tomada (No caso Edition Upgrade) e a lista de features que sofrerão com esta atualização:

Clique em Upgrade. Após isso é só aguardar o SQL Server terminar de atualizar todos os binários necessários para essa atualização. Quando isso acontecer, reinicie a máquina.

Obs: No post não foi focado cuidados que devem ser tomados no caso dessa atualização. É uma boa prática sempre realizar backup dos databases de sistema, quando for realizar alguma alteração que afeta as configurações da instancia do SQL Server

Espero ter ajudado

Abs

Realizando Backups SSMS

Agora que sabemos quais opções de backup o SQL Server oferece, vamos ver como realiza-los. O SQL Server oferece diversas formas para a criação de backup: Vamos  as mais utilizadas, através do SQL Server Management Studio (SSMS) ou usando comandos Transact-SQL. Em ambos os casos, é possível criar todos os tipos de backups abordados acima.
Criando um backup através do SQL Server Management Studio (Maintenance Plain)

Para acessar as janelas de opções do Maintenance Plain se conecte ao servido, conforme mostra figura abaixo:

Expanda os nós do SSMS até o Maintenance Plain

Clique com o botão direito em cima do nome Maintenance Plans e escolha a opção New Maintenance Plans.

Aparecerá a caixa de diálogo para que você especifique o nome do Plano de Manutenção.

Clique em Ok.

Aparecerá o painel de desenvolvimento do Plano de Manutenção.

Clique e arraste o componente Maintenance Cleannup Task para a parte amarela, conforme imagem:

Dê um duplo clique sobre o componente e a seguinte tela aparecerá:

No textbox Folder é o caminho de onde ficarão os arquivos de backups que serão deletados.
O checkBox permite a opção de excluir backups dentro de subpastas. E no File Age você pode escolher o tempo que os arquivos serão deletados, por defaulr são 4 semanas. Clique no botão com reticências ao lado do textbox folder e aparecerá uma nova janela, para escolher o caminhos de onde estarão os backups.

No nosso Server essa estrutura de pasta já estava criada, portanto, é opcional aonde serão colocados os arquivos de backup.

Obs: Nunca coloque arquivos de backup no C: do servidor, sempre deixe-os em discos separados em caso de falhas não haverá perda dos mesmos.

Clique Ok.

A nossa janela de Maintenance Cleannup Task deve ficar da seguinte maneira:
Clique em Ok.

Clique e arraste para o Painel o componente de backup

Dê um duplo clique no componente

Backup Type: Tipo do backup que será feito Full, Transactional ou Differential, como foi falado no inicio do documento.
DataBases: Quais databases serão backupeadas.
All Databases = Todas os banco de dados
System DataBases = Todos os Bancos de sistema
All User Databases = Todos os banco de dados de usuários, exceto tempdb, model, msdb e master. Conforme figura:

Escolha All User Databases e clique em ok.

Nossa tela de backup deverá ficar da seguinte maneira

Clique em Ok.

Clique na seta verde do componente Maintenance Cleannup Task e arraste para o Back Up Database Task, e os componentes ficarão da seguinte maneira:

Clique no calendário no canto superior direito do painel, pois, será hora de fazer o Schedule da rotina.

A seguinte janela aparecerá:

Occurs: Frequencia com que a atividade irá ser executada, no nosso caso será Daily
Daily frequency: Qual o horário que irá acontecer a rotina.

A nossa janela deve ficar da seguinte maneira:

Clique em Ok.

Clique em Salvar (ícone do disquete na parte superior da tela).

Feche o SQL Server.
Automaticamente será criado um Job no SQL Server Agent do servidor de banco de dados.

Espero ter ajudado….

Particionando Tabelas

Ola galera, hoje vou falar de um assunto super bacana. No artigo de hoje veremos como funciona o particionamento de tabelas dentro do SQL Server. As vezes me perguntou, vale a pena fazer um artigo sobre assuntos que já tem de “kilo” sobre essas informações na net? Como sempre gosto de estar estudando, e essa semana falando com meu amigo Marcelo ele disse: “Que estudar sobre isso? Escrever um artigo,é a melhor maneira”. Então lá vamos nós…

Imagine que você tem uma tabela em seu banco de dados que possuem milhões de informações por ano, e os usuários estão reclamando que quando uma consulta é feita as informações demoram para ser retornadas. Analisando a olho clinico a consulta, você percebeu que 85% das consultas eram feitas em dados menor que o ano de 2010 e, as demais são realizadas em dados acima do ano de 2010.  Que dizer que para o nosso dia-a-dia usamos mais informações que estão a partir do anode de 2010

O que podemos fazer neste caso é particionar os dados em três partes. O SQL Server pode particionar tabela, index ou index view.

O SQL Server permite realizar o particionamento horizontal dos dados. Antes de particionar os dados o SQL Server necessita de algumas passos que devem ser executados:

  • Criar uma partition function.
  • Definie um ou mais filegroups.
  • Criar uma partition scheme.

Partition Function

Define o conjunto lógico dos dados que serão distribuídos dentro da partition scheme. Cada partition function requer um nome e um tipo de dados. O tipo de dados da partição pode ser qualquer tipo de dados nativo do SQL Server exceto text, ntext,image,varbinary(max), timestamp, xml, varchar(max), clr type and tipo de dados definido pelo usuário. Colunas calculadas no SQL Server devem ter a propriedade PERSITED para ser usado em uma partition function. O código a seguir mostra como uma partição é criada:

USE TK70450

GO

CREATE PARTITION FUNCTION PF_Year (datetime)

AS RANGE LEFT

FOR VALUES (‘2010-01-01′,’2011-01-01′,’2012-01-01’)

O left define o “ponteiro” para a partição, como foi definido left a partição ficará mais o menos desta maneira:

Para verificar o range de valores da partition function você pode executar um select na view de sistema:

select * from sys.partition_range_values

where function_id = 65539

Partition Scheme

Ao contrário da partition function que é uma definição lógica, a partition scheme é uma definição física de onde os dados serão armazenados depois de particionado. A partition scheme define a estrutura de armazenamento dos dados. Para criar a partition scheme os File Groups já devem existir no seu banco de dados. Você só pode ter uma única partition scheme para uma partition function, porém, uma partition function pode ser usada em uma ou mais partition schemes. Se uma partition function tiver três “pontos” de definições lógica a partition scheme deve conter no mínimo quatro filegroups mapeados.

USE TK70450

GO

CREATE PARTITION SCHEME PS_YEAR AS PARTITION PF_YEAR TO (FG1,FG2,FG3,FG4)

O armazenamento ficará mais o menos da seguinte forma:

Obs: Os filegroups devem ser criados antes da partition scheme.

Para verificar quais as partition functions que foram criadas, realize um select na seguinte view de sistema:

USE TK70450

GO

select * from sys.partition_schemes

Particionando uma tabela.

Após os pré-requisitos serem cumpridos, a tabela pode ser particionada. Por padrão, todo objeto que você cria no seu banco de dados é armazenado no filegroup padrão. Se a clausula ON for especificada, você pode definir qual filegroup definido pelo usuário o objeto pode ser armazenado. Porque a partition scheme é apenas a definição de armazenamento é necessário apenas definir qual partition schema particionará a tabela na cláusula ON conforme abaixo:

USE TK70450

GO

IF OBJECT_ID(‘dbo.TB_OPER’) IS NOT NULL

BEGIN

      drop table dbo.TB_OPER

END

CREATE TABLE dbo.TB_OPER

(

COD_OPER Int identity(1,1)NOT NULL,

COD_CONTRATO VARCHAR(30) NOT NULL,

DT_OPER datetime NOT NULL

)ON PS_YEAR(DT_OPER)

Após a tabela ser particionada, vamos realizar algumas inserções para verificar o resultado de particionamento da  nossa tabela.

USE TK70450

GO

INSERT INTO dbo.TB_OPER(COD_CONTRATO,DT_OPER)

VALUES (‘XYZ45690′,’2009-12-25’),(‘CVB01234′,’2010-05-05’),(‘GTHTH4569′,’2011-11-15’),(‘TYUIOP789′,’2012-05-01’)

Como pode ser visto através da query abaixo, podemos analisar que o nosso objeto está “divido em quatro pedaços” que é exatamente a quantidade de filegroups que definimos dentro da partition scheme.

USE TK70450

GO

SELECT      OBJECT_NAME(object_id) AS Nom4DaTabela

            ,partition_id, partition_number,rows

FROM sys.partitions WHERE object_id = OBJECT_ID(‘dbo.TB_OPER’)

É possível ver também a quantidade de linhas que contém em cada partição em que a nossa tabela foi “dividida”.

Por hoje é só…

Espero que tenha ficado um pouco mais claro para as pessoas que estão começando agora. Tentei fazer o mais simples possível e espero ter ajudado.

Trouble shooting da TempDB SQL Server

Esse procedimento é dedicado ao meu amigo Jeferson Kiocia  que me  ensinou muitas coisas, sobre windows, Contadores de Performance, Infra,Storage e etc.. Na verdade ele me disse: “Faz um procedimento sobre algo quando “Ferrar” o disco da TempDB! Quando der uma M…. Você tem que ter a solução rápido”. E aqui estamos. Fica a dica: Sempre documente os procedimentos, mesmo, que ainda não tenha acontecido. Porque quando acontecer, você será cobrado e a dor de cabeça será tremenda e ainda você pode ouvir “Se você sabia que isso poderia acontecer, porque não fez um procedimento?”.

Quando usar este procedimento?

O procedimento deve ser usando quando o array que hospeda a tempdb falhar(Nunca deveria acontecer, leia no final do post). Quando isso acontece o SQL Server retorna um erro,pois, o caminho onde a tempdb estava não foi encontrado.

Cenário: Imagine um cenário que temos a tempdb em um disco de nome S, conforme print:

E esse disco falhou ou a letra não é mais a mesma(se você trocou a letra depois da falha). Quando o SQL Server iniciar na próxima vez ele retornará as seguintes mensagens no Event Viewer:

Erro:1

Erro:2


Erro:3

Quando aparecer qualquer dessas mensagens, que dizer que a tempdb não foi encontrada. A solução para esse tipo de problema é: Se conectar ao SQL Server via prompt de comando e alterar o arquivo da tempdb de unidade. Vá até a a pasta aonde encontra-se os binários do SQL Server.

Você deve acessar esse caminho via promt de comand, conforme print:

Digite a linha de comando abaixo para inicar o SQL Server pelo prompt.

Sqlservr.exe -f -m -sNomeDaInstancia

O print mostra a linha digitada:

Obs: O SQL Server Agent deve estar desabilitado para a execução desta linha de comando. Se o SQL Server estiver ativo, você não conseguirá usar o SQL em single user mode. Os parâmetros são explicados abaixo:
f ? Executa o sql server com as configurações minimas.
m ? Entra no sql server em single user mode.
s ? Nome da instancia do sql server. Esse nome pode ser obtido no SQL Server Configuration Manager.

Nome da Instancia.

Com o SQL Server iniciado no prompt que foi aberto:

Quando o SQL Server é inicado desta maneira, a tempdb por default vai para o diretorio de bases de dados padrão do SQL Server de instalação(aonde encontra-se a master, model e msdb). Abra um novo prompt do MS-DOS e use um utilitário de linha de comando para mudar a tempdb de diretório. Use a linha de comando abaixo:

osql.exe -E -sNomeDaInstancia

Conforme o print abaixo:

Obs: O parâmetro -E é a conexão trusted (integrada com o windows), o usuário que está executando este procedimento deve ter permissões de SA no SQL Server. Neste exemplo usamos o login TMKTSQLSVC que é uma conta do windows com permissões de SA.

Após conectado ao SQL Server, execute os comandos abaixo para modificar a tempdb para a nova unidade.

USE MASTER
GO
ALTER DATABASE tempdb MODIFY FILE
(NAME= tempdev, FILENAME= ‘F:/MSSQL2005/MSSQL/SYS_DBS/DATA/tempdb.mdf’)
GO
ALTER DATABASE tempdb MODIFY FILE
(NAME= templog, FILENAME= ‘F:/MSSQL2005/MSSQL/SYS_DBS/LOG/templog.ldf’)

O SQL Server retorna a mensagem que os arquivos da tempdb serão movidos após a próxima reinicialização do serviço do SQL Server.

Digite exit na janela aonde você digitou os comandos e aperte a tecla enter.
Na outra janela de prompt que está em aberta pressione CTRL C aparecerá uma mensagem solicitando confirmação se você deseja fazer o shutdown do SQL Server.

Pressione a tecla Y.

Inicie o SQL Server normalmente.

Após o procedimento o SQL Server deve subir normalmente.

Obs: Leitura no final do post: A tempdb sempre deve estar em um disco que oferece redundância, de preferência um disco com alta velocidade para escrita e leitura(RAID 10). Esse procedimento foi criado em um ambiente onde a tempdb estava em um RAIDO.

Espero ter ajudado.
Abssssss

Integrando os contadores do System Monitor ao SQL Server Profiler

Esse artigo é dedicado ao meu amigo Marcelo Fernandes da Silva (Misifio/D2). Graças a ele, estou no “mundo SQL Server”. Sempre está me incentivando e me dando uns helps.Quem o conhece sabe o quanto ele conhece(É muitooo viuuuu!) de SQL Server. Vamos ao trabalho.

Esse procedimento pode ser implementado em um ambiente que existe diversos sites, e você deseja realizar um procedimento pró-ativo de melhoria no ambiente começando pelo código T-SQL que é executado no mesmo.

Capturando Log Counters.

O primeiro passo é capturar as informações numericas relacionadas a gargalo de disco, processamento, memória e etc. Abra o System Monitor conforme figura abaixo:

Clique com o botão direito em Counters Log e escolha a opção New Log Settings..

Aparecerá uma caixa solicitando o nome do Counter Log a ser criado. Preencha e clique em OK.

Aparecerá a já nela de configurações dos contadores a ser escolhido.

Current log file name:  Caminho e Nome do arquivo do contador
Add Objects:  Lista de objetos do System Monitor
Add Counters:  Lista de Contadores do System Monitor
Interval: Intervalo de tempo em que os dados serão coletados
Run As: Credencial que irá executar o counter log e gravar os arquivos na pasta que será selecionada.

Clique em add counters para adicionarmos os contadores. No nosso exemplo será usado apenas alguns contadores do objeto processador. Clique em Add.

Na guia Log Files, você configura o tamanho que terá o arquivo do contadores e qual o caminho aonde o mesmo será gravado.

Clique em configure.

Será aberta a janela aonde o arquivo deverá ser salvo. Por default o arquivo é sempre salvo no diretório C:. Aqui nós criaremos uma pasta no diretório F: e apontaremos o caminho do arquivo para esta pasta(CountersSQL).

Clique em OK.

Na guia Schedule, podemos agendar o horário que o contador inicie, intervalo e etc. Não usaremos isso, pois, iniciaremos o mesmo manualmente.

Clique em OK

Voltando para a guia General. Preencha o textbox com a credencial que irá executar o contador e clique em Set Password. Abrirá uma janela parecida com a do print abaixo:

Obs: Neste exemplo estou usando a conta do serviço do SQL Server, pois, a mesma possui direitos de Admins do dominio. Sempre use uma conta que tem permissão de gravação na pasta no qual será gravado o log. Clique em OK. Depois clique em OK na guia General.

Na opção de counters Log irá aparecer o seu contador e qual caminho você salvou.


Clique com o botão direito e inicie o contador, o mesmo mudará de cor, conforme print:

Iremos para a segunda parte do procedimento

Unificando as ferramentas

Para que esta tarefa seja concluída você deve ter um arquivo do profiler que estava executando na mesma hora em que o Counter Log foi iniciado. Um dos pré-requisitos para que os dados sejam “mesclados” é que o profiler deve ter sido criado com a coluna Start Time selecionada.

Vamos partir do pressuposto que o arquivo do profiler já havia sido coletado e o counter log também. E que os mesmos foram executados durante o mesmo periodo.

Abra o profiler e vá em Open/File/Trace File

Navegue até a pasta aonde está salvo o arquivo .trc do profiler do SQL Server.

Clique em Abrir.
Novamente no Menu File escolha a seguinte opção:

Ao clicar nesta opção, você deverá passar o caminho aonde esta salvo o counter log que foi criado.

Clique em abrir.

A janela com os contadores que você escolheu anteriormente irá aparecer todos desmarcados. Marque os check box apenas do tempo total de processamento.

Obs: Você pode escolher mais que um contador, neste exemplo apenas usaremos o tempo de processamento. Apenas um contador facilita a visualização e se for um problema de processamento em um devido horário, não tem porque adicionar contadores de disco.

Após você escolher o contador o resultado será apresentado, conforme abaixo:

Você pode analisar em qual horário foi o pico de processamento e o que estava rodando no exato momento no profiler.

Espero que seja uma dica útil para alguém que esta começando….

Removendo Cursor – Dica Rápida

Ola Galera, blz? Como o tempo está um pouco corrido essa vai ser uma dica rápida que pode ser usada no dia-a-dia.
Imaginem o cenário que é necessário criar uma string com diversos ID’s. E esses ID’s estão dentro de uma tabela. A idéia inicial seria realizar um loop para incrementar uma variavel e montar a nossa string.. Vamos ao exemplo:
O script abaixo cria uma tabela com os códigos do cliente que iremos usar para montar a nossa string.
use tempdb
go
if OBJECT_ID(‘dbo.tb_cliente’) is not null
drop table dbo.tb_cliente
go
create table dbo.tb_cliente(id int identity, codigoCliente int , nome varchar(50))
go
–Bloco que popula a tabela com informações dos clientes
set nocount on
declare @i int = 1
while @i <= 100
begin
insert into dbo.tb_cliente(codigoCliente,nome)values(@i,replicate(‘Thiago’,2))
set @i = @i + 1 *(2)
end
go

Declaramos as variaveis @count, @total para realizar o nosso loop. As variáveis @idCliente e @idClienteString serão usadas para armazenar os codigos concatenados. A tabela temporaria #CodigosCliente é usada para guardar as informações pertinentes ao predicado IN. O script abaixo preenche a tabela temporária com os registros que serão montados para o loop. Após a inserção, o total de registros e colocado em uma variável para que iniciemos a montagem da string.
declare @count int , @total int
declare @idCliente varchar(max), @idClienteString varchar(max)
create table #CodigosCliente(id int identity, codigoCliente int)
–Popula a temporaria com as informacoes que sera colocadas em uma string
insert into #CodigosCliente(codigoCliente)
select codigoCliente
from dbo.tb_cliente where codigoCliente between 10 and 50

set @idCliente = ‘ ‘
set @idClienteString = ‘ ‘
select  @total =count(id) from #CodigosCliente
set @count = 1
if @total > 0
begin
set @idClienteString = ‘codigoCliente IN ( ‘
end
else
begin
set @idClienteString = ‘codigoCliente IN (0 ‘
end
while @count <= @total
begin
if  @count > 1
begin
set @idClienteString = @idClienteString + ‘, ‘
end
select @idCliente=codigoCliente from #CodigosCliente where id=@count
set @idClienteString = @idClienteString + @idCliente
set @count = @count + 1
end
set @idClienteString = @idClienteString + ‘ )’
print @idClienteString
go
Após a execução do script acima, o SQL Server irá montar o predicado in com os valores, conforme imagem:

Conforme imagem acima, temos o resultado esperado, porém, operações linha-a-linha são mais custosas para o SQL Server. Nós poderíamos obter o mesmo resultado com o script abaixo:

declare @idCliente varchar(max )
declare @idClienteString varchar(max)
SET @idCliente =
SET @idClienteString =
SET @idClienteString = ‘codigoCliente IN (‘
SELECT @idCliente=@idCliente +‘,’+CONVERT(varchar(max),codigoCliente)from#CodigosCliente
SELECT @idCliente =substring(@idCliente,2,LEN(@idCliente))
SET @idClienteString = @idClienteString + @idCliente +‘)’
PRINT @idClienteString
Como podemos ver, temos o mesmo resultado com uma menor quantidade de linhas uma instrução única evitando o uso de um loop.

Créditos: Alexandre José Malachias….Valeu Boss

Constraints vs Query Performance

Neste post irei demonstrar a importancia de  criar constraints no SQL Server. Graças as Foreign keys e check constraints o optimizer pode criar planos mais eficientes para as querys. Dado o script abaixo da criação das tabelas temos a tabela de Customers e de Orders. Na modelagem proposta que dizer que um Customer pode ter uma ou mais Orders. Notem que o script de criacao da constraint fisica nessa tabela e feita na tabela “filha” no nosso caso a de Orders.
CREATE TABLEdbo.Customers (CustomerID INT  PRIMARYKEY)
CREATE TABLE dbo.Orders(OrderID INT  PRIMARYKEY,CustomerID INT NOT  NULL CONSTRAINT FKOrdersCustomers REFERENCES dbo.Customers(CustomerID)).
Executando a query abaixo, podemos notar que temos duas tabelas na consulta, mas se vc executar a query com o plano de execução o sql server uma apenas o operador fisico para acessar uma das tabelas. Esse compportamento acontece, pois, o otimizador sabe que não é necessario executar um teste de existencia dos registros, pois a FK garante que serao requeridas todas orders para referenciar com o customer. Conforme print abaixo:
SELECT O.OrderID,o.CustomerID
FROM dbo.Orders AS o
WHERE EXISTS(SELECT CustomerID FROM dbo.Customers AS c
WHERE c.CustomerID=o.CustomerID)

Agora o que aconteceria se desabilitarmos a constraint? Vamos aos testes (Amo muito tudo isso..rs)
ALTER TABLE dbo.Orders NOCHECK CONSTRAINT FKOrdersCustomers

Execute novamente  a query, pressione o CTRL+M antes para habilitar o Include Actual Execution Plan. Agora perceba que  o plano de execucao mudou, isso porque a constraint foi desabilitada e o SQL Server nao pode garantir que todas as Orders  tem um customer valido.
SELECT O.OrderID,o.CustomerID
FROM dbo.Orders AS o
WHERE EXISTS(SELECT CustomerID FROM dbo.Customers AS c WHERE c.CustomerID=o.CustomerID)

Para voltarmos ao plano anterior devemos habilitar novamente a constraint, conforme instrução abaixo:
ALTER TABLE dbo.Orders CHECK CONSTRAINT FKOrdersCustomers

Execute a query novamente e veja o plano de execucao. Note que o plano de execucao continua o mesmo de quando a constraint foi desabilitada.

Esse comportamento se da ao devido fato que: O SQL server nao pode garantir que nao foi inserido um registro nao valido enquanto a FK estava desabilitada. Vc pode verificar isso atraves da dmv sys.foreign_keys. A FK foi marcada como “nao confiavel“. Veja a query abaixo:
select name , is_not_trusted from sys.foreign_keys
Com o resultado da query acima  vc vera que a coluna is_not_trusted esta marcada com o valor 1  , indica que a FK nao e confiavel.
O que pode ser feito nesse caso e adicionar a opção WITH CHECK para a clausula alter table que habilita a constraint:

alter table dbo.Orders with check check constraint FKOrdersCustomers

Se a query for executada novamente vera que o plano de execucao sera o visto da primeira implementacao.

Se nesse meio tempo que a FK foi  desabilitada algum registro que nao condiz com a  implementacao da constraint fosse inserido . Uma mensagem de erro seria retornado para o SQL Server. Por exemplo se uma order fosse inserida com um CustomerID NULL.

Referencia: Microsoft Database Developer 2008 TK 70-433