Триколор 

Использование Management Studio с Database Engine

Моя компания только что прошла свой ежегодный процесс обзора, и я, наконец, убедил их, что настало время найти лучшее решение для управления нашей схемой/сценами 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

Среда SQL Server Management Studio имеет два основных назначения: администрирование серверов баз данных и управление объектами баз данных. Эти функции рассматриваются далее.

Администрирование серверов баз данных

Задачи администрирования, которые можно выполнять с помощью среды SQL Server Management Studio, включают, среди прочих, следующие:

    регистрация серверов;

    подключение к серверу;

    создание новых групп серверов;

    управление множественными серверами;

    пуск и остановка серверов.

Эти задачи администрирования описываются в следующих подразделах.

Регистрация серверов

Среда SQL Server Management Studio отделяет деятельность по регистрации серверов от деятельности по исследованию баз данных и их объектов. (Действия этих обоих типов можно выполнять посредством обозревателя объектов.) Прежде чем можно использовать базы данных и объекты любого сервера, будь то локального или удаленного, его нужно зарегистрировать.

Сервер можно зарегистрировать при первом запуске среды SQL Server Management Studio или позже. Чтобы зарегистрировать сервер базы данных, щелкните правой кнопкой требуемый сервер в обозревателе объектов и в контекстном меню выберите пункт Register. Если панель обозревателя объектов скрыта, то откройте ее, выполнив команду меню View --> Object Explorer. Откроется диалоговое окно New Server Registration (Регистрация нового сервера), как это показано на рисунке ниже:

Выберите имя сервера, который нужно зарегистрировать, и тип проверки подлинности для этого сервера (т.е. проверка подлинности Windows или проверка подлинности SQL Server), после чего нажмите кнопку Save.

Подключение к серверу

Среда SQL Server Management Studio также разделяет задачи регистрации сервера и подключения к серверу. Это означает, что при регистрации сервера автоматического подключения этого сервера не происходит. Чтобы подключиться к зарегистрированному серверу, нужно щелкнуть правой кнопкой требуемый сервер в окне инспектора объектов и в появившемся контекстном меню выбрать пункт Connect (Подключиться).

Создание новой группы серверов

Чтобы создать новую группу серверов в панели зарегистрированных серверов, щелкните правой кнопкой узел Local Server Groups (Группы локальных серверов) в окне Registered Server и в контекстном меню выберите пункт New Server Group (Создание группы серверов). В открывшемся диалоговом окне New Server Group Properties (Свойства новой группа серверов) введите однозначное имя группы и, по выбору, ее описание.

Управление множественными серверами

Посредством обозревателя объектов среда SQL Server Management Studio позволяет администрировать множественные серверы баз данных (называемые экземплярами) на одном компьютере. Каждый экземпляр компонента Database Server имеет свой собственный набор объектов баз данных (системные и пользовательские базы данных), который не разделяется между экземплярами.

Для управления сервером и его конфигурацией щелкните правой кнопкой имя сервера в обозревателе объектов и в появившемся контекстном меню выберите пункт Properties (Свойства). Откроется диалоговое окно Server Properties (Свойства сервера), содержащее несколько страниц, таких как General (Общие), Security (Безопасность), Permissions (Разрешения) и т.п.

На странице General отображаются общие свойства сервера:

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

Можно изменить имя сервера, присвоив ему новое имя. Для этого щелкните правой кнопкой требуемый сервер в окне обозревателя объектов и в контекстном меню выберите пункт Register. Теперь можно присвоить серверу новое имя и изменить его описание. Серверы не следует переименовывать без особой на это надобности, поскольку это может повлиять на другие серверы, которые ссылаются на них.

Запуск и останов серверов

Сервер Database Engine по умолчанию запускается автоматически при запуске операционной системы Windows. Чтобы запустить сервер с помощью среды SQL Server Management Studio, щелкните правой кнопкой требуемый сервер в инспекторе объектов и в контекстном меню выберите пункт Start (Запустить). Это меню также содержит пункты Stop (Остановить) и Pause (Приостановить) для выполнения соответствующих действий с сервером.

