Лекция №22

7. Лекция: Использование отсоединения и присоединения для переноса данных: версия для печати и PDA  В данной лекции освещено использование репликации для переноса данных, в частности, рассмотрена репликация моментальных снимков, репликация транзакций и репликация слиянием

Если вам нужна уверенность в том, что вы знаете, какие данные переносите, и при этом вы можете вывести базу данных в автономный режим на некоторый период времени, использование отсоединения и присоединения для переноса данных, возможно, будет для вас подходящим вариантом. Главный недостаток метода отсоединения/присоединения заключается в том, что придется смириться с недоступностью базы данных на тот короткий промежуток времени, когда база данных будет отсоединена, потому что необходимо создать копию файла .mdf (mdf – это сокращение от master database file), что возможно только тогда, когда база данных отсоединена. Значит, база данных будет недоступна до тех пор, пока вы не завершите создание копии базы данных. Если база данных достаточно велика, возможно, не удастся создать копию в приемлемый промежуток времени.

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

Если ваше предприятие работает известное количество часов с определенным периодом простоя, то отсоединение/присоединение может оказаться для вас наилучшим вариантом. После окончания рабочего дня можно отсоединить базу данных, скопировать ее в нужное место, присоединить к серверу-адресату, а затем снова присоединить к серверу-источнику. Давайте выполним эту процедуру от начала до конца.

Отсоединяем базу данных

1. В меню Start (Пуск) выберите All Programs,. Microsoft SQL Server 2005, SQL Server Management Studio (Все программы, Microsoft SQL Server 2005, Среда SQL Server Management Studio). Соедините сервер-источник с Object Explorer (Обозревателем объектов).

2. Щелкните правой кнопкой мыши на базе данных, которую нужно перенести, и выберите из контекстного меню команды Tasks, Detach (Задачи, Отсоединение). При этом откроется диалоговое окно Detach Database (Отсоединение базы данных) (см. рис. вверху следующей страницы):

увеличить изображение

3. Установите флажок Drop Connection (Удалить соединения), а затем нажмите кнопку OK. После этого база данных будет отсоединена, благодаря чему файлы .mdf и .ldf станут доступными для перемещения или копирования в папку на сервере-адресате. Теперь скопируйте файлы .mdf или .ldf базы данных на целевой сервер.

Совет. Ниже приводится инструкция T-SQL для отсоединения базы данных.

EXEC sp_detach_db AdventureWorks

GO

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

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

1. В SQL Server Management Studio щелкните правой кнопкой на папке Databases (Базы данных) и выберите из контекстного меню команду Attach (Присоединить). При этом откроется диалоговое окно Attach Database (Присоединение базы данных) (см. рис. вверху следующей страницы):

увеличить изображение

2. Нажмите кнопку Add (Добавить), а затем выберите файл или файлы, которые нужно присоединить.

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

Совет. Если нужно присоединить копии файлов базы данных к серверу-источнику, в результате чего на одном сервере окажутся две изначально одинаковые базы данных, то при присоединении можно изменить имя базы данных. Для этого нужно только указать другое имя в столбце Attach As (Присоединить как) в первом списке.

3. После нажатия кнопки ОК вы получаете успешно отсоединенную базу данных.

Совет. Ниже приводится инструкция T-SQL для присоединения базы данных.

EXEC sp_attach_db @dbname = N'AdventureWorks',

@filename1 = N'd:\MSSQL\AdventureWorks.mdf',

GO

Если для переноса данных была выбрана стратегия Отсоединение/ Присоединение, то не забудьте снова присоединить базу данных, которую вы отсоединили. Если это не будет сделано, то приложения, которым известно это размещение базы данных, не будут работать, как следует, если вообще будут работать.

Использование репликации для переноса данных

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

• Репликация моментальных снимков

• Репликация транзакций

• Репликация слиянием

SQL Server 2005 поддерживает новый подход к репликации, который называется одноранговой репликацией. Этот подход в действительности подразумевает группу серверов, которые сконфигурированы на использование репликации транзакций для перемещения данных между серверами.

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

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

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

Настраиваем конфигурацию одноранговой репликации

1. Запустите SQL Server Management Studio.

