Configurando opções de acesso a banco de dados Sql Server 2008

 

Existem algumas opções de controle de acesso e capacidade de mudança de dados parametrizáveis no banco de dados, são elas:

ONLINE

Define o status do banco de dados. Quando o banco de dados estiver com o status ONLINE, significa que todas as operações serão executadas normalmente.

OFFLINE

Define também o status do banco de dados. Quando o banco de dados estiver com o status OFFLINE, significa que o mesmo não esta acessível.

EMERGENCY

Outro status do banco de dados que define que apenas pode ser acessado por um membro do role db_owner e que o único comando que permite ser executado é SELECT.

READ_ONLY

Um banco de dados configurado no modo READ_ONLY indica que está disponível apenas para consulta, não sendo possível realizar qualquer tipo de gravação e todo o log de transação será removido.

READ_WRITE

Indica que o banco de dados está disponível para leitura e gravação. Toda alteração no banco de dados para modo de read_only ou read_write, faz com que o log de transação seja recriado.

SINGLE_USER

Indica que apenas um usuário por vez pode estar conectado ao banco de dados

RESTRICTED_USER

Permite que somente membros das roles db_owner, dbcreator e sysadmin tenham acesso ao banco de dados.

MULTI_USER

Configuração padrão de um banco de dados, permite que vários usuários tenham acesso simultaneamente.

Para realizar a alteração do modo de acesso ou capacidade de mudança do banco de dados deve ser realizado com ALTER DATABASE conforme exemplo a seguir:

ALTER DATABASE <banco_de_dados> SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Rollback immediate faz com que todas as transações abertas sejam revertidas imediatamente e usuários não autorizados sejam desconectados para que a nova alteração de modo de acesso passe a valer. Pode ser utilizado também a opção ROLLBACK AFTER <segundos>  que irá respeitas o número de segundos indicados antes de reverter ou finalizar transações.

Obrigado

Configurando opções automáticas de banco de dados–Sql Server 2008

 

Existem opções que podem ser habilitadas no banco de dados que permitem sua execução automática, são elas:

AUTO_CLOSE

Se esta opção estiver ativada em seu banco de dados, faz com que ao ser finalizada a última conexão o Sql Server desligue o banco de dados e libere todos os recursos da máquina ocupados. Assim que uma conexão ao banco de dados é solicitada, o Sql Server inicia o banco de dados e volta a alocar os recursos necessários. Por padrão na criação do banco de dados essa opção é desativada;

 

AUTO_SHRINK

Quando ativada essa opção, o Sql Server passa a verificar constantemente a utilização de espaço alocado para os arquivos de dados e log de transação. Ao finalizar a verificação, se o Sql Server identificar que a utilização do espaço tiver um percentual de 25% de espaço livre alocado, os arquivos serão reduzidos automaticamente para liberação de espaço em disco. É recomendado manter essa opção desativada e realizar a redução de espaço livre manualmente quando necessário

 

AUTO_CREATE_STATISTICS

Se essa opção estiver ativada, faz com o que o Sql Server crie automaticamente as estatísticas não encontradas no momento da otimização do processamento da query. É sabido que a criação  das estatísticas gera uma certa sobrecarga de processamento e tempo, mas como vantagem temos o desempenho da consulta que com certeza compensa “pagar o preço”.

 

AUTO_UPDATE_STATISTICS e AUTO_UPDATE_STATISTICS_ASYNC

Quando ativada uma das opções acima, permite que o Sql Server atualize as estatísticas desatualizadas durante a otimização da consulta (AUTO_UPDATE_STATISTICS) ou realiza a atualização das estatísticas de forma assíncrona durante a otimização da consulta (AUTO_UPDATE_STATISTICS_ASYNC).

 

Obrigado

Modos de recuperação de banco de dados–SQL Server 2008

 

O modo de recuperação indica as formas de gerenciamento do log de transação além disso determina os tipos backups disponibilizados para serem aplicados no banco de dados, são eles:

• Completo (Full)
• Registro em massa (Bulk-logged)
• Simples (Simple)

Completo (Full)

Um banco de dados no modo de recuperação completo, todas as alterações realizadas (DML e DDL) são registradas no log de transação, sendo possível recuperar o banco de dados a partir de um determinado ponto no tempo. Todas as alterações realizadas no banco de dados são mantidas no log de transação e só serão removidas com a execução de um backup de log de transação.

 

Registro em massa (Bulk-logged)

Banco de dados com alto volume de dados em transações podem sofrer problemas de performance com o modo de recuperação definido como FULL. O modo de recuperação de registro em massa diferentemente do modo FULL não registra linha a linha das alterações no log de transação para bulk operation (bcp, bulk insert, select..into, create index alter index…rebuild) e sim registra as extensões. Desta forma, não é possível realizar o backup de um banco de dados a partir de um determinado ponto no tempo.

 

