Eric J.'s questions

Temos um aplicativo que consulta um banco de dados SQL periodicamente ao longo do dia. Há períodos de atividade zero ou apenas leve, intercalados com solicitações individuais de quantidades relativamente grandes de dados. Quando essas solicitações chegam, o objetivo principal é fornecer os dados rapidamente, e o objetivo secundário é fazer isso de maneira econômica. Devido à natureza do aplicativo, é bastante improvável que os dados/índices tenham sido armazenados em cache na RAM da consulta anterior (diferentes usuários, trabalhando em diferentes partes dos dados).

Para um sistema que experimenta um uso relativamente estável, ouvi a regra geral de observar o comprimento da fila do disco e manter esse número relativamente pequeno. Isso será executado especificamente na AWS, onde vi a regra geral de que um comprimento de fila de disco de 1 por 100 IOPS é razoável.

Como posso estimar os requisitos de IO para tal sistema? O comprimento da fila de disco é um indicador confiável ao lidar com consultas individuais em rajadas? Existem outras métricas que devo considerar?

Um aplicativo da Web precisa ser executado sys.dm_fts_parserpara descobrir quais palavras a Pesquisa de texto completo do SQL Server usou para corresponder documentos com a finalidade de realçar a sintaxe. Esse procedimento armazenado só pode ser executado se o chamador estiver na sysadminfunção de servidor.

Permissões

Requer associação na função de servidor fixa sysadmin e direitos de acesso à lista de palavras irrelevantes especificada.

http://msdn.microsoft.com/en-us/library/cc280463%28v=sql.105%29.aspx

Como seria muito imprudente conceder essa função de servidor ao usuário do servidor da Web, tentei criar um procedimento armazenado que é executado como um usuário separado que está nessa função.

create procedure usp_fts_parser
    @query nvarchar(max)
with execute as owner
as
select display_term from sys.dm_fts_parser(@query, 1033, 0, 0);
go

Em seguida, criei um usuário login_sign_fts_parser, tornei-o o proprietário do procedimento armazenado

altere a autorização em usp_fts_parser para login_sign_fts_parser

e tente permitir que os direitos de usuário do banco de dados do servidor web representem esse usuário:

GRANT IMPERSONATE ON USER::[IIS APPPOOL\Strategic Window] TO login_sign_fts_parser

Quando tento executar usp_fts_parser, recebo o erro:

Não é possível executar como principal do banco de dados porque o principal "login_sign_fts_parser" não existe, esse tipo de principal não pode ser representado ou você não tem permissão.

Por que recebo esse erro? Como posso cumprir meu objetivo de execução sys.dm_fts_parsersem conceder permissões indevidas ao usuário do banco de dados do servidor web?

Fundo

Estou escrevendo um script Powershell para

• Backup de um banco de dados para um arquivo no disco
• Compacte e criptografe o arquivo (7-Zip/AES256)
• Copie o backup criptografado para o Amazon S3
• Excluir o arquivo de backup

O script deve ser executado em uma conta do Windows com o privilégio mínimo definido para realizar essas tarefas e funciona muito bem com o SQL Server Express 2008R2.

No entanto, com o SQL Server 2008R2 Web Edition, o arquivo de backup é gravado de forma que apenas os Administradores tenham controle total do arquivo. Embora meu usuário menos privilegiado possa ler o arquivo, ele não pode excluí -lo .

Pergunta

É possível configurar o SQL Server para gravar o arquivo de backup de forma que os usuários comuns possam excluí-lo?

ATUALIZAR

Aqui está o script conforme solicitado em um comentário. Não consegui encontrar um aplicativo de console S3 funcional para Windows, então escrevi um básico. A fonte está incluída abaixo. Esses arquivos são liberados para o domínio público sem qualquer garantia.

S3EncryptedBackup.ps1

