How to become an author
Search
Write a publication
Pull to refresh

SQL Server 2011 — Автономная база данных

Reading time11 min
Views14K
Lumber room
From sandbox
В течение последних лет Microsoft внедрила множество интересных технологий, которые прочно вошли в арсенал разработчиков. Кардинальные изменения были включены в SQL Server 2005, после чего SQL Server 2008 развил и укрепил успех. Denali несет в себе множество новых инструментов, а так же расширений функционала для существующих. В этой статье в деталях рассмотрим один из новых инструментов, который, я уверен, придется по душе разработчикам баз данных. Этот инструмент, фича ­ – автономные базы данных (Contained Database). Рассмотрим что они собой представляют, как с ними работать, к чему можно применить и другие вещи.

Что не так с текущими базами?


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

Вот некоторые из ключевых проблем:
  • Потеря информации во время разворачивания базы или передвижения ее между серверами.
    Во время передвижения базы данных между серверами, то такая информация как логины, пароли, задания агента SQL Server и прочее не может быть передвинуто вместе с базой. Потому что эта информация принадлежит серверу баз данных.  Пересоздание упомянутых объектов  руками не самое приятное занятие, которое к тому же занимает много времени и не гарантирует защиты от ошибок.
  • Различия между Разработкой и Разворачиванием приложений.
    При разворачивании приложения разработчиков могут поджидать самые разные неприятности, начиная от несовпадения тонких настроек окружения, таких как разрешения на создание новых логинов, отключенная командная строка (xp_cmdshell), языковые настройки сервера.
  • Вопросы безопасности в администрировании приложения.
    Весьма сложно администрировать и поддерживать отдельно стоящую базу данных из-за того, что, например, задания агента SQL Server доступны в пределах всего сервера, что влечет за собой повышение привилегий отдельно взятых пользователей. Однако эти повышенные права дают доступ к другим частям сервера, которые не желательно открывать. Все это может вести к серьезным проблемам безопасности.

После того, как обозначили ключевые недостатки существующих баз, перейдем к описанию нового типа.


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


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

Таблицы, функции, процедуры, ограничения, схемы, типы, библиотеки, представления, логины, задания агента SQL Server, системные настройки, связанные сервера (linked servers) – все хранится в базе.

Естественно, что главным преимуществом такой базы будет легкость разворачивания и переноса. Достаточно только развернуть базу и она сразу готова  к работе. Больше не будет забытых скриптов для пользователей, их ролей, агентов и тд.

Термины, которые надо запомнить


Application boundary (граница приложения) – граница между экземпляром сервера и кодом приложения. Под кодом приложения понимается вся база со всеми объектами, которые могут понадобиться в процессе работы.

Application Model (модель приложения) – внутри границ приложения есть место, где идет разработка и управление приложением.

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

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

Non-contained database (зависимая база данных) –база, для которой свойство containment = NONE. База зависит от некоторых объектов принадлежащих экземпляру сервера.

Fully contained database (автономная база данных) – база, которая не позволяет каким-либо объектам или функциям пересекать границы приложения.

Partially contained database (частично зависимая база данных) – база, которая позволяет некоторым объектам действовать с пересечением границ приложения. Доступно в CTP 1.

Contained user (автономный пользователь)

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


4 шага для создания автономной базы данных


Я думаю, что на данный момент уже достаточно теоретических знаний и концепций о том, как работает такая база данных, и настало время немного размяться «в поле». Следующие 4 шага описывают как создать автономную базу данных.

Шаг 1. Разрешить использование автономных баз данных на уровне сервера.

Шаг 2. Создать базу данных и выставить режим автономности как частичный. Свойство CONTAINMENT должно быть равно Partial.

Шаг 3. Создать автономного пользователя в новой базе данных.

Шаг 4. Зайти в новую базу данных под учетной записью автономного пользователя.

Теперь рассмотрим каждый шаг подробно и в картинках.

Шаг 1. Разрешить использование автономных баз данных на уровне сервера.


Присоединитесь к экземпляру нового SQL Server 2011 и из Обозревателя объектов (Object Explorer) вызовите контекстное меню для сервера. В контекстном меню необходимо выбрать пункт Properties (Свойства).



Перейдите на страницу Advanced и на ней необходимо выставить значение для свойства Enable Contained Databases равное TRUE.



То же самое может быть достигнуто с помощью скрипта.
--Enabled Advanced options
-- разрешить работать с настройками с закладки Advanced
sp_configure 'show advanced', 1;
RECONFIGURE WITH OVERRIDE;
Go

--Enabled Database Containment
--Разрешить использование автономных баз данных
sp_configure 'contained database authentication', 1;
RECONFIGURE WITH OVERRIDE;
go

 

Шаг 2. Создать базу данных и выставить режим автономности как частичный


Создадим новую базу и назовем ее TestContainedDB.

