Gerando backups do SQL Server por PowerShell

No primeiro artigo desta semana vimos como gerar backups do SQL Server por .NET, agora veremos alternativas mais simples para tal, com o PowerShell.

Para o nosso primeiro script de Backup com PowerShell, precisaremos referenciar os assemblies do SMO, e criar um código semelhante ao que vimos com .NET:

# Referências
[void][system.reflection.assembly]::loadfrom("C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Smo.dll")
[void][system.reflection.assembly]::loadfrom("C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.SmoExtended.dll")

# Objetos
$srv = new-object Microsoft.SqlServer.Management.Smo.Server(".\SQLEXPRESS")

$bk = new-object Microsoft.SqlServer.Management.Smo.Backup

# Definindo de qual banco de dados será executado o backup
$bk.Database = "BASE1"

# Definindo onde será salvo o backup
$bk.Devices.AddDevice("C:\TEMP\BASE1_PowerShell.bak", [Microsoft.SqlServer.Management.Smo.DeviceType]::File)

# Executando o backup
$bk.SqlBackup($srv)

Mas quando no contexto do PowerShell do SQL Server (scripts executados em Jobs ou diretamente pelo PowerShell presente no SQL Server Management Studio ou SQLPS.exe), teremos algumas facilidades, como não precisar referenciar os assemblies do SMO e utilizar de “atalhos” para instanciar as variáveis do SQL Server (ex.: Smo.Server):

# Sem referências

# Objetos
$srv = get-item SQLSERVER:\SQL\LOCALHOST\SQLEXPRESS

$bk = new-object Microsoft.SqlServer.Management.Smo.Backup

# Definindo de qual banco de dados será executado o backup
$bk.Database = "BASE1"

# Definindo onde será salvo o backup
$bk.Devices.AddDevice("C:\TEMP\BASE1_PowerShell.bak", [Microsoft.SqlServer.Management.Smo.DeviceType]::File)

# Executando o backup
$bk.SqlBackup($srv)

Referências:

How To Load .NET Assemblies In A PowerShell Session

Gerando backups do SQL Server por .NET

No artigo da semana passada, tivemos uma breve introdução à biblioteca SMO, que pode ser tanto utilizada pelo .NET quanto pelo PowerShell para criar aplicações ou scripts para gerenciar o SQL Server.

Nesta semana, demonstrarei como utilizar as classes Smo.Backup e Smo.BackupDeviceItem, de um outro assembly do SMO, o SmoExtended, que poderá ser encontrado no caminho:

C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.SmoExtended.dll

As classes Smo.Backup e Smo.BackupDeviceItem são responsáveis respectivamente por gerar backup e determinar onde este será salvo.

Para demonstrar a utilização destas classes, adequei o exemplo da semana passada, que relacionava os bancos de dados de uma determinada instancia do SQL Server, para também possibilitar identificar a data de ultimo backup de um determinado banco de dados e realizar o backup do mesmo:

Ajuste realizado:

// Gerando a classe Smo.Backup
var backup = new Backup { Database = database.Name };

// Definindo com o Smo.BackupDeviceItem onde será salvo o backup
var path = string.Format(@"C:\Temp\{0}_{1:yyyy-MM-dd HH_mm_ss}.bak", database.Name, DateTime.Now);

backup.Devices.AddDevice(path, DeviceType.File);

// Executando o backup
backup.SqlBackup(server);

Gerenciando o SQL Server por meio de aplicações .NET – Primeiros passos

Já tratamos no blog duas formas de interação da .NET Framework com o SQL Server, sendo a primeira e principal forma interação, por ADO.NET para execução de comandos T-SQL,  e a segunda forma, por SQL CLR para estender funcionalidades do SQL com .NET.

Além destas duas formas de interação, temos o SMO (SQL Server Management Objects) que nos permite gerenciar o SQL Server utilizando um coleção de assemblies .NET, que podem ser utilizados em scripts do PowerShell ou em aplicações .NET de forma bem transparente.

Há alguns anos escrevi um artigo para revista Mundo .NET sobre este assunto, mas não demonstrei muito do potencial que temos com SMO.

A partir deste artigo, pretendo iniciar uma nova série de artigos que tratarão várias formas de utilizar SMO com .NET e também com PowerShell (mais como um comparativo de como fazer com .NET e com PowerShell).