2. Щелкните правой кнопкой мыши на узле Replication (Репликация) в дереве Object Explorer (Обозреватель объектов) и выберите из контекстного меню команду Configure Distribution (Настройка распространения). Будет запущен мастер Configure Distribution Wizard (Мастер настройки распространителя), который необходимо запустить на обоих серверах, если эти серверы ранее не были настроены как распределители. В этом упражнении мы согласимся с настройками мастера по умолчанию.

3. Затем нужно будет создать публикацию. Выберите один из серверов, на котором будет настроена публикация. Разверните узел Replication (Репликация) в Object Explorer (Обозревателе объектов) и щелкните правой кнопкой на папке Local Publications (Локальные публикации). Выберите из контекстного меню команду New Publication (Создать публикацию), чтобы запустить мастер New Publication Wizard (Мастер создания публикаций). В окне этого мастера можно указать базу данных и определить набор данных, подлежащих репликации. Здесь много настраиваемых параметров. Сначала задайте Publication Type (Тип публикации) как Transactional Publication (Публикация транзакций), а затем выберите таблицу для публикации (в данном примере выберите Address).

4. На странице Snapshot Agent (Агент моментальных снимков) установите флажок Create A Snapshot Immediately (Создать моментальный снимок немедленно).

5. На странице Agent Security (Агенты безопасности) введите имя действующего пользователя, учетные данные которого будет использовать Snapshot Agent (Агент моментальных снимков) в процессе работы.

6. Установите флажок Create The Publication (Создать публикацию) на странице Wizard Actions (Действия мастера), а затем укажите имя публикации на заключительной странице мастера.

7. Нажмите кнопку Finish (Готово), чтобы завершить процесс создания публикации. Публикация готова.

8. После того, как вы настроили публикацию, можно указать ее для одноранговой репликации. Выберите в Object Explorer (Обозревателе объектов) узел Local Publications (Локальные публикации). В правой панели вы должны увидеть только что созданную публикацию.

9. Щелкните правой кнопкой мыши на новой публикации и выберите из контекстного меню команду Properties (Свойства).

10. В диалоговом окне Publication Properties (Свойства публикации) выберите страницу Subscription Options (Параметры подписки). Здесь можно установить параметр Allow Peer-To-Peer Subscriptions (Разрешать одноранговые подписки) на True, как показано ниже. Нажмите кнопку ОК, чтобы закрыть это диалоговое окно.

увеличить изображение

11. В следующем действии убедитесь в том, что схемы одинаковы для всех баз данных. Это можно сделать, создав резервную копию базы данных на сервере-источнике, а затем восстановив базу данных на целевом сервере при помощи процедуры, которая объяснялась ранее в этой лекции.

12. Затем нужно сконфигурировать одноранговую топологию. Для этого щелкните правой кнопкой мыши на новой публикации и выберите из контекстного меню команду Configure Peer-To-Peer Topology (Конфигурация одноранговой топологии). После этого будет запущен мастер, который настроит публикации и подписки на настраиваемых серверах.

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

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

Использование служб SSIS для переноса данных

Из всех методов переноса данных SQL Server Integration Services, SSIS (Службы интеграции SQL Server) – самый гибкий и надежный метод. Они могут не только переносить данные, но и трансформировать их. Хотя возможность трансформации очень полезна, в этой книге она не рассматривается. Мы рассмотрим только различные варианты, доступные в SSIS для перемещения данных от системы-источника до целевой системы, любые сложные методы трансформации, возможные в SSIS, не рассматриваются.

В то время, как описанные ранее методы требуют, чтобы вы работали в среде SQL Server Management Studio, для использования служб интеграции SQL Server вам придется перейти в Visual Studio 2005 IDE (Интегрированная среда разработки). SQL Server поставляется с версией этого IDE, которая называется "SQL Server Business Intelligence Development Studio", BIDS. Если программа Visual Studio 2005 уже установлена на компьютере, используемом для разработки, то у вас уже есть два способа заполучить одни и те же функции.