#param ($SERVER = $(throw "Usage: S3EncryptedBackup.cmd -SERVER NameOfSqlServerInstance -DB NameOfDbToBackup"), 
#       $DB = $(throw "Usage: S3EncryptedBackup.cmd -SERVER NameOfSqlServerInstance -DB NameOfDbToBackup"),
#       $BACKUPFOLDER = $(throw "Usage: S3EncryptedBackup.cmd -SERVER NameOfSqlServerInstance -DB NameOfDbToBackup"),
#       $BUCKET = $(throw "Usage: S3EncryptedBackup.cmd -SERVER NameOfSqlServerInstance -DB NameOfDbToBackup"))

### DEV: Set these locally.  For prod, require as parameter
$SERVER=".\YOUR_SERVER_HERE"
$DB="YOUR_DB_NAME_HERE"
$BACKUPFOLDER="YOUR_BACKUP_FOLDER_HERE"
$BUCKET="YOUR_S3_BUCKET_HERE"

### EDIT System Configuration
$S3='E:\Software\S3Backup\S3Backup.exe'
$SEVEN="`"C:\Program Files (x86)\7-Zip\7z.exe`"" # Edit if 7-Zip installation differs

### For AES Encryption:
$AES_KEY="YOUR_ENCRYPTION_KEY_HERE"

### Edit the environment variables below only if the AWS keys change
$AWS_ACCESS_KEY_ID="YOUR_AWS_KEY_HERE"
$AWS_SECRET_ACCESS_KEY="YOUR_AWS_SECRET_HERE"

### Create the DB Backup

$BACKUP_FILE=$BACKUPFOLDER + "\" + $DB + "_" + (get-date -F yyyyMMdd) + ".bak"
$BACKUP_CMD="BACKUP DATABASE [" + $DB + "] TO  DISK = N'" + $BACKUP_FILE + "' WITH NOFORMAT, INIT,  NAME = N'" + $DB + "-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10"
$BACKUP_7Z="$BACKUP_FILE.7z"

Remove-Item -Path $BACKUP_FILE -Include *.bak
Remove-Item -Path $BACKUP_7Z -Include *.7z

Invoke-Expression "SQLCMD.exe -S $SERVER -Q `"$BACKUP_CMD`""

if ($LastExitCode -ne 0) 
{
    throw "SQL Backup failed with exit code $LastExitCode."
}

### 7-Zip the file with AES Encryption
$SEVEN_ARGS=@("a", "$BACKUP_7Z", "$BACKUP_FILE", "-p$AES_KEY", "-mx3")

Start-Process $SEVEN -ArgumentList $SEVEN_ARGS -NoNewWindow -Wait

### Transfer to S3

# Uses our own S3Backup.exe because S3.exe from http://s3.codeplex.com/ crashed and is no longer supported

echo "Start S3 Upload to $BUCKET of $BACKUP_7Z"

$S3_PUT_ARGS=@("-Bucket:$BUCKET", "-Path:$BACKUP_7Z", "-Key:AWS_ACCESS_KEY_ID", "-Secret:$AWS_SECRET_ACCESS_KEY")

Start-Process $S3 -ArgumentList $S3_PUT_ARGS -NoNewWindow -Wait

if ($LastExitCode -ne 0) 
{
    throw "S3 Command failed with exit code $LastExitCode."
}

echo "Done!"

Remove-Item -Path $BACKUP_FILE -Include *.bak
Remove-Item -Path $BACKUP_7Z -Include *.7z

S3Backup.cs

Não incluí minha biblioteca para analisar os parâmetros da linha de comando. Você terá que reescrever essa parte, o que deve ser bem direto. Se sua instância da AWS estiver usando o IAM para gerenciar chaves, você não precisará transmiti-las como parâmetros.

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Amazon.Runtime; // Use NuGet to reference the AWS SDK 
using Amazon.S3;
using Amazon.S3.Model;
using Amazon.S3.Transfer;

