39. 29. Секционирование таблиц: определение, назначение, функция секционирования, схема секционирования,ограничения.

Чтобы создать секционированную таблицу или индекс, необходимо выполнить следующие шаги:

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

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

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

Создание функции секционирования (x<=5 , 5 > x <=10, 10 < x <= 15, x > 15):

CREATE partition FUNCTION part_func (int)

AS range LEFT FOR VALUES (5, 10, 15);

Схема секционирования:

CREATE partition scheme my_scheme

AS partition my_scheme TO (f1, f2, f3 , f_actual);

-- где f1, f2, f3 и f_actual - файловые группы

Затем создаем таблицу с указанием нашей функции секционирования:

CREATE TABLE tbl_parted

(

id int IDENTITY(1, 1) NOT NULL,

val varchar(20) NULL,

CONSTRAINT [PK_tbl_parted] PRIMARY KEY CLUSTERED ( id ASC ) ON my_scheme(id)

)

ON my_scheme (id)

Создать файловую группу:

-- Create the database

CREATE DATABASE test_filegroup

go

USE test_filegroup

go

-- Create a new file group

ALTER DATABASE test_filegroup

ADD filegroup nonclustindexes go

-- Add a file to the file group, we can now use the file group to store data

ALTER DATABASE test_filegroup ADD FILE ( name = nonclustindexes, filename =

'E:\MSSQL\DEV\NonClustIndexes.ndf', size = 5mb, maxsize = 100mb,

filegrowth = 5mb ) TO filegroup nonclustindexes

go

40. 30. Моментальные снимки базы данных (snapshot): определение, назначение, создание, применение, технология copy-on-write.

Моментальный снимок БД – это фиксация состояния БД на текущий момент времени.

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

Создается на основании существующей БД, отдельно существовать не может. Пример:

Создаем БД [SS2_ SmelowDB] как первичную, с файловыми группами Primary, G1_Dat1, G2_Dat2, G2_Dat2, и с соответствующими в них файлами Primary.ss2, data1.ss1, data2.ss2, data3.ss3, как снапшот на основании БД SmelowDB. Расширение файлов может быть произвольным.

Чтобы получить данные из снапшота, в селект запросе следует писать:

Select * from имя_снап.схема.имя_таблицы. Пример:

Снапшот можно использовать только для выборки данных, но никак не для изменения:

Восстановить БД на момент снимка можно с помощью restore:

Следует отметить, что если в БД будут иметься более одного снимка, то восстановление невозможно. Только по удалению всех, кроме нужного, можно восстановить БД.

Технология copy-on-write заключается в том, что копирование данных в снапшот будет происходить лишь при изменении их в БД, сам снапшот создается пустым. Все селект запросы, идущие к снимку, сначала делают выборку из него, затем, если данных не обнаружено, то селект-запрос ретранслируется к базе данных. Если какие либо строки были изменены или удалены в БД, то перед их изменением копия отправилась в снапшот. Следовательно, если будем делать запрос к снапшоту на выборку этих строк, он возьмет их из снимка и не будет ретранслировать к БД, т.к. там они уже, либо изменены, либо не существуют.

41. 31. Основы безопасности: определения аутентификации, авторизации и конфиденциальности, разрешения серверные, базы данных и объектные, принципал, объекты Login, User, фиксированные роли сервера и базы данных, специальные пользователи, пользовательские роли, роли приложений, схема объектов базы данных, стандартные схемы, принципы разрешения имен, имперсонизация.

Аутентификация – это процедура проверки подлинности пользователя. Чаще всего с помощью имени и пароля. В MS SQL SERVER бывает:

• Windows-аутентификация;

• аутентификация SQL-сервера;

• смешанная аутентификация.

Авторизация – это процедура проверки прав и ограничений уже аутентифицированного пользователя.

Конфиденциальность – это способность системы предотвращать утечку (разглашение) какой-либо информации.

Разрешение (permission) – разрешение на выполнение какой-либо операции (свойство принципала). Используются при авторизации, проверяется, есть ли у пользователя разрешения, некоторые свойства принципала, которые определяют его возможности авторизации.

{разрешения серверные, базы данных и объектные,} ??

Принципал – это объект информационной системы, сущность, которая может запрашивать ресурсы SQL Server.

Принципалы уровня Windows

• Имя входа домена Windows

• Локальное имя входа Windows

Принципалы уровня SQL Server

• Имя входа SQL Server (login)

• Роль сервера

Принципалы уровня базы данных

• Пользователь базы данных (user)

• Роль базы данных

• Роль приложения

Login – это принципал, который идентифицируется внутри сервера (объект сервера).

User – это принципал, который идентифицируется внутри БД (объект БД).

Роль в SQL – это поименованный набор разрешений. Различают роли серверные, базы данных и фиксированные. Все роли, кроме public, фиксированные роли - набор разрешений, который нельзя ни убавить, ни прибавить. Можно только назначать логину роль.

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