Если вы запустите Visual Studio или BIDS, при условии, что вы установили клиент и инструменты разработки из SQL Server, то увидите различные параметры проекта. Если у вас установлены любые из инструментов разработки Visual Studio, вы увидите их в обеих средах. То же справедливо и для инструментов разработки SQL Server. Обе среды разработки отражают установленные вами компоненты.

Настраиваем свойства проекта SSIS

1. В меню Start (Пуск) выберите All Programs,. Microsoft SQL Server 2005, SQL Server Business Intelligence Development Studio (Все программы, Microsoft SQL Server 2005, SQL Server Business Intelligence Development Studio).

2. В меню File (Файл) выберите команду New (Создать), затем Project (Проект). Откроется окно New Project (Новый проект), показанное ниже. Это диалоговое окно содержит все типы проектов, которые доступны для создания. Для этого упражнения выберите из списка Project Type (Тип проекта) узел Business Intelligence Projects и шаблон Integration Services Project из списка Templates (Шаблоны). После этого можно изменить имя и путь к проекту и решению. Когда вы настроите все параметры в соответствии со своими предпочтениями, нажмите кнопку ОК,

Совет. Решение (Solution) может содержать много проектов (Projects).

увеличить изображение

Если вы в первый раз запустили Visual Studio или BIDS, потратьте немного времени на то, чтобы познакомиться с интерфейсом программы. Как видно из рисунка, этот интерфейс в корне отличается от интерфейса DTS (Data Transformation Services). Дизайн интерфейса был полностью изменен и в значительной степени модернизирован. Вы увидите также четыре вкладки, которые представляют собой области конструктора. В этом примере мы воспользуемся только областями конструктора вкладки Control Flow (Поток управления) и вкладки Data Flow (Поток данных).

увеличить изображение

Совет. Мы коснулись только тех возможностей SSIS, которые лежат на поверхности. Рекомендуется полностью изучить возможности инструментов.

3. Добавьте задачу Data Flow (Поток данных) в область конструктора вкладки Control Flow (Поток управления). Перетащите мышью элемент Data Flow Task (Задача "Поток данных") из панели элементов Control Flow Items (Элементы потока управления) в область конструктора вкладки Control Flow (Поток данных).

4. После добавления задачи можно либо дважды щелкнуть ее, либо перейти на вкладку Data Flow (Поток данных), чтобы открыть область конструктора этой вкладки.

5. Мы сконцентрируемся на получении данных от сервера-источника на целевой сервер. Чтобы выполнить необходимые настройки, придется создать два диспетчера соединений. Щелкните правой кнопкой мыши в панели Connection Managers (Диспетчеры соединений) и выберите из контекстного меню команду New OLE DB Connection (Создать соединение OLE DB). Выберите для Provider (Поставщик) значение Native OLE DB\SQL Native Client. Нажмите кнопку New (Создать), чтобы создать новое соединение. Введите Server Name (Имя сервера) и выберите имя базы данных. Нажмите кнопку ОК, чтобы создать первый диспетчер соединений. Те же действия необходимо выполнить на целевом сервере.

6. После создания диспетчера соединения перетащите мышью элемент OLE DB Source (Источник OLE DB), показанный на рисунке, из панели элементов Data Flow (Поток данных) в область конструктора вкладки Data Flow (Поток данных).

7. Перечеркнутая красная окружность на значке OLE DB Source показывает, что необходимо настроить источник. Дважды щелкните значок, чтобы запустить OLE DB Source Editor (Редактор источника "OLE DB").

увеличить изображение

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

9. Затем нужно будет перетащить мышью элемент OLE DB Destination (Целевой объект OLE DB), как показано ниже, из секции панели элементов Data Flow Destination (Целевой объект потока данных) в панель инструментов в области конструктора вкладки Data Flow (Поток данных).

На значке Destination object (Целевой объект) появляется перечеркнутая красная окружность, показывающая, что необходимо настроить параметры источника. Однако для целевого объекта придется также задать столбцы ввода. Для этого выделите значок OLE DB Source (Источник OLE DB), затем перетащите мышью зеленую стрелку с объекта Source (Источник) на объект Destination (Целевой объект), чтобы соединить эти два объекта.

