Instalando PostgreSQL no Linux (CentOS)

Instalando MySQL no Linux (CentOS)

Dicas - SQLServer 2008R2

Alterando o Schema de uma tabela

O uso de schemas é uma boa prática para organizar a estrutura e as permissões de um banco de dados SQL Server.

Todos os objetos pertencentes a um Schema herdam suas permissões (desde que não existam permissões explícitas para cada objeto). Além disso, cada objeto pertence a um, e somente um, Schema.

Apesar do schema fazer parte da identificação de todo objeto, o simples renomeio de uma tabela  não possibilita associá-la a outro Schema.

Para realizar esta tarefa, deve-se utilizar a cláusula TRANSFER do comando ALTER SCHEMA.

Veja a sintaxe abaixo:

ALTER SCHEMA nome_do_schema TRANSFER outro_schema.nome_da_tabela; 

Note que o uso desta cláusula permite que a tabela nome_data_tabela seja "importada" para o Schema nome_do_schema.

No exemplo abaixo, 2 Schemas são criados (Marketing e RecursosHumanos). Em seguida, cria-se a a tabela Funcionario no Schema Marketing. Suponhamos que esta seja uma associação equivocada, e que a tabela Funcionario deva pertencer ao Schema RecursosHumanos.

-- Criando Schema para a área de Marketing
CREATE SCHEMA Marketing
GO

-- Criando Schema para a área de RecursosHumanos
CREATE SCHEMA RecursosHumanos
GO

-- Criando tabela Funcionario no Schema Marketing
CREATE TABLE Marketing.Funcionario
(
id int identity(1,1) PRIMARY KEY,
nome VARCHAR(100)
)
GO

/* Alterando o Schema da tabela Funcionario. 

(DE: Marketing PARA: RecursosHumanos) */
ALTER SCHEMA RecursosHumanos TRANSFER Marketing.Funcionario

GO

Observações:

·      Tome cuidado ao alterar nomes ou schemas de objetos de um banco de dados. Tenha sempre em mente que este objeto pode ser referenciado por aplicações ou mesmo por outros objetos existente no banco de dados. Caso isso aconteça, estas referências serão inválidas.

Dica Importante SQL Server 2008 Management Studio

Por padrão o SQL Server 2008 Management Studio vem configurado para não ter a mudança de uma tabela usando o modo “designer” sem que apague-a e re-crie novamente. O que acontece é o seguinte:

           

Se eu peguei uma tabela no meu banco de dados na internet, e ao tentar acrescentar um campo de auto-incremento ou chave primária ele deixa. No momento de salvar a tabela, o gerenciador mostra um alerta falando que não consegue salvar o que foi mudado na tabela.

Para realizar tal tarefa, será necessário desmarcar uma opção no gerenciador. Depois de abrir o gerenciador do SQL Server 2008, cliquei no menu Tools e escolhi a opção Options.

Uma nova tela se abre e existem várias opções específicas. Acesse a opção chamada Designers e ao 

lado direito deve ser desmarcado o item Prevent saving changes that require table 

re-creation.

Depois de desmarcar esta opção, é necessário fechar o gerenciador totalmente. Depois de abrir o

gerenciador a funcionalidade de edição vai funcionar perfeitamente. Sem que necessite apagar a tabela

e criá-la novamente.

Arquivo de log grande no sql server

Em ambientes de desenvolvimento, testes e implantação de sistemas é comum o banco de dados sofrer grandes alterações, importações de dados massivas, exclusões de grande porcentagem de dados e transações com milhares de operações que aumentam o arquivo de log do banco de dados consideravelmente.

Uma saída no SQL Server para estes ambientes que não precisam de backups, é utilizar bancos de dados no modo de recuperação simples (recovery model, simple), onde o arquivo de log do banco de dados (*.ldf) somente será utilizado para gerenciar as transações correntes (e operações de replicação), diminuindo bastante a necessidade de espaço no HD.

Então, nas propriedades do “bando” de dados, aba “Options”, temos a propriedade “Recovery Model”, onde alteramos para Simple e damos OK!

É só isso? Sim!