Управление базами данных посредством обозревателя объектов Object Explorer

Задачи администрирования, которые можно выполнять с помощью среды SQL Server Management Studio, включают:

    создание баз данных, не прибегая к использованию языка Transact-SQL;

    модифицирование баз данных, не прибегая к использованию языка Transact-SQL;

    управление таблицами, не прибегая к использованию языка Transact-SQL;

    создание и исполнение инструкций SQL (описывается в следующей статье).

Создание баз данных без использования T-SQL

Новую базу данных можно создать посредством обозревателя объектов Object Explorer . Как можно судить по его названию, обозреватель объектов также можно использовать для исследования объектов сервера. С панели этого инструмента можно просматривать все объекты сервера и управлять сервером и базами данных. Дерево иерархии объектов сервера содержит, среди прочих папок, папку Databases (Базы данных). Эта папка, в свою очередь, содержит несколько подпапок, включая папку для системных баз данных, и по папке для каждой базы данных, созданной пользователем.

Чтобы создать базу данных посредством обозревателя объектов, щелкните правой кнопкой узел Databases и выберите пункт меню New Database (Создать базу данных). В открывшемся диалоговом окне New Database в поле Database name введите имя новой базы данных, после чего нажмите кнопку ОК.

Каждая база данных обладает несколькими свойствами, такими как тип файла, начальный размер и т.п. Список страниц свойств базы данных расположен в левой панели диалогового окна New Database. Страница General (Общие) диалогового окна Database Properties содержит, среди прочего, такую информацию, как имя, владелец и параметры сортировки базы данных:

Свойства файлов данных определенной базы данных перечисляются на странице Files (Файлы) и содержат такую информацию, как имя и начальный размер файла, расположение базы данных, а также тип файла (например, primary). База данных может храниться в нескольких файлах. В SQL Server применяется динамическое управление дисковым пространством. Это означает, что можно сконфигурировать размер базы данных для автоматического увеличения и уменьшения по мере надобности.

Чтобы изменить свойство Autogrowth (Автоувеличение) на странице Files, в столбце Autogrowth нажмите значок троеточия (...) и внесите соответствующие изменения в диалоговом окне Change Autogrowth. Чтобы позволить автоматическое увеличение размера базы данных, нужно установить флажок Enable Autogrowth . Каждый раз, когда существующий размер файла недостаточен для хранения добавляемых данных, сервер автоматически запрашивает систему выделить файлу дополнительное дисковое пространство. Объем дополнительного дискового пространства (в процентах или мегабайтах) указывается в поле File Growth (Увеличение размера файла) в том же диалоговом окне. А в разделе Maximum File Size (Максимальный размер файла) можно или ограничить максимальный размер файла, установив переключатель Limited to (MB) (Ограничение (Мбайт)), или снять ограничения на размер, установив переключатель Unlimited (Без ограничений) (это настройка по умолчанию). При ограниченном размере файла нужно указать его допустимый максимальный размер.

На странице Filegroups (Файловые группы) диалогового окна Database Properties отображаются имена файловых групп, к которым принадлежит файл базы данных, раздел файловой группы (по умолчанию или заданный явно), а также операции, разрешенные для выполнения с файловой группой (чтение и запись или только чтение).

На странице Options (Параметры) диалогового окна Database Properties можно просмотреть и модифицировать все параметры уровня базы данных. Существуют следующие группы параметров: Automatic (Автоматически), Containment (Включение), Cursor (Курсор), Miscellaneous (Вспомогательные), Recovery (Восстановление), Service Broker (Компонент Service Broker) и State (Состояние). Группа State содержит, например, следующие четыре параметра:

Database Read-Only (База данных доступна только для чтения)

