T-SQL Tuesday #26 – Segundas Chances
0 (0)

Todo mundo merece uma segunda chance,, ou para fazer melhor, ou para simplesmente não fazer,,, então,,, até no SQL temos uma segunda chance…

Dessa vez o Sr. David Howard (Blog | Twitter) deu a oportunidade de escrevermos novamente sobre algum tópico anterior do T-SQL Tuesday, ou para melhora-lo ou para fazer um se você não participou…

Eu resolvi melhorar o meu script para montar um Mirror com SQLCMD,,, na versão anterior (#25) se o banco de dados tiver mais que um datafile você tem que modificar o script na mão e compensar a criação desse outro arquivo… Nesta versão o script vai ler o arquivo de backup em uma temp table e montar o script de restore com todas as variáveis…

Vale lembrar que você precisa habilitar a opção de SQLCMD no SSMS e não esqueça de trocar o caminho do backup…

Everyone deserves a second chance, or to do better or to simply not do, then, even in SQL havea second chance…
This time Mr. David Howard (blog | twitter) gave us the opportunity to write again about a previous topic of Tuesday’s T-SQL, or to improve it or make one if you did not participate …
I decided to improve my script to create a Mirror with SQLCMD, in the previous version (#25) if the database has more than one datafile you have to modify the script and make the creation of another file … this version will read backup file in a temp table and mount the restore script with all the variables …
Remember that you need to enable the option of SQLCMD in SSMS and do not forget to change the backup path …

:SETVAR PrincipalServer (local)\INST01
:SETVAR MirrorServer (local)\INST02
:SETVAR SERVER (servername)
:SETVAR db DB_NAME
go

:ON ERROR EXIT
go

:CONNECT $(PrincipalServer)

ALTER DATABASE $(db)
 SET RECOVERY FULL
go

CREATE ENDPOINT Mirroring
 STATE=STARTED
 AS TCP (LISTENER_PORT=5091)
 FOR DATABASE_MIRRORING (ROLE=ALL)
GO

:CONNECT $(MirrorServer)

CREATE ENDPOINT Mirroring
 STATE=STARTED
 AS TCP (LISTENER_PORT=5092)
 FOR DATABASE_MIRRORING (ROLE=ALL)
GO

:CONNECT $(PrincipalServer)

BACKUP DATABASE $(db)
TO DISK = 'D:\DB01\local\$(db).bak'
WITH INIT
GO

:CONNECT $(MirrorServer)

DECLARE @InstanceName sql_variant,
 @InstanceDir sql_variant,
 @SQLDataRoot nvarchar(512),
 @ExecStr nvarchar(max)

SELECT @InstanceName = ISNULL(SERVERPROPERTY('InstanceName'), 'MSSQLServer')

EXECUTE master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
 'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL',
 @InstanceName, @InstanceDir OUTPUT

SELECT @ExecStr = 'EXECUTE master.dbo.xp_regread '
 + '''HKEY_LOCAL_MACHINE'', '
 + '''SOFTWARE\Microsoft\Microsoft SQL Server\'
 + convert(varchar, @InstanceDir)
 + '\Setup'', ''SQLDataRoot'', @SQLDataRoot OUTPUT'

EXEC master.dbo.sp_executesql @ExecStr
 , N'@SQLDataRoot nvarchar(512) OUTPUT'
 , @SQLDataRoot OUTPUT

IF @SQLDataRoot IS NULL
BEGIN
 RAISERROR ('I can´t find where restore the Database... I going to stop...', 16, -1)
END

CREATE TABLE #BackupFileList
( LogicalName sysname NULL
 , PhysicalName sysname NULL
 , [Type] char(1)
 , FileGroupName sysname NULL
 , Size bigint
 , MaxSize bigint
 , FileId smallint
 , CreateLSN numeric(25,0)
 , DropLSN numeric(25,0)
 , UniqueId uniqueidentifier
 , ReadOnlyLSN numeric(25,0)
 , ReadWriteLSN numeric(25,0)
 , BackupSizeInBytes bigint
 , SourceBlockSize bigint
 , FileGroupId smallint
 , LogGroupGUID uniqueidentifier
 , DifferentialBaseLSN numeric(25,0)
 , DifferentialBaseGUID uniqueidentifier
 , IsReadOnly bit
 , IsPresent bit
 , TDEThumbPrint varbinary(32)
)

INSERT #BackupFileList
 EXEC('RESTORE FILELISTONLY FROM DISK = ''D:\DB01\local\$(db).bak''')

UPDATE #BackupFileList
 SET PhysicalName
 = @SQLDataRoot
 + N'\Data\'
 + REVERSE(SUBSTRING(REVERSE(PhysicalName)
 , 1, PATINDEX('%\%', REVERSE(PhysicalName)) -1))

DECLARE @LogicalName sysname
 , @PhysicalName sysname

DECLARE FileListCursor CURSOR FAST_FORWARD FOR
 SELECT LogicalName, PhysicalName
 FROM #BackupFileList

OPEN FileListCursor

FETCH NEXT FROM FileListCursor INTO @LogicalName, @PhysicalName

SELECT @ExecStr = N'RESTORE DATABASE $(db)' +
 N' FROM DISK = ''D:\DB01\local\$(db).bak''' +
 N' WITH MOVE ''' + @LogicalName + N''' TO ''' + @PhysicalName + N''''

FETCH NEXT FROM FileListCursor INTO @LogicalName, @PhysicalName

WHILE @@FETCH_STATUS <> -1
BEGIN
 SELECT @ExecStr = @ExecStr + N', MOVE ''' + @LogicalName
 + ''' TO ''' + @PhysicalName + ''''
 FETCH NEXT FROM FileListCursor INTO @LogicalName, @PhysicalName
END

SELECT @ExecStr = @ExecStr + N' , NORECOVERY, REPLACE'

-- if you receive the error message because the script don´t found where he need to restore, you can uncomment the next line and get the restore command,,,
-- to restore manual...
-- SELECT @ExecStr

EXEC (@ExecStr)

DEALLOCATE FileListCursor
GO

:CONNECT $(PrincipalServer)
SELECT DATABASEPROPERTYEX(N'$(db)', N'Status') AS 'Principal Database State' -- Returns ONLINE
SELECT db_name(sd.[database_id]) AS [Database Name],
 sd.mirroring_guid,
 sd.mirroring_state,
 sd.mirroring_state_desc,
 sd.mirroring_partner_name,
 sd.mirroring_witness_name,
 sd.mirroring_witness_state,
 sd.mirroring_witness_state_desc,
 sd.mirroring_role,
 sd.mirroring_role_desc,
 sd.mirroring_role_sequence,
 sd.mirroring_safety_level,
 sd.mirroring_safety_level_desc,
 sd.mirroring_safety_sequence,
 sd.mirroring_failover_lsn
FROM sys.database_mirroring AS sd
WHERE sd.[database_id] = db_id(N'$(db)')
go

:CONNECT $(MirrorServer)
SELECT DATABASEPROPERTYEX(N'$(db)', N'Status') AS 'Mirror Database State' -- Returns RESTORING
SELECT db_name(sd.[database_id]) AS [Database Name],
 sd.mirroring_guid,
 sd.mirroring_state,
 sd.mirroring_state_desc,
 sd.mirroring_partner_name,
 sd.mirroring_witness_name,
 sd.mirroring_witness_state,
 sd.mirroring_witness_state_desc,
 sd.mirroring_role,
 sd.mirroring_role_desc,
 sd.mirroring_role_sequence,
 sd.mirroring_safety_level,
 sd.mirroring_safety_level_desc,
 sd.mirroring_safety_sequence,
 sd.mirroring_failover_lsn
FROM sys.database_mirroring AS sd
WHERE sd.[database_id] = db_id(N'$(db)')
go

:CONNECT $(PrincipalServer)

BACKUP LOG $(db)
TO DISK = 'D:\DB01\local\$(db)_Log.bak'
WITH INIT
GO

:CONNECT $(MirrorServer)

RESTORE LOG $(db)
FROM DISK = 'D:\DB01\local\$(db)_Log.bak'
WITH NORECOVERY
GO

ALTER DATABASE $(db)
 SET PARTNER = 'TCP://$(server):5091'
GO

:CONNECT $(PrincipalServer)

ALTER DATABASE $(db)
 SET PARTNER = 'TCP://$(server):5092'
GO

SELECT db_name(sd.[database_id]) AS [Database Name],
 sd.mirroring_guid,
 sd.mirroring_state,
 sd.mirroring_state_desc,
 sd.mirroring_partner_name,
 sd.mirroring_witness_name,
 sd.mirroring_witness_state,
 sd.mirroring_witness_state_desc,
 sd.mirroring_role,
 sd.mirroring_role_desc,
 sd.mirroring_role_sequence,
 sd.mirroring_safety_level,
 sd.mirroring_safety_level_desc,
 sd.mirroring_safety_sequence,
 sd.mirroring_failover_lsn
FROM sys.database_mirroring AS sd
WHERE sd.[database_id] = db_id(N'$(db)')
GO
Click to rate this post!
[Total: 0 Average: 0]

T-SQL Tuesday #025 – Truques
0 (0)

Este mês o Sr. Allen White (Blog | Twitter)  pediu para as pessoas dizerem quais truques elas utilizam para deixar o trabalho mais fácil…

Nós temos muitos códigos em powershell que ajudam a melhorar o dia, mas, powershell,,, aahh,,, se você usar o google, poderá encontrar muita gente escrevendo bons códigos que podem ajudá-lo.

Eu escrevi uma coisa legal, mas em T-SQL, para criar bases em mirror,,, Sim, eu sei, você pode encontrar um monte de blogs falando sobre como criar esse tipo de ambiente,,, mas eu vou fazer isso usando SQLCMD dentro do Management Studio,,, por quê? só pela diversão…

Eu vou pular a parte da criação dos end-poins porque você já sabe…

A parte legal está logo depois da versão em inglês,,,

Hi, i try translate all the text above, sorry if have some mistakes,,,

This month Mr. Allen White (Blog | Twitter) ask people to say what tricks we use to make our job easier…

We have a lot of code in powershell helping to improve the day, but, powershell,,, aahh,,, if you use the google will find a lot of people writing good scripts to help you.

I going to write some nice,, but in T-SQL, just to create a database mirror,,,, Yes, I know, you can find a lot of blogs talking about who to create a database mirror,,, but I will do this using the SQLCMD inside the Management Studio,, why? just for fun…

I´ll skip the creation of end-points because you already know…

The good part is here:


/* Change the principal server name and instance */
:SETVAR principal_server "SERVERNAME\INSTANCE1"
/* Change the mirror server name and instance */
:SETVAR mirror_server "SERVERNAME\INSTANCE2"
/* in this case I use the same server, but you can add one more setvar and add the other server */
:SETVAR server "SERVERNAME"
/* Database name to mirror */
:SETVAR db "DB_NAME"

:connect $(principal_server)

alter database $(db) set recovery full

backup database $(db)
to disk = 'D:\DB01\inst01\$(db).bak'
with compression, stats = 5

backup log $(db)
to disk = 'D:\DB01\inst01\$(db).trn'
with compression, stats = 5

go
:connect $(mirror_server)

restore database $(db)
from disk = 'D:\DB01\inst01\$(db).bak'
with file = 1,
move '$(db)' to 'D:\DB01\local\$(db).mdf',
move '$(db)_log' to 'D:\DB01\local\$(db).ldf',
norecovery, nounload, stats = 5

restore database $(db)
from disk = 'D:\DB01\inst01\$(db).trn'
with norecovery, nounload, stats = 5

alter database $(db)
set partner = 'TCP://$(server):5022'
go

:connect $(principal_server)

/* In this part you can change the setvar if was created other one */
alter database $(db)
set partner = 'TCP://$(server):5023'

 

Click to rate this post!
[Total: 0 Average: 0]

The Log scan in database model is not valid
0 (0)

Um pouco da história antes….

Dia 08/12/2011 choveu muito aqui em sampa,,, tivemos problemas com a elétrica aqui no prédio, ocasionando diversas quedas de energia,,,

Tenho instalado no desktop um SQL Server 2008 R2 Express, onde tenho algumas bases para testes,, até ai nenhuma novidade,,,

Quando cheguei no dia seguinte (09/12/2011) percebi que um sistema que uso para testar algumas bases não estava funcionando,,, a mensagem era simples: “Não consigo acessar o SQL”

Tentei iniciar o serviço do SQL e, claro, não consegui,,, Acessei os eventos do sistema e achei a seguinte mensagem:

event model

Interessante,,, o transaction log do model foi corrompido… isso é legal…

Iniciei o SQL em modo de segurança e rodei um DBCC CHECKDB, não fez diferença…

Como a base Model não é uma das bases mais vitais do SQL e eu não tenho backup dela,,, fui no diretório de Templates e copiei a base de lá para o diretório onde estava a base de “produção”. Iniciei o SQL e ele carregou sem problemas….

templates

Lembrando que, não é porque existem outras bases de sistema por aqui que você pode sobre-escrever a Master ou a MSDB…

Aí vem uma pergunta,,, por que o SQL não subiu com a base em suspect ou offiline? ou qualquer outro aviso? simples… a base Model, por mais simples que seja serve como base para criar o TempDB,,, sem ela,, o SQL não consegue criar o mínimo para o TempDB… eu consegui iniciar o SQL em modo de segurança porque usei a traceflag 3609 onde ele não recriou o tempdb…

Click to rate this post!
[Total: 0 Average: 0]

Quais planos de execução estão na memória?
0 (0)

Em sua maioria, a memória utilizada pelo SQL Server é utilizada para armazenar dados (buffer) e planos de execução (cache de procedure). Nesse post vou mostrar quanta memória está alocada para cache de procedures

O SQL Server armazena o cache usando 8kb por página de dados. Usando a dynamic view sys.dm_os_memory_cache_counters podemos ver um resumo do que está alocado:

SELECT TOP 6
LEFT([name], 20) as [NOME],
LEFT(]TYPE], 20) as [TIPO],
[single_pages_kb] + [multi_pages_kb] as [cache_kb],
[entries_count]
FROM sys.dm_os_memory_cache_counters
order by single_pages_kb + multi_pages_kb DESC

Vou focar nos 3 principais resultados dessa query:

  • CACHESTORE_OBJCP – Esse são planos compilados para stored procedures, triggers e functions
  • CACHESTORE_SQLCP – São os planos que não fazem parte de procedures, functions e triggers, inclui basicamente SQL dinâmico.
  • CACHESTORE_PHDR – Esse é responsável por verificar a sintaxe de views, constrains, também resolve o nome de tabelas e colunas

Você pode monitorar o numero de páginas no cache usando o Performance Monitor usando SQLServer:Plan Cache que armazena os contadores de Páginas de Cache. SQL Plans (CACHESTORE_SQLCP), Object Plans (CACHESTORE_OBJCP) e Bound Trees (CACHESTORE_PHDR).

Nos podemos ver individualmente as entradas no cache usando a dynamic view sys.dm_exec_cached_plans

SELECT usercounts, cacheobjtype, objtype, plan_handle
FROM sys.dm_exec_cached_plans

A query lista os planos de execução mais utilizados. Ela inclui os planos para stored procedures, adhoc ou SQL dinâmico, triggers, views. Se você quiser ver o SQL associado ao plano (que no final das contas é o que realmente queremos) será necessário usar o sys.dm_exec_sql_text:

SELECT TOP 100
objtype,
p.size_in_bytes
LEFT(., 150) as [SQL]
from sys.dm_exec_cached_plans p
outer apply sys.dm_exec_sql_text (p.plan_handle) sql
order by usercount desc
Click to rate this post!
[Total: 0 Average: 0]

Um bom programa para quebra galho
0 (0)

Estava revirando uns arquivos no meu HD externo e achei um programa muito legal para

conexão ODBC.

Antes de falar do programa,,, a historinha…

Conheci uma vez uma empresa que o pessoal não deixava acessar o servidor com SQL por TS nem SSMS,,,

Perguntei pra eles como eu iria ajudar a identificar os problemas se não podia fazer muita coisa… me explicaram que era política da empresa, eu poderia acessar de qualquer outra forma, mas não poderia conectar meu note da rede nem instalar qualquer aplicativo na estação…

Foi com essa necessidade que conheci o ODBC QueryTool,,, ele é um programinha bem legal… de graça e funciona sem precisar instalar nada…

1_app

Você pode usar uma conexão existente de ODBC ou criar uma na hora…

2_connect

Como um quebra-galho para esse tipo de situação,,, acho que ele server para dar uma ajuda…

3_query

Você pode usar ele também para testar as conexões das estações cliente, com as restrições de usuário, restrições do SQL,,,

configurando o ODBC com as devidas informações, eu mostrei para um cliente como a aplicação iria se comportar com o fail-over das bases mirror.

Você pode baixar no site do SourceForge, ou no meu Skydrive, esse é um dos programas que vale a pena ter no pendrive.

Click to rate this post!
[Total: 0 Average: 0]

T-SQL Tuesday #23 – Joins
0 (0)

Para o pessoal não ficar corrido entre escrever uns posts meia boca e se preocupar com os preparativos para o SQLPASS, o pessoal resolveu dar uma adiantada no T-SQL Tuesday desse mês,,,

Este mês ele é hospedado por Stuart Ainsworth (Blog | Twitter) e fala sobre joins..

Quer saber mais sobre Joins?

Vamos lá,,,

meu exemplo é bem simples,,, mostra os waits que estão acontecendo no SQL…

select
w.session_id,
w.wait_duration_ms,
w.wait_type,
w.blocking_session_id,
w.resource_description,
s.program_name,
t.text,
t.dbid,
s.cpu_time,
s.memory_usage
from sys.dm_os_waiting_tasks w
inner join sys.dm_exec_sessions s on
w.session_id = s.session_id
inner join sys.dm_exec_requests r on
s.session_id = r.session_id
outer apply sys.dm_exec_sql_text (r.sql_handle) t
where s.is_user_process = 1

Ele vai tentar associar a requisição ao wait.

Click to rate this post!
[Total: 0 Average: 0]

Malditos SQL Aliases
0 (0)

Estava em um cliente montando uma estratégia para migração de 2 instâncias de SQL 2000 para uma única instância de SQL 2005… até aí tranquilo,,, certo?

Listamos os linked servers, jobs, usuários e senhas, bases, collation,,, etc,,, tudo quase pronto pra migração….

Ai, lembrei de um detalhe… algumas bases estavam em uma instância default do SQL,,, e vamos migrar todas para um SQL em uma instância,,, ai vem o problema:

  1. Aplicações antigas
  2. Algumas não possuem código fonte
  3. Outras precisam de projeto para alterar o fonte
  4. Muitas estações com o aplicativo
  5. etc..

Legal,,, iai? Só criar o SQL Aliases (Iniciar>Executar>Cliconfg)? boa,,, pena que tem que criar em maquina a maquina,,, Redirecionar DNS? até funcionaria se o outro SQL não tivesse instância. Poderíamos alterar o DNS e “adicionar o servidor” com o sp_addserver (sp_addserver ‘nome_do_host’, local, duplicate_ok) o problema é que a instância tem que escutar a 1433, até ai nenhum grande problema, adiciona como porta secundária.

como fazer o deploy de aliases rápido? Policy… cria uma chave de registro e adiciona o aliases…

  • “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo”
  • Nome:”Nome_do_alias”
  • Tipo: Sequencia
  • Valor: “DBMSSOCN,ServidorX\Instancia,1433”

Outra forma, utilizar um “proxy” de SQL,,, tipo F5, Green SQL,,,

Click to rate this post!
[Total: 0 Average: 0]

TableDiff
0 (0)

Imagine a situação do post Corrompendo um Banco SQLmas na vida real,,,

Você chega um belo dia na empresa e, lê nos seus emails que, o SQL apresentou falha na estrutura de dados e algumas páginas de dados podem ter sido corrompidas,,,

Como um bom DBA, você corre para ver se o backup da noite foi feito, se os backups de transaction log também estão sendo feitos e descobre que sim,,,, todos os arquivos necessários para restaurar o banco estão lá,,,

Legal,,, mas,,, e agora? dependendo da utilização do banco você pode restaurar o backup da madrugada, os de log até o horário do problema e dali pra frente o que der pra fazer…

Em outros casos,,, você não pode se dar ao luxo de perder informação,,,,

Uma das formas seria executar o restore do banco em outro lugar, ou na mesma instância mas com outro nome, executar o checkdb e remover a página com problema e trazer a diferença dos dados,,, até aqui nada tão complicado,,, tirando o fato de se a tabela for muito grande, ou muito complexa e a query para mostrar essa diferença for muito complicado…

Para ajudar a resolver esse problema, o SQL possui uma ferramenta bem interessante chamada TableDiff.

O conceito dela é bem simples: Instância de origem, base de origem, tabela de origem, Instância de destino, base de destino, tabela de destino e o que você quer fazer…

Ex:

"C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe" -sourceserver MyServer1
                                                             -sourcedatabase MyDatabase1
                                                             -sourcetable MyTable1
                                                             -destinationserver MyServer1
                                                             -destinationdatabase MyDatabase1
                                                             -destinationtable MyTable2
                                                             -et DiffsTable

 

Nesse outro exemplo ele gera um arquivo com INSER/UPDATE/DELETE

"C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe" -sourceserver MyServer1
                                                             -sourcedatabase MyDatabase1
                                                             -sourcetable MyTable1
                                                             -destinationserver MyServer1
                                                             -destinationdatabase MyDatabase1
                                                             -destinationtable MyTable2
                                                             -et DiffsTable
                                                             -f d:\MyTable1_MyTable2_diff.sql

 

Simples certo? em teoria não é muito complicado…

Da pra deixar mais fácil? sim,,, com certeza…

O Sr. Mladen Prajdic (Blog | Twitter) desenvolveu uma interface bem interessante para ajudar na utilização do executável…

O link para download pode ser encontrado no post aqui, ou diretamente aqui.

O aplicativo é bem simples de usar, o ponto de atenção é que você precisa indicar onde está o executável do TableDiff.exe.

tablediffgui

Ele é bem auto-explicativo, pequeno e o principal,,, é de graça !!!

Vale gastar uns minutos para aprender a usar ele, vai que em um dia de emergência você precisa de uma ajuda rápida para solucionar um problema, ou ver a diferenças nas tabelas do seu logshipping….

Click to rate this post!
[Total: 0 Average: 0]

Catalogar objetos
0 (0)

Esse código faz parte daquele tipo de código para gerar documentação.

Não faz nada muito complexo, apenas mostra o objeto, quem é dono, que tipo e quando foi criado.

SELECT   [NAME]          AS OBJECT_NAME,
           USER_NAME(UID)  AS OWNER,
           TYPE = CASE
                    WHEN XTYPE = 'u' THEN 'table'
                    WHEN XTYPE = 'c' THEN 'check constraint'
                    WHEN XTYPE = 'd' THEN 'default constraint'
                    WHEN XTYPE = 'f' THEN 'foreign key constraint'
                    WHEN XTYPE = 'fn' THEN 'scalar function'
                    WHEN XTYPE = 'if' THEN 'inline table function'
                    WHEN XTYPE = 'p' THEN 'stored procedure'
                    WHEN XTYPE = 'pk' THEN 'primary key'
                    WHEN XTYPE = 'tf' THEN 'table function'
                    WHEN XTYPE = 'tr' THEN 'trigger'
                    WHEN XTYPE = 'uq' THEN 'unique constraint'
                    WHEN XTYPE = 'v' THEN 'view'
                  END,
           CRDATE          AS CREATION_DATE
  FROM     SYSOBJECTS
  WHERE    XTYPE IN ('u','c','d','f',
                     'fn','if','p','pk',
                     'tf','tr','u','uq',
                     'v')
ORDER BY XTYPE

 

Click to rate this post!
[Total: 0 Average: 0]