Кср на сервере. Защита. До сроков.

КСР на сервере. Защита. До сроков.

 

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

Реализация информационных систем в трехуровневой архитектуре

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

Трехуровневая архитектура

Клиент Сервер(ы)

Пользовательские сервисы

Бизнес сервисы

Сервисы данных

 

 

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

Бизнес-сервисы – эта группа отвечает за определенные правила. В трехуровневых системы объекты могут находиться на своих собственных серверах, распределяться между несколькими серверами, находиться на одном сервере с сервисами данных,

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

 

СУБД SQL Server содержит:

· База данных – является объектом наивысшего уровня, к которому можно обратиться внутри SQL Server.

· Журнал транзакций – объект, осуществляющий протоколирование операций ведения баз данных(БД)

Транзакция – неделимая операция. Откат транзакции – можно откатиться в ее начало.

· Таблицы – единственный объект БД, предназначены для хранения пользовательских данных.

· Группы файлов – по умолчанию все, связано с БД находиться в одном файле. Это первичная группа, такая группа только одна. Если в процессе использования БД планируется размещение ее на нескольких дисках, в этом случае можно создать вторичные файлы БД, до 255 групп.

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

· Представление – виртуальные таблицы(virtual tables), которые отображают данные, хранящиеся в других таблицах, и для пользователя во многом напоминают таблицы.

· Хранимые процедуры – поименованные наборы команд на языке Transact-SQL, хранимые на сервере и вызываемые на выполнение пользователями.

· Определенные пользователем функции – набор команд Transact-SQL, сохраненных пользователем в виде функции.

· Пользователи – представляют собой идентификаторы отдельных пользователей для входа в систему SQL Server.

· Роли – объект, позволяющих объединять пользователей в группы для доступа к БД.

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

· Определение по умолчанию – описывают значения, которые присваиваются столбцам таблицы, если при добавлении строки явно не было указано значение для соответствующего столбца.

· Определенные пользователем типы данных – типы данных создаваемые пользователями.

· Полнотекстовые каталоги – находят фразы в больших блоках текста в полях БД.

 

SQL Server 2000 Edition Enterprises предлагает следующие специальные функции:

· Кластеризация;

· Распределенные разделенные представления;

· Индексированные представления;

· Разделенные кубы;

· Поддерживает наибольшее количество памяти(до 64 Гбайт);

· Поддерживает более 4 процессоров(до 32);

 

Системные базы данных SQL Server 2000 и их назначение.

MASTER – содержит специальные набор таблиц, которые отслеживают целиком всю систему. Например, при создании БД ее описание размещается в таблице sysdatebase БД master. Эта БД не может быть удалена.

MODEM – является шаблоном, задаваемым администратором, используемая для создания любых пользовательских баз данных. Содержит параметры по умолчанию, которые можно переопределять при создании соответствующей базы данных пользователя. Эта БД не может быть удалена.

TEMPDB - одна из ключевых рабочих областей сервера. Хранит все временные объекты: промежуточные таблицы для решения сложных запросов, временные таблицы пользователя. Это единственная БД, которая при перезапуске SQL Server строится заново.

MSDB – хранит информацию об администрировании и управления сервером. В этой БД служба SQL Agent сохраняет все системные задачи. При удалении не происходит полного краха системы, но использовать агента невозможно.

БД pubs и Northwind – учебные, созданные для тренировок и экспериментов, не влияющие на работу сервера. БД и журнал занимают 4Мб. Обе БД можно инсталлировать заново, запустив сценарии instpubs.sql и instnwind.sql.

 

 

ОСНОВНЫЕ УТИЛИТЫ

Электронные книги SQL Server Books Online(BOL) – приложение, представляющая доступ к справочной информации SQL Server.

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

Netlibs, поставляемый с SQL Server 2000, включает:

· Именованные каналы,

· Мультипротокол

· Разделяемая память

· NWLink IPX/SPX

Процесс общения клиента и сервера:

· Клиентское приложение обращается к драйверу

· Драйвер вызывает клиентскую библиотеку

· Клиентская библиотека вызывает соответствующий сетевой протокол и передает данные серверной библиотеке

· Серверная библиотека передает клиентские запросы SQL Server

Enterprise Manager – инструмент при помощи которого происходит администрирование SQL Server посредствам графического пользовательского интерфейса.

С помощью ЕМ можно:

· Создавать, редактировать и удалять Бд и их объекты;

· Создавать, редактировать и удалять пакеты преобразования данных. DTS используется при необходимости перемещения данных из одного места в другое, а также выполнения преобразования – данные считываются в одной форме, а затем записываются в другой;

· Управлять запланированными заданиями, такими как резервное копирование;

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

· Регулировать задание веб-публикаций

· Управлять системой безопасности, включая контроль таким объектом, как роли, логины, удаленные и взаимосвязанные серверы.

· Создавать каталоги полнотекстового поиска

· Управлять настройками конфигурации сервера

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

· Запускать некоторые другие средства SQL Server, такие как анализатор запросов и профайлер SQL Server.

Анализатор запросов Query Analyzer

Это средство для проведения интерактивных сеансов с выбранным SQL Server. В нем можно выполнять операторы. Утилита позволяем создавать специальные пакетные файлы *.sql, который будет содержать текст, введенного SQL запроса для дальнейшего использования.

SQL Server Service Manager

Утилита, выполняющая работу, связанная с запуском, остановкой и приостановкой работы SQL Server

 

 

В SQL Server полное имя объекта состоит из 4 частей:

[Имя_сервера.[Имя_базы_данных.[Имя_владельца.]]]Имя_объекта

 

Создание пользовательской базы данных можно выполнить с помощью инструкции Т-SQL:

CREATE DATABASE <имя_базы_данных>

[ON[PRIMARY]

[

([NAME=<логическое_имя_фаила>,]

FILENAME=<имя_фаила>

[,SIZE=<размер_БД>]

[,MAXSIZE=<максимальный_размер_в_Mегабайтах>]

[,FILEGROWTH=<размер_в_мегабайтах>|<величина_в_процентах>])

]

[FILEGROUP<имя_группы_фаилов>]

[<описание_аналогичное_выше>]

[LOG ON]

[<описание, аналогичное_выше>]

[COLLAGE<способ_сравнения>]

[FOR LOAD|FOR ATTACH] ]

ON – используется для определения местоположения файлов с самими данными и для указания расположения файла журнала. В первом случае ключевое слово PRIMARY означает, что идущие далее параметры файла относятся именно к первичной группе, предназначенной для физического хранения файлов. Если она пропущена, то первичным является первый файл в списке.

Name – соответствует логическому имени файла, т.е. внутреннему имени, используемому SQL Server при ссылке на файл (понадобиться для изменения размера БД и/или файла).

FILENAME – имя реального физического фа1ла. То файл будет размещен в подпапку \Data внутри папки Program Files\MSSQL (если вы меняли путь поумолчанию, при инсталляции, то в соответствующей папке установки), он примет тоже имя, что и база данных и расширение.mdf. Если вы работаете с файловым журналом, его название будет состоять из имени базы данных,

SIZE – определяетс размер БД. По умолчанию размер задается в мегабайтах, но указав суффикс КВ после числового значения, можно задать размер базы и в килоайтах. Учтите, что размер БД должен быть не меньше чем размер БД model/ Если вы не указали размер БД в параметре SIZE, то для нее будет установлен начальный размер, равный размеру БД model.

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

FILEGROWTH – в данной опции задается величина приращения файла базы данных при его заполнении. В качестве альтернативного варианта можно также задать величину приращения в процентах от текущего размера файла БД.

LOG ON – опция позволяет задать определенный набор файлов для хранения журнала базы данных и путь к ним. Если параметр не определен, SQL Server по умолчанию создает единственный файл журнала с размером в 25% от размера файла данных.

COLLATE – используется для управления порядком сортировки и сравнения символов, чувствительность к регистру. В предыдущих версиях SQL Server порядок сравнения мог устанавливаться только на уровне сервера в целом. В настоящее время вы можете задавать порядок сравнения не только для отдельной БД, но и для конкретных столбцов конкретной таблицы.

FOR LOAD – для обеспечения обратной совместимости с более разными версиями(данный параметр создавал начальный макет базы данных для последующей загрузка информации из резервной копии).

FOR ATTACH - применяется для подключения существующих БД к текущему серверу. Искомые файлы должны являться частью БД, которая была перед этим корректно отключена при помощи хранимой процедуры sp_detach_db.

 

ТИПЫ ОГРАНИЧЕНИЙ

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

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

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

 

ОГРАНИЧЕНИЯ КЛЮЧЕЙ