Позволяет установить доступ к базе данных полный доступ или доступ только для чтения. В последнем случае пользователи не могут модифицировать данные. Значение по умолчанию этого параметра - False.

Restrict Access (Ограничение доступа)

Устанавливает количество пользователей, которые могут одновременно использовать базу данных. Значение по умолчанию - MULTI_USER.

Database State (Состояние базы данных)

Описывает состояние базы данных. Значение по умолчанию этого параметра - Normal.

Encryption Enabled (Шифрование включено)

Определяет режим шифрования базы данных. Значение по умолчанию этого параметра - False.

На странице Extended Properties (Расширенные свойства) отображаются дополнительные свойства текущей базы данных. На этой странице можно удалять существующие свойства и добавлять новые.

На странице Permissions (Разрешения) отображаются все пользователи, роли и соответствующие разрешения.

Остальные страницы Change Tracking (Отслеживание изменений), Mirroring (Зеркальное отображение) и Transaction Log Shipping (Доставка журналов транзакций) описывают возможности, связанные с доступностью данных.

Модифицирование баз данных

С помощью обозревателя объектов можно модифицировать существующие базы данных, изменяя файлы и файловые группы базы данных. Чтобы добавить новые файлы в базу данных, щелкните правой кнопкой требуемую базу данных и в контекстном меню выберите пункт Properties. В открывшемся диалоговом окне Database Properties выберите страницу Files и нажмите кнопку Add, расположенную внизу раздела Database files. В раздел будет добавлена новая строка, в поле Logical Name которой следует ввести имя добавляемого файла базы данных, а в других полях задать необходимые свойства этого файла. Также можно добавить и вторичную файловую группу для базы данных, выбрав страницу Filegroups (Файловые группы) и нажав кнопку Add.

Упомянутые ранее свойства базы данных может модифицировать только системный администратор или владелец базы данных.

Чтобы удалить базы данных с помощью обозревателя объектов, щелкните правой кнопкой имя требуемой базы данных и в открывшемся контекстном меню выберите пункт Delete (Удалить).

Управление таблицами

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

Для практики создания таблиц, в базе данных SampleDb создадим таблицу Department. Чтобы создать таблицу базы данных с помощью обозревателя объектов, разверните в нем узел Databases, а потом узел требуемой базы данных, щелкните правой кнопкой папку Tables и в открывшемся контекстном меню выберите пункт New Table. В верхней части с правой стороны окна средства Management Studio откроется окно для создания столбцов новой таблицы. Введите имена столбцов таблицы, их типы данных и разрешение значений null для каждого столбца, как это показано в правой верхней панели на рисунке ниже:

Чтобы выбрать для столбца один из поддерживаемых системой типов данных, в столбце Data Type (Тип данных) выберите, а затем нажмите направленный вниз треугольник у правого края поля (этот треугольник появляется после того, как будет выбрана ячейка). В результате в открывшемся раскрывающемся списке выберите требуемый тип данных для столбца.

Тип данных существующего столбца можно изменить на вкладке Column Properties (Свойства столбца) (нижняя панель на рисунке). Для одних типов данных, таких как char, требуется указать длину в строке Length, а для других, таких как decimal, на вкладке Column Properties требуется указать масштаб и точность в соответствующих строках Scale (Масштаб) и Precision (Точность). Для некоторых других, таких как int, не требуется указывать ни одно из этих свойств. (Недействительные значения для конкретного типа данных выделены затененным шрифтом в списке всех возможных свойств столбца.)

Чтобы разрешить значения null для данного столбца, следует установить для него соответствующий флажок поля. Также, если для столбца требуется значение по умолчанию, его следует ввести в строку Default Value or Binding (Значение по умолчанию или привязка) панели Column Properties. Значение по умолчанию присваивается ячейке столбца автоматически, если для нее явно не введено значение.

Столбец Number является первичным ключом таблицы Department. Чтобы сделать столбец первичным ключом таблицы, щелкните его правой кнопкой и в контекстном меню выберите пункт Set Primary Key (Задать первичный ключ).

