Теле2

Управление доступом в sql. Бесперебойная работа и безопасность данных

С выходом SQL Server 2005 разработчики и администраторы баз данных (DBA) получат целый комплект новых средств управления. Версия SQL Server 2005 отличается не только набором добавленных в нее новых служб; при разработке этого продукта создание новых инструментов управления для DBA тоже было одной из важнейших задач. На смену SQL Server 2000 Enterprise Manager и Query Analyzer пришел новый программный инструмент - SQL Server Management Studio. Еще необходимо упомянуть о двух новых вспомогательных средствах: SQL Server Configuration Manager и Surface Area Configuration tool. Прежде чем приступать к рассмотрению возможностей SQL Server Management Studio, давайте познакомимся с каждым из этих инструментов.

Средства управления конфигурацией

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

SQL Server Configuration Manager. Этот инструмент помогает управлять учетными записями различных служб SQL Server 2005 на сервере, а также самими этими службами. Непосредственно из SQL Server Management Studio можно запускать и устанавливать службы SQL Server 2005, подобно тому как это раньше делалось с помощью Enterprise Manager, так что заново изучать SQL Server Configuration Manager не придется. Данная программа может быть вызвана через меню Start в Windows или через Computer Management в меню Administrative Tools. А чтобы запустить SQL Server Management Studio, который мы рассмотрим в следующем разделе, можно воспользоваться диалоговым окном Management Studio Registered Servers.

SQL Server Configuration Manager используется для управления службами SQL Server, сетевыми библиотеками и SQL Native Client (см. экран 1 ). Здесь можно устанавливать и изменять параметры учетных записей служб, а также включать или отключать эти службы. Данный инструмент частично перекрывает функции двух других средств, которые осуществляют управление этими службами и другими функциями с точки зрения управления защитой от возможных угроз.

Средства настройки. В SQL Server 2005 появилось много новых служб. Теперь, в дополнение к стандартным службам, таким как SQL Server, SQL Agent и Analysis Server, можно еще управлять параметрами службы SQL Server Integration Services (ранее Data Transformation Services, DTS), Reporting Services, Full-Text Search и SQL Browser.

При установке SQL Server 2005 эти службы представляют собой объект для потенциальных атак. Для принятия мер по минимизации поверхности атаки Microsoft предлагает два новых средства настройки, одно из которых предназначено для управления службами, а другое - для настройки параметров соединений. На конечной стадии процесса установки SQL Server 2005 имеются ссылки на эти программы, они также доступны через меню Start. С помощью этих инструментов можно контролировать те службы и функции, которые представляют собой потенциально уязвимые места для атак на SQL Server, любую из этих служб можно включать или отключать непосредственно из этих программ.

С помощью утилиты Surface Area Configuration for Services and Connections можно запускать и отключать различные службы (см. экран 2 ). Здесь же можно управлять статусом службы SQL Browser, которая работает через порт UDP 1434 и преобразует соединения в именованные объекты.

Программа Surface Area Configuration for Features, окно которой показано на экране 3 , позволяет управлять соединениями, в том числе специализированными удаленными запросами, настройками CLR, хранимыми процедурами Database Mail (SMTP), удаленными выделенными административными подключениями (Dedicated Administrator Connections), конечными точками служб Web (SOAP), OLE-автоматизацией расширенных хранимых процедур, конечными точками Service Broker, хранимыми процедурами SQL Mail (MAPI), xp_cmdshell и Web Assistant. Все эти функции по умолчанию отключены и должны активизироваться выборочно, по мере необходимости. Для включения многих из этих функций можно использовать системную хранимую процедуру sp_configure, с ее помощью также можно задействовать наиболее важные команды T-SQL на конечных точках.

Management Studio

В SQL Server 2005 появилось новое консолидированное средство управления - SQL Server Management Studio. Это всеобъемлющий инструмент, в котором объединены функции, актуальные для DBA и для разработчиков. Management Studio представляет собой комплексное программное средство, которое содержит столько возможностей, что для их подробного описания одной статьи явно недостаточно. Что касается наиболее значительных новшеств и изменений, имеющихся в данном продукте, то их можно описать тремя фразами: управление сервером, редактирование запросов и анализ производительности.

Management Studio - это огромный «ящик с инструментами»: разработчики Microsoft тщательно отобрали лучшие функции администрирования из Enterprise Manager и лучшие функции анализа и редактирования запросов из Query Analyzer, а затем соединили их в новом программном средстве. Далее в статье мы бросим беглый взгляд на эти новые возможности.

Запустив оболочку Management Studio в первый раз, вы обнаружите в ней сходство с Visual Studio. Но несмотря на то что Management Studio базируется на оболочке Visual Studio и имеет с ней некоторое внешнее сходство, это обманчивое впечатление: Management Studio представляет собой абсолютно новое программное средство, разработанное с использованием управляемого кода.

Сразу становится очевидно, что это два типа диалогов: центральная, не перемещаемая часть окна Management Studio Window называется областью окна документа, а объекты, размещаемые по краям, называются компонентами. Изначально в окне документа по умолчанию отображается страница сводки (Summary), содержащая некоторые отчетные данные о зарегистрированных серверах SQL Server. При первом запуске Management Studio в левой части будут отображаться диалоги компонентов Registered Servers и Object Explorer, а окно документа, справа, будет содержать страницу Summary (см. экран 4 ). Здесь отображается предлагаемый по умолчанию набор компонентов, но его можно изменить, добавив нужные компоненты в выбранные места. Страница Summary может содержать перечень компонентов узла Object Explorer или формировать отчет по выбранному узлу. Некоторое время поработав с компонентами, вы познакомитесь с третьим типом диалогов, так называемыми немодальными диалоговыми окнами, которые не связаны с основным окном Management Studio.

Как и в Visual Studio, каждое из окон компонентов является фиксируемым и скрываемым (hideable). С помощью значка канцелярской кнопки, имеющегося в панели кнопок каждого из окон, можно включить функцию автоматического скрытия (auto-hide), еще до нее можно добраться, щелкнув правой кнопкой мыши в самом верху окна. Скрываемые автоматически окна удобны в тех случаях, когда требуется дополнительное экранное пространство для расширения Object Browser, авторизующих запросов (authoring queries) и т. д. Характер представления этих окон можно изменить, нажав кнопку Window Position (кнопка с изображением небольшой треугольной стрелки, направленной вниз, которая имеется в правом верхнем углу каждого из окон компонентов).

Через меню View можно задействовать многие другие окна компонентов. Для целей администрирования наиболее актуальны активные по умолчанию окна Registered Servers и Object Explorer. Что касается окон компонентов Solution Explorer, Template Explorer и Properties, то они больше пригодятся для редактирования запросов и анализа. В Management Studio запоминается последнее отображение и параметры конфигурации окна, поэтому все они сохранятся при следующем запуске программы. Если нужно сбросить настройки окна в состояние по умолчанию, то в меню Window следует выбрать пункт Reset Window.

В Management Studio реализована поддержка администрирования и редактирования сценариев для многих элементов: экземпляров базы данных SQL Server, вместе с Analysis Services, Report Services, Integration Services и SQL Server Mobile. Все эти типы служб могут регистрироваться через Management Studio с помощью Register Servers, а затем их можно администрировать с помощью Object Explorer. Здесь можно редактировать сценарии T-SQL, сценарии Analysis Services и сценарии SQL Server Mobile, а также объединять их в проекты с помощью окна Solution Explorer, что будет показано в разделе, посвященном редактированию запросов.

Администрирование

Для того чтобы в полной мере оценить те новые возможности, которые получили администраторы баз данных с появлением Management Studio, сначала сравним Management Studio с Enterprise Manager, стандартным средством администрирования в SQL Server 2000. Программа Enterprise Manager представляет собой встроенную в SQL Server 2000 графическую утилиту, предназначенную для выполнения задач администрирования сервера баз данных. Данной утилите присущ ряд недостатков, в частности с ее помощью можно управлять только реляционным механизмом SQL Server и службой SQL Agent, но не службой Analysis Services. Кроме того, Enterprise Manager не может эффективно отображать большое количество серверов, баз данных или объектов баз данных. Вторая проблема заключается в том, что для решения многих задач здесь используются модальные диалоги, поэтому после запуска какой-либо процедуры, например резервного копирования, часто приходится запускать еще одну копию Enterprise Manager для продолжения наблюдения за сервером. И наконец, Enterprise Manager - это оснастка Microsoft Management Console (MMC), что обусловливает не слишком стабильную работу данного приложения и имеющие место весьма неприятные сбои, а порой оно просто перестает отвечать.