Mas se você possui um arquivo de log que já esta ocupando muito espaço em disco, não seria interessante diminuir um pouco ele? Então após aplicar o modo de recuperação simples, vá na aba “Files” altere o “Initial Size (MB)” para um valor que você considere adequado para seu banco de dados e dê OK!

Mas algumas vezes só alterar esta propriedade não ajuda muito…



Então, nas “Tasks”, temos a opção “Shrink”, “Files”:



Neste nova janela, escolha a propriedade “File type” “Log” e dê OK:

Pronto, agora você tem um banco de dados com log reduzido.

Obs.: Até mesmo em ambientes de desenvolvimento não recomendo realizar shrink de qualquer outro tipo de arquivo que não seja o de log, então somente utilize shrink em arquivos de dados se realmente for necessário (ex.: exclusão de tabelas gigantes), pois shrink em arquivos de dados pode afetar consideravelmente o desempenho do banco de dados, por fragmentar de índices e arquivos de dados, e também no caso do arquivo de log, gerar processamento desnecessário para alocar mais espaço em disco em futuras operações.

PostgreSQL - Alterando senha do usuário postgres

Na instalação do PostgreSQL ele não te dá nenhuma oportunidade de criar a senha do usuário "postgres", sendo assim, o comando abaixo resolverá: 

$ sudo -u postgres psql 

Em seguida, insira a tua senha de root. 

Agora você está no console do PostgreSQL. Sendo assim, agora vamos alterar a senha do usuário: 

# alter user postgres with encrypted password 'senha'; 

Pronto, agora você já poderá conectar facilmente neste SGBD.

Acesso externo no PostgreSQL

O primeiro passo: 

Editar o arquivo postgresql.conf. A localização deste arquivo pode variar, no Ubuntu por exemplo, a localização é como segue: 

/etc/postgresql/8.4/main 

Para editar: 

# nano postgresql.conf 

Neste arquivo, editaremos somente uma linha, procure por este parâmetro: 

listen_addresses = '*'

O valor desta variável deve ser modificado para um asterisco(*). 

Pronto. Ctrl+O para Salvar e Ctrl+X para sair. 

O segundo passo: 

Editar o arquivo pg_hba.conf. A localização é a mesma do caso anterior. 

Para editar: 

# nano pg_hba.conf 

Adicione a seguinte linha: 

# IPv4 local connections:
host    all         all           0.0.0.0/0           md5

Pronto. Novamente, Ctrl+O para salvar e Ctrl+X para sair. 

Observe o md5, desta forma, para acesso externo, o usuário deverá se autenticar. Outro valor possível seria "trust", mas aí, vai de cada um.... Outra situação... 

Pronto, agora é só restartar o PostgreSQL: 

# /etc/init.d/postgresql-8.4 restart 

Agora o acesso externo pode ser feito...

MySQL: Duplicando uma tabela com apenas 1 comando SQL

Duplicar uma tabela no MySQL, mantendo sua estrutura e importando os registros da tabela original, é mais fácil do que eu imaginava.

No console de seu MySQL digite a seguinte query:

CREATE TABLE NOVA_TABELA SELECT * FROM TABELA_PAI;

Backup de database

O gerenciamento de backup é uma das tarefas mais importantes de um DBA, pois a disponibilidade do ambiente dependerá da existência ou não desse valioso recurso. Existem 5 tipos de backups: full, incremental, diferential, copy e diary.

Backup Full: O backup full é um backup completo de dados. Durante o backup, os arquivos tem seu atributo de archive alterado, informando que os mesmos sofreram backup.

Backup Incremental: O backup incremental copia todo o conteúdo alterado ou criado desde o último backup full, alterando o atributo de archive dos dados.

Backup Diferential: O backup diferential copia todo o conteúdo alterado ou criado desde o último backup full, porém não altera o atributo de archive dos dados.

Backup do tipo Copy: O backup copy é um backup full, porém ele não altera o atributo de archive, permitindo que não haja interrupção na sequencia de backups.