Simples (Simple)

Esse modo de recuperação, registra no log de transação as operações exatamente da maneira que é realizado no modo FULL, porem, isso não indica que os arquivos de log serão armazenados permanentemente, ou seja, sempre que o processo de checkpoint do banco de dados for executado serão truncados. Um banco de dados no modo de recuperação Simple, não pode ser recuperado a partir de um ponto no tempo

Script para identificar qual modo de recuperação parametrizado no banco de dados:

SELECT name, recovery_model_desc    FROM sys.databases       WHERE name = 'XXXX' ; –substituir pelo nome do banco
GO

Script para alteração do modo de recuperação do banco de dados:

ALTER DATABASE XXXXX SET RECOVERY FULL ;

ALTER DATABASE XXXXX SET RECOVERY BULK_LOGGED ;

ALTER DATABASE XXXXX SET RECOVERY SIMPLE ;

Obrigado

Criação de arquivo utilizando Select–Oracle

 

Segue dica para geração de arquivo a partir de uma consulta de forma rápida utilizando SqlPlus:

 

{
echo "set pagesize 0"
echo " Select ColunaExemplo1||','||ColunaExemplo2||','||ColunaExemplo3 ||','||ColunaExemplo4 ||','||ColunaExemplo5||','||
ColunaExemplo6||','||ColunaExemplo7||','||ColunaExemplo8 FROM Tabela_TESTE;"
} | sqlplus -s usário/Senha@Servidor >> Resultado.log

Como saída, será gerado o arquivo resultado.log com o resultado do Select, podendo esse arquivo futuramente ser importado como um CSV.

O script foi utilizado para geração de uma arquivo para uma grande massa de dados e a resposta\performance foi satisfatória.

 

Obrigado

Consultar índices particionados de uma Tabela–SqlServer

 

Segue para identificar e consultar os índices particionados de uma determinada tabela:

select distinct
   p.[object_id],
   TbName = OBJECT_NAME(p.[object_id]),
   index_name = i.[name],
   index_type_desc = i.type_desc,
   partition_scheme = ps.[name],
   data_space_id = ps.data_space_id,
   function_name = pf.[name],
   function_id = ps.function_id
from sys.partitions p
inner join sys.indexes i
   on p.[object_id] = i.[object_id]
   and p.index_id = i.index_id
inner join sys.data_spaces ds
   on i.data_space_id = ds.data_space_id
inner join sys.partition_schemes ps
on ds.data_space_id = ps.data_space_id
inner JOIN sys.partition_functions pf
   on ps.function_id = pf.function_id
WHERE p.[object_id] = object_id('Nome_tabela')
order by    TbName, index_name ;

Obs.: Substituir pelo nome da tabela desejado

Obrigado

Quais são as consultas mais executadas? - Oracle

 

Segue consulta para identificar quais as 20 consultas mais executadas no seu banco de dados.

SELECT sql_text, executions
  FROM (SELECT sql_text, executions,
        RANK() OVER (ORDER BY executions DESC) exec_rank
        FROM v$sql)
WHERE exec_rank <= 20;

Obrigado

Quando foi atualizado as estatísticas da tabela–SQL Server

 

Segue consulta para identificar quando foi atualizada a estatística de uma determinada tabela:

No exemplo a seguir foi utilizada a tabela 'HumanResources.Department'

SELECT name AS index_name,
STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated
FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID('HumanResources.Department')
GO

index_name	StatsUpdated
PK_Department_DepartmentID	2012-03-29 13:52:19.380
AK_Department_Name	2012-03-29 13:52:22.550

Obrigado

Criar LDF a partir do MDF–SQL Server

 

Baixei o AdventureWorksLT2008.MDF da Microsoft, mas ao tentar importar o banco de dados o SQL Server informa que não foi possível encontrar o arquivo de LOG (.ldf).

Segue o comando para criar o ldf a partir de uma arquivo mdf:

EXEC sp_attach_single_file_db
    @dbname = 'AdventureWorksLT2008',
    @physname = 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\AdventureWorksLT2008_Data.mdf'

Onde:

@dbname – Nome do Banco de Dados

@physname – Diretório onde esta armazenado o arquivo mdf.

Obrigado

SQL Server Data Compression–Introdução

 

Há uma coisa que todos os DBA sabe com certeza, e isso é que seus bancos de dados irão crescer com o tempo. Quanto mais dados tivermos, mais trabalho o SQL Server tende realizar.

Ok, mas em que o Data Compression me ajuda?

A compressão de dados em SQL Server oferece dois benefícios potenciais ao DBA:

• Reduzir o tamanho dos arquivos físicos (MDF/NDF), reduzindo a quantidade de armazenamento em disco.
• Reduzir a quantidade de I/O necessário para uma carga de dados, ajudando a impulsionar
  desempenho.