10. После того, как вы установили соединение, можно настроить свойства источника, щелкнув правой кнопкой мыши значок OLE DB Destination (Целевой объект OLE DB) и выбрав из контекстного меню команду Edit (Изменить). В OLE DB Destination Editor (Редакторе назначения объектов OLE DB), который показан на следующем рисунке, укажите диспетчер соединения, выберите Data Access Mode (Режим доступа к данным), выделите целевую таблицу или представление и сопоставьте столбцы. Здесь можно настроить и некоторые другие свойства, которые могут быть полезны, когда вы тестируете свое решение, в том числе, свойства Table Lock (Блокировка таблицы) и Rows Per Batch (Строк на пакет). Необходимо просмотреть эти свойства,. чтобы определить, не походят ли они к вашей ситуации.

Совет. Выбирая один из параметров Fast Load (Быстрой загрузки) для Data Access Mode (Режима доступа к данным), вы используете параметр высокопроизводительной массовой загрузки при подкачке данных. При использовании этого режима доступны дополнительные параметры, которые можно найти в нижней части диалогового окна. Эти параметры разработаны для повышения производительности процесса загрузки. Дополнительную информацию о них можно найти в Электронной документации SQL Server 2005.

увеличить изображение

Совет. Прежде чем нажать кнопку ОК, необходимо настроить сопоставление столбцов. Однако если столбцы имеют одинаковые имена, можно щелкнуть элемент Mapping (Сопоставление) в левой части списка, после чего будет выполнено сопоставление по умолчанию.

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

Совет. Если вы хотите протестировать этот проект, можно выполнить его отладку, так же, как и для любого другого проекта Visual Studio, используя команду меню Debug (Отладка) или кнопку Debug (Отладка) в IDE.

Можно использовать RowCount Data Flow Transformation (Трансформация потока данных RowCount ), как показано на рисунке, как для назначения, и просмотреть перемещаемые данных при помощи службы Data Viewer (Просмотр данных).

Службы SSIS предоставляют гораздо больше гибкости в процессе проектирования и отладки, которые ранее обслуживались службами Data Transformation Services. Рекомендую поэкспериментировать с этим инструментом, поскольку можно обнаружить для него дополнительные сферы применения.

Преимущество использования SSIS заключается в том, что можно выполнить фильтрацию перемещаемых таблиц. Кроме того, можно выбрать для переноса несколько назначений, можно даже использовать цикл для нескольких назначений, если их схемы и загрузки идентичны. Если вам нужен конструктивный способ перемещения данных, то этот вариант может быть для вас лучшим вариантом. Дополнительную информацию о SSIS можно найти в Электронной документации SQL Server 2005 в теме "Обзор служб интеграции".

Автоматизация и планирование переноса данных

Перенос данных можно автоматизировать при помощи служб SQL Server Agent (Агент SQL Server) и SSIS (Служб интеграции SQL Server). Агент можно использовать изолированно, чтобы планировать различные методы перемещения данных, описанные выше. SSIS можно применять также для графического связывания различных задач, при этом используется преимущество встроенного механизма рабочего потока. В этом разделе мы рассмотрим от начала до конца процедуру настройки резервного копирования, а затем составим для нее расписание.

Планируем операцию резервного копирования при помощи службы Агент SQL Server

1. В SQL Server Management Studio откройте узел SQL Server Agent (Агент, SQL Server) в Object Explorer (Обозревателе объектов) и выберите папку Jobs (Задания).

2. Нажмите правой кнопкой мыши на папке Jobs (Задания) и выберите из контекстного меню команду New Job (Создать задание), как показано ниже. Это диалоговое окно, как и многие функции SQL Server 2005, было обновлено.

увеличить изображение

3. Затем настройте действия, необходимые для выполнения резервного копирования. Выберите страницу Steps (Шаги) в панели Select A Page (Выбор страницы), чтобы вывести на экран пустой список шагов задания. Нажмите кнопку New (Создать), чтобы открыть диалоговое окно New Job Step (Новый этап задания). Согласитесь с типом по умолчанию (сценарием Transact SQL Script (T-SQL)) и с базой данных по умолчанию master. В текстовом поле Command (Команда) введите сценарий резервного копирования, который мы разработали ранее в разделе "Только полная резервная копия". Этот сценарий можно также найти среди файлов примеров под именем FullBackupScript.sql. Введите имя для шага и нажмите кнопку ОК, чтобы закрыть диалоговое окно New Job Step (Этап нового задания).