Завершив все работы по созданию таблицы, щелкните крестик вкладки конструктора таблиц. Откроется диалоговое окно с запросом, сохранить ли сделанные изменения. Нажмите кнопку Yes, после чего откроется диалоговое окно Choose Name (Выбор имени) с запросом ввести имя таблицы. Введите требуемое имя таблицы и нажмите кнопку OK. Таблица будет сохранена под указанным именем. Чтобы отобразить новую таблицу в иерархии базы данных, в панели инструментов обозревателя объектов щелкните значок Renew (Обновить).

Для просмотра и изменения свойств существующей таблицы разверните узел базы данных, содержащей требуемую таблицу, разверните узел Tables в этой базе данных и щелкните правой кнопкой требуемую таблицу, а затем в контекстном меню выберите пункт Properties. В результате для данной таблицы откроется диалоговое окно Table Properties. Для примера, на рисунке ниже показано диалоговое окно Table Properties на вкладке General для таблицы Employee базы данных SampleDb.

Чтобы переименовать таблицу, в папке Tables щелкните ее правой кнопкой в списке таблиц и в контекстном меню выберите пункт Rename. А чтобы удалить таблицу, щелкните ее правой кнопкой и выберите пункт Delete.

Создав все четыре таблицы базы данных SampleDb (Employee, Department, Project и Works_on - подробную структуру таблиц вы можете найти в исходниках), можно использовать еще одну возможность среды SQL Server Management Studio, чтобы отобразить диаграмму типа "сущность - отношение" - диаграмму (ER) (entity-relationship) этой базы данных. (Процесс преобразования таблиц базы данных в диаграмму "сущность - отношение" (ER) называется обратным проектированием.)

Чтобы создать диаграмму ER для базы данных SampleDb, щелкните правой кнопкой ее подпапку Database Diagrams (Диаграммы баз данных) и в контекстном меню выберите пункт New Database Diagram (Создать диаграмму базы данных). Если откроется диалоговое окно, в котором спрашивается, создавать ли вспомогательные объекты, выберите ответ Yes.

После этого откроется диалоговое окно Add Table, в котором нужно выбрать таблицы для добавления в диаграмму. Добавив все необходимые таблицы (в данном случае все четыре), нажмите кнопку Close, и мастер создаст диаграмму, подобную показанной на рисунке ниже:

На рисунке показана только промежуточная, а не конечная диаграмма ER базы данных SampleDb, поскольку, хотя на ней и показаны все четыре таблицы с их столбцами (и соответствующими первичными ключами), на ней все же отсутствуют отношения между таблицами. Отношение между двумя таблицами основывается на первичном ключе одной из таблиц и возможным соответствующим столбцом (или столбцами) другой таблицы.

Между таблицами базы данных SampleDb существует три отношения. Таблица Department имеет отношение типа 1:N с таблицей Employee, поскольку каждому значению первичного ключа таблицы Department (столбец Number) соответствует одно или более значений столбца DepartmentNumber таблицы Employee (в одном отделе может работать несколько сотрудников).

Аналогично существует отношение между таблицами Employee и Works_on, поскольку только значения, которые присутствуют в столбце первичного ключа таблицы Employee (Id) также имеются в столбце EmpId таблицы Works_on. Третье отношение существует между таблицами Project и Works_on, т.к. только значения, которые присутствуют в первичном ключе таблицы Project (Number) также присутствуют в столбце ProjectNumber таблицы Works_on.

Чтобы создать эти три отношения, диаграмму ER нужно реконструировать, указав для каждой таблицы столбцы, которые соответствуют ключевым столбцам других таблиц. Такой столбец называется внешним ключом (foreign key) . Чтобы увидеть, как это делается, определим столбец DepartmentNumber таблицы Employee, как внешний ключ таблицы Department. Для этого выполним следующие действия:


Подобным образом создаются и другие два отношения. На рисунке ниже показана диаграмма ER, отображающая все три отношения между таблицами базы данных SampleDb:

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

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

<блок_операторов>::=

Сгруппированные команды воспринимаются интерпретатором SQL как одна команда. Подобная группировка требуется для конструкций поливариантных ветвлений, условных и циклических конструкций. Блоки BEGIN...END могут быть вложенными.

Некоторые команды SQL не должны выполняться вместе с другими командами (речь идет о командах резервного копирования, изменения структуры таблиц, хранимых процедур и им подобных), поэтому их совместное включение в конструкцию BEGIN...END не допускается.

Нередко определенная часть программы должна выполняться только при реализации некоторого логического условия. Синтаксис условного оператора показан ниже:

<условный_оператор>::=

IF лог_выражение

{ sql_оператор | блок_операторов }

{sql_оператор | блок_операторов } ]

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

<оператор_цикла>::=

WHILE лог_выражение

{ sql_оператор | блок_операторов }

{ sql_оператор | блок_операторов }

Цикл можно принудительно остановить, если в его теле выполнить команду BREAK. Если же нужно начать цикл заново, не дожидаясь выполнения всех команд в теле, необходимо выполнить команду CONTINUE.

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

<оператор_поливариантных_ветвлений>::=

CASE входное_значение

WHEN {значение_для_сравнения |

лог_выражение } THEN

вых_выражение [,...n]

[ ELSE иначе_вых_выражение ]

Если входное значение и значение для сравнения совпадают, то конструкция возвращает выходное значение. Если же значение входного параметра не найдено ни в одной из строк WHEN...THEN, то тогда будет возвращено значение, указанное после ключевого слова ELSE.

Основные объекты структуры базы данных SQL-сервера

Рассмотрим логическую структуру базы данных.

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

Логически данные в SQL Server организованы в виде объектов. К основным объектам базы данных SQL Server относятся следующие объекты.

Краткий обзор основных объектов баз данных.

Таблицы

Все данные в SQL содержатся в объектах, называемых таблицами . Таблицы представляют собой совокупность каких-либо сведений об объектах, явлениях, процессах реального мира. Никакие другие объекты не хранят данные, но они могут обращаться к данным в таблице. Таблицы в SQL имеют такую же структуру, что и таблицы всех других СУБД и содержат:

· cтроки; каждая строка (или запись) представляет собой совокупность атрибутов (свойств) конкретного экземпляра объекта;

· cтолбцы; каждый столбец (поле) представляет собой атрибут или совокупность атрибутов. Поле строки является минимальным элементом таблицы. Каждый столбец в таблице имеет определенное имя, тип данных и размер.

Представления

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

Хранимые процедуры

Хранимые процедуры представляют собой группу команд SQL, объединенных в один модуль. Такая группа команд компилируется и выполняется как единое целое.

Триггеры

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

Функции

Функции в языках программирования – это конструкции, содержащие часто исполняемый код. Функция выполняет какие-либо действия надданными и возвращает некоторое значение.

Индексы

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


©2015-2019 сайт
Все права принадлежать их авторам. Данный сайт не претендует на авторства, а предоставляет бесплатное использование.
Дата создания страницы: 2016-08-08

05.01.15 28.1K

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

MS SQL Server

Данный программный продукт представляет собой систему управления базами данных (СУБД ) реляционного типа, разработанную корпорацией Microsoft . Для манипуляции данными используется специально разработанный язык Transact-SQL . Команды языка для выборки и модификации базы данных построены на основе структурированных запросов:


Реляционные базы данных построены на взаимосвязи всех структурных элементов, в том числе и за счет их вложенности. Реляционные базы данных имеют встроенную поддержку наиболее распространенных типов данных. Благодаря этому в SQL Server интегрирована поддержка программного структурирования данных с помощью триггеров и хранимых процедур.

Обзор возможностей MS SQL Server


