Информация

Последние записи

Теги


Блоги


Записи из всех блогов с тегом: ms sql


Группировка объектов в SQL Server Management Studio

Приветствую тебя, читатель!

В этом посте речь пойдет о группировке объектов в обозревателе SQL Server Management Studio.

читать дальше...
автор: SQL Refactor Studio Team добавлено: 27 янв 16 просмотры: 2637, комментарии: 1



Скриптование всех пользователей, ролей БД и их прав

Часто необходимо заскриптовать пользователей базы данных, а затем их снова накатить либо на новой базе данных , либо на старой, к примеру такое часто необходимо при восстанвление тестовой среды.
Ниже приводится скрипт скриптует пользователей и их права:



--1 создание таблицы для логинов
print 'Создание таблиц для хранения учетных записей и их прав'
IF NOT EXISTS (SELECT * FROM tempdb.sys.objects
              WHERE object_id = OBJECT_ID(N'tempdb.[dbo].[logins]') AND type in (N'U'))
begin
      create table tempdb.dbo.logins
      ( a varchar(2048),
       l int)
end
else
begin 
 truncate table tempdb.dbo.Logins
end

IF NOT EXISTS (SELECT * FROM tempdb.sys.objects
              WHERE object_id = OBJECT_ID(N'tempdb.[dbo].[loginsscripts]') AND type in (N'U'))
begin
      create table tempdb.dbo.loginsscripts
      ( a varchar(2048))
end
else
begin 
 truncate table tempdb.dbo.[loginsscripts]
end
print 'Конец Создание таблиц для хранения учетных записей и их прав'

--2 создание процедур скриптования 
print 'Создание процедур для скриптования логинов '
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USERGRANT]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE P...
читать дальше...
автор: dbasimple добавлено: 22 ноя 15 просмотры: 3203, комментарии: 0



Скрипт список разрешений пользователей на сервер MS SQL Server


 При миграции с одного MS SQL server на другой сервер, переустановки сервера  необходимо кроме как скопировать  учетные записи, нужно скопировать и их разрешения на сервер MS SQL Server.

Ниже приводится скрипт  получения списка прав на MS SQL Server(скрипт применим для версий MS SQL Server 2005 и старше):

select 
 'sqlcommand'=
   case
     when s.class_desc='SERVER' then  
         case
             when  s.state_desc<>'GRANT_WITH_GRANT_OPTION'then
              s.state_desc+' '+s.permission_name +' TO ['+p.name+']' collate Cyrillic_General_CI_AS
             else 
              'GRANT '+s.permission_name +' TO ['+p.name+'] ' +'WITH GRANT OPTION ' collate Cyrillic_General_CI_AS
         end
    
     when s.class_desc='ENDPOINT' then
        case
             when  s.state_desc<>'GRANT_WITH_GRANT_OPTION'then
              s.state_desc+' '+s.permission...
читать дальше...
автор: dbasimple добавлено: 16 ноя 15 просмотры: 2444, комментарии: 0



Скрипт переноса пользователей ролей сервера

При переходе с одного сервера на другой, необходимо так же перенести пользователей и вхождения в роли сервера.

Ниже приводится скрипт, которые предоставляет это сделать:

IF NOT EXISTS (SELECT * FROM tempdb.sys.objects WHERE object_id = OBJECT_ID(N'tempdb.dbo.#t')

AND type in (N'U'))

BEGIN
  create table #t

  ( serverrole nvarchar(40),

  membername nvarchar(200),

  membersid varbinary(85)

  )
end

else
  truncate table #t

declare @serverrole nvarchar(40)
declare @Membername nvarchar(200)
declare @sql nvarchar(250)

insert into #t
exec sp_helpsrvrolemember -- 'sysadmin'
--Если необходим список конкретной роли, то нужно указать её, иначе получится скрипт для всех ролей
 
declare cur cursor for
select serverrole,Membername from #t
where Membername not in ('sa','NT AUTHORITY\SYSTEM',

                                           'NT SERVICE\MSSQLSERVER','NT SERVICE\SQLSERVERAGENT',

                                           'NT AUTHORITY...
читать дальше...
автор: dbasimple добавлено: 21 окт 15 просмотры: 1901, комментарии: 0



Основы MS SQL Server шифрования на примере симметричного шифрования

Рассмотрим один из способов шифрования данных MS SQL Server 2008R2, а именно использование симметричного ключа, далее с помощью данного ключа зашифруем столбец в таблице.

Для начало немного теории:

Симметричный ключ –это ключ шифрования который используется как для шифрования данных, так и для их расшифровки. При его создании он должен быть зашифрован с помощью одним из средств: сертификат, пароль, симметричным\ассиметричным ключом. В примере будем шифровать с помощью сертификата.

Для того чтобы создать сертификаты и симметричный ключ в базе данных, нужно создать главный ключ базы данных (master key). Главный ключ базы данных  шифруется с помощью главного ключа службы, и хранится как в самой базе данных так и её копия в базе данных master.

Главный ключ службы является вершиной иерархии шифрования ms sql server. Он создается автоматически, когда он понадобится при шифровании.
Для шифрования отдельный строк данных  необходимы функции шифрования – мы будем использовать EncryptByKey \ DescryptByKey.

В итоге имеем следующую цепочку:

Главный ключ службы -> Главный ключ базы данных -> Ключи и сертификаты в базе данных.
Соответственно, не имея одного из компонентов, мы не сможем расшифровать наши данные. Поэтому не забываем делать резервные копии ключей.
Перейдем от теории к практики:
Задача: зашифровать поле  в таблице.

1) Для начало создадим ключ базы данных:

CREATE MASTER KEY ENCRYPTION BY
    PASSWORD= 'superpassword#12'

2)Затем создадим сертифик...
читать дальше...
автор: dbasimple добавлено: 14 окт 15 просмотры: 3590, комментарии: 1



MS SQL SERVER. Query_hash и Query_plan_hash, что в этих числах

Дмитрий Горчилин

По материалам:

Блог инженеров технической поддержки SQL Server. Microsoft. Россия

http://blogs.technet.com/b/sqlruteam/archive/2014/11/09/sql_5f00_server_5f00_query_5f00_hash_5f00_and_5f00_query_5f00_plan_5f00_hash_5f00_part_5f00_1.aspx

http://blogs.technet.com/b/sqlruteam/archive/2014/11/11/sql_5f00_server_5f00_query_5f00_hash_5f00_and_5f00_query_5f00_plan_5f00_hash_5f00_part_5f00_2.aspx

Две части, третьей нет, интрига. А почему? Попробую пояснить.



Цитата:

Анализ содержимого процедурного кэша показал, что огромное количество одинаковых по коду запросов имеют записи в процедурном кэше.

Как видно на рисунке ниже некоторые планы повторяются по 200 и более раз.

Для получения этих данных я воспользовался этим "волшебным" числом "query_hash".

За счет многократного повторения запросов израсходовано дополнительно 62 ГБ оперативной памяти.

Как видно из рисунка ниже если бы планы выполнения многократно не повторялись, то для их размещения понадобилось бы 20 ГБ вместо 82 ГБ.



Приводится скрипт, который пришлось немного докрутить.



select sum(max_plan_size_mb) from

(select eqs.query_hash,max(eqp.size_in_bytes)/1048576 max_plan_size_mb

from sys.dm_exec_query_stats eqs

join sys.dm_exec_cached_plans eqp

on eqs.plan_handle =eqp.plan_handle

group by eqs.query_hash

)as a



select sum(max_plan_size_mb ) from

(select eqs.query_hash,sum(cast(eqp.size_in_bytes as bigint))/1048576 max_plan_size_mb

from sys.dm_exec_query_stats eqs

join sys.dm_exec_cached_plans eqp

on eqs.plan_handle =eqp.plan_handle

group by eqs.query_hash

)as a



Получаются какие-то совершенно космические цифры. Кто-то упорно жрет память, на некоторых серверах оказывается, что злодеи сожрали примерно треть оперативной памяти.

Но не надо отчаиваться! Инженеры не врачи, их бояться не нужно. Вот это полный размер и количество закэшированных планов, цифра значительно, в разы меньше посчитанной прошлыми скриптами:



SELECT sum(cast(size_in_bytes as bigint))/1048576 as sum_plan_size_mb,count(*) as count

FROM sys.dm_exec_cached_plans



Что за чудеса?

Дело в том, что в sys.dm_exec_cached_plans МЕНЬШЕ записей, чем в sys.dm_exec_query_stats. Несколько записей sys.dm_exec_query_stats могут ссылаться на один план. Соответственно, их скриптом один и тот же план может быть посчитан несколько раз. Соответственно, несколько раз просуммирован его размер, что неправильно.



Вывод 1: дурят православных
Вывод 2:не доверяйте бездумно инженерам, особенно если это инженеры Microsoft :)



Что до третьей части- судя по всему, авторы планировали там рассказать о принудительной параметризации, но кто-то из читателей оказался шибко умным, прочел текст внимательно. Инженеры обиделись и не написали больше.
автор: Дмитрий Горчилин добавлено: 07 окт 15 просмотры: 1297, комментарии: 0



Основы прозрачного шифрования MS SQL Server


Продолжая тему защиты данных в базе данных в MS SQL Server, рассмотрим прозрачное шифрование (Transparent Data Encryption - TDE ) в MS SQL Server.  Демонстрация шифрования будет проводится на MS SQL Server 2012 SP1 Enterprise Edition  для базы данных клиентов [DbClients], но данный сценарий будет рабочий и для MS SQL Server 2005/2008. Прозрачное шифрование доступно только в редакциях Enterprise или Datacenter.
Итак, для начало немного теории:

Как всегда все хорошо описано в BOL -http://msdn.microsoft.com/ru-ru/library/bb934049.aspx .
Функция прозрачного шифрования данных (TDE) выполняет в реальном времени шифрование и дешифрование файлов данных и журналов в операциях ввода-вывода. При шифровании используется ключ шифрования базы данных (DEK), который хранится в загрузочной записи базы данных для доступности при восстановлении.Ключ шифрования базы данных является симметричным ключом, защищенным сертификатом, который хранится в базе данных master на сервере, или асимметричным ключом, защищенным модулем расширенного управления ключами.Функция прозрачного шифрования данных защищает «неактивные» данные, то есть файлы данных и журналов

Основное прозрачного шифрования, это то , что:
Шифрование файла базы данных проводится на уровне страниц. Страницы в зашифрованной базе данных шифруются до записи на диск и дешифруются при чтении в память. Прозрачное шифрование данных не увеличивает размер зашифрованной базы данных.

При этом не стоит забывать, что прозрачное шифрование не ...
читать дальше...
автор: dbasimple добавлено: 21 сен 15 просмотры: 2496, комментарии: 0



Генерация выражения SELECT - загружаем в базу MS SQL Server данные из внешних источников

Задача:
Допустим, у Вас есть таблица MS Excel (MS Access, или другой источник данных) и Вам нужно перенести ее в базу данных SQL Server.

Какие существуют традиционные способы выполнения этой задачи:
1. Воспользоваться функцией OPENROWSET.
2. Сформировать ручками запрос SELECT прямо в Excel.
3. Master Data Import / DTS/ SSIS

Безусловно все эти способы занимают много времени. В случае с OPENROWSET не всегда на сервере установлен нужный провайдер.

Существует способ выполнить эту задачу за пару кликов мышкой...

читать дальше...
автор: SQL Refactor Studio Team добавлено: 13 фев 15 просмотры: 2199, комментарии: 0



Эффективный аудит архивного копирования баз данных

Архивное копирование баз данных одна из задач любого лица, ответственного за поддержку баз данных. Для проведения архивного копирования кроме стандартной команды
BACKUP DATABASE
которая доступна в MS SQL Server, есть масса утилит от сторонних компаний. Наличие достаточно большого числа решений от различных поставщиков несомненно свидетельствует о важности этой задачи. Но выполнение регулярного архивного копирования это только часть задачи, не менее важной частью является регулярный контроль этой процедуры. Вот об эффективном контроле мы и поговорим в этой статье.

Проверка выполненного архивного копирования - это ежедневная задача и сделав её максимально короткой позволит сберечь ежедневное время для действительно важных задач и не будет обременять администратора. Задача становится всё более и более сложной при увеличении количества баз данных. В моём хозяйстве случае количество баз на продуктивных серверах, требующих контроля ежедневно, исчисляется тысячами и даже просмотр отчёта (если он даже генерируется автоматически) непозволительная роскошь трата времени.

Для эффективного выполнения этой задачи в утилите MSSQLServerAuditor доступен отчёт, где на одной(!) странице сгруппированы только проблемы о всех типах архивного копирования (полное, инкрементальное и архивация журнала транзакций). Как правило, отчёт содержит только небольшое число ошибок и позволяет сразу сконцентрироваться на решении только проблемных моментов и не просматривать большинство задач выполненных успешно.

К сожалению ошибки случаются, но наличие эффективного контроля позволяет понять проблему и приступить к решению. Причины ошибок заключаются в сбоях или проблемах дисковой системы (банально кончилось место на ленте), сбоях в сети, ошибок в регламентных задачах, новых базах, которые забыли включить в регулярное архивное копирование, отключении или прерывании по разным причинам архивирования. Т.е. ранняя диагностика проблемы с архивным копированием позволит ещё и решить параллельные задачи и в целом предоставить эффективную информационную среду для выполнения бизнес задач.
автор: saycale добавлено: 21 авг 13 просмотры: 1161, комментарии: 0



Новый блог об эффективном администрировании MS SQL сервера

Блог посвящён администрированию и разработке для MS SQL Server. В качестве примеров я привожу примеры боевых реальных серверов, используемых в промышленной эксплуатации. В моём хозяйстве окружении используются сервера баз данных начиная с версии 7.0 (остался только один) и до 2014 (один, только для изучения возможностей). Основная масса серверов работает под MS SQL 2008, хотя всё ещё значительная часть под MS SQL 2000, но постепенная миграция на MS SQL 2008 идёт и доля серверов MS SQL 2000 стремительно сокращается. Для эффективного управления всем окружением используется утилита MSSQLServerAuditor, которая является основным инструментом управления и помогает эффективно справляться со всё возрастающей сложностью административных задач и сократить время на ежедневные задачи по мониторингу и управлению всего парка серверов.
автор: saycale добавлено: 21 авг 13 просмотры: 1362, комментарии: 0


предыдущие записи