namespace Tools
    class Program
    {
        static void Main(string[] args)
        {
            Args parsedArgs = new Args(args);

            string bucket = parsedArgs.Get("Bucket");

            if (bucket == null || bucket == string.Empty)
            {
                Console.WriteLine("Backup bucket must be specified as -Bucket:MyBucketName");
                Environment.Exit(1);
            }

            string path = parsedArgs.Get("Path");
            if (path == null || path == string.Empty)
            {
                Console.WriteLine(@"Backup path (directory or file) must exist and be specified as  -Path:D:\My\Path or -Path:D:\My\Path\MyFile.ext");
                Environment.Exit(2);
            }

            bool fileMode;

            if (Directory.Exists(path))
            {
                fileMode = false;
            }
            else if (File.Exists(path))
            {
                fileMode = true;
            }
            else
            {
                fileMode = false; // Compiler wants initialized value
                Console.WriteLine("Backup file does not exist.");
                Environment.Exit(3);
            }

            string key = parsedArgs.Get("Key");
            string secret = parsedArgs.Get("Secret");

            bool keyParmsOK = ((key == null && secret == null) || (key != null && secret != null));
            if (!keyParmsOK)
            {
                Console.WriteLine("If the parameters Key or Secret are specified, both must be specified.");
                Environment.Exit(4);
            }

            Transfer(fileMode, path, bucket, key, secret);

            Environment.Exit(0);
        }

        private static void Transfer(bool fileMode, string path, string bucket, string key, string secret)
        {
            AmazonS3Client s3 = null;
            try
            {
                if (key == null)
                {
                    s3 = new AmazonS3Client();
                }
                else
                {
                    AWSCredentials cred = new BasicAWSCredentials(key, secret, true);
                    s3 = new AmazonS3Client(cred);
                }

                using (TransferUtility transfer = new TransferUtility(s3))
                {
                    try
                    {                        
                        var listResult = s3.ListBuckets();
                        var exists = (from b in listResult.Buckets where b.BucketName == bucket select b).Any();
                        if (!exists)
                        {
                            s3.PutBucket(new PutBucketRequest() { BucketName = bucket, BucketRegion = S3Region.US });
                        }
                        if (fileMode)
                        {
                            transfer.Upload(path, bucket);
                        }
                        else
                        {
                            transfer.UploadDirectory(path, bucket);
                        }
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine("Error transfering to S3: " + Environment.NewLine + ex.ToString());
                        Environment.Exit(5);
                    }
                }
            }
            finally
            {
                if (s3 != null) s3.Dispose();
            }
        }
    }
}

Pesquisando na web, encontrei conselhos conflitantes sobre se há um impacto no desempenho ao especificar VARCHARcolunas excessivamente largas, por exemplo VARCHAR(255), quando VARCHAR(30)provavelmente será o suficiente.

Eu vejo consistentemente que há um impacto no desempenho se a linha inteira exceder 8060 bytes. Fora isso, vejo discordância.

A afirmação é verdadeira de que o padrão é SET ANSI PADDING ON= potencial para muitos espaços à direita ? Contanto que a largura total da linha seja menor que 8060, há alguma preocupação real de desempenho em VARCHARcolunas superdimensionadas?

Evidência de que a largura da coluna é importante

O mesmo vale para os tipos de dados CHAR e VARCHAR. Não especifique mais caracteres nas colunas de caracteres que você precisa.

https://www.sql-server-performance.com/w2k-filesystem-affects-performance/

• Comprimento é uma restrição nos dados (como CHECK, FK, NULL etc)
• Desempenho quando a linha excede 8060 bytes
• Não pode ter restrição ou índice exclusivo (a largura da coluna chave deve ser < 900)
• O padrão é SET ANSI PADDING ON= potencial para muitos espaços à direita

Quais são as consequências da configuração de varchar(8000)?

Evidência de que a largura da coluna NÃO importa

Se você está falando sobre varchar e nvarchar, não, não há penalidade por permitir um comprimento de campo maior.

https://stackoverflow.com/questions/7025996/overstating-field-size-in-database-design

O tipo de dados varchar, por outro lado, consome apenas a quantidade de espaço real usado mais 2 bytes para sobrecarga

http://sqlfool.com/content/PerformanceConsiderationsOfDataTypes.pdf