Tipos de compressão de dados

A partir do SQL Server 2008 foi oferecido duas formas de compressão de dados:

ROW - Nível de compressão de dados por linha. Esta característica de compressão leva em consideração os tipos de dados que definem a coluna da tabela, por exemplo, uma coluna char(50) e o valor armazenado na coluna em sua maioria é 15 caracteres, neste caso a compressão por linha vai ocupar em disco somente o espaço exigido para os 15 caracteres, ou seja, com esse tipo de compressão não será alocado espaço em disco para valores zero ou nulos fazendo com que mais linhas sejam alocadas em uma mesma página. Alguns outros tipos de dados podem ser comprimidos, mas não é aplicado para todos.

PAGE – Nível de compressão de dados por página. Além de armazenar dados de forma eficiente dentro de uma linha, a compressão otimiza a página de armazenamento de várias linhas em uma página, minimizando a redundância de dados. A compressão de página usa compactação de prefixo(procura padrões comuns no início de cada coluna, exemplo, todas que comecem com XPTO%)  e compressão de dicionário (procura por correspondências exatas em todas as colunas e linhas de cada pagina) que são substituídas por uma referencia abreviada de menor caracteres ocupando menos espaço.

Quando uma tabela ou índice não possui configuração para compressão de dados o seu tipo estará definido como NONE.

Obrigado

Verifica existencia do arquivo–Visual Basic 2008–SSIS

 

Código utiliza no SSIS para verificação da existencia de uma arquivo antes de realizar a Leitura

' Microsoft SQL Server Integration Services Script Task

' Write scripts using Microsoft Visual Basic 2008.

' The ScriptMain is the entry point class of the script.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _

<System.CLSCompliantAttribute(False)> _

Partial Public Class ScriptMain

Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

Enum ScriptResults

Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success

Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure

End Enum

Public Sub Main()

If System.IO.File.Exists(Dts.Variables("sCaminhoArquivo").Value & Dts.Variables("sNomeArquivo").Value) Then

Dts.Variables("sExisteArquivo").Value = 1

Else

Dts.Variables("sExisteArquivo").Value = 0

End If

Dts.TaskResult = ScriptResults.Success

End Sub

End Class

No exemplo acima, acaso o arquivo exista será alimentado a variável sExisteArquivo, onde pode ser utilizada em um precedence Constraint Editor realizando a validação antes de fazer a leitura do Data Flow.

 

Obrigado

Diferença entre Replace e Stuff–SqlServer

 

Abaixo exemplo com as diferença da aplicação das 2 funções:

Utilizando o Replace, parte da string é substituída por outra. A seguir será substituída a string “Martinez” por “Teste”.

Select Replace('Fabio Martinez','Martinez','Teste')

column1
-----------
Fabio Teste

Já com a função Stuff podemos substituir uma posição específica da string. A seguir será substituída na string a partir da sétima posição 5 caracteres.

Select stuff('Fabio Martinez',7,5,'Teste')

column1
--------------
Fabio Testenez

Obrigado

Order by–Nulos primeiro

 

Como realizar a ordenação de uma massa de dados trazendo primeiro os registros cujo a coluna possui valores nulos?

No exemplo abaixo será ordenado pela coluna de nome do cliente trazendo primeiro os valores nulos.

Oracle:

SELECT CdNotaFiscal, NmCliente
FROM NOTAFISCAL
ORDER BY NmCliente NULLS FIRST

Sql Server \ Sybase:

SELECT CdNotaFiscal, NmCliente
FROM NOTAFISCAL
ORDER BY CASE WHEN NmCliente IS NULL THEN 'A' ELSE NmCliente END

Obrigado

Diferença entre PFILE e SPFILE – ORACLE

 

São arquivos utilizados na inicialização do bando de dados Oracle.

Ao iniciar uma instancia, o Oracle realizada a leitura de “um dos arquivos”  que armazena as características (parâmetros – Memória, caminho de arquivos,etc) do banco de dados.

Como disse anteriormente tanto o PFILE como o SPFILE são arquivos, porém SPFILE é uma versão binária do PFILE. O PFILE é armazenado com o nome init.ora e o SPFILE como spfile.ora.

Ok, e quando o Oracle utiliza um ou outro?

Ao tentar iniciar uma instancia, o Oracle procura inicialmente pelo SPFILE, se não encontrar procure pelo PFILE.

Por se tratar de uma arquivo binário, o SPFILE não pode ser alterado manualmente, mas isso não quer dizer que não possa ser alterado, os parâmetros podem ser alterados através do comando:

Alter system set “parâmetros”  = “valor”;

Alguns parâmetros podem ser alterados dinamicamente, ou seja, sem a necessidade de reiniciar o banco de dados. Para saber quais os parâmetros podem ser alterados dinamicamente execute a seguinte consulta:

Select * from V$Parameter;

As colunas ISSES_MODIFIABLE identifica parâmetros que podem ser alterados em sessão (Alter Session) e a coluna ISSYS_MODIFIABLE identifica parâmetros que podem ser alterados dinamicamente com Alter Session, onde,  quando for IMMEDIATE a alteração passa a ter vigor imediatamente após a alteração, DEFERRED passa a valer para as próximas sessões e FALSE valor estático, ou seja, só ira valer no caso de reinicialização da instancia.

Para verificar a existência e caminho do seu SPFILE:

show parameter SPFILE;

NAME       TYPE    VALUE
---------- ------ ------------------------------
spfile     string C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0 SERVER\DBS\SPFILEXE.ORA

Obrigado

Sistemas operacionais–SqlServer 2008

 

Quais os sistemas operacionais suportados para cada versão do SqlServer 2008?

SqlServer Express:

• Windows XP Professional SP2 ou superior
• Windows Vista Home Basic ou superior
• Windows XP Home Edition SP2 ou superior
• Windows XP Home Reduced Media Edition
• Windows XP Tablet Edition SP2 ou superior
• Windows XP Media Center 2002 SP2 ou superior
• Windows XP Professional Reduced Media Edition
• Windows XP Professional Embedded Edition Feature Pack 2007 SP2
• Windows XP Professional Embedded Edition para Point of Service SP2
• Windows Server 2003 Samall Bussiness Server Standard Edition R2 ou superior

SqlServer Developer e Evaluation:

• Windows XP Professional SP2 ou superior
• Windows Vista Home Basic ou superior

Sistemas operacionais suportados por todas as versões do SqlServer 2008:

• Windows Server 2008 Standard ou superior
• Wndows Server 2003 Standard SP2 ou superior

Obs: Por utilizar recursos .Net Framework, o SqlServer 2008 não é suportado pelo Windows Server 2008 Server Core que não possui tais.

 

Fonte – Kit de treinamento MCTS (Exame: 70-432)

Obrigado

 

Requisitos mínimos para instalação do SqlServer 2008

 

Segue os requisitos mínimos para instalação do SqlServer 2008 em 32 e 64 bits:

 

32 Bits:

Processador – Pentium III ou superior

Velocidade do processador – 1,0 gigahertz (GHz) ou superior

Memória – 512 megabytes (MB)

64 Bits

Processador – Itanium, Opteron Athelon ou Xeon/Pentium com suporte para EM64T

Velocidade do processador – 1,6 gigahertz (GHz) ou superior

Memória – 512 megabytes (MB)

Quanto ao espaço livre para instalação, isso vai depender dos serviços e ferramentas selecionadas no momento da instação.

Obrigado

Uso do Log de Transação–Sql Server

 

Informa o percentual de uso do log de transação de todos os banco de dados.

dbcc sqlperf (logspace)

 

Database Name	Log Size (MB)	Log Space Used (%)	Status
master	0,9921875	64,56693267822266	0
tempdb	399,9921875	60,40694046020508	0
msdb	3,9921875	37,279842376708984	0
CURSO	5999,9921875	100,00083923339844	0

4 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]

 

Obrigado

Tamanho do arquivo–Visual Basic 2008

 

Segue código que utilizei no SSIS para verificar o tamanho do arquivo. No meu caso precisa verificar se o arquivo gerado possuia informação, senão o mesmo deveria ser deletado.

 

Código para verificação do tamanho do arquivo:

 

' Microsoft SQL Server Integration Services Script Task

' Write scripts using Microsoft Visual Basic 2008.

' The ScriptMain is the entry point class of the script.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.IO

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _

<System.CLSCompliantAttribute(False)> _

Partial Public Class ScriptMain

Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

Enum ScriptResults

Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success

Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure

End Enum

Public Sub Main()

Dim sReader As StreamReader

Dim strContents As String

sReader = File.OpenText(Dts.Variables("sCaminhoArquivoGerencial").Value & Dts.Variables("sNomeArquivoLote").Value)

strContents = sReader.ReadToEnd()

sReader.Close()

 

If strContents.Length <> 0 Then

    Dts.Variables("sTamanhoArquivo").Value = strContents.Length

Else

     Dts.Variables("sTamanhoArquivo").Value = 0

End If

Dts.TaskResult = ScriptResults.Success

End Sub

End Class

 

Obrigado

Como identificar o Isolation Level–Sql Server

 

Para identifcar não só o Isolation Level mas também outros comandos:

DBCC useroptions

Set Option	Value
textsize	2147483640
language	us_english
dateformat	mdy
datefirst	7
lock_timeout	-1
quoted_identifier	SET
ansi_null_dflt_on	SET
ansi_warnings	SET
ansi_padding	SET
ansi_nulls	SET
concat_null_yields_null	SET
isolation level	read committed

Obrigado