Все упомянутые ограничения в Management Studio устранены. Отсюда можно администрировать не только экземпляры реляционного механизма, но также и Analysis Services. Management Studio использует новый прикладной интерфейс SQL Management Objects (SMO) API, что обеспечивает эффективное управление большим количеством объектов баз данных. И наконец, везде, где это возможно, используются немодальные диалоги, разработанные с использованием стабильного управляемого кода.

Registered servers. В Management Studio, в отличие от Enterprise Manager, окно, предназначенное для регистрации серверов и их организации, отделено от окна просмотра серверных объектов. Информацию о зарегистрированных серверах или группах серверов можно экспортировать в конфигурационный файл формата XML и далее импортировать на тот же самый или какой-либо другой сервер, просто щелкнув правой кнопкой на соответствующем зарегистрированном сервере или группе серверов и выбрав пункт Export.

Кроме того, в SQL Server 2005 также разнесены функции управления службами SQL Server и функции управления сервером. Например, из Registered Servers нельзя запускать или останавливать службы - для этих целей предназначен компонент SQL Server Configuration Manager. Также в Management Studio разделены процедуры регистрации сервера, подключения к серверу и просмотра объектов сервера. Разумеется, в ходе регистрации сервера через окно Registered Servers устанавливается кратковременное соединение с сервером в целях проверки, но данное соединение не поддерживается постоянно. Тем не менее, если щелкнуть правой кнопкой мыши на зарегистрированном сервере, а затем выбрать пункт Object Explorer или New Query, то информация о соединении, сохраненная вместе с информацией о зарегистрированном сервере, будет использоваться SQL Server в дальнейшем для установления соединений с данным сервером.

Object Explorer. Это основной инструмент для организации объектов сервера. Object Explorer предоставляет не только функции администрирования, но и возможности редактирования запросов. Если поглубже заглянуть внутрь процессора баз данных SQL Server 2005, то можно, например, увидеть, что результирующее дерево параметров имеет сходство с Enterprise Manager. Однако здесь еще можно регистрировать экземпляры Analysis Services и управлять ими, что не было предусмотрено в классическом Enterprise Manager.

Для каждого из узлов, отображаемых в дереве Object Explorer, могут вызываться различные административные диалоги. Например, если щелкнуть правой кнопкой мыши на имени сервера баз данных, будет вызвано немодальное диалоговое окно Server Properties с несколькими закладками, с помощью которого можно просматривать и изменять конфигурацию параметров администрирования для данного сервера.

Когда будет развернут узел Object Explorer на уровне SQL Server database engine, обратите внимание, что SQL Agent имеет собственный узел и все объекты кода T-SQL подпадают под категорию узла Programmability, который имеет в своем составе узел для размещения сборок (assembly). Если развернуть дерево на уровне Analysis Server, то здесь вы также увидите новый узел, предназначенный для размещения хранимых процедур, которые тоже являются сборками.

Диалоговые окна Object Explorer базируются на SMO, который пришел на смену SQL-DMO, реализованному в SQL Server 2000, и представляет собой усовершенствованную объектную модель, обладающую большей гибкостью. SMO работает в асинхронном режиме, что позволяет открывать узел сервера и работать со многими объектами или методами, не оказывая влияния на другие функции. Допустим, мы открываем базу данных, содержащую сотни или даже тысячи таблиц, и в то время, пока она открывается, можем открыть еще один узел и выполнить какую-либо другую работу. Также следует упомянуть, что SMO содержит все необходимые механизмы для работы со сценариями, что позволяет автоматизировать с помощью сценариев практически любые действия, выполняемые в Object Explorer.

Выполнение задач администрирования

В Object Explorer системные объекты отделены от объектов пользовательской категории. Например, системные базы данных (master, model, msdb и tempdb) относятся к категории System Databases, в то время как новые учебные базы данных, AdventureWorks и AdventureWorksDW, являются пользовательскими. Здесь следует иметь в виду, что учебные базы данных по умолчанию не устанавливаются, поэтому, если в ходе установки продукта не был выбран соответствующий параметр, по ее окончании у вас не будет ни одной доступной пользовательской базы.

Как и в случае установки свойств сервера, можно настраивать параметры базы данных, вызвав окно Database Properties. В Management Studio также поддерживаются средства проектирования таблиц и представлений (известные в SQL Server 2000 как Visual Data Tools), в том числе инструмент Database Diagrams. В те диалоговые окна, которые предназначены для работы с объектами баз данных, теперь встроен механизм поддержки сценариев, так что любые действия здесь могут быть автоматизированы с помощью сценариев (см. экран 5 ).

Компонент Object Explorer в Management Studio содержит узлы, предназначенные для управления параметрами безопасности. Также можно управлять унаследованными от SQL Server 2000 пакетами DTS, службами Notification Services, триггерами уровня базы данных, Database Mail и триггерами DDL уровня сервера.

Как можно было ожидать, в Management Studio встроены механизмы управления SQL Server Agent, но при этом сюда добавлен новый узел для управления SQL Server Agent Proxy (посредник SQL Server Agent). Через SQL Server Agent Proxy для каждого отдельного шага задания может определяться соответствующий контекст безопасности, это достигается путем предоставления посредников безопасности пользователю Windows компонентом SQL Agent. Обычно в заданиях T-SQL на уровне шагов посредники не используются, и тем не менее с их помощью в заданиях можно пошагово назначать посредников для элементов управления Active X, операций xp_cmdshell, репликаций, а также служб Analysis Services, SQL Server и SQL Server Integration Services.

Редактирование запросов

Помимо большого количества новых административных функций, в Management Studio появилась полнофункциональная среда для работы с запросами, существенно превосходящая по функциональности Query Analyzer из SQL Server 2000.

SQL Server 2000 Query Analyzer больше всего подходит для редактирования кода T-SQL и сценариев. И хотя в Query Analyzer имеется прекрасный редактор T-SQL, тем не менее ему свойствен ряд ограничений. В частности, с его помощью нельзя редактировать другие типы сценариев SQL Server, например такие, как запросы MDX. Если в Query Analyzer выполняется редактирование запроса или сценария, то при этом окно редактирования должно иметь соединение с SQL Server, причем подключиться к другому серверу в данном сеансе редактирования нельзя. Что касается графика плана исполнения, то возможности Query Analyzer прекрасно подходят для анализа хода выполнения запроса, однако графическое представление выходных данных не переносимо. Кроме того, хотя в Query Analyzer можно редактировать сценарии T-SQL, находящиеся на диске в виде файлов, в SQL Server 2000 отсутствует интерфейс прямого доступа к программам контроля версий. И последнее. Хотя с помощью инструмента Object Browser, имеющегося в Query Analyzer, можно выполнять в базе данных поиск объектов кода T-SQL, его нельзя использовать ни для каких задач, связанных с управлением базой данных.

Что касается возможностей редактирования в Query Analyzer, то практически все они сохранились и в Management Studio, но в новом продукте все описанные выше ограничения устранены. С помощью Management Studio можно редактировать все типы сценариев SQL Server, причем не только написанные на T-SQL, но и XML-сценарии. При редактировании запросов необязательно иметь постоянное соединение с сервером, теперь это можно делать и в автономном режиме либо в ходе сеанса переключиться на другой сервер, на котором данный запрос должен быть выполнен. Полученное в Management Studio графическое отображение плана исполнения на выходе может быть передано в другой сеанс Management Studio, соответственно можно работать с полученными результатами без необходимости подключения к исходному серверу. Имеющийся в Management Studio компонент Object Explorer содержит всю функциональность Object Explorer из Query Analyzer, но здесь также в полном объеме реализована поддержка функций администрирования.