Существуют 4 общепринятых типа ключей:

первичный;

внешний;

альтернативный;

инверсный.

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

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

 

Ограничения первого ключа

Первичный ключ (primary key) представляет собой уникальный идентификатор строки.

Первичный ключ должен содержать уникальные значения (и поэтому не может принимать нулевые значения).

Создать первичный ключ при помощи команды CREATE TABLE либо команды ALTER TABLE.

CREATE TABLE Customers

(CustomerNo int NOT NULL PRIMARY KEY,

CustomerName varchar (25) NOT NULL)

 

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

ALTER TABLE Employees

ADD CONSTRAINT PK_EmployeeID

PRIMARY KEY (EmployeeID)

 

Ограничения внешнего ключа

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

<имя_столбца><тип_данных>

<допустимость_NULL_значения>

FOREYGN KEY REFERENCES <имя_таблицы>

(<имя_столбца>)

[ON DELETE {CASCADE NO ACTION}]

[ON UPDATE {CASCADE NO ACTION}]

_______________________________________________________________________

Пример:

 

CREATE TABLE OrderDetails(

OrderID int NOT NULL,

UnitPrice money NOT NULL,

PartNo varchar(10) NOT NULL,

8000

…

CONSTRAINT PK_OrderDetails PRIMARY KEY (OrderID, PartNo),

CONSTRAINT PK_Orde FPREYGN KEY (OrderID)

ON DELETE NO ACTION

ON UPDATE CASCADE

)

 

Добавление внешнего ключа в существующую таблицу:

= Первичный и внешний ключ может существовать для одного и того же поля.

= В каждой таблице может присутствовать от 0 до 253 внешних ключей.

= Единственное ограничивающее условие – данный столбец может иметь только один внешний ключ.

= На один и тот же столбец может ссылаться неограниченное количество таблиц.

 

ALTER TABLE Orders

ADD CONSTRAINT FK_EmployeeOrders

FOREYGN KEY (EmployeeID) REFERENCES

Employees (EmployeeID)

 

Ограничение уникальности

= Таблица может иметь более одного ограничения уникальности.

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

ALTER TABLE Employees

ADD CONSTRAINT AK_EmployeeSSN

UNIQUE (SSN)

Выполнив запуск хранимой процедуры EXEC sp_helpconstraint <имя_таблицы>, можно получить сведения об именах, критериях и статусе ограничений данной таблицы.

 

Ограничение проверки

ALTER TABLE Customers

ADD CONSTRAINT CN_CustomerPhone

CHECK (Phone

LIKE ‘ ([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9])’)

Нельзя применить к кома

Фиксированные роли сервера

 

Sysadmin	Выполнение любых действий на сервере (системные администраторы SQL Server).
Serveradmin	Настройка конфигурации, выключение сервера.
Setupadmin	Управление связанными серверами и процедурами, автоматически запускающимися при старте сервера.
Sedurityadmin	Управление учетными записями и правами на создание БД, а также для контроля журнала ошибок (доступ к log фаилу).
Processadmin	Управление процессами, запущенными на сервере (можно уничтожить долго выполняющийся процесс).
Dbcreator	Создание и модификация БД.
Diskadmin	Управление файлами сервера (контроль того, с какими файлами связана данная группа, подключение и отсоединение БД).
bylkadmin	Представление прав на выполнение команды BULK INSERT - массового копирования БД. Иначе подобная операция может быть выполнена только при помощи роли sysadmin.

 

Роли БД позволяет объединить пользователей в одну административную единицу и работать с ней как с обычным пользователем.

 

Фиксированные роли БД

 

db_owner	Полный доступ к БД(множество пользователей, обладающих правами владельца БД).
db_accessadmin	Создание и удаление пользователей БД из числа уже имеющихся
db_securityadmin	Не дает возможность создавать новых пользователей, но позволяет управлять разрешениями, ролями и членами ролей
db_ddladadmin	Выполнение любых команд DDL(но нельзя GRANT, DENY и REVOKEN)
db_backupoperator	Выполнение команд модуля DBCC(DBCC CHECKPOINT, DBCC DBREINDEX, и т п), а также команды BACKUP - резервного копирования БД
db_datareader	Возможность чтения данных во всех таблицах БД
db_datawriter	Право применения INSERT, UPDATE, DELETE в любой таблице БД
db_denydatareader	Запрет просмотра данных в любой таблице БД
db_denydatawriter	Запрет применения INSERT, UPDATE, DELETE во всех таблице БД

 