4. После создания шага или шагов необходимо составить расписание. Перейдите на страницу Schedules (Расписания), затем нажмите кнопку New (Создать). Откроется окно New Job Schedule (Новое расписание задания), показанное ниже. В этом диалоговом окне можно составить расписание, которое будет отвечать вашим потребностям. Выберите нужные параметры и нажмите кнопку ОК, чтобы закрыть диалоговое окно New Job Schedule (Новое расписание задания).

увеличить изображение

Совет. Можно также нажать кнопку Pick (Выбрать) на странице Schedules (Расписания) и выбрать существующее расписание. Если у вас есть несколько заданий, которые должны выполняться по одинаковым или похожим расписаниям, можно выбрать расписание из списка существующих на сервере расписаний.

5. В завершение воспользуйтесь страницей Alerts and Notifications (Предупреждения и уведомления), чтобы настроить отправку предупреждений или уведомлений, которые позволят вам получить информацию об успешном или неуспешном завершении шага задания или шагов задания. Чаще всего для этой цели используют уведомления.

Страница Notifications (Уведомления) также позволяет автоматически удалить задание после выполнения, если нужно запустить задание только один раз. Внесите соответствующие изменения в уведомления и предупреждения, а затем нажмите кнопку ОК, чтобы закрыть диалоговое окно New Job Schedule (Новое расписание задания).

Можно также запланировать выполнение пакетов SSIS при помощи агента SQL Server. Задания агента поддерживают прямое развертывание пакетов. (Развернутые пакеты – это такие пакеты, которые можно установить на другой сервер). Можно указать SQL Server Integration Services Package (Пакет службы SQL Server Integration Services) в качестве типа в диалоговом окне New Job Step (Новый шаг задания), показанном ниже, и выбрать различные параметры пакета, в том числе, параметры конфигурации и выполнения.

увеличить изображение

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

Заключение

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

Краткий справочник по 6-7 лекциям

Чтобы	Выполните следующие действия
Создать резервную копию базы данных в SQL Server Management Studio	Щелкните правой кнопкой мыши на базе данных в окне Object Explorer (Обозреватель объектов) и выберите команды Task, Back UP (Задачи, Создать резервную копию).
Восстановить базу данных через интерфейс данных в окне Object Explorer (Обозреватель SQL Server Management Studio	Щелкните правой кнопкой мыши на базе объектов) и выберите из контекстного меню команду Restore Database (Восстановить базу данных).
Восстановить базу данных через интерфейс SQL Server Management Studio	Щелкните правой кнопкой мыши на базе данных в окне Object Explorer (Обозреватель объектов) и выберите команды Task, Detach (Задачи, Отсоединить).
Присоединить базу данных в SQL Server Management Studio	Щелкните правой кнопкой мыши на базе данных в окне Object Explorer (Обозреватель объектов) и выберите команды Task, Attach (Задачи, Присоединить).
Настроить одноранговую репликацию в SQL Server Management Studio	Щелкните правой кнопкой мыши на узле Replication (Репликация) в дереве Object Explorer (Обозреватель объектов) и выберите из контекстного меню команду Configure Distribution (Настройка распространения). Запустите этот мастер на обоих серверах группы репликации.
Настроить проект SSIS	Запустите SQL Server Business Intelligence Development Studio (BIDS). Выберите в меню команды File, New, Project (Файл, Создать, Проект), затем выберите узел Business Intelligence Projects (Проекты Business Intelligence) из списка Project Type (Тип проекта) и шаблон Integration Services Project из списка Templates (Шаблоны). Добавьте и сконфигурируйте диспетчер соединения, источник данных и назначение данных.
Запланировать операцию резервного копирования при помощи обозревателе Object Explorer (Обозреватель службы Агент SQL Server	В SQL Server Management Studio откройте узел SQL Server Agent (SQL Server, Агент) в объектов). Щелкните правой кнопкой мыши на папке Jobs (Задания), а затем выберите из контекстного меню команду New Job (Новое задание).