Работа в автономном режиме. Для запуска нового запроса в Management Studio существует несколько способов. При выборе из меню File пункта New можно создавать запросы трех типов: запрос T-SQL, запрос Analysis Services MDX, DMS или XMLA либо запрос SQL Server Mobile. Кроме того, созданные запросы можно запускать из Object Explorer или Solution Explorer, о чем мы поговорим далее.

Процесс редактирования запросов в Management Studio является независимым от соединения. Запросы можно запускать, имея подключение к серверу, не имея его сначала и установив в нужный момент либо переключаться на другой сервер для выполнения этого же запроса. Это дает возможность наиболее естественным образом использовать предусмотренную в SQL Server 2000 практику редактирования запросов из сценариев и текстовых файлов. При переходе в режим редактирования появится несколько дополнительных пиктограмм редактирования запросов, предназначенных, в частности, для установки, разрыва и изменения соединения. При редактировании запросов в Management Studio можно задействовать функцию auto-hide для окна Object Browser, тогда освободится дополнительное пространство экрана для редактирования.

Решения и проекты. Management Studio имеет расширенные возможности редактирования, он позволяет объединять созданные сценарии и текстовые файлы в приложения (solution) и проекты (project). Проект представляет собой именованную коллекцию сценариев и текстовых файлов, которые обычно размещаются в стандартном каталоге Windows, имеющем то же имя, что и сам проект. Приложение - это набор из одного или более проектов, при этом все проекты, входящие в состав данного приложения, могут быть включены в каталог с именем, соответствующим имени приложения, но могут быть и приложения, содержащие только один проект и, соответственно, имеющие один вариант размещения. Для создания нового проекта в Management Studio нужно выбрать меню File, затем New, Project, при этом запускается диалоговое окно New Project.

В Management Studio по умолчанию приложению присваивается то же имя, что и проекту, а размещаются приложение и проект в каталоге My Documents. В тех случаях когда необходимо объединить большое количество сценариев в один крупный проект, который желательно разбить на несколько подчиненных проектов (sub-project), можно собрать несколько проектов в одно приложение с осмысленным именем и задать для него необходимое размещение. При этом сначала создается новый проект, а затем указывается приложение, в состав которого он должен быть включен. Имя, присваиваемое решению по умолчанию, может быть изменено. После того как были созданы проект и соответствующее решение, можно осуществлять навигацию по приложению и входящим в него проектам с помощью компонента Solution Explorer, как показано на экране 6 .

Предположим, требуется модифицировать несколько объектов, относящихся к схеме AdventureWorks HumanResources. При этом часть сценариев будет модифицировать таблицы, часть - представления, а какие-то из них будут изменять код хранимых процедур. Сценарии каждого из этих типов могут быть объединены в соответствующие проекты, а проекты затем объединяются в приложение. Один из способов реализации описанного подхода показан на экране 6: здесь приложение с именем soln_HR содержит несколько проектов, каждый из которых соответствует определенному типу вносимых в схему HR изменений.

Используя функциональность приложений и проектов, можно создавать наборы сценариев для SQL Server Analysis Services или для SQL Mobile, группируя их в собственные проекты. Наборы, определяемые как проекты, будут помещаться в соответствующие каталоги на диске и отображаться в Solution Explorer в виде отдельных узлов. Каждому проекту можно назначить имя и указать папку для размещения на диске. По умолчанию каталог проекта размещается внутри каталога приложения, и все файлы данного проекта будут храниться в этом каталоге.

Возможность организации файлов в проекты и приложения - это только одно из преимуществ новой технологии работы со сценариями в SQL Server. Кроме этого, Management Studio имеет тесную взаимосвязь с программами проверки исходного кода, если данная система контроля исходного кода является подключаемым модулем. В пакете Visual SourceSafe (VSS) имеется однозначное соответствие между проектами VSS и проектами Management Studio, поэтому после создания проекта и приложения в Management Studio приложение целиком можно загрузить в VSS для выполнения соответствующей проверки, а затем выполнять проверку на уровне отдельных проектов или файлов. Для доступа к функциям загрузки в систему контроля кода следует щелкнуть правой кнопкой на любом узле в Solution Explorer. Настроить параметры проверки кода можно в диалоговом окне Options, которое вызывается из меню Tools программы Management Studio. В этом же окне (Tools, Options) можно включить некоторые другие функции редактирования, такие как нумерация строк и динамическая справка.

Интерактивная работа со сценариями баз данных. Для редактирования в базе данных объектов T-SQL можно использовать Object Explorer. Когда вы попадаете внутрь программируемого объекта базы данных, такого как хранимая процедура, функция или триггер, вы можете вызвать редактор запросов T-SQL, для чего требуется выбрать New или Modify. Если выбрано New, тогда будет использоваться шаблон, параметры которого можно просматривать и изменять с помощью компонента Template Explorer. При выборе варианта Modify генерируется сценарий ALTER, соответствующий выбранному объекту. Функции New и Modify заменили компоненты Assisted Editor, существовавшие в ранних бета-версиях Management Studio для SQL Server 2005.

Усовершенствования для плана исполнения. Management Studio имеет два существенных расширения, связанных с возможностями просмотра планов. Здесь, как и в Query Analyzer, можно просматривать графический вариант плана исполнения, однако и сами пиктограммы, и цветовая гамма претерпели изменения. Кроме того, теперь большая часть пиктограмм показывает детали той операции, которую SQL Server выполняет в настоящий момент. Если нажать на значок с символом «+» в правом нижнем углу окна запроса на план исполнения, то можно перейти к увеличенному отображению запроса. А если выбрать какой-либо из узлов запроса на план исполнения, то в диалоговом окне Properties будет отображена дополнительная информация об этом действии.

Я думаю, многим понравится новая возможность, появившаяся в Management Studio, позволяющая сделать графическое изображение плана исполнения переносимым. Начать можно с прямого вывода в XML с помощью SET SHOWPLAN_XML ON. Если результаты запроса передаются в контейнер, тогда результирующий набор данных будет включать ссылку на документ XML, содержащий информацию о плане исполнения. Если щелкнуть по этой ссылке, то можно просмотреть содержимое плана исполнения в редакторе XML Management Studio.

В целях графического просмотра данные XML можно экспортировать в переносимый (portable) формат. В полученном наборе данных нужно щелкнуть правой кнопкой на ссылке XML и сохранить данные в виде файла с расширением.sqlplan, как показано на экране 7 . Если открыть сохраненный файл через пункт Open меню File, то Management Studio отобразит XML-документ в виде графического изображения плана исполнения, как показано на экране 8 .

Обратите внимание, что в диалоговом окне Properties по-прежнему отображается информация из плана исполнения. Наличие этой возможности позволяет отправить план исполнения по электронной почте коллеге или сотруднику службы поддержки, который сможет просматривать его без необходимости подключения к конкретной базе данных.

Соберем все вместе

Имеющийся в SQL Server 2005 инструментарий существенно расширяет возможности как управления, так и разработки. Например, интерфейс SQL Server Management Studio является настраиваемым, поэтому в каждом случае можно для себя решить, какие функции являются самыми необходимыми, и, соответственно, скрыть те функции, которые не будут использоваться в работе. Разумеется, в процессе освоения новых инструментов работы можно столкнуться с неизбежными в ходе обучения проблемами, но, как сказал поэт, «не пропадет ваш скорбный труд».

Кэлен Дилани - Независимый консультант и инструктор по SQL Server. Имеет сертификаты MCT и MCSE. Автор книги Inside SQL Server 2000 (Microsoft Press). [email protected]

Рон Талмадж - Владелец и руководитель Prospice LLC, консалтинговой компании в области баз данных из Сиэтла. Руководитель группы Pacific Northwest SQL Server Users Group, имеет сертификаты MCSD и MCP в SQL Server, ведет рассылку PASSnews и является SQL Server MVP. Написал Microsoft SQL Server 7.0 Administrator?s Guide (издательство Prima).

Моя компания только что прошла свой ежегодный процесс обзора, и я, наконец, убедил их, что настало время найти лучшее решение для управления нашей схемой/сценами SQL. В настоящее время у нас есть только несколько сценариев для ручного обновления.