Hoje, vamos começar com uma aplicação .NET simples que ilustra bem a utilização do SMO.

Tento um projeto .NET, o primeiro passo será encontrar os assemblies que compõe a SMO, no caso do SQL Server 2008 (100). Eles podem ser encontrados nas pastas:

C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies
C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies

Caso não os encontre, você pode instalá-los:
http://www.microsoft.com/download/en/details.aspx?id=16177#SMO
(Microsoft SQL Server 2008 Management Objects)

Para a nossa primeira aplicação, precisaremos dos seguintes assemblies referenciados em nossa aplicação:

Microsoft.SqlServer.Smo.dll
Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.Management.Sdk.Sfc.dll

Adicionadas as referencias ao projeto, será possível criar uma aplicação simples em .NET (em C# no meu caso) para relacionar os bancos de dados de uma determinada instancia do SQL Server:

using System;
using System.Linq;
using System.Security;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

namespace DemoSmo
{
    class Program
    {
        static void Main()
        {
            Console.WriteLine("Servidor:");
            var serverInstance = Console.ReadLine();
            Console.WriteLine();

            Console.WriteLine("Usuário:");
            var user = Console.ReadLine();
            Console.WriteLine();

            ServerConnection conn;

            if (!string.IsNullOrEmpty(user))
            {
                Console.WriteLine("Senha:");
                var pass = ReadPassword();
                Console.WriteLine();

                // Criando um objeto de conexão com SQL Authentication
                conn = new ServerConnection(serverInstance, user, pass);
            }
            else
            {
                // Criando um objeto de conexão com Windows Authentication
                conn = new ServerConnection(serverInstance);
            }

            // Criando um objeto que abstrai a instância do SQL Server
            var server = new Server(conn);

            Console.WriteLine("Carregando lista de bancos de dados ...");
            Console.WriteLine();

            try
            {
                // Recuperando a lista de banco de dados
                var lst = server.Databases;

                // Escrevendo a relação de banco de dados encontrados
                foreach (var db in lst.Cast<Database>().OrderBy(e => e.ID))
                {
                    Console.WriteLine("{0}. {1}", db.ID, db.Name);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }

            Console.WriteLine();
            Console.WriteLine("Concluído ...");
            Console.ReadKey();
        }

        // Método para evitar que a senha digitada apareça na tela da aplicação
        private static SecureString ReadPassword()
        {
            var pass = new SecureString();

            while (true)
            {
                var key = Console.ReadKey(true);

                if (key.Key == ConsoleKey.Enter) break;

                pass.AppendChar(key.KeyChar);

                Console.Write("*");
            }

            Console.WriteLine();

            return pass;
        }
    }
}

E por fim, executando a aplicação por SQL Authentication:

E por Windows Authetication:

Por enquanto não há muitos materiais no blog sobre SMO, então você pode esperar novos artigos serem publicados, encontrar outras fontes pela internet ou comprar a revista que tem o artigo que escrevi sobre SMO.

Até o próximo artigo!

A verdade sobre a instalação em Português do SQL Server

PowerShell – Especial de Natal

Boa noite pessoas,

Seguindo tradição do blog, pelo terceiro Natal consecutivo, estava planejando algo bem diferente dos anos anteriores, e em conversas com o @WilliamPietro, tivemos a ideia de criar um script de PowerShell especial para esta data.

Abaixo o script:

$a = (Get-Host).UI.RawUI
$a.WindowTitle = "PowerShell - Especial de Natal"
$b = $a.WindowSize
$b.Width = 18
$b.Height = 12
$a.WindowSize = $b

$c = "        .`n       .#.`n      .###.`n     .#%##%.`n    .%##%###.`n   .##%###%##.`n  .#%###%##%##.`n        #`n        #`n"

1..300 | % {
	$a.BackgroundColor = "darkgreen"
	$a.ForegroundColor = "white"
	cls
	Write-Host $c
	Write-Host "    HO HO HO"
	Wait-Event -timeout 1
	$a.BackgroundColor = "red"
	$a.ForegroundColor = "white"
	cls
	Write-Host $c
	Write-Host "   Feliz Natal"
	Wait-Event -timeout 1
}

Resultado, durante aproximadamente 10 minutos a tela do PowerShell exibirá de forma alternada as seguintes mensagens:

Boas festas, e que tenhamos um ótimo 2012!

Artigos relacionados:

Artigo de Natal de 2009

Artigo de Natal de 2010

Desenvolvendo componentes COM+ com .NET

Quando se trabalha com .NET em sistemas mistos com plataformas legadas, como ASP Classic, VB, VBScript, Delphi ou até mesmo Ole Automation do SQL Server, o .NET ainda permite interoperabilidade com estes sistemas por meio de componentes COM+, seja o .NET consumindo COM+ ou a plataforma legada consumindo um COM+ desenvolvido em .NET.

Como existem poucos artigos em português para explicar como desenvolver COM+ com .NET, procurei a demonstrar a criação de componente simples com C# no Visual Studio 2010.

Para a criação do componente, utilizei um projeto do tipo Class Library:

A este projeto adicionei referência à System.EnterpriseServices:

Nas propriedades do projeto, editei a “Assembly Information”,

especificando o Assembly como COM-Visible:

De acordo com a plataforma do sistema legado (x64, x86) que o COM+ irá trabalhar, defini esta em Plataform target:

Obs.: Esta opção é muito importante, pois é muito comum criar um componente em x64 e este não ser visível para uma aplicação VB x86, assim como tentar criar componentes x86 para tentar acessar por um VBScript que será executado em uma plataforma x64.

Por fim, criei uma chave “strong name” para o projeto:

Para uma classe .NET ser considerada componente, ela deverá ser herdada de System.EnterpriseServices.ServicedComponent, conforme abaixo, onde temos uma classe simples cujo o seu único método retorna um texto:

using System.EnterpriseServices;

namespace DemoCom
{
    public class Objeto : ServicedComponent
    {
        public string Teste()
        {
            return "Demo COM+ //By SQL From Hell";
        }
    }
}

Após realizar o build do projeto, utilizei o regsvcs de acordo com a plataforma x86/x64 e versão da .NET Framework do projeto, informando o caminho do seu assembly (DLL) onde esta “$(TargetPath)”.

C:\Windows\Microsoft.NET\Framework\v4.0.30319\regsvcs.exe $(TargetPath)

C:\Windows\Microsoft.NET\Framework64\v4.0.30319\regsvcs.exe $(TargetPath)

Após a instalação do componente, este será visível na relação de COM+ Applications no Component Services:

Para testar o COM+, utilizarei um VBScript simples:

Dim obj
Set obj = CreateObject("DemoCom.Objeto")
MsgBox obj.Teste

Também sendo possível por Ole Automation do SQL Server:

DECLARE @obj INT

DECLARE @return INT, @text NVARCHAR(4000)

-- Criando a 'instância' do componentes

EXEC @return = sp_OACreate 'DemoCom.Objeto', @obj OUT

-- Verificando se a chamada obteve sucesso.

IF @return = 0 PRINT 'COM: OK'

-- Chamando o método Teste

EXEC @return = sp_OAMethod @obj, 'Teste', @text OUT

IF @return = 0 PRINT 'Teste: OK'

PRINT 'Resultado: ' + @text

E por fim, para desinstalar o componente, bastará utilizar o mesmo regsvcs informando “/u”.

C:\Windows\Microsoft.NET\Framework\v4.0.30319\regsvcs.exe /u $(TargetPath)

C:\Windows\Microsoft.NET\Framework64\v4.0.30319\regsvcs.exe /u $(TargetPath)

Artigos relacionados:

Acessando dados de Web Service com Ole Automation Procedures

Gerando templates das System Views e Information Functions

Boa noite pessoas, para limpar minha pasta de scripts, segue outras duas queries que ainda não tinha colocado no blog.

Gerando templetes das System Views:

SELECT
[name] AS [View]
,'SELECT * FROM [sys].['+ [name] + ']' AS Query
FROM sys.all_views
WHERE [schema_id] = SCHEMA_ID('sys')
ORDER BY [name]

Gerando templates das Information Functions:

SELECT
[name] AS [Function]
,'SELECT * FROM [sys].['+ [name] + ']'
+ ISNULL('(' + LEFT([parameters], LEN([parameters]) - 1) + ')', '')
AS Query
FROM sys.all_objects o
CROSS APPLY
(
SELECT CAST((
SELECT [name] + ', '
FROM [sys].[all_parameters]
WHERE [object_id] = o.[object_id]
FOR XML PATH('')
) AS VARCHAR(8000)) AS [parameters]
) p
WHERE [schema_id] = SCHEMA_ID('sys')
AND [type] = 'IF'
ORDER BY [name]

Iniciando com XQuery – Namespaces têm solução!

É muito comum em cenário com XML ter situações que no cabeçalho do arquivo há “xmlns” e nós com prefixos “xsi:”, “xsd:” e entre outros, deixando o DBA ou desenvolvedor responsável pelas consultas confuso com o fato de consultas com simples XQueries não trazerem nenhum resultado.

Mesmo que o comportamento “padrão” nestes cenários na maioria das vezes é dar um REPLACE no cabeçalho do XML, vamos aprender como resolver esta situação de forma prática.

O XML que usarei para o exemplo possui um namespace bem comum para dados oriundos de aplicações como o http://tempuri.org/, que é o namespace padrão do exemplo (URL declarada com xmlns sem qualquer sufixo):

DECLARE @X XML

SET @X = '
<ArrayOfDsItem xmlns="http://tempuri.org/" xmlns:ns2="http://tempuri2.org/">
  <DsItem Code="1" Value="ACRE" Value2="AC">
    <ns2:Status>Não existe</ns2:Status>
  </DsItem>
  <DsItem Code="5" Value="BAHIA" Value2="BA" />
  <DsItem Code="11" Value="MATO GROSSO" Value2="MT" />
  <DsItem Code="13" Value="MINAS GERAIS" Value2="MG" />
  <DsItem Code="19" Value="RIO DE JANEIRO" Value2="RJ" />
  <DsItem Code="21" Value="RIO GRANDE DO SUL" Value2="RS" />
  <DsItem Code="24" Value="SÃO PAULO" Value2="SP" />
</ArrayOfDsItem>'

Visto que estamos tratando de um namespace padrão, podemos determinar ou por T-SQL ou por XQuery, que este é o bendito namespace de todo o XML:

-- Definindo o namespace padrão
;WITH XMLNAMESPACES (DEFAULT 'http://tempuri.org/')
SELECT
	E.value('@Value', 'varchar(25)') AS Estado,
	E.value('@Value2', 'char(2)') AS Sigla
FROM @X.nodes('//ArrayOfDsItem/DsItem') AS N(E)

SELECT
	E.value('@Value', 'varchar(25)') AS Estado,
	E.value('@Value2', 'char(2)') AS Sigla
FROM @X.nodes('
	declare default element namespace "http://tempuri.org/";
	//ArrayOfDsItem/DsItem
') AS N(E)

Ou podemos criar um namespace sinônimo (ns), o que afetará um pouco a nossa XQuery:

-- Definindo um sinonimo para namespace padrão (ns)
;WITH XMLNAMESPACES ('http://tempuri.org/' AS ns)
SELECT
	E.value('@Value', 'varchar(25)') AS Estado,
	E.value('@Value2', 'char(2)') AS Sigla
FROM @X.nodes('//ns:ArrayOfDsItem/ns:DsItem') AS N(E)

SELECT
	E.value('@Value', 'varchar(25)') AS Estado,
	E.value('@Value2', 'char(2)') AS Sigla
FROM @X.nodes('
	declare namespace ns = "http://tempuri.org/";
	//ns:ArrayOfDsItem/ns:DsItem
') AS N(E)

Mas e agora, tenho dois ou mais namespaces no XML, o que podemos fazer? Podemos optar por qualquer uma das práticas anteriores (definindo um sinônimo ou definindo o namespace padrão) e mais os sinônimos para os namespaces secundários:

-- Definindo o namespace padrão e o sinonimo para o secundário (ns2)
;WITH XMLNAMESPACES (
	DEFAULT 'http://tempuri.org/',
	'http://tempuri2.org/' AS ns2
)
SELECT
	E.value('@Value', 'varchar(25)') AS Estado,
	E.value('@Value2', 'char(2)') AS Sigla,
	E.value('ns2:Status[1]', 'varchar(25)') AS Comentario
FROM @X.nodes('//ArrayOfDsItem/DsItem') AS N(E)

SELECT
E.value('@Value', 'varchar(25)') AS Estado,
E.value('@Value2', 'char(2)') AS Sigla,
E.value('
	declare namespace ns2 = "http://tempuri2.org/";
	ns2:Status[1]
' , 'varchar(25)') AS Comentario
FROM @X.nodes('
	declare default element namespace "http://tempuri.org/";
	//ArrayOfDsItem/DsItem
') AS N(E)

-- Definindo sinonimos para namespace padrão (ns) e para o secundário (ns2)
;WITH XMLNAMESPACES (
	'http://tempuri.org/' AS ns,
	'http://tempuri2.org/' AS ns2
)
SELECT
	E.value('@Value', 'varchar(25)') AS Estado,
	E.value('@Value2', 'char(2)') AS Sigla,
	E.value('ns2:Status[1]', 'varchar(25)') AS Comentario
FROM @X.nodes('//ns:ArrayOfDsItem/ns:DsItem') AS N(E)

SELECT
E.value('@Value', 'varchar(25)') AS Estado,
E.value('@Value2', 'char(2)') AS Sigla,
E.value('
	declare namespace ns2 = "http://tempuri2.org/";
	ns2:Status[1]
' , 'varchar(25)') AS Comentario
FROM @X.nodes('
	declare namespace ns = "http://tempuri.org/";
	//ns:ArrayOfDsItem/ns:DsItem
') AS N(E)

Ficou legal, não ficou? Mas nada te impede de colocar alguns REPLACEs na sua query e ser feliz…

No próximo artigo veremos com iniciar um pequeno caos com o FOR XML e Namespaces…

Contando os minutos para o SQL Saturday 100

Bom dia pessoas,

Neste sábado, estaremos (eu, o @WilliamPietro e o @VitorBitner) em São Paulo para assistir o melhor evento de SQL Server que este país já viu, o SQL Saturday 100. Evento que contará com a presença de feras de SQL Server do Brasil e de outros países, permitindo além de ver novidades do SQL Server 2012, também a oportunidade de aprender e conhecer estas feras.

Agradecimento ás esposas e namoradas que com um pouco de LeroLeroInjetion* permitiram a nossa viagem para lá!

Abaixo a minha agenda:

*: Termo utilizado pelo Vitor, não assumo responsabilidades… XD

Boas práticas – Três dicas a respeito da função GETDATE()

Sim, você não entendeu errado, estou dedicando este artigo ás boas práticas da utilização (e não utilização) da função GETDATE.

Neste artigo, tratarei de três práticas que considero importantes quando requer a utilização desta função, mas se alguém quiser comentar sobre outras práticas ou soluções que considere melhores, fique a vontade.

A primeira dica esta relacionada à divergência da data do servidor/cliente da aplicação em comparação ao servidor do banco de dados, pois é comum aparecer problemas em regras de negócios que envolvem a utilização da função GETDATE() e outras funções com finalidades semelhantes pela aplicação (ex.: VB.NET/C# – System.DateTime.Now()) quando a data/hora dos servidores/clientes estão diferentes (horário de verão que o diga). Desta forma, procure criar políticas para manter as datas/horas dos servidores sincronizadas, e utilizar somente a data/hora de um dos servidores (normalmente o servidor de banco de dados) nas regras de negócio.

Outra dica é referente ao conflito de fuso horário (ou timezone), pois quando você possui servidores em dois ou mais lugares distantes no mundo, a função GETDATE() pode gerar um pouco de dor de cabeça. Assim, utilizar a função SYSDATETIMEOFFSET ao invés de GETDATE, e o tipo DATETIMEOFFSET ao invés de DATETIME, permitirá solucionar estes conflitos e também dará ‘transparência’ em relação aos fusos horários nas consultas.

A função GETUTCDATE também pode ser útil para fusos horários distintos, mas não oferece a mesma transparência em relação ás datas entre os diversos fusos, por utilizar o horário UTC.

Para ‘conversões’ entre os fusos horários, utilize a função SWITCHOFFSET.

A terceira dica é referente ao conflito com os valores obtidos pela função GETDATE() em um mesmo script. Sim, isso existe! Ou estava pensando que o valor da função GETDATE() é obtido só uma vez durante todos os comandos de um script, veja o exemplo:

A fim de solucionar estas divergências, opte pela utilização de variáveis para identificar o valor da função GETDATE() durante os comandos de um script, exemplo:

Espero que tenham gostado destas dicas.

Ainda tenho outras dicas a respeito da precisão das datas/horas, mas as deixo para uma próxima oportunidade.