СУБД является частью длинной цепочки специализированного программного обеспечения, которое корпорация Microsoft создала для разработчиков. А это значит, что все звенья этой цепи (приложения ) глубоко интегрированы между собой.

То есть их инструментарий легко взаимодействует между собой, что во многом упрощает процесс разработки и написания программного кода. Примером такой взаимосвязи является среда программирования MS Visual Studio . В ее инсталляционный пакет уже входит SQL Server Express Edition .

Конечно, это не единственная популярная СУБД на мировом рынке. Но именно она является более приемлемой для компьютеров, работающих под управлением Windows, за счет своей направленности именно на эту операционную систему. И не только из-за этого.

Преимущества MS SQL Server :

  • Обладает высокой степенью производительности и отказоустойчивости;
  • Является многопользовательской СУБД и работает по принципу «клиент-сервер »;

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

  • Тесная интеграция с операционной системой Windows ;
  • Поддержка удаленных подключений;
  • Поддержка популярных типов данных, а также возможность создания триггеров и хранимых процедур;
  • Встроенная поддержка ролей пользователей;
  • Расширенная функция резервного копирования баз данных;
  • Высокая степень защищенности;
  • Каждый выпуск включает в себя несколько специализированных редакций.

Эволюция SQL Server

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

  • Microsoft SQL Server 1.0 – вышел еще в 1990 году. Уже тогда эксперты отмечали высокую скорость обработки данных, демонстрируемую даже при максимальной нагрузке в многопользовательском режиме работы;
  • SQL Server 6.0 – вышел в 1995 году. В этой версии впервые в мире была реализована поддержка курсоров и репликации данных;
  • SQL Server 2000 – в этой версии сервер получил полностью новый движок. Большая часть изменений коснулась лишь пользовательской стороны приложения;
  • SQL Server 2005 – увеличилась масштабируемость СУБД , во многом упростился процесс управления и администрирования. Был внедрен новый API для поддержки программной платформы .NET ;
  • Последующие выпуски – были направлены на развитие взаимодействия СУБД на уровне облачных технологий и средств бизнес-аналитики.

В базовый комплект системы входит несколько утилит для настройки SQL Server . К ним относятся:


Диспетчер конфигурации. Позволяет управлять всеми сетевыми настройками и службами сервера базы данных. Используется для настройки SQL Server внутри сети.
  • SQL Server Error and Usage Reporting :


Утилита служит для настройки отправки отчетов об ошибках в службу поддержки Microsoft .


Используется для оптимизации работы сервера базы данных. То есть вы можете настроить функционирование SQL Server под свои нужды, включив или отключив определенные возможности и компоненты СУБД .

Набор утилит, входящих в Microsoft SQL Server , может отличаться в зависимости от версии и редакции программного пакета. Например, в версии 2008 года вы не найдете SQL Server Surface Area Configuration .

Запуск Microsoft SQL Server

Для примера будет использована версия сервера баз данных выпуска 2005 года. Запуск сервера можно произвести несколькими способами:

  • Через утилиту SQL Server Configuration Manager . В окне приложения слева выбираем «SQL Server 2005 Services », а справа — нужный нам экземпляр сервера БД . Отмечаем его и в подменю правой кнопки мыши выбираем «Start ».

  • С помощью среды SQL Server Management Studio Express . Она не входит в инсталляционный пакет редакции Express . Поэтому ее нужно скачивать отдельно с официального сайта Microsoft .

Для запуска сервера баз данных запускаем приложение. В диалоговом окне «Соединение с сервером » в поле «Имя сервера » выбираем нужный нам экземпляр. В поле «Проверка подлинности » оставляем значение «Проверка подлинности Windows ». И нажимаем на кнопку «Соединить »:

Основы администрирования SQL Server