Backup do tipo Diary: Backup do tipo diary copia todos os arquivos que foram modificados no dia da execução, e não altera o atributo de archive dos dados.

A diferença entre backup diferential e incremental é no momento de realizar o restore dos dados. Para combinação de backup full e diferential, precisaremos restaurar primeiro o backup full e depois o último backup diferential, já para uma operação de restore com um backup full e incremental, precisaremos restaurar primeiro o backup full e depois todos os backups incrementais, desde o último backup  full. Isso nos mostra que em um ambiente com backup full e diferential, teremos um processo de backup mais demorado, porém o processo de restore será mais rápido, ao contrário do backup full e incremental, onde o processo de backup dos dados é mais rápido (pois apenas os arquivos que foram alterados sofrem backup), porém o processo de restore é mais demorado, pois será necessário várias fitas para restauração completa dos dados, dependendo do volume a ser restaurado. O esquema de backup a ser utilizado vai depender da necessidade de cada empresa.

Para o SQL Server, não há backups incrementais ou diary, apenas full, copy, diferential e transaction log. As regras são as mesmas para backup full e diferential, porém para backups de transaction logs é necessário que o database esteja com o recovery model configurado para Full (falarei de recovery model em outro artigo).

ATENÇÃO: backups criados em uma versão do SQL Server não podem ser restaurados em versões anteriores.

ATENÇÃO 2: a compressão de dados em backups é suportada apenas no SQL Server 2008 e 2012.

Listando queries com alto consumo de CPU

Um problema recorrente na vida de qualquer DBA é quando o SGBD começa a “devorar” os recursos do servidor (memória, disco ou CPU). Irei mostrar nesse post como detectar e tratar situações que o processo do SQL Server faz alta utilização de CPU.

Abra a console do SQL Server Management studio, abra o query editor e execute a seguinte instrução:

SELECT TOP 20
GETDATE() AS “Collection Date”,
qs.execution_count AS “Execution Count”,
SUBSTRING(qt.text,qs.statement_start_offset/2 +1,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END -
qs.statement_start_offset
)/2
) AS “Query Text”,
DB_NAME(qt.dbid) AS “DB Name”,
qs.total_worker_time AS “Total CPU Time”,
qs.total_worker_time/qs.execution_count AS “Avg CPU Time (ms)”,
qs.total_physical_reads AS “Total Physical Reads”,
qs.total_physical_reads/qs.execution_count AS “Avg Physical Reads”,
qs.total_logical_reads AS “Total Logical Reads”,
qs.total_logical_reads/qs.execution_count AS “Avg Logical Reads”,
qs.total_logical_writes AS “Total Logical Writes”,
qs.total_logical_writes/qs.execution_count AS “Avg Logical Writes”,
qs.total_elapsed_time AS “Total Duration”,
qs.total_elapsed_time/qs.execution_count AS “Avg Duration (ms)”,
qp.query_plan AS “Plan”
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE
qs.execution_count > 50 OR
qs.total_worker_time/qs.execution_count > 100 OR
qs.total_physical_reads/qs.execution_count > 1000 OR
qs.total_logical_reads/qs.execution_count > 1000 OR
qs.total_logical_writes/qs.execution_count > 1000 OR
qs.total_elapsed_time/qs.execution_count > 1000
ORDER BY
qs.execution_count DESC,
qs.total_elapsed_time/qs.execution_count DESC,
qs.total_worker_time/qs.execution_count DESC,
qs.total_physical_reads/qs.execution_count DESC,
qs.total_logical_reads/qs.execution_count DESC,
qs.total_logical_writes/qs.execution_count DESC

O resultado dessa querie irá listar as 20 instruções sendo executadas no servidor, consumindo mais recursos de CPU, como mostra a imagem abaixo:

Outro detalhe a ser percebido nesse resultado é o valor da linha 6, coluna “Total CPU Time“, expresso em microssegundos, e quando convertemos para minutos, identificamos que a querie em questão tomou exatos 17.8 minutos para executar !

É claro que apenas a identificação dessas queries não resolvem o problema, mas pelo menos já sabemos onde devemos atuar para otimizar o desempenho do SQL Server.