Я работал с VS2008 Database Edition в другой компании, и это потрясающий продукт. Мой босс попросил меня взглянуть на SQL Compare by Redgate и искать любые другие продукты, которые могут быть лучше. Сравнение SQL также является отличным продуктом. Однако, похоже, что они не поддерживают Perforce.

Вы использовали для этого множество продуктов?

Какие инструменты вы используете для управления SQL?

Что должно быть включено в требования, прежде чем моя компания совершит покупку?

10 ответов

Я не думаю, что есть инструмент, который может обрабатывать все части. VS Database Edition не позволяет создать достойный механизм выпуска. Запуск отдельных скриптов из браузера решений недостаточно масштабируется в больших проектах.

Как минимум вам нужно

IDE/editor
репозиторий исходного кода, который может быть запущен с вашей IDE
соглашение об именах и организации различных сценариев в папках
процесс обработки изменений, управления релизами и выполнения развертываний.

Последняя пуля - это то, где вещи обычно ломаются. Вот почему. Для лучшей управляемости и отслеживания версий вы хотите сохранить каждый объект db в свой собственный файл script. То есть каждая таблица, хранимая процедура, представление, индекс и т.д. имеет свой собственный файл.

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

2 варианта, которые я использовал:

Помимо сохранения всех отдельных объектов базы данных в своих файлах, у вас есть сценарии выпуска, которые являются конкатенацией отдельных скриптов. Недостаток этого: у вас есть код в 2 местах со всеми рисками и недостатками. Потенциал: запуск выпуска так же просто, как выполнение одиночного script.

напишите небольшой инструмент, который может читать метаданные script из манифеста выпуска и выполнить eadch script, указанный в манифесте на целевом сервере. Для этого нет недостатка, за исключением того, что вам нужно писать код. Этот подход не работает для таблиц, которые нельзя отбросить и воссоздать (как только вы живете, и у вас есть данные), поэтому для таблиц вы будете иметь скрипты изменений. Таким образом, на самом деле это будет комбинация обоих подходов.

Я нахожусь в лагере "script it yourself", поскольку сторонние продукты будут только доводить вас до управления кодом базы данных. У меня нет одного script для каждого объекта, потому что объекты меняются со временем, а девять раз из десяти просто обновляют мою "таблицу создания" script, чтобы иметь три новых столбца были бы неадекватными.

Создание баз данных по большому счету тривиально. Настройте кучу скриптов CREATE, упорядочьте их правильно (создайте базу данных перед схемами, схемы перед таблицами, таблицы перед процедурами, вызовите процедуры перед вызовами и т.д.) И сделайте это. Управление изменением базы данных не так просто:

Если вы добавите столбец в таблицу, вы не сможете просто отбросить таблицу и создать ее с новым столбцом, потому что это приведет к уничтожению всех ваших ценных производственных данных.
Если Fred добавляет столбец в таблицу XYZ, а Mary добавляет другой столбец в таблицу XYZ, какой столбец добавляется первым? Да, порядок столбцов в таблицах не имеет значения [потому что вы никогда не используете SELECT *, правильно?], Если вы не пытаетесь управлять базой данных и отслеживать управление версиями, после чего у вас есть две "действительные" базы данных, которые не выглядят как друг друга, становятся настоящей головной болью. Мы используем SQL-сравнение не для управления, а для обзора и отслеживания вещей, особенно во время разработки, и немногие "они разные (но это не magger)", ситуации, которые мы можем, могут помешать нам заметить различия, которые имеют значение.
Аналогично, когда несколько проектов (разработчиков) работают одновременно и отдельно в общей базе данных, это может стать очень сложным. Возможно, все работают над проектом Next Big Thing, когда вдруг кто-то должен начать работу над исправлениями ошибок в проекте Last Big Thing. Как вы управляете требуемыми модификациями кода, когда порядок выпуска является переменным и гибким? (Действительно забавные времена.)
Изменение структур таблиц означает изменение данных, и это может стать адски сложным, когда вам приходится иметь дело с обратной совместимостью. Вы добавляете столбец "DeltaFactor", хорошо, так что вы делаете, чтобы заполнить это эзотерическое значение для всех ваших существующих (прочитанных: устаревших) данных? Вы добавляете новую таблицу поиска и соответствующий столбец, но как вы заполняете ее для существующих строк? Такие ситуации могут случаться не часто, но когда они это делают, вы должны делать это сами. Сторонние инструменты просто не могут предвидеть потребности вашей бизнес-логики.

По сути, у меня есть CREATE script для каждой базы данных, за которой следует серия сценариев ALTER, поскольку наша база кода изменяется со временем. Каждый script проверяет, может ли он быть запущен: это правильный "вид" базы данных, были выполнены необходимые предварительные сценарии, этот script уже запущен. Только когда пройдены проверки, script выполнит свои изменения.

В качестве инструмента мы используем SourceGear Fortress для управления базовым исходным кодом, Redgate SQL Compare для общей поддержки и устранения неполадок, а также ряд домашних сценариев на основе SQLCMD для "массового" развертывания скриптов с изменениями на несколько серверов и базы данных и отслеживать, кто применял какие скрипты к базам данных в какое время. Конечный результат: все наши базы данных являются стабильными и стабильными, и мы можем с готовностью доказать, какая версия есть или была в любой момент времени.

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

Обычно я использую MS Server Management Studio для управления sql, работы с данными, разработки баз данных и отладки его, если мне нужно экспортировать некоторые данные в sql script или мне нужно создать какой-то сложный объект в базе данных, я использую EMS SQL Management Studio для SQL Server, потому что там я могу более четко видеть, что узкие разделы моего кода и визуальный дизайн в этой среде дают мне легче

У меня есть проект с открытым исходным кодом (лицензирован под LGPL), который пытается решить проблемы, связанные с правильной версией схемы DB для (и более) SQL Server (2005/2008/Azure), bsn ModuleStore . Весь процесс очень близок к понятию, объясненному сообщением Филиппа Келли здесь.

В принципе, отдельная часть набора инструментов скриптирует объекты базы данных SQL Server схемы БД в файлы со стандартным форматированием, поэтому содержимое файла изменяется только в том случае, если объект действительно изменился (в отличие от сценариев сделанный VS, который также создает скрипты и т.д., отмечая все измененные объекты, даже если они фактически идентичны).

Но набор инструментов выходит за рамки этого, если вы используете.NET: он позволяет встраивать скрипты SQL в библиотеку или приложение (в виде встроенных ресурсов), а затем сравнивать сравниваемые встроенные скрипты с текущим состоянием в базе данных. Изменения, не связанные с таблицей (те, которые не являются "деструктивными изменениями" по определение Мартина Фаулера), могут применяться автоматически или по запросу (например, создание и удаление объектов, таких как представления, функции, хранимые процедуры, типы, индексы) и сценарии изменения (которые необходимо записать вручную) могут быть применены в том же процессе; также создаются новые таблицы, а также их установочные данные. После обновления схема БД снова сравнивается с сценариями, чтобы обеспечить успешное обновление БД до того, как изменения будут совершены.

Обратите внимание, что весь код сценариев и сравнения работает без SMO, так что у вас нет болезненной зависимости SMO при использовании модуля bsn ModuleStore в приложениях.

В зависимости от того, как вы хотите получить доступ к базе данных, набор инструментов предлагает еще больше - он реализует некоторые возможности ORM и предлагает очень хороший и полезный интерфейсный подход для вызова хранимых процедур, включая прозрачную поддержку XML с собственным.NET XML классов, а также для TVP (Table-Valued Parameters) как IEnumerable

Вот мой script для отслеживания хранимых proc и udf и триггеров в таблице.

Создайте таблицу для хранения существующего исходного исходного кода proc

Ввести таблицу со всеми существующими данными триггера и script

Создайте триггер DDL для отслеживания изменений на них