После создания открываем ее свойства через контекстное меню



Открываем закладку Options и выбираем для опции Containment type: свойство Partial.



То же самое может быть достигнуто с помощью скрипта.
USE [master]
GO

CREATE DATABASE [TestContainedDB]
 CONTAINMENT = PARTIAL
 ON  PRIMARY
LOG ON
GO

ALTER DATABASE [TestContainedDB] SET COMPATIBILITY_LEVEL = 110
GO


Шаг 3. Создать автономного пользователя в новой базе данных.


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



Задаем имя учетной записи и пароль. Пусть для примера это будет testuser\testuser.



Указываем, что пользователь будет владельцем базы. Для этого на странице Membership отмечаем галочкой пункт db_owner.

Эти же действия можно совершить при помощи TSql
USE [TestContainedDB]
GO
CREATE USER [TestUser]
WITH PASSWORD='testuser',
DEFAULT_SCHEMA=[dbo]
GO

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



Шаг 4. Зайти в новую базу данных под учетной записью автономного пользователя.


Для демонстрации шага надо завершить работу в SSMS, и войти снова следуя описанным шагам.





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



После этого надо нажать на кнопку Options и перейти на закладку Connection Properties.



На этой закладке надо указать к какой базе данных мы собираемся присоедениться. В данном случае это TestContainedDB.

Теперь можно жать на кнопку Connect, и мы окажемся в автономном окружении базы.



Конвертация базы в автономную


Я думаю после описания плюсов автономной базы данных и того, как ее можно создать, вы задумались о том, можно ли перевести существующую базу в автономный режим. Можно. Сейчас будет продемонстрирован такой процесс. Поскольку демонстрация будет вестись на тестовой базе, то для начала ее и создам, воспользовавшись скриптом ниже:
USE [master]
GO

CREATE DATABASE [NonContainedDB]
 CONTAINMENT = NONE
 ON  PRIMARY
GO

ALTER DATABASE [NonContainedDB] SET COMPATIBILITY_LEVEL = 110
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [NonContainedDB].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

Затем создаем таблицу с данными.
-- Drop the table if it exists
-- удалить таблицу, если она существует
IF EXISTS (SELECT * FROM sys.objects WHERE name = N'tbl_Players' AND type = 'U')
    DROP TABLE tbl_Players
GO
SET ANSI_NULLS ON
GO
--Create the table
-- создать таблицу
CREATE TABLE tbl_Players (
	PlayerID INT IDENTITY,
	PlayerName VARCHAR(15),
	BelongsTo VARCHAR(15),
	MatchPlayed INT,
	RunsMade INT,
	WicketsTaken INT,
	FeePerMatch NUMERIC(16,2)
)