В отличае от сервера БД могут иметь пользовательские роли и роли приложения, которые создает администратор с помощью Enterprise Manager или Transact-SQL индивидуально для групп пользователей и групп приложений, наделяя их необходимыми правми доступа к конкретной БД.

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

Sp_addrole [@rolename=]<’имя_роли’>[,[@ownername=]<’владелец_роли’>]

 

В любую роль БД можно включать:

a) Пользователь сервера

b) Роли сервера

c) Пользователей Windows

d) Группы пользователей Windows

Добавление пользователей

Sp_addroleemember [@rolename=]<’имя_роли’>,

[@membername=]<’имя_пользователя’>]

EXES Sp_addrolemember ‘OutTestRole’, ‘RealUser”

 

Удаление пользователей из роли:

EXES Sp_droprolemember ‘OutTestRole’, ‘RealUser”

 

Удаление роли

EXES Sp_droprolemember ‘OutTestRole’

 

РОЛИ ПРИЛОЖЕНИЙ:

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

Они не могут использоваться для подключения к БД.

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

Далее в сеансе работы приложение будет обладать правами данной роли.

 

ИНДЕКСАЦИЯ В СУБД

 

Понятие индекса

Основная проблема в СУБД – это поиск нужных данных за минимальное время.

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

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

Поэтому для таблицы с данными создаются специальные таблицы для каждого критерия поиска, которые называются ИНДЕКСЫ.

Индекс – это структура данных для быстрого поиска записей в таблице по значению ключа.

 

МЕТОДЫ ОРГАНИЗАЦИИ ИНДЕКСА

Первичный ключ

a) Плотным индексом(некластерный индекс) Индексно прямой файл

b) Неплотным индексом(кластерный индекс) Индексно последовательный файл

c) Б-деревья

 

 

Вторичный ключ

a) Инвертируемые списки

 

 

Кластерный индекс

Алгоритм поиска данных

начало

Поиск индексного блока

Блок существует

Вычислить номер блока основного файла

да

нет

Алгоритм удаления записи:

Начало => поиск индексного блока => удаление записи в индексном блоке => запись индексного блока => чтение блока основного файла => пометить запись на удаление в блоке => запись блока основного файла => конец

 

 

Некластерный индекс

Алгоритм добавления данных

Начало = поиск индексного блока = чтение блока основной области = добавление записи в блок основной области = переполнение – (да) перестроение индекса - (нет) запись блока основной области = конец.

 

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

Начало = поиск индексного блока = чтение блока основной памяти = удаление записи в блоке основной области = запись блока основной области = конец.

 

Индекс Б-дерево

Со второго уровня используется неплотный индекс

 

 

ОПЕРАТОР СОЗДАНИЯ ИНДЕКСА.

СУБД всегда создает индекс для первичного ключа таблицы.

Для создания индексов для других полей используется оператор SQL.

CREATE [UNIQUE] INDEX имя_индекса

ON имя_таблицы (имя_столбци [ASC|DESC][….])

 

Пример. Создать индекс для таблицы ЗАКАЗ по вторичному ключу

CREATE INDEX Заказ_FK_инд

ON Заказ (MFR, КодТов)

 

В каждой СУБД оператор создания индексов содержит дополнительные предложения специфические для каждой СУБД.

 

ОПЕРАТОР СОЗДАНИЯ ИНДЕКСА

Оператор для создания индекса в transact – SQL

 

CREATE [UNIDUE] создание уникального индекса

[CLASTEED|NOCLASTERED] создание неплотного или плотного индекса

INDEX имя_индекса

ON {имя_таблицы|имя_представления}

(имя_столбца[ASC|DESC][….]) по возрастанию или убыванию

WITH

[<параметры>][….n]]]

[ON имя_файла_группы] расположение индекса в файлах ОС БД

<параметры> - это резервирование на каждой странице индекса

свободного пространства

{PAD_INDEX|FILLFACTOR = %запол. | степень заполнения свободного пространства.

IGNORE_DUP_KEY|DROP_EXISTING|

STATIDTICS_NORECOMPUTE|SORT_IN_TEMPDB

}

 

 

……………………………………………………………………………………………………

 

КСР на сервере. Защита. До сроков.