/****** Object: Table . Script Date: 9/17/2014 11:36:54 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE . ( IDENTITY(1, 1) NOT NULL , (1000) NULL , (1000) NULL , (1000) NULL , (1000) NULL , NULL , NTEXT NULL ,CONSTRAINT PRIMARY KEY CLUSTERED ( ASC) WITH (PAD_INDEX = OFF ,STATISTICS_NORECOMPUTE = OFF ,IGNORE_DUP_KEY = OFF ,ALLOW_ROW_LOCKS = ON ,ALLOW_PAGE_LOCKS = ON) ON ) ON GO ALTER TABLE . ADD CONSTRAINT DEFAULT("") FOR GO INSERT INTO . ( , , , , ,) SELECT "sa" ,"loginitialdata" ,r.ROUTINE_NAME ,r.ROUTINE_TYPE ,GETDATE() ,r.ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES r UNION SELECT "sa" ,"loginitialdata" ,v.TABLE_NAME ,"view" ,GETDATE() ,v.VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS v UNION SELECT "sa" ,"loginitialdata" ,o.NAME ,"trigger" ,GETDATE() ,m.DEFINITION FROM sys.objects o JOIN sys.sql_modules m ON o.object_id = m.object_id WHERE o.type = "TR" GO CREATE TRIGGER ON DATABASE FOR CREATE_PROCEDURE ,ALTER_PROCEDURE ,DROP_PROCEDURE ,CREATE_INDEX ,ALTER_INDEX ,DROP_INDEX ,CREATE_TRIGGER ,ALTER_TRIGGER ,DROP_TRIGGER ,ALTER_TABLE ,ALTER_VIEW ,CREATE_VIEW ,DROP_VIEW AS BEGIN SET NOCOUNT ON DECLARE @data XML SET @data = Eventdata() INSERT INTO sysupdatelog VALUES (@data.value("(/EVENT_INSTANCE/LoginName)", "nvarchar(255)") ,@data.value("(/EVENT_INSTANCE/EventType)", "nvarchar(255)") ,@data.value("(/EVENT_INSTANCE/ObjectName)", "nvarchar(255)") ,@data.value("(/EVENT_INSTANCE/ObjectType)", "nvarchar(255)") ,getdate() ,@data.value("(/EVENT_INSTANCE/TSQLCommand/CommandText)", "nvarchar(max)")) SET NOCOUNT OFF END GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO ENABLE TRIGGER ON DATABASE GO

«» — это единая универсальная среда для доступа, настройки и администрирования всех компонентов MS SQL Server, а также для разработки компонентов системы, редактирования текстов запросов, создания скриптов и пр. Благодаря наличию большого количества визуальных средств управления, «Среда SQL Server Management Studio » позволяет выполнять множество типовых операций по администрированию MS SQL Server администраторам с любым уровнем знаний SQL Server. Удобная среда разработки, встроенный веб-браузер для быстрого обращения к библиотеке MSDN или получения справки в сети, подробный учебник, облегчающий освоение многих новых возможностей, встроенная справка от сообществ в Интернете и многое другое позволяют максимально облегчить процесс разработки в среде SQL Server, а также дает богатые возможности для создания различных сценариев SQL Server. Об установке и запуске программы «Среда SQL Server Management Studio » и пойдет речь в данной статье.

1. Установка программы «Среда SQL Server Management Studio»

Программа «Среда SQL Server Management Studio » поставляется вместе с дистрибутивом MS SQL Server. Для ее установки необходимо отметить компоненты

Средства управления - основные (Management Tools - Basic)
- Средства управления - полный набор (Management Tools - Complete)

на странице выбора компонент программы установки MS SQL Server.

Подробно про установку компонент MS SQL Server я писал в статье .

2. Запуск программы «Среда SQL Server Management Studio»

По умолчанию файлы программы «SQL Server Management Studio » устанавливаются в «C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\Management Studio\». Для запуска «SQL Server Management Studio » следует запустить исходный файл Ssms.exe , находящийся в данной директории.

Также в Microsoft Windows Server 2012 (R2) ярлык для запуска «SQL Server Management Studio » можно найти в списке всех программ.

А в Microsoft Windows Server 2008 (R2) в меню «Пуск » (Start) — «Microsoft SQL Server 2012 » — «Среда SQL Server Management Studio ».

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

<АдресСервера > — имя или IP адрес сервера для экземпляра по умолчанию
<АдресСервера >\<ИмяЭкземпляра > — для именованного экземпляра SQL Server
<ИмяПсевдонима > — если применяются SQL Server

В случае проверки подлинности Windows (Windows Authentication), используются данные текущей учетной записи Windows. Если необходимо использовать учетные данные другого пользователя, то необходимо и программу запустить . В случае использования проверки подлинности SQL Server (SQL Server Authentication), необходимо ввести имя пользователя и пароль существующего пользователя SQL Server.

Введя имя экземпляра сервера и данные для авторизация необходимо нажать «Соединить » (Connect) для подключения к выбранному SQL серверу.

Автор: Майк Вайнер (Mike Weiner)
Соавтор: Бурцин Пэйтел (Burzin Patel)
Редакторы: Любор Коллар (Lubor Kollar), Кевин Кокс (Kevin Cox), Билл Эммерт (Bill Emmert), Грег Хузмайер (Greg Husemeier), Пол Бурпо (Paul Burpo), Джозеф Сак (Joseph Sack), Дэнни Ли (Denny Lee), Санджай Мишра (Sanjay Mishra), Линдси Аллен (Lindsey Allen), Марк Суза (Mark Souza)

Microsoft SQL Server 2008 содержит ряд улучшений и новых функциональных возможностей, расширяющих функциональность предыдущих версий. Администрирование и обслуживание баз данных, поддержание управляемости, доступности, безопасности и производительности - все это входит в обязанности администратора базы данных. В этой статье описаны десять самых полезных новых функций SQL Server 2008 (в алфавитном порядке), облегчающих работу администратора БД. Помимо краткого описания, для каждой из функций приведены возможные ситуации ее применения и важные рекомендации по использованию.

Монитор активности

При устранении проблем, связанных с производительностью, или отслеживании работы сервера в реальном времени администратор обычно запускает ряд скриптов или проверяет соответствующие источники сведений, чтобы собрать общие данные о выполняющихся процессах и выявить причину проблемы. Монитор активности SQL Server 2008 объединяет такие сведения, предоставляя наглядную информацию по выполняющимся и недавно выполнявшимся процессам. Администратор БД может как просматривать высокоуровневые сведения, так и проанализировать любой из процессов более детально и ознакомиться со статистикой ожидания, что облегчает выявление и разрешение проблем.

Чтобы открыть монитор активности, щелкните правой кнопкой мыши на имени зарегистрированного сервера в обозревателе объектов, затем выберите Монитор активности или же воспользуйтесь стандартным значком на панели инструментов в среде SQL Server Management Studio. Монитор активности предлагает администратору раздел обзора, внешне похожий на Диспетчер задач Windows, а также компоненты детального просмотра отдельных процессов, ожидания ресурсов, ввода-вывода в файлы данных и последних ресурсоемких запросов, как показано на рис. 1.

Рис. 1: Вид представления Монитора активности SQL Server 2008 в среде Management Studio

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

С помощью монитора активности администратор также может выполнять следующие задачи:

· Приостанавливать и возобновлять работу монитора активности одним щелчком правой кнопки мыши. Это позволяет администратору «сохранить» сведения о состоянии на определенный момент времени, они не будут обновлены или перезаписаны. Но не забывайте, что при обновлении данных вручную, развертывании или сворачивании раздела старые данные будут обновлены и утеряны.

· Щелкните правой кнопкой мыши элемент строки, чтобы отобразить полный текст запроса или графический план выполнения с помощью пункта меню «Последние ресурсоемкие запросы».

· Выполнять трассировку приложением Profiler или завершать процессы в представлении «Процессы». События приложения Profiler включают события RPC :Completed , SQL :BatchStarting и SQL :BatchCompleted , а также Audit Login и Audit Logout .

Монитор активности также позволяет отслеживать активность любого локального или удаленного экземпляра SQL Server 2005, зарегистрированного в среде SQL Server Management Studio.

Аудит

Возможность отслеживать происходящие события и регистрировать сведения о них, включая сведения о пользователях, обращающихся к объектам, а также времени и содержании вносимых изменений, помогает администратору обеспечить соблюдение стандартов соответствия нормативным или организационным требованиям по безопасности. Кроме того, понимание происходящих в среде событий также может помочь при разработке плана по снижению рисков и поддержания безопасности в среде.

В SQL Server 2008 (только выпуски Enterprise и Developer) SQL Server Audit реализует автоматизацию, позволяющую администратору и другим пользователям подготавливать, сохранять и просматривать аудиты различных компонентов серверов и баз данных. Функция предусматривает возможность аудита с детализацией уровня сервера или базы данных.

Существуют группы действий аудита на уровне сервера, например, следующие:

· FAILED_LOGIN_GROUP отслеживает неудачные попытки входа в систему.

· BACKUP_RESTORE_GROUP сообщает, когда создавалась резервная копия базы данных или выполнялось ее восстановление.

· DATABASE_CHANGE_GROUP проводит аудит времени создания, изменения или удаления базы данных.

Группы действий аудита на уровне базы данных включают следующие:

· DATABASE_OBJECT_ACCESS_GROUP вызывается при каждом выполнении инструкции CREATE, ALTER или DROP для объекта базы данных.

· DATABASE_OBJECT_PERMISSION_CHANGE_GROUP вызывается при использовании инструкций GRANT, REVOKE или DENY для объектов базы данных.

Существуют и другие действия аудита, например, SELECT, DELETE и EXECUTE. Дополнительные сведения, в том числе полный список всех групп и действий аудита, см. в разделе Группы действий и действия аудита SQL Server .

Результаты аудита можно направить для последующего просмотра в файл или журнал событий (системный журнал или журнал событий безопасности Windows). Данные аудита создаются с применением Расширенных событий - еще одной новой функции SQL Server 2008.

Аудиты SQL Server 2008 позволяют администратору ответить на вопросы, на которые раньше было очень сложно ответить постфактум, например, «Кто удалил этот индекс?», «Когда была изменена хранимая процедура?», «Какое внесенное изменение может мешать пользователю получить доступ к этой таблице?» и даже «Кто выполнил инструкцию SELECT или UPDATE для таблицы [ dbo .Payroll ] ?».

Дополнительные сведения об использовании аудита SQL Server и примеры его реализации см. в разделе Руководство по обеспечению соответствия требованиям в SQL Server 2008 .

Сжатие резервных копий

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

При использовании сжатия резервных копий SQL Server 2008 файл резервной копии сжимается по мере его записи, благодаря чему требуется не только меньше дискового пространства, но и меньше операций ввода-вывода, а резервное копирование занимает меньше времени. В ходе лабораторных испытаний с реальными пользовательскими данными во многих случаях наблюдалось уменьшение размера файла резервной копии на 70-85%. Кроме того, испытания показали, что длительность операций копирования и восстановления сократилась примерно на 45%. Следует отметить, что дополнительная обработка при сжатии увеличивает загрузку процессоров. Чтобы отделить во времени ресурсоемкий процесс копирования от других процессов и минимизировать его влияние на их работу, можно воспользоваться другой описанной в этом документе функцией - Resource Governor .

Сжатие включается путем добавления предложения WITH COMPRESSION в команду BACKUP (дополнительные сведения см. в разделе Электронная документация по SQL Server) или установкой этого параметра на странице Параметры диалогового окна Резервное копирование базы данных . Чтобы не требовалось вносить изменения во все существующие сценарии резервного копирования, реализован глобальный параметр, включающий сжатие всех создаваемых на экземпляре сервера резервных копий по умолчанию. (Этот параметр доступен на странице Настройки базы данных диалогового окна Свойства сервера ; его также можно установить, выполнив хранимую процедуру sp _ configure со значением параметра backup compression default , равным 1). Команда создания резервной копии требует явного задания параметра сжатия, а команда восстановления автоматически распознает сжатую резервную копию и распаковывает ее при восстановлении.

Сжатие резервных копий - исключительно полезная функция, сберегающая дисковое пространство и время. Дополнительные сведения о настройке сжатия резервных копий см. в техническом примечанииНастройка производительности сжатия резервных копий в SQL Server 2008 . Примечание. Создание сжатых резервных копий поддерживается только в выпусках SQL Server 2008 Enterprise и Developer , однако все выпуски SQL Server 2008 позволяют восстанавливать сжатые резервные копии.

Серверы централизованного управления

Часто администратор БД управляет сразу многими экземплярами SQL Server. Возможность централизации управления и администрирования многими экземплярами SQL в единой точке позволяет экономить существенные усилия и время. Реализация серверов централизованного управления, доступная в среде SQL Server Management Studio посредством компонента «Зарегистрированные серверы», позволяет администратору выполнять различные административные операции над многими серверами SQL Servers из единой консоли управления.

Серверы централизованного управления позволяют администратору зарегистрировать группу серверов и выполнять над ними, как над единой группой, например, следующие операции:

· Многосерверное выполнение запросов: теперь из одного источника можно выполнить скрипт на многих серверах SQL Server, данные будут возвращены этому источнику, причем раздельно выполнять вход в каждый из серверов не требуется. Это может быть особенно полезным в случае, когда необходимо просмотреть или сравнить данные с нескольких серверов SQL Server, не выполняя распределенный запрос. Кроме того, при условии поддержки синтаксиса запроса предыдущими версиями SQL Server, запускаемый из редактора запросов SQL Server 2008 запрос может выполняться и на экземплярах SQL Server 2005 и SQL Server 2000. Дополнительные сведения см. в блоге рабочей группы по управляемости SQL Server в разделе Выполнение многосерверных запросов в среде SQL Server 2008 .

· Импорт и определение политик на многих серверах: в рамках функциональности Управления на основе политик (еще одной новой функции SQL Server 2008, также описанной в этой статье), SQL Server 2008 обеспечивает возможность импорта файлов политик в отдельные группы серверов централизованного управления и позволяет определять политики на всех серверах, зарегистрированных в определенной группе.

· Управление службами и вызов диспетчера конфигурации SQL Server: инструмент «серверы централизованного управления» помогаает создать центр управления, в котором администратор БД может просмотреть и даже изменить (при наличии соответствующих разрешений) состояние служб.

· Импорт и экспорт зарегистрированных серверов: серверы, зарегистрированные в серверах централизованного управления (Central Management Servers), могут экспортироваться и импортироваться при их передаче между администраторами или различными установленными экземплярами SQL Server Management Studio. Эта возможность служит альтернативой импорту или экспорту администратором его собственных локальных групп в SQL Server Management Studio.

Не забывайте, что разрешения применяются с помощью проверки подлинности Windows, поэтому права и разрешения пользователей могут различаться на различных серверах, зарегистрированных в группе сервера централизованного управления. Дополнительные сведения см. в разделе Администрирование нескольких серверов с помощью серверов централизованного управления и в блоге Кимберли Трипп (Kimberly Tripp): Центральные серверы управления SQL Server 2008 - знакомы ли вы с ними?

Сборщик данных и хранилище данных управления

Настройка производительности и диагностика занимают много времени и могут требовать профессиональных навыков работы с SQL Server, а также понимания внутренней структуры баз данных. Системный монитор Windows (Perfmon), профилировщик SQL Server Profiler и динамические административные представления решали часть этих задач, но они нередко оказывали влияние на работу сервера, были трудоемки в применении или задействовали методы сбора разрозненных данных, затрудняющие их последующее объединение и интерпретацию.

Чтобы предоставить понятные сведения о производительности системы, позволяющие предпринять конкретные меры, в SQL Server 2008 реализовано полностью расширяемое средство сбора и хранения данных о производительности - сборщик данных. Оно содержит несколько непосредственно готовых к работе агентов сбора данных, централизованное хранилище данных о производительности, так называемое хранилище данных управления, и несколько подготовленных заранее отчетов для представления собранных данных. Сборщик данных - это масштабируемое средство, обеспечивающее сбор и объединение данных из различных источников, таких как динамические административные представления, монитор производительности Perfmon и запросы Transact-SQL, в соответствии с полностью настраиваемой частотой сбора данных. Сборщик данных можно расширить, реализовав сбор данных по любому измеряемому атрибуту приложения.

Еще одна полезная функция хранилища данных управления - это возможность его установки на любом сервере SQL Server с последующим сбором данных с одного или нескольких экземпляров SQL Server. При этом минимизируется влияние на производительность рабочих систем, а также улучшается масштабируемость в контексте отслеживания и сбора данных со многих серверов. При лабораторных испытаниях наблюдаемая потеря пропускной способности при выполнении агентов и работе хранилища данных управления на нагруженном сервере (с применением рабочей нагрузки OLTP) составила приблизительно 4%. Потеря производительности может изменяться в зависимости от периодичности сбора данных (упомянутое испытание велось при расширенной рабочей нагрузке, с передачей данных в хранилище каждые 15 минут), она также может резко увеличиваться во время периодов сбора данных. В любом случае следует ожидать некоторого уменьшения доступных ресурсов, так как процессDCExec.exe использует определенный объем памяти и ресурсы ЦП, а запись в хранилище данных управления повысит нагрузку на подсистему ввода-вывода и потребует выделения пространства в месте расположения файлов данных и журнала.На диаграмме (рис. 2) показан типичный отчет сборщика данных.

Рис. 2: Вид отчета сборщика данных SQL Server 2008

В отчете показана деятельность SQL Server за время периода сбора данных. В нем собраны и отражены такие события, как ожидания, использование ЦП, ввода-вывода и памяти, а также статистика по ресурсоемким запросам. Администратор может также перейти к детальному рассмотрению элементов отчетов, сконцентрировавшись на отдельном запросе или операции, чтобы исследовать, определить и устранить проблемы, связанные с производительностью. Эти возможности сбора данных, их хранения и создания отчетов позволяют реализовать упреждающее отслеживание состояния серверов SQLServer в среде. При необходимости они позволяют возвращаться к историческим данным, чтобы понять и оценить изменения, повлиявшие на производительность за отслеживаемый период. Сборщик данных и хранилище данных управления поддерживаются во всех выпусках SQLServer 2008, кроме SQLServerExpress.

Сжатие данных

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

Сжатие данных, представленное в SQL Server 2008, помогает разрешить эти проблемы. Эта функция позволяет администратору избирательно сжимать любые таблицы, секции таблиц или индексы, благодаря чему уменьшается занимаемое пространство на диске и в памяти, а также размер операций ввода-вывода. Сжатие и распаковка данных нагружают процессор; тем не менее, во многих случаях дополнительная нагрузка на процессор более чем компенсируется выигрышем в объемах ввода-вывода. В конфигурациях, в которых ввод-вывод является «узким местом», сжатие данных также может обеспечить рост производительности.

В некоторых лабораторных испытаниях включение сжатия данных обеспечивало экономию 50-80% дискового пространства. Экономия пространства значительно различалась: если в данных содержалось мало повторяющихся значений, или значения использовали все выделяемые для указанного типа данных байты, экономия была минимальной. При этом производительность многих рабочих нагрузок не увеличивалась. Однако при работе с данными, содержащими много числовых данных и много повторяющихся значений, отмечались значительная экономия дискового пространства и рост производительности, составляющий от нескольких процентов до 40-60% для некоторых образцов рабочих нагрузок запросов.

SQLServer 2008 поддерживает два типа сжатия: сжатие строк , при котором сжимаются отдельные столбцы таблицы, и сжатие страниц , при котором страницы данных сжимаются с помощью сжатия строк, префиксов и словарного сжатия. Достигаемая степень сжатия сильно зависит от типов данных и содержимого базы данных. В общем, при использовании сжатия строк уменьшается дополнительная нагрузка на операции приложений, однако уменьшается и степень сжатия, то есть выигрывается меньше места. В то же время сжатие страниц приводит к большей дополнительной нагрузке на приложение и загрузке процессора, но и экономит значительно больше пространства. Сжатие страниц является надмножеством сжатия строк, то есть если объект или секция объекта сжимаются с помощью сжатия страниц, к ним также применяется и сжатие строк. Кроме того, SQLServer 2008 поддерживает формат хранения vardecimal из SQL Server 2005 с пакетом обновления 2 (SP2). Следует учитывать, что, поскольку этот формат является подмножеством сжатия строк, он считается устаревшим и будет исключен из будущих версий продукта.

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

Сжатие данных можно провести с помощью инструкций Transact-SQL или мастера сжатия данных. Чтобы определить возможное изменение размера объекта при его сжатии, можно воспользоваться системной хранимой процедуройsp _estimate _data _compression _savings или мастером сжатия данных. Сжатие базы данных поддерживается только в выпусках SQLServer 2008 Enterprise и Developer. Оно реализуется исключительно в самих базах данных и не требует внесения каких-либо изменений в приложения.

Дополнительные сведения об использовании сжатия см. в документе Создание сжатых таблиц и индексов .

Управление на основе политик

Во многих бизнес-сценариях необходимо поддерживать определенные конфигурации или соблюдать политики либо на определенном сервере SQLServer, либо многократно, в группе серверов SQLServer. Администратор или организация могут потребовать применения особой схемы именования всех создаваемых пользовательских таблиц или хранимых процедур или требовать определенные изменения применялись к конфигурации на многих серверах.

Управление на основе политик (PBM) предоставляет администратору широкий набор возможностей по управлению средой. Политики можно создавать и выполнять проверку на соответствие им. Если цель проверки (например, ядро базы данных, база данных, таблица или индекс SQLServer) не соответствует требованиям, администратор может автоматически перенастроить ее в соответствии с этими требованиями. Также существует ряд режимов определения политик (многие из которых автоматизированы), упрощающих проверку соответствия требованиям политик, регистрацию в журнале нарушений политики и отправку уведомлений, и даже выполняющих откат изменений для обеспечения соответствия требованиям политики. Дополнительные сведения о режимах определения и об их сопоставлении с аспектами (понятием управления на основе политик (PBM), также обсуждаемым в этом блоге) см. в разделе Блог об управлении SQL Server на основе политик .

Политики можно экспортировать и импортировать в виде XML-файлов для их определения и применения на многих экземплярах серверов. Кроме того, в среде SQLServerManagement Studio и в представлении зарегистрированных серверов политики можно определять на многих серверах, зарегистрированных в локальной группе серверов или в группе сервера централизованного управления.

В предыдущих версиях SQL Server может быть реализована не вся функциональность управления на основе политик. Тем не менее, функцию составление отчетов политики можно использовать на серверах SQL Server 2005 и SQL Server 2000. Дополнительные сведения об использовании управления на основе политик см. в разделе Администрирование серверов с помощью управления на основе политик в электронной документации по SQLServer. Дополнительные сведения о самой технологии политик с примерами см. в разделе Руководство по обеспечению соответствия в SQL Server 2008 .

Прогнозируемая производительность и параллелизм

Многие администраторы сталкиваются со значительными трудностями при поддержке серверов SQLServers с постоянно изменяющимися рабочими нагрузками и обеспечении предсказуемого уровня производительности (или минимизации расхождений в планах запросов и производительности). Неожиданные изменения производительности при выполнении запросов, изменения планов запросов и/или общие связанные с производительностью проблемы могут быть вызваны рядом причин, в том числе повышением нагрузки от выполняющихся на сервере SQLServer приложений или обновлением версии самой базы данных. Предсказуемость выполняемых на сервере SQLServer запросов и операций значительно облегчает достижение и поддержание целей по уровню доступности, производительности и/или непрерывности бизнес-деятельности (выполнение соглашений об уровне обслуживания и уровне операционной поддержки).

В SQLServer 2008 некоторые функции изменены для увеличения прогнозируемости производительности. Так, внесены определенные изменения в структуры планов SQLServer 2005 (закрепление планов ) и добавлена возможность управления эскалацией блокировок на уровне таблиц. Оба улучшения способствуют более прогнозируемому и упорядоченному взаимодействию между приложением и базой данных.

Во-первых, структуры планов (Plan Guide):

В SQL Server 2005 было реализовано улучшение стабильности и предсказуемости запросов с помощью новой на тот момент функции - «структур планов», содержавших указания для выполнения запросов, которые нельзя было изменить непосредственно в приложении. Дополнительные сведения см. в техническом документе Принудительное использование планов запросов . Хотя подсказка в запросе USE PLAN является очень мощной функцией, она поддерживала только операции SELECT DML и часто была неудобна в применении из-за чувствительности структур планов к форматированию.

В SQL Server 2008 механизм структур планов расширен в двух направлениях: во-первых, расширена поддержка подсказки в запросе USE PLAN, которая теперь совместима со всеми инструкциями DML (INSERT, UPDATE, DELETE, MERGE); во-вторых, введена новая функция закрепления планов , позволяющая непосредственно создавать структуру плана (закрепление) любого плана запроса, существующего в кэше планов SQL Server, как показано в следующем примере.

sp_create_plan_guide_from_handle
@name = N’MyQueryPlan’,
@plan_handle = @plan_handle,
@statement_start_offset = @offset;

У структуры планов, созданной любым способом, имеется область базы данных; она хранится в таблице sys.plan_guides . Структуры планов лишь влияют на процесс выбора плана запроса оптимизатором, но не избавляют от необходимости компиляции запроса. Также добавлена функция sys.fn_validate_plan_guide , для проверки существующих структур планов SQL Server 2005 и обеспечения их совместимости с SQL Server 2008. Закрепление планов доступно в выпусках SQL Server 2008 Standard, Enterprise и Developer.

Во-вторых, эскалация блокировок:

Эскалация блокировки часто вызывало проблемы блокировки, а иногда даже взаимоблокировку. Устранять эти проблемы приходилось администратору. В предыдущих версиях SQLServer можно было управлять эскалацией блокировок (флаги трассировки 1211 и 1224), но это было возможно только для детализации на уровне экземпляра. Для одних приложений это устраняло проблему, а для других вызывало еще большие проблемы. Другим недостатком алгоритма эскалации блокировок в SQL Server 2005 было то, что блокировки секционированных таблиц укрупнялись напрямую до уровня таблиц, а не до уровня секций.

SQLServer 2008 предлагает решение обеих проблем. В нем реализован новый параметр, позволяющий управлять эскалацией блокировки на уровне таблиц. С помощью команды ALTERTABLE можно выбрать отключение эскалации или эскалацию до уровня секций для секционированных таблиц. Обе эти возможности улучшают масштабируемость и производительность без нежелательных побочных эффектов, затрагивающих другие объекты в экземпляре. Эскалация блокировки задается на уровне объекта базы данных и не требует внесения каких-либо изменений в приложения. Оно поддерживается во всех выпусках SQLServer 2008.

Resource Governor

Поддерживать устойчивый уровень обслуживания, предотвращая бесконтрольные запросы и обеспечивая выделение ресурсов для важнейших рабочих загрузок, раньше было сложно. Отсутствовала возможность гарантировать выделение определенного объема ресурсов набору запросов, отсутствовало управление приоритетами доступа. У всех запросов были равные права на доступ ко всем доступным ресурсам.

Новая функция SQL Server 2008 - «Регулятор ресурсов»(Resource Governor) - помогает справиться с этой проблемой, давая возможность дифференцировать рабочие нагрузки и распределять ресурсы в соответствии с потребностями пользователей. Ограничения регулятора ресурсов легко перенастраиваются в реальном времени при минимальном воздействии на выполняющиеся рабочие нагрузки. Распределение рабочих нагрузок по пулу ресурсов настраивается на уровне соединения, причем этот процесс полностью прозрачен для приложений.

На диаграмме ниже показан процесс выделения ресурсов. В данном сценарии настраиваются три пула рабочих нагрузок (рабочие нагрузки Admin, OLTP и Report), после чего пулу рабочих нагрузок OLTP присваивается высший приоритет. В то же время настраиваются два пула ресурсов (пул Pool и пул Application) с заданными ограничениями по объему памяти и времени процессора (ЦП). На последнем этапе рабочая нагрузка Admin назначается пулу Admin, а рабочие нагрузки OLTP и Report назначаются пулу Application.

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

— Регулятор ресурсов использует учетные данные входа, имя узла или имя приложения в качестве «идентификатора пула ресурсов», поэтому использование для приложения одного имени входа при определенных количествах клиентов, приходящихся на один сервер, может усложнить создание пулов.

— Не поддерживается группировка объектов на уровне базы данных, при которой доступ к ресурсам регулируется на основе объектов базы данных, к которым осуществляется доступ.

— Настраивать можно только использование ресурсов процессора и памяти. Управление ресурсами ввода-вывода не реализовано.

— Динамическое переключение рабочих нагрузок между пулами ресурсов после соединения невозможно.

— Регулятор ресурсов поддерживается только в выпусках SQL Server 2008 Enterprise и Developer и может использоваться только для ядра базы данных SQL Server; управление службами SQL Server Analysis Services (SSAS), SQL ServerIntegration Services (SSIS) и SQL Server Reporting Services (SSRS) не поддерживается.

Прозрачное шифрование данных (TDE)

Многие организации уделяют очень большое внимание вопросам безопасности. Существует множество различных слоев, защищающих один из самых ценных активов организации - ее данные.Чаще всего организации успешно защищают используемые данные с помощью мер физической защиты, брандмауэров и строгих политик ограничения доступа. Однако, в случае утери физического носителя с данными, например, диска или ленты с резервной копией, все перечисленные меры обеспечения безопасности оказываются бесполезными, так как злоумышленник может просто восстановить базу данных и получить полный доступ к данным. SQL

Server 2008 предлагает решение этой проблемы путем прозрачного шифрования данных (TDE). При шифровании TDE данные в операциях ввода-вывода шифруются и дешифруются в реальном времени; файлы данных и журналов шифруются с помощью ключа шифрования базы данных (DEK). DEK - это симметричный ключ, защищаемый сертификатом, который хранится в базе данных >master сервера, или асимметричный ключ, защищаемый модулем расширенного управления ключами (EKM).

Функция TDE защищает «неактивные» данные, поэтому данные в файлах MDF, NDF, и LDF невозможно просматривать с помощью редактора шестнадцатеричных данных или каким-либо другим способом. Однако активные данные, например, результаты выполнения инструкции SELECT в среде SQL Server Management Studio, останутся видимыми для пользователей, у которых имеются права на просмотр таблицы. Кроме того, поскольку функция TDE реализована на уровне базы данных, база данных может задействовать индексы и ключи для оптимизации запросов. TDE не следует путать с шифрованием на уровне столбцов - это отдельная функция, позволяющая шифровать даже активные данные.

Шифрование базы данных - одноразовый процесс, который можно запустить командой Transact — SQL или из среды SQL Server Management Studio , после чего он выполняется в фоновом потоке. Состояние шифрования или дешифровки можно отслеживать с помощью динамического административного представления sys.dm_database_encryption_keys . Во время проведенных лабораторных испытаний шифрование базы данных размером 100 Гб с применением алгоритма шифрования AES _128 заняло около часа. Хотя накладные расходы при использовании TDE определяются в основном рабочей нагрузкой приложения, в некоторых из проведенных испытаний эти дополнительные расходы составили менее 5%. Следует учитывать одну особенность, которая может повлиять на производительность: если TDE используется в любой из баз данных на экземпляре, то также шифруется и системная база данных tempDB . Наконец, при одновременном использовании различных функций необходимо учитывать следующее:

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

TDE позволяет организации обеспечить соответствие требованиям нормативных стандартов и общему уровню защиты данных. TDE поддерживается только в выпусках SQL Server 2008 Enterprise и Developer ; его активация не требует внесения изменений в существующие приложения. Дополнительные сведения см. в разделе Шифрование данных в выпуске SQL Server 2008 Enterprise или в обсуждении в Прозрачное шифрование данных .

Подводя итог, можно сказать, что в SQL Server 2008 предлагаются функции, усовершенствования и возможности, облегчающие работу администратора базы данных. 10 самых популярных из них описаны здесь, но в SQL Server 2008 есть и много других возможностей, упрощающих жизнь администраторов и других пользователей. Списки «10 лучших функций» по другим направлениям работы с SQL Server можно найти в других статьях «Лучшие 10 … в SQL Server 2008» на этом сайте. Полный список функций и их подробное описание см. в электронной документации по SQL Server и на обзорном веб-сайте SQL Server 2008 .