Фиксированные роли базы данных задаются на уровне базы данных и предусмотрены в каждой базе данных. Каждый пользователь (user) базы данных принадлежит к роли базы данных public.

В отличие от серверных ролей, которые могут быть только встроенными, роли баз данных могут быть как встроенными, так и пользовательскими. Встроенные роли баз данных обладают предопределенным набором разрешений, а пользовательские роли можно использовать для группировки пользователей при предоставлении разрешений. Создать пользовательскую роль можно с помощью скрипта или студии: CREATE ROLE DBRole1;

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

Роль public – не фиксированная, т.е. можно добавлять разрешения. Если пользователю не назначена никакая роль, то public обладают все пользователи. При подключении все пользователи приобретают набор разрешений public.

При создании любой базы данных в ней автоматически создаются четыре специальных пользователя (информация 2005 сервера!!!):

• dbo (от database owner) — пользователь-владелец базы данных. Он автоматически создается для того логина, от имени которого была создана эта база данных. Конечно же, как владелец, он получает полные права на свою базу данных.

• При создании база данных включает в себя пользователя guest по умолчанию. Разрешения, предоставленные пользователю guest, наследуются пользователями, которые не имеют четной записи пользователя в базе данных.Пользователя guest нельзя удалить, но его можно отключить, если отменить его разрешение CONNECT.

• INFORMATION_SCHEMA — этому пользователю не может соответствовать ни один логин. Его единственное значение —быть владельцем схемы INFORMATION_SCHEMA, в которой хранятся представления системной информации для базы данных;

• sys — этому специальному пользователю, как и INFORMATION_SCHEMA, не могут соответствовать логины. Он является владельцем схемы SYS, к которой принадлежат системные объекты базы данных.

Роль приложения – это разрешения, предоставляемые не пользователю, а приложению, из которого пользователь подключается к базе данных. Применение этого способа выглядит так:

• пользователь от имени любого логина подключается к серверу и делает нужную базу данных текущей;

• затем пользователь выполняет хранимую процедуру sp_setapprole, чтобы активизировать указанную им роль приложения. При этом в базе данных он получает права этой роли приложения (и теряет свои текущие права);

• после того, как необходимость в правах роли приложения закончилась, пользователь может опять переключиться на свою исходную учетную запись (sp_unsetapprole.)+

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

42. 32. Применение XML: правильные и валидные документы XML, конструкции FOR XML AUTO, FOR XML PATH, функция OPENXML, тип XML, применение XQUERY-конструкций, коллекции XML-схем и их применение, типизированые XML-столбцы,

XML: Extensible Markup Language – расширяемый язык разметки.

Правильно построенный документ – соответствует синтаксическим правилам XML.

Валидный документ – соответствует семантическим правилам. Семантические правила описываются с помощью специальных схем.

Для получения таблицы из реляционного вида в формат XML, используют следующие конструкции:

FOR XML AUTO – используется в селект-запросе для получения строк в простейшем xml-формате (атрибутная форма таблицы). Пример:

Имя тега будет совпадать с именем таблицы, а в качестве атрибутов будут имена столбцов:

FOR XML PATH – используется в селект-запросе для получения строк в не атрибутной, теговой форме. Т.е. имена столбцов будут записаны отдельно в каждом теге.

Функция OPENXML позволяет получить доступ к XML-данным так, как если бы это был реляционный набор строк. Чтобы писать запросы в отношении XML-документа с использованием OPENXML, необходимо сначала вызвать хранимую процедуру sp_xml_preparedocument. Таким образом, производится синтаксический анализ XML-документа и возвращается дескриптор для проанализированного документа, готового к использованию. Проанализированный документ является представлением дерева объектной модели документа (DOM) различных узлов в XML-документе. Дескриптор документа передается OPENXML. Затем инструкция OPENXML выдает представление документа в виде набора строк, основываясь на переданных ей аргументах.

Пример: declare @xml varchar(2000)= --наш xml-документ

'<?xml version="1.0" encoding="UTF-8"?> <ROOT> <X> <AUDITORIUM>1</AUDITORIUM> <AUDITORIUM_NAME>1</AUDITORIUM_NAME> <AUDITORIUM_CAPACITY>1</AUDITORIUM_CAPACITY> <AUDITORIUM_TYPE>ЛК</AUDITORIUM_TYPE> </X> <X> <AUDITORIUM>9</AUDITORIUM> <AUDITORIUM_NAME>9</AUDITORIUM_NAME> <AUDITORIUM_CAPACITY>9</AUDITORIUM_CAPACITY> <AUDITORIUM_TYPE>ЛК</AUDITORIUM_TYPE> </X> </ROOT>',

@hxml int

EXEC sp_xml_preparedocument @hxml output, @xml -- процедура перевода, получаем дескриптор