Перед тем, как запустить MS SQL Server , нужно кратко ознакомиться с основными возможностями его настройки и администрирования. Начнем с более детального обзора нескольких утилит из состава СУБД :

  • SQL Server Surface Area Configuration – сюда следует обращаться, если нужно включить или отключить какую-либо возможность сервера баз данных. Внизу окна находятся два пункта: первый отвечает за сетевые параметры, а во втором можно активировать выключенную по умолчанию службу или функцию. Например, включить интеграцию с платформой .NET через запросы T-SQL :

В стандарте SQL определены два оператора: GRANT и REVOKE пре­доставления и отмены привилегий соответственно.

Оператор предоставления привилегий имеет следующий формат:

GRANT {<список действий> | ALL PRIVILEGES }

ON <имя_объекта>

ТО {<имя_пользователя> | PUBLIC }

Здесь список действий определяет набор действий из обще допустимого перечня действий над объектом данного типа.

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

<имя_объекта> - задает имя конкретного объекта: таблицы, представления, хра­нимой процедуры, триггера.

<имя_пользователя> или PUBLIC определяет, кому предоставляются данные приви­легии.

Параметр WITH GRANT OPTION является необязательным и определяет режим, при котором передаются не только права на указанные действия, но и право переда­вать эти права другим пользователям. Передавать права в этом случае пользова­тель может только в рамках разрешенных ему действий.

Рассмотрим пример, пусть у нас существуют три пользователя с абсолютно уни­кальными именами user1, user2 и user3. Все они являются пользователями од­ной БД.

Userl создал объект Tab1, он является владельцем этого объекта и может пере­дать права на работу с эти объектом другим пользователям. Допустим, что поль­зователь user2 является оператором, который должен вводить данные в Tab1 (например, таблицу новых заказов), а пользователь user3 является менеджером отдела, который должен регулярно про­сматривать введенные данные.

Для объекта типа таблица полным допустимым перечнем действий является на­бор из четырех операций: SELECT, INSERT, DELETE, UPDATE. При этом операция об­новления может быть ограничена несколькими столбцами.

Общий формат оператора назначения привилегий для объекта типа таблица бу­дет иметь следующий синтаксис:

GRANT {[.INSERT][.DELETE][.UPDATE

(<список столбцов>)]} ON <имя_таблицы>

TO {<имя_пользователя> | PUBLIC }

В рассматриваемом случае необходимо выполнить следующие назначения:

Эти назначения означают, что пользователь user2 имеет право только вводить новые строки в отношение Tab1, а пользователь user3 имеет право просматри­вать все строки в таблице Tab1.

При назначении прав доступа на операцию модификации можно уточнить, зна­чение каких столбцов может изменять пользователь. Допустим, что менеджер отдела имеет право изменять цену на предоставляемые услуги. Предположим, что цена задается в столбце SENA таблицы Tab1. Тогда операция назначения при­вилегии пользователю user3 может измениться и выглядеть следующим образом:

GRANT SELECT, UPDATE (SENA)

Если наш пользователь user1 предполагает, что пользователь user4 может его за­мещать в случае его отсутствия, то он может предоставить этому пользователю все права по работе с созданной таблицей Tab1.

GRANT ALL PRIVILEGES

WITH GRANT OPTION

В этом случае пользователь user4 может сам назначать привилегии по работе с таблицей Tab1 в отсутствие владельца объекта пользователя user1. Поэтому в случае появления нового оператора пользователя user5 он может назначить ему права на ввод новых строк в таблицу командой:

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

GRANT SELECT, UPDATE, DELETE

WITH GRANT OPTION

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

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

Так как представления могут соответствовать итоговым запросам, то для этих представлений недопустимы операции изменения, и, следовательно, для таких представлений набор допустимых действий ограничивается операцией SELECT. Если же представления соответствуют выборке из базовой таблицы, то для та­кого представления допустимыми будут все 4 операции- SELECT, INSERT, UPDATE и DELETE.

Для отмены ранее назначенных привилегий в стандарте SQL определен опера­тор REVOKE. Оператор отмены привилегий имеет следующий синтаксис:

REVOKE {<список операций>| ALL PRIVILEGES}

ON <имя_объекта>

FROM {<список пользователей | PUBLIC }

(CASCADE | RESTRICT }

Параметры CASCADE или RESTRICT определяют, каким образом должна произво­диться отмена привилегий. Параметр CASCADE отменяет привилегии не только пользователя, который непосредственно упоминался в операторе GRANT при пре­доставлении ему привилегий, но и всем пользователям, которым этот пользова­тель присвоил привилегии, воспользовавшись параметром WITH GRANT OPTION.

Например, при использовании операции

REVOKE ALL PRIVILEGES

TO user4 CASCADE

будут отменены привилегии и пользователя user5, которому пользователь user4 успел присвоить привилегии.

Параметр RESTRICT ограничивает отмену привилегий только пользователю, не­посредственно упомянутому в операторе REVOKE. Но при наличии делегирован­ных привилегий этот оператор не будет выполнен.Так, например, операция:

REVOKE ALL PRIVILEGES

TO user4 RESTRICT

не будет выполнена, потому что пользователь user4 передал часть своих полно­мочий пользователю user5.

Посредством оператора REVOKE можно отобрать все или только некоторые из ра­нее присвоенных привилегий по работе с конкретным объектом. При этом из описания синтаксиса оператора отмены привилегий видно, что можно отобрать привилегии одним оператором сразу у нескольких пользователей или у целой группы PUBLIC.

Поэтому корректным будет следующее использование оператора REVOKE

TO user2, user4 CASCADE

При работе с другими объектами изменяется список операций, которые исполь­зуются в операторах GRANT и REVOKE.

По умолчанию действие, соответствующее запуску (исполнению) хранимой про­цедуры, назначается всем членам группы PUBLIC.

Если необходимо изменить это условие, то после создания хранимой процедуры необходимо записать оператор REVOKE

TO PUBLIC CASCADE

И теперь можно назначить новые права пользователю user4

Системный администратор может разрешить некоторому пользователю созда­вать и изменять таблицы в некоторой БД. Тогда он может записать оператор предоставления прав следующим образом:

GRANT CREATE TABLE ALTER TABLE DROP TABLE

В этом случае пользователь user1 может создавать, изменять или удалять табли­цы в БД DB_LIB, однако он не может разрешить создавать или изменять таблицы в этой БД другим пользователям, потому что ему дано разрешение без права де­легирования своих возможностей.

В некоторых СУБД пользователь может получить права создавать БД. Напри­мер, в MS SQL Server системный администратор может предоставить пользова­телю main_user право на создание своей БД на данном сервере. Это может быть сделано следующей командой:

GRANT CREATE DATABASE

По принципу иерархии пользователь main_user, создав свою БД, теперь может предоставить права на создание или изменение любых объектов в этой БД дру­гим пользователям.

В СУБД, которые поддерживают однобазовую архитектуру, такие разрешения недопустимы. Например, в СУБД Oracle на сервере создается только одна БД, но пользователи могут работать на уровне подсхемы (части таблиц БД и свя­занных с ними объектов). Поэтому там вводится понятие системных привиле­гий. Их очень много, 80 различных привилегий.

Для того чтобы разрешить пользователю создавать объекты внутри этой БД, используется понятие системной привилегии, которая может быть назначена од­ному или нескольким пользователям. Они выдаются только на действия и кон­кретный тип объекта. Поэтому если системный администратор предо­ставил пользователю право создания таблиц (CREATE TABLE), то для того чтобы он мог создать триггер для таблицы, ему необходимо предоставить еще одну системную привилегию CREATE TRIGGER. Система защиты в Oracle считается од­ной из самых мощных, но это имеет и обратную сторону - она весьма сложная. Поэтому задача администрирования в Oracle требует хорошего знания как се­мантики принципов поддержки прав доступа, так и физической реализации этих возможностей.