Цель работы: организация распределённой БД "Телефонный справочник" на базе MS SQL Server.

Часть первая. Демонстрация независимости от размещения

Исходные данные - БД в 3НФ

Расположение таблиц

Нетбук	Ноутбук
Places - таблица населённых пунктов Streets – таблица улиц Houses – таблица домов	Own – таблица владельцев Telephone – таблица с № телефонов

Описание процесса выполнения работы

1. На двух компьютерах выполнить скрипт создания пользователя stasya

/* Создание пользователя stasya с паролем abcdef; выполняется на обоих комп. */

CREATE LOGIN stasya WITH PASSWORD='abcdef';

CREATE USER stasya FOR LOGIN stasya;

/* Устанавливаем владельцем БД stasya пользователя stasya; выполняется на обоих комп. */

ALTER AUTHORIZATION ON DATABASE::stasya TO stasya;

/* Добавляем пользователя stasya в группу sysadmin для связывания серверов на обоих комп*/

EXEC sp_addsrvrolemember 'stasya','sysadmin'

2. В настройках MS SQL присвоить серверу имя (псевдоним)

Средства настройки → Диспетчер конфигурации SQL Server → Псевдонимы

На ноутбуке создать псевдоним для нетбука

Имя псевдонима	сервер	протокол	параметры
netbook	192.168.1.3	tcp	1433

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

Имя псевдонима	сервер	протокол	параметры
nоtebook	192.168.1.2	tcp	1433

3. Связать серверы

Объекты сервера → связанные серверы

Тип сервера установить в: Сервер SQL Server

Связанный сервер: netbok (псевдоним второго сервера; для ноутбука – это netbook)

Открыть вкладку Безопасность → Устанавливать с использованием следующего контекста безопасности

Удаленный вход: stasya

С паролем: abcdef

Выполнить на обоих компьютерах.

4. Создать синонимы на таблицы, находящиеся на другом компьютере для того чтобы не писать полное имя сервера (Например, NETBOOK.stasya.dbo.places).

На ноутбуке будут храниться две таблицы (own и telephone). Создаем их на ноутбуке и копируем данные из таблиц с нетбука, далее удаляем на нетбуке эти таблицы и создаем на них синононимы.

На ноутбуке

/*Создаём на ноутбуке синонимы/ссылки на таблицы, находящиеся на другом сервере*/

CREATE SYNONYM places FOR NETBOOK.stasya.dbo.places;

CREATE SYNONYM streets FOR NETBOOK.stasya.dbo.streets;

CREATE SYNONYM houses FOR NETBOOK.stasya.dbo.houses;

/* Создаём таблицы для хранения данных на ноутбуке */

CREATE TABLE own(ownid INTEGER NOT NULL PRIMARY KEY,owntype CHAR(1),houseid INTEGER,flatroom INTEGER,postcode INTEGER,own VARCHAR(256) NOT NULL,CHECK(owntype='К' OR (owntype='О' AND houseid IS NULL AND flatroom IS NULL)),CHECK(postcode>0));

CREATE TABLE telephone(phone CHAR(8) PRIMARY KEY,ownid INTEGER REFERENCES own(ownid) ON DELETE CASCADE,CHECK(patindex('[0-9][0-9]-[0-9][0-9]-[0-9][0-9]',phone)=1));

/* Копируем таблицы с нетбука на ноутбук */

INSERT INTO own SELECT * FROM NETBOOK.stasya.dbo.own;

INSERT INTO telephone SELECT * FROM NETBOOK.stasya.dbo.telephone;

На нетбуке

/*Создаём на нетбуке синонимы/ссылки на таблицы, находящиеся на другом сервере*/

CREATE SYNONYM own FOR NOTEBOOK.stasya.dbo.own;

CREATE SYNONYM telephone FOR NOTEBOOK.stasya.dbo.telephone;

5. Написать скрипт для выборки данных, выполнить его на двух машинах, посмотреть время выполнения.

/* Тестовый скрипт выборки данных - выбирает информацию о случайном номере телефона из БД */

DECLARE @tel VARCHAR(8); /* Переменная - случайный номер телефона */

DECLARE @i INTEGER; /* Переменная - итерация */

/* Переменная - временная таблица */

DECLARE @t TABLE(phone VARCHAR(8),owntype CHAR(1),postcode INTEGER,place VARCHAR(128),street VARCHAR(128),houseno VARCHAR(16),flatroom INTEGER,own VARCHAR(128));

SET @i=0; /* устанавливаем счётчик итераций в 0 */

WHILE (@i<10000) /* пока счётчик меньше 10 000 выполняем выборку информации */

BEGIN

/* Получаем случайный номер телефона */

SET @tel=CAST(round(RAND()*14+21,0) AS VARCHAR(2))+'-'+CAST(round(RAND()*89+10,0) AS VARCHAR(2))+'-'+CAST(round(RAND()*89+10,0) AS VARCHAR(2));

/* Делаем выборку данных из распределённой БД и вставку во временную таблицу строки с информацией о номере телефона */

INSERT INTO @t SELECT phone,owntype,postcode,place,street,houseno,flatroom,own FROM telephone LEFT JOIN own ON telephone.ownid=own.ownid LEFT JOIN houses ON houses.houseid=own.houseid LEFT JOIN streets ON streets.streetid=houses.streetid LEFT JOIN places ON places.placeid=streets.placeid WHERE phone=@tel;

/* Удаляем строку из временной таблицы */

DELETE FROM @t;

/* Увеличиваем номер итерации на 1 */

SET @i=@i+1;

END;

6. Зафиксировать время выполнения скрипта на каждом узле

1 узел (ноутбук)	2 узел (нетбук)
5 мин 57 сек	3 мин 3 сек
5 мин 57 сек	3 мин 8 сек
5 мин 56 сек	3 мин 3 сек
5 мин 59 сек	3 мин 6 сек
5 мин 57 сек	3 мин 6 сек
Среднее время выполнения:
5 мин 57 сек	3 мин 5 сек

Время выполнения скрипта на ноутбуке медленнее, так как с нетбука выбирается большой объем данных относительно объёма данных на ноутбуке.