INSERT INTO AUDITORIUM(AUDITORIUM,AUDITORIUM_NAME,AUDITORIUM_CAPACITY,AUDITORIUM_TYPE)

SELECT * FROMopenxml(@hxml, 'ROOT/X',2)—вызов функции с параметрами

WITH

(

AUDITORIUM char(10),

AUDITORIUM_NAME varchar(200),

AUDITORIUM_CAPACITY int,

AUDITORIUM_TYPE char(10)

)

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

К числу аргументов OPENXML относятся:

• дескриптор XML-документа

• выражение XPath для распознавания узлов, которые должны быть сопоставлены со строками

• описание набора строк, который должен быть создан;

• сопоставление столбцов набора строк и узлов XML;

В данном примере 2-флаг, означает, что сопоставление, ориентированное на элементы. Демонстрируемый набор строк предоставляет или потребляет строки, где каждый элемент XML представлен в виде строки. XML-теги сопоставляются с предоставленными в предложении WITH столбцами, на основе соответствия имен.

Тип XML – это встроенный тип в SQL Server, в котором могут храниться данные в Xml-формате. Можно создать столбец типа xml, переменную. Типизированная переменная создается при указании коллекции XML-схем, также, как и при создания столбца :

DECLARE @x XML (production.productdescriptionschemacollection) --переменная

CREATE TABLE t1

( col1 INT,

col2 XML (production.productdescriptionschemacollection)

) --столбец

Коллекция XML-схем — это сущность класса метаданных, подобная таблице в базе данных. Можно создавать, изменять и удалять эти схемы. Схемы, указанные в инструкции CREATE XML SCHEMA COLLECTION (язык Transact-SQL), автоматически импортируются в создаваемую коллекцию XML-схем. Коллекция XML-схем хранит импортированные XML-схемы и используется для решения следующих задач: проверка экземпляров XML; типизация XML-данных, хранимых в базе данных.

43. 33. Копирование и восстановление базы данных: модели восстановления, усечение журнала, типы резервного копирования, процесс восстановления базы данных, отсоединение и присоединение баз данных.

Модель восстановления - это параметр БД, хранится в master.

Модель восстановления SIPMLE

При Simple модели БД будет восстановлена до последней копии.

Модель Full (полная)- БД будет восстановлена до последней завершенной транзакции.

Модель Bulg Logger (c неполным протоколированием) БД будет восстановлена до последнего журнала.

Журнал БД- это специальный файл, используемый для восстановления БД.

Упреждающее журналирование

Задача сервера как можно меньше работать с диском и больше с буфером. Check point – делает одинаковой страницу на диске и в оперативной памяти. В журнале транзакций фиксируются все изменения, произошедшие в БД. Прежде чем будет изменена страница в буфере, эта страница будет зафиксирована в журнале. Таким образом, журнал содержит все информацию, позволяющую восстановить информацию буферного КЭШа. Процесс записи в журнал и есть упреждающее журналирование.

Количество виртуальных журналов фиксировано, они именуются и имеют логическое начало.

О процедуре усечения журнала следует говорить при определенной модели восстановления:

• при simple усечение будет происходить при каждом check point, усекаются, начиная с минимального, и все полностью заполненные журналы;

• при full или Bulg Logger — после создания резервной копии журналов, при условии, что со времени предыдущей операции резервного копирования была достигнута контрольная точка (check point). Неактивную часть журнала нельзя усечь, пока в резервную копию не попали все записи журнала.

Полное резервное копирование базы данных (ПРКБД)

1. ПРКБД: самый простой и надежный способ сохранения данных; обычно делается периодически (например, каждую ночь).

2. ПРКБД: в результате полная копия базы данных; из полной копии восстанавливаются все файлы, данные, метаданные; данные в непротиворечивом состоянии.

3. ПРКБД: при копировании база данных находится в обычном рабочем режиме, пользователи могут выполнять транзакции, те транзакции, которые успели зафиксироваться, будут отражены в копии, другие - подлежат откату.

1. Недостатки полного резервного копирования: восстанавливается только состояние БД соответствующее копии, часто не сделаешь (долго), мешает клиентам.

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

РРКБД – копирование только изменений прошедших с момента последней копии; чаще всего РРКБД сочетается с ПРКБД.

Резервное копирование журнала тарнзакций

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

Tail-log- копирование последнего незаполненного журнала плюс заполненного до этого всего журнала, усечение не происходит.

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

EXEC sp_detach_db 'AdventureWorks2012'

Присоединение БД- это возврат БД в рабочее состояние. С помощью процедуры sp_attach_db, указываем путь к файлам БД, которую хотим присоединить.

EXEC Sp_attach_db

@dbname = N'AdventureWorks2012',

@filename1 =

N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\AdventureWorks2012_Data.mdf'

,

@filename2 =

N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\AdventureWorks2012_log.ldf'

;