--Insert the records
-- внести записи в таблицу
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('A. Won','India',10,440,10, 1000000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('A. Cricket','India',10,50,17, 400000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('B. Dhanman','India',10,650,0,3600000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('C. Barsat','India',10,950,0,5000000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('A. Mirza','India',2,3,38, 3600000)

INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('M. Karol','US',15,44,4, 2000000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('Z. Hamsa','US',3,580,0, 400)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('K. Loly','US',6,500,12,800000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('S. Summer','US',87,50,8,1230000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('J.June','US',12,510,9, 4988000)

INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('A.Namaki','Australia',1,4,180, 999999)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('Z. Samaki','Australia',2,6,147, 888888)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('MS. Kaki','Australia',40,66,0,1234)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('S. Boon','Australia',170,888,10,890)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('DC. Shane','Australia',28,39,338, 4444499)

INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('S. Noami','Singapore',165,484,45, 5678)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('Z. Biswas','Singapore',73,51,50, 22222)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('K. Dolly','Singapore',65,59,1,99999)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('S. Winter','Singapore',7,50,8,12)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('J.August','Singapore',9,99,98, 890)

 

Для полноты картины добавим хранимую процедуру.
If Exists (Select * from sys.objects where name = 'usp_SelectRecordsByPlayerName' and type = 'P')
    Drop Procedure usp_SelectRecordsByPlayerName
Go
-- Create the  stored procedure
Create Procedure [dbo].[usp_SelectRecordsByPlayerName]
( @PlayerID int )
As
Begin
	Select
		PlayerID
		,PlayerName
		, BelongsTo
		, MatchPlayed
		,RunsMade
		,WicketsTaken
		,FeePerMatch
	From
	tbl_Players
	Where PlayerId = @PlayerID
End

 

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

Шаг 1


На первом шаге нужно будет создать нового пользователя на уровне сервера и пользователя для базы. Это можно сделать с помощью такого скрипта:
--Create a login on the server
-- Создаем пользователя на уровне сервера
CREATE LOGIN NonContainedUser
WITH PASSWORD = 'somepassword@123'

--Create a "non-contained" users for the login on the server
-- создаем автономного пользователя
USE NonContainedDB
GO
CREATE USER NonContainedUser FOR LOGIN NonContainedUser
GO

 

Шаг 2


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

SELECT
	class_desc
	,feature_name
	,feature_type_name
FROM sys.dm_db_uncontained_entities

 

Результат должен быть как на скриншоте ниже.



Можно проигнорировать ROUTE. Итак, по данным скрипта, у нас имеется 2 объекта в связанной базе данных.

Для определения пользователей принадлежащих серверу можно запустить такой скрипт:
USE NonContainedDB
GO
SELECT dp.name
FROM sys.database_principals dp
JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE dp.authentication_type = 1
  AND sp.is_disabled = 0

 

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



Шаг 3


На базе NonContainedDB надо вызвать контекстное меню и выбрать Properties. Затем перейти на закладку Options и выбрать для свойства Containment type значение Partial.

Другой способ для описанных действий заключается в написании скрипта
USE master
GO
ALTER DATABASE NonContainedDB SET CONTAINMENT=PARTIAL;
GO

 

После того как настройка выставлена, необходимо мигрировать пользователей на автономную базу.
USE NonContainedDB
GO
EXEC sp_migrate_user_to_contained @username = N'NonContainedUser',
                                  @rename = N'keep_name',
                                  @disable_login = N'disable_login'

 

Процедура sp_migrate_user_to_contained нужна для миграции пользователей в автономную базу. Она конвертирует пользователей уровня сервера в пользователей автономной базы. После процедуры можно снова запустить скрипт определяющий зависимых пользователей.
USE NonContainedDB
GO
SELECT dp.name
FROM sys.database_principals dp
JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE dp.authentication_type = 1
  AND sp.is_disabled = 0

 

И результат:



Можно убедиться что NonContainedUser больше не появляется. Это означает что пользователь был изменен и его учетная запись была заблокирована на уровне сервера.

Шаг 4.


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

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



Бэкап автономной базы данных.


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

Интерфейс


Присоединитесь к базе данных с помощью SSMS, в навигаторе объектов (Object Explore) находите желаемую базу. В контекстном меню идете по пунктам Tasks > Backup



Скрипт


Сделать бэкап базы можно с помощью нехитрого скрипта.
BACKUP DATABASE TestContainedDB
TO DISK='<File Path>\TestContainedDB.bak'

Восстановление архивной базы


Опять же есть два пути: через интерфейс пользователя и с помощью скрипта.

Интерфейс


Через интерфейс все делается схожим образом с архивированием

Присоединитесь к базе данных с помощью SSMS, в навигаторе объектов (Object Explore) находите желаемую базу. В контекстном меню идете по пунктам Tasks > Restore
 

Скрипт


RESTORE DATABASE TestContainedDB
FROM DISK='<File Path>\TestContainedDB.bak'

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

Msg 12824, Level 16, State 1, Line 1

The sp_configure value 'contained database authentication' must be set to 1 in order to restore a contained database. You may need to use RECONFIGURE to set the value_in_use.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

Из которого становится ясно, что необходимо активировать опцию Contained Database Authentication в настройках SQL Server на уровне экземпляра сервера. Эта настройка по умолчанию выключена.  Выполните скрипт ниже для устранения проблемы.
--Enabled Advanced options
-- включаем возможность редактирования опций
sp_configure 'show advanced', 1;
RECONFIGURE WITH OVERRIDE;
go
--Enabled Database Containment
-- включаем возможность аутентификации для автономных баз данных
sp_configure 'contained database authentication', 1;
RECONFIGURE WITH OVERRIDE;
go

 

Еще немного информации об автономных базах данных


Методы аутентификации поддерживаемые автономными базами остались те же:
  • SQL Server Authentication
  • Windows Based Authentication

Изменение базы данных изменилось. CREATE / ALTER DATABASE работают теперь по-другому. Выражение Alter Database <database name> больше не работает. Вместо имени базы надо указывать служебное слово CURRENT. 
ALTER DATABASE CURRENT

 

Дополнительно по автономным базам данных можно почитать здесь.

Это перевод статьи Niladri Biswas. У него есть еще интересные статьи про новый SQL Server 2011. Если понравился перевод, то я могу выложить остальные части в переводе. Статьи очень большие и их лучше разбивать. В итоге выйдет 4-5 частей.

Переводы из цикла:
MS SQL Server 2011: Автономные базы данных, новый объект Sequence, оператор Offset, обработка ошибок, конструкция With Result Set, новое в SSMS.
Tags:
Hubs:
+30
Comments11
70
Karma
0
Rating
Андрей Гордиенков @VioletTape

Пользователь

Send message

Articles

Show the best of all time
Unlock dark mode

Your account

Sections

Information

Services

Support
© 2006–2024, Habr