Книги, научные публикации
Pages: | 1 | ... | 2 | 3 | 4 | 5 | 6 | ... | 10 | MCSA/MCSE, MCDBA Training Kit Exam 70-228 Microsoft SQL Server 2000 System Administration Microsoft-Press курс SA/MCSE, MCDBA ...
-- [ Страница 4 ] --10. В поле Initial Size (MB) в соответствующей строке введите 50.
11. В поле By Percent введите 15.
12. В группе элементов Maximum File Size установите переключатель в положение Restrict File Growth (MB) и в поле Restrict File Growth (MB) введите 100.
13. Перейдите на вкладку Transaction Log.
Просмотрите, какие параметры установлены по умолчанию для журнала транзакций.
14. В поле Initial Size (MB) вместо значения по умолчанию введите 20.
15. В поле By Percent группы элементов управления File Growth введите 25.
16. В группе элементов Maximum File Size установите переключатель в положение Restrict File Growth (MB), и в поле Restrict File Growth (MB) введите 75.
17. Щелкните OK, чтобы создать новую БД.
Заметьте, что в той ветви дерева консоли, где отображается структура БД, отобра жается новая БД SSEMDB.
18. Щелкните объект SSEMDB правой кнопкой и выберите Properties.
Откроется вкладка General диалогового окна SSEMDB Properties.
19. Перейдите на вкладку Data Files и просмотрите свойства файлов данных БД SSEMDB.
20. На вкладке Transaction Log просмотрите свойства файла журнала транзакций БД SSEMDB.
21. Чтобы закрыть окно SSEMDB Properties, щелкните Cancel.
22. Не закрывайте главное окно SQL Server Enterprise Manager.
Создание пользовательской базы данных с помощью оператора CREATE DATABASE При создании пользовательской БД в операторе Transact-SQL CREATE DATABASE необходимо указать ее имя. Все другие параметры не являются обязательными. Син таксис Transact-SQL позволяет задавать определенные свойства для нескольких фай лов данных, создавать пользовательские группы файлов, размещать дополнительные файлы данных в определенных группах файлов и задавать свойства для журналов тран закций. Полная информация о синтаксисе оператора CREATE DATABASE, а также ряд примеров, содержится в справочной системе SQL Server Books Online.
Примечание При изучении синтаксиса команд Transact-SQL используйте примеры, приведенные в SQL Server Books Online. Это самый простой способ быстро изучить новую команду.
Создание базы данных, состоящей из одного файла данных и файла журнала, с помощью Transact-SQL Выполните в SQL Query Analyzer следующий оператор, чтобы создать БД с именем TSQLDB. При этом одновременно с созданием БД не создаются дополнительные файлы данных и журналов транзакций и не определяются их свойства.
CREATE DATABASE TSOLDB Создание пользовательской базы данных Занвтие Создание базы данных, состоящей из нескольких файлов, с помощью Transact-SQL В следующем примере создается БД с именем TSQLDB, которая состоит из основно го файла данных размером 100 Мб, дополнительного файла данных размером 500 Мб и файла журнала транзакций размером 250 Мб. В этом примере определяются следу ющие свойства основного файла данных: начальный размер 100 Мб, приращение раз мера 25 Мб и максимальный размер файла данных 250 Мб. Для дополнительного фай ла данных устанавливаются следующие свойства: начальный размер 500 Мб, прираще ние размера 50 Мб и максимальный размер файла данных 1000 Мб. Для журнала тран закций в этом примере устанавливается начальный размер 250 Мб, приращение разме ра 40% и максимальный размер журнала транзакций 500 Мб.
CREATE DATABASE TSQLDB ( NAME = TSQLDB1, FILENAME = 'D:\SQL_Data\TSQLDB1.mdf, SIZE = 100, MAXSIZE = 250, FILEGROWTH = 25 }, ( NAME = TSQLDB2.
FILENAME = 'E:\SQL_Data\TSQLOB2.ndfw, SIZE = 500, MAXSIZE - 1000, FILEGROWTH = 50 ) LOG ON ( NAME = TSQLDB_Log, FILENAME = 'F:\SQLJ-og\TSQLDB2.1edf*, SIZE - 250, MAXSIZE = 500, FILEGROWTH = 40% ) Упражнение З. Создание БД в SQL Query Analyzer при помощи оператора Transact-SQL CREATE DATABASE В этом упражнении вы воспользуетесь SQL Query Analyzer и создадите БД с помощью оператора CREATE DATABASE.
Чтобы создать БД в SQL Query Analyzer с использованием оператора Transact-SQL >Х CREATE DATABASE 1. В меню Tools SQL Server Enterprise Manager выберите SQL Query Analyzer.
Откроется окно SQL Query Analyzer.
2. В панели инструментов SQL Query Analyzer щелкните кнопку Load SQL Script или нажмите сочетание клавиш Ctrl+Shift+P.
Откроется окно Open Query File.
3. Выберите путь к сохраненному файлу сценария C:\SelfPacedSQL\CH_6 и открой те файл сценария CreateDB.sql.
4. Просмотрите этот файл. Вот его текст:
CREATE DATABASE TSQLDB ON 156 Создание и конфигурирование пользовательских баз данных Глава ( NAME = TSQLD6_OATA, FILENAME = 'C:\Prograin Files\Microsoft SQL Server\MSSQL\Data\TSQLDB.mdf', SIZE = 10, MAXSIZE - 25, FILEGROWTH = 5 ) LOG ON ( NAME = TSQLDB^LOG, FILENAME - 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\TSQLDB.Idf', SIZE - 4, MAXSIZE = 12, FILEGROWTH = 40% ) 5. Выполните сценарий, сохраненный в файле CreateDB.sql.
В панели запросов выводится сообщение о создании БД TSQLDB. Размер файла TSQLDB_DATA - 10 Мб, файла TSQLDB_LOG - 4 Мб.
6. В панели инструментов щелкните кнопку Clear Window.
7. В панели запросов введите EXEC sp^helpdb TSQLDB и нажмите сочетание клавиш Ctrl+E, чтобы выполнить данный запрос.
8. В панели запросов отображаются свойства БД TSQLDB.
9. В панели инструментов щелкните кнопку Clear Window.
10. Сверните окно SQL Query Analyzer.
Создание баз данных и объектов БД в SQL Server Enterprise Manager с помощью сценариев Работая с SQL Server Enterprise Manager, вы можете использовать сценарии Transact SQL, с помощью которых создаюгся все существующие объекты БД (таблицы, ин дексы, представления, зарегистрированные пользователи, группы пользователей и учетные записи). Эти сценарии можно использовать для последующего повторного создания любого объекта БД в той же или в другой БД (например при восстановле нии в случае потери или нарушения целостности данных). Вы можете создать один сценарий, при помощи которого создаются все объекты БД, или отдельные сценарии для каждого объекта.
Чтобы создать сценарий Transact-SQL, на вкладке General диалогового окна Gene rate SQL Scripts щелкните правой кнопкой нужный объект и выберите All Tasks\Gene rate SQL Script (рис. 6-11).
Вы можете выбрать создаваемые сценарием объекты в списке. Если в БД не было создано ни одного объекта, список объектов останется пустым.
Определить дополнительные параметры сценария можно на вкладке Formatting. Для разных видов объектов здесь имеется свой набор параметров. По умолчанию сцена рий содержит операторы CREATE И DROP для каждого создаваемого объекта (рис. 6-12).
На вкладке Options (рис. 6-13) можно изменить следующие параметры, использу емые в сценарии: параметры доступа к различным объектам, в том числе права досту па к различным объектам и используемые учетные записи, параметры таблиц и со здаваемых файлов.
Занятие 1 Создание пользовательской базы данных Рис. 6-11. Вкладка General диалогового окна Generate SQL Scripts ale ЫД Si ripts SEL1PAI HXPU-1SQLDB CREATE TABLE SampleTable [SampteColumnl datetЩ NULL.
SampleUDT] GO Рис. 6-12. Вкладка Formatting диалогового окна Generate SQL Scripts Sll ГРЛССОС PIJ rSIJIliH Рис. 6-13. Вкладка Options диалогового окна Generate SQL Scripts 158 Создание и конфигурирование пользовательских баз данных Глава Примечание Вы можете написать один сценарий для создания схемы и всех объектов БД или создать отдельный сценарий для каждого объекта.
Упражнение 4. Генерация сценария, описывающего базу данных TSQLDB В этом упражнении вы воспользуетесь SQL Server Enterprise Manager и сгене рируете сценарий Transact-SQL для воссоздания БД TSQLDB.
*Х Чтобы сгенерировать сценарий Transact-SQL, описывающий базу данных TSQLDB 1. В дереве консоли SQL Server Enterprise Manager последовательно раскройте кон тейнеры Microsoft SQL Servers, SQL Server Group, экземпляра по умолчанию и за тем Ч контейнер Databases.
В списке баз данных экземпляра SQL Server по умолчанию БД TSQLDB отсут ствует, поскольку она была создана при помощи другой клиентской утилиты.
2. Щелкните Databases правой кнопкой и выберите Refresh.
Список БД обновится, и в нем появится TSQLDB.
3. Щелкните БД TSQLDB правой кнопкой и выберите All Tasks\Generate SQL Script.
Откроется диалоговое окно Generate SQL Scripts Ч SelfPacedSQL\TSQLDB.
4. Перейдите на вкладку Options.
5. В группе элементов управления Security Scripting Options пометьте флажок Script Database и щелкните ОК.
Откроется окно Save As.
в. В раскрывающемся списке Save In выберите папку C:\SelfPacedSQL\CH_6.
7. В поле File Name введите TSQLDB и щелкните Save.
8. Щелкните ОК в окне сообщения Scripting.
9. Разверните окно SQL Query Analyzer.
10. В панели инструментов SQL Query Analyzer щелкните кнопку Load SQL Script.
Откроется окно Open Query File.
11. Укажите путь к файлу C:\SelfPacedSQL\CrI_6 и откройте файл сценария TSQLDB.sql.
В самом начале сценария TSQLDB.sql содержатся операторы Transact-SQL DROP DATABASE и CREATE DATABASE. Кроме того, сценарий также содержит опера торы, устанавливающие различные параметры БД TSQLDB. Подробнее о пара метрах БД Ч в занятии 2 этой главы.
12. Закройте SQL Query Analyzer.
Резюме Пользовательские БД можно создавать в SQL Server Enterprise Manager или в SQL Query Analyzer при помощи сценариев Transact-SQL. Начинающие пользователи мо гут создавать простые БД с помощью мастера Create Database Wizard в SQL Server Enterprise Manager. Чтобы создавать более сложные БД, используйте непосредствен но SQL Server Enterprise Manager или создайте и сохраните сценарий Transact-SQL, a затем выполните его в SQL Query Analyzer. Если свойства файла данных и журнала транзакций не были определены явно, то при создании пользовательской БД SQL Server 2000 использует значения по умолчанию для всех не определенных пользователем свойств файла данных и журнала транзакций. При создании новая пользовательская БД наследует системные и пользовательские объекты БД model, а также ее параметры.
Настройка параметров базы данных Занятие 2 Х< GQ Занятие 2. Настройка параметров базы данных Помимо свойств файлов данных и журнала транзакций пользовательских БД, можно также задать ряд других параметров, которые определяют ее характеристики. На этом занятии вы познакомитесь с этими параметрами и научитесь определять и изменять параметры существующих БД, в том числе БД model.
Изучив материал этого занятия, вы сможете:
v" перечислить настраиваемые параметры БД;
Х/ просмотреть параметры БД;
S изменить настраиваемые параметры БД.
Продолжительность занятия Ч около 15 минут Параметры базы данных Параметры БД определяют различные аспекты стандартного поведения БД. Парамет ры пользовательской БД наследуются при ее создании от БД model. Есть пять типов параметров:
Х управление некоторыми автоматическими аспектами поведения БД (например автоматическое создание и поддержка данных статистики, автоматическое обнов ление данных статистики и уменьшение размера файлов БД);
Х использование различных видов курсоров и выбор типа курсора, используемого по умолчанию (например локальные или глобальные курсоры);
Х выбор модели восстановления (полная, записи копирования и простая);
Х совместимость с ANSI (например использование значений NULL, как определе но в ANSI, и использование идентификаторов, заключенных в кавычки);
Х режим доступа к БД (например только для чтения и доступ, предоставленный толь ко владельцу БД Ч dbo) В большинстве сред, как правило, администратор изменяет значения лишь неко торых из перечисленных параметров. К периодически изменяемым параметрам от носятся выбор модели восстановления БД (см. главы 5 и 8) и ограничение уровня пользовательского доступа к БД. Ограничение доступа к пользовательским БД необ ходимо при выполнении различных административных задач (допустим, при восста новлении БД) или при создании БД, доступной только для чтения. К параметрам, позволяющим ограничить доступ к БД, относятся: установка уровня доступа к БД только для чтения, разрешение устанавливать соединение с БД только владельцам БД и пользователям, обладающим ролями dbcreator и sysadmin на сервере БД, а также выбор режима монопольной работы с БД. Подробнее об остальных параметрах БД Ч в разделе Setting Database Options справочной системы SQL Server Books Online.
Просмотр значений параметров базы данных Просмотреть установленные значения параметров БД можно при помощи SQL Server Enterprise Manager или системной функции DATABASEPROPERTYEX. В SQL Server Enterprise Manager собраны наиболее часто изменяемые параметры. Чтобы просмо треть их, щелкните БД правой кнопкой, выберите Properties и в окне SSEMDB Pro perties перейдите на вкладку Options (рис. 6-14).
Глава 160 Создание и конфигурирование пользовательских баз данных Рис. 6-14. Значения параметров БД Чтобы определить, какая модель восстановления выбрана для БД TSQLDB, вос пользуйтесь системной функцией DATABASEPROPERTYEX. Выполните следующий запрос в SQL Query Analyzer:
SELECT OATABASEPROPERTYEX ('TSQLiJB', 'RECOVERY') Изменение значений параметров БД Значения наиболее часто изменяемых параметров БД можно установить, выбрав соот ветствующие параметры в SQL Server Enterprise Manager, либо выполнив оператор Transact-SQL ALTER DATABASE. Например, чтобы выбрать модель записи копирова ния для БД TSQLDB, выполните следующий запрос:
ALTER DATABASE TSQLDB SET RECOVERY BULK_LOGGED Примечание Отобразить или изменить значения некоторых параметров БД можно также с помошью системной хранимой процедуры sp_dboption. Однако она поддержи вается только для совместимости с предыдущими версиями SQL Server и не позволяет просматривать или изменять значения тех параметров БД, которые были добавлены в SQL Server 2000.
Резюме Параметры БД определяют ее поведение по умолчанию. Они наследуются от БД model.
В большинстве случаев вам потребуется изменять значения лишь нескольких парамет ров, а именно: выбрать соответствующую модель восстановления БД и ограничить уро вень доступа к БД для выполнения административных задач или создания пользова тельской БД, доступной только для чтения. Просмотреть текущие значения парамет ров БД можно при помощи SQL Server Enterprise Manager или системной функции DATAPROPERTYEX. Чтобы изменить эти параметры для всех новых БД, измените па раметры БД model. Чтобы изменись их для существующей БД, используйте SQL Server Enterprise Manager или оператор Transact-SQL ALTER DATABASE.
Занятие 3 Изменение размера базы данных Занятие 3, Изменение размера базы данных После того как вы создали пользовательскую БД, вам нужно будет изменять ее раз мер. Объем файлов данных обычно растет с течением времени;
возрастает также на грузка на систему, и, следовательно, файлы журнала транзакций заполняются быс трее. В ряде случаев вам нужно будет уменьшить размер файлов данных или журна лов транзакций. В SQL Server 2000 предусмотрен ряд механизмов управления изме нением размера БД. На этом занятии вы познакомитесь с тем, как использовать авто матические методы контроля размера БД, имеющиеся в SQL Server 2000. Вы также узнаете, как вручную увеличить размер файлов данных и журналов транзакций и как добавить дополнительные файлы в БД. Наконец, вы узнаете, как уменьшить размер файлов данных и журналов транзакций.
Изучив материал этого занятия, вы сможете:
/ использовать автоматическое увеличение размера файлов БД;
использовать автоматическое уменьшение размера файлов БД;
Х/ увеличивать и уменьшать размер файлов данных вручную;
S ^ увеличивать и уменьшать размер файлов журнала транзакций вручную;
добавлять дополнительные файлы БД и файлы журнала в БД.
S Продолжительность занятия Ч около 30 минут Автоматическое увеличение размера файлов При создании пользовательской БД по умолчанию выбирается автоматическое уве личение размера как файлов данных, так и журнала транзакций при заполнении со ответствующих файлов. Автоматическое увеличение размера файлов рекомендуется использовать на этапе внедрения прикладных программ и в небольших прикладных системах, где нет администратора БД, контролирующего свободное пространство на диске (например в настольных приложениях). Автоматическое увеличение размера файлов используется для настольных БД. Однако в большинстве сред, где SQL Server 2000 используется как сервер БД, автоматическое увеличение размера файлов долж но использоваться только как крайняя, временная мера, поскольку может существен но снизить производительность системы. При каждом увеличении размера файла нагрузка на систему существенно возрастает. Кроме того, частое увеличение диско вого пространства, выделенного для файлов БД, приведет к фрагментации диска, осо бенно если этот диск используется также другими прикладными программами. По этому в реальных рабочих системах, предназначенных для промышленной эксплуа тации, необходимо следить за объемом данных и журналов транзакций, при необхо димости увеличивая или уменьшая их размер вручную во время наименьшей загру женности БД.
Примечание Помимо увеличения нагрузки во время изменения размера файлов, авто матическое увеличение размера файлов приводит также к небольшой, но постоянной до полнительной системной нагрузке, обусловленной непрерывным контролем размера каж дого файла. Непрерывный контроль за размером файлов необходим, чтобы определить, когда его нужно увеличивать.
Создание и конфигурирование пользовательских баз данных Глава & Администратор БД должен контролировать объем данных и журналов транзакций, как можно реже используя, а по возможности и вовсе отказавшись от автоматическо го увеличения размера файлов. Если вы все же используете автоматическое увеличе ние, устанавливайте достаточно большую величину приращения размера файлов, что бы рост файла происходил нечасто. Кроме того, необходимо всегда ограничивать максимальный размер файла, чтобы диск не переполнился вследствие неограничен ного увеличения размера файла (это особенно важно, если на диске размешены так же другие файлы). Чтобы контролировать рост файла и свободное пространство, име ющееся на диске, установите соответствующее системное оповещение. Когда размер файла увеличивается автоматически и объем свободного пространства на диске уменьшается до заданного значения, система выдает соответствующее предупрежде ние. Подробнее о настройке системных оповещений Ч в главе 13.
Изменить параметры автоматического увеличения размера файлов можно из SQL Server Enterprise Manager, а также при помощи оператора Transact-SQL ALTER DATA BASE. Например, чтобы отключить автоматическое увеличение размера для основ ного файла данных БД TSQLDB, выполните следующий запрос:
ALTER DATABASE TSQLDB MODIFY FILE { NAME = 'tsqldb.data'. FILEGROWTH = 0 ) Примечание Если задать для параметра FILEGROWTH нулевое значение, размер фай ла не превысит изначально заданного значения. При переполнении файла добавить в него новые данные не получится. Чтобы добавлять данные в базу, нужно добавить до полнительные или увеличить размер существующих файлов данных. При переполнении файла журнала транзакций завершить следующие транзакции не удастся до тех пор, пока не будет увеличен размер файлов журнала транзакций или пока в базу не будут добавлены дополнительные журнелы транзакций, либо не будет выполнено архивиро вание журнала транзакций с последующим его усечением.
Упражнение 5. Установка параметров автоматического увеличения размера файлов в SQL Server Enterprise Manager В этом упражнении вы установите значения параметров автоматического уве личения размера файлов в SQL Server Enterprise Manager.
*Х Установите значения параметров автоматического увеличения размера файлов в SQL Server Enterprise Manager 1. В дереве консоли SQL Server Enterprise Manager последовательно раскройте кон тейнеры Microsoft SQL Servers, SQL Server Group, экземпляра SQL Server по умол чанию и затем Ч Databases.
2. Щелкните Northwind правой кнопкой и выберите команду Properties.
Откроется вкладка General диалогового окна Northwind Properties, где указаны различные свойства БД Northwind.
3. Перейдите на вкладку Data Files.
Для основного файла данных БД Northwind выбрано автоматическое увеличение размера с величиной приращения, составляющей 10% от размера файла. Макси мальный размер файла не ограничен.
4. В группе элементов управления Maximum File Size установите переключатель в по ложение Restrict File Growth (MB). В поле Restrict File Growth (MB) введите 25.
Занятие 3 Изменение размера базы данных -| со 5. Перейдите на вкладку Transaction Log.
6. В группе элементов управления File Growth установите переключатель в положение In Megabytes. В поле In Megabytes наберите 5.
7. Щелкните ОК., чтобы сохранить изменения БД Northwind.
Автоматическое уменьшение размера файлов Размер файлов БД можно не только автоматически увеличивать, но и уменьшать. Вы можете установить параметры пользовательской БД таким образом, чтобы ее размер автоматически уменьшался, если в файле данных или файле журнала транзакций бу дет много свободного дискового пространства. По умолчанию автоматическое умень шение размера БД не используется. Хотя оно иногда полезно для настольных прило жений и на этапе внедрения прикладных программ, не рекомендуется использовать его в реальных промышленных системах из соображений производительности. Как и в случае автоматического увеличения размера файлов, так и в случае использования автоматического уменьшения их размера производительность системы может суще ственно снизиться. Размер файлов данных и журналов транзакций нужно устанавли вать таким образом, чтобы использовать выделенное пространство максимально эф фективно. Если нужно уменьшить размер файла, выполните эту задачу вручную, ког да пользователи не работают активно с БД. Также крайне неэффективно использо вать обе возможности автоматического контроля размера БД Ч автоматическое уменьшение и увеличение размера файла. Чтобы задействовать автоматическое умень шение размера файлов БД, используйте SQL Server Enterprise Manager или оператор Transact-SQL ALTER DATABASE.
Изменение размера файлов данных вручную Наблюдая за изменением объема свободного пространства в файлах данных, вы мо жете оценить, когда требуется увеличивать размер файлов данных. Выполнять это нужно во время минимальной нагрузки на систему. Для увеличения размера файла БД можно использовать SQL Server Enterprise Manager или оператор Transact-SQL ALTER DATABASE. Например, чтобы установить размер основного файла данных БД TSQLDB в 15 Мб, выполните следующий запрос:
ALTER DATABASE TSQLDB MODIFY FILE ( NAME = 'tsqldb_data', SIZE = 15 ) Для уменьшения размера файла данных вручную используйте SQL Server Enterprise Manager или оператор Transact-SQL DBCC SHRINKFILE. Например, чтобы умень шить размер основного файла данных БД TSQLDB, выполните следующий запрос, указав значение размера файла 7 Мб:
USE TSQLDB DBCC SHRINKFILE ( 'tsqldb_data', 7 ) При выполнении оператора DBCC SHRINKFILE операция производится только над текущей БД. Во время уменьшения размера файл данных сокращается с коьца.
По умолчанию все использованные страницы в сокращаемой части файла данных пе ремещаются на свободное пространство в начале файла данных, чтобы его можно было уменьшить до нужного размера. Можно также сократить файл данных до послед него выделенного экстента без перемещения страниц или перемещать страницы без сокращения файла данных. Файл данных невозможно сократить до размера меньше го, чем объем данных, которые он содержит, или размера БД model. С помощью 7- 164 Создание и конфигурирование пользовательских баз данных Глава DBCC SHRINKFILE можно сократить файл данных до размера меньшего, чем его первоначальный размер. Следует также помнить о том, что можно сокращать файл данных, когда пользователи работают с БД, но не в то время, когда создаются резерв ные копии БД или файл журнала транзакций.
Оператор DBCC SHRINKFILE можно использовать, чтобы уменьшить размер всей БД, включая все файлы данных и все журналы транзакций, до определенного процента от текущего размера. Используя оператор DBCC SHRINKFILE, нельзя уменьшить БД до размера меньше первоначального.
Примечание Оператор ALTER DATABASE используется только для увеличения раз мера файла.
Упражнение 6. Изменение размера файла данных с помощью SQL Server Enterprise Manager В этом упражнении вы измените размер файла данных средствами SQL Server Enterprise Manager.
^ Чтобы изменить размер файла данных с использованием SQL Server Enterprise Manager 1. В дереве консоли SQL Server Enterprise Manager последовательно раскройте кон тейнеры Microsoft SQL Servers, SQL Server Group, экземпляра SQL Server по умол чанию и затем Ч Databases.
2. Щелкните Northwind правой кнопкой и выберите Properties.
Откроется вкладка General диалогового окна Northwind Properties, где перечисле ны различные свойства БД Noithwind.
3. Перейдите на вкладку Data Files.
В столбце Space Allocated (MB) таблицы свойств файлов данных просмотрите раз мер основного файла данных БД Northwind Ч он должен быть равен 3 Мб.
4. В поле Space Allocated (MB) введите 7.
5. Щелкните OK, чтобы сохранить изменения в БД Northwind.
Проверка свободного места и изменение размера журнала транзакций вручную Помимо регулярных проверок объема свободного пространства в файлах данных, необходимо регулярно проверять наличие свободного места в журналах транзакций.
Если используется модель восстановления отдельных операций или модель восста новления результатов копирования, необходимо регулярно создавать резервные ко пии журнала транзакций для усечения журналов, чтобы освободить пространство для дополнительных записей. Если используется модель восстановления отдельных опе раций, то при выполнении операций, затрагивающих большие объемы данных, а так же при операциях загрузки данных довольно быстро могут привести к переполнению файла журнала транзакций. Если иы замечаете, что ваши журналы транзакций запол няются быстрее, чем следует, нужно либо чаще архивировать журналы транзакций, либо позволять SQL Server 2000 автоматически увеличивать размер журналов тран закций всякий раз, когда в нем больше не окажется свободного места, или увеличи Занятие 3 Изменение размера базы данных вать размер журналов транзакций вручную. Увеличить размер журнала транзакций можно с помошью SQL Server Enterprise Manager или оператора Transact-SQL ALTER DATABASE аналогично тому, как увеличивается размер файла данных.
Переполнение журнала транзакций приводит к остановке работы SQL Примечание Server 2000.
Чтобы уменьшить размер журнала транзакций вручную, используйте SQL Server Enterprise Manager или оператор DBCC SHRINKFILE. Уменьшить размер всех фай лов данных и журналов транзакций одновременно можно при помощи SQL Server Enterprise Manager или оператора DBCCSHRINKDATABASE, Аналогично файлу дан ных, сокращение журнала транзакций происходит с конца. При этом сокращение выполняется отдельными модулями, основной единицей которых является виртуаль ный файл журнала. Если сокращаемый журнал транзакций содержит неактивные вир туальные файлы в конце, он будет уменьшен на их размер, чтобы размер был макси мально приближен к требуемому. Требуемый размер округляется до верхнего значения размера следующего виртуального файла журнала. Например, если вы решили сокра тить размер журнала, состоящего из шести виртуальных файлов, с 300 Мб до 150 Мб, то его удастся сократить до 180 Мб при условии, что виртуальный файл в конце журна ла транзакций пуст.
Если в конце журнала транзакций нет достаточного количества пустых виртуаль ных файлов, чтобы обеспечить необходимый объем свободного пространства, SQL Server 2000 освобождает максимально возможный объем. SQL Server 2000 помещает в последний активный виртуальный файл, находящийся в конце файла журнала, до полнительные записи так, чтобы MinLSN переместился в начало файла после завер шения всех длинных транзакций и всех процессов репликации, где участвуют какие либо транзакции (рис. 6-15).
Виртуальный Виртуальный Виртуальный Виртуальный журнал 1 журнал 2 журнал 3 журнал И -< Ч Допол Х;
;
нительные записи ^Е и w\/nua п& Т Т t ?
linLSN Указанный пользователем Конец Начало размер, до которого следует логического логического сократить журнал журнала журнала Рис. 6-15. Уменьшение размера журнала транзакций путем добавления дополнительных записей После этого SQL Server 2000 отправляет сообщение о необходимости заархивиро вать журнал транзакций, чтобы удалить виртуальные файлы журнала в конце файла.
После архивирования нужно снова выполнить команду DBCC, чтобы сократить жур нал транзакции до желаемого размера (рис. 6-16).
Глава Х gg | Создание и конфигурирование пользовательских баз данных Виртуальный Виртуальный Виртуальный Виртуальный журнал 1 журнал 2 журнал 3 журнал Г Конец Начало логического логического журнала журнала Рис. 6-16. Журнал транзакций после уменьшения Создание дополнительных файлов данных и файлов журнала транзакций Если нужно создать дополнительные файлы данных или журнала транзакций (когда свободное пространство находится на другом диске или если необходимо повысить производительность системы, разместив файлы БД на нескольких дисках), вы може те использовать SQL Server Enterprise Manager или оператор Transact-SQL ALTER DATABASE. Создавая дополнительные файлы БД с помошью SQL Server Enterprise Manager, вы просто добавляете файл в диалоговом окне свойств БД. По умолчанию все дополнительные файлы данных добавляются в основную группу файлов (рис. 6-17). О том, как работать с пользовательскими группами файлов, Ч в занятии 4 этой главы.
Рис. 6-17. Добавление новых файлов в БД Занятие 3 Изменение размера базы данных В следующем примере в БД TSQLDB добавляется файл при помоши оператора Transact-SQL ALTER DATABASE.
ALTER DATABASE TSQLDB ADD FILE {NAME - 'TSQLDB2_DATA', FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\TSQLDB2.ndf, SIZE = 10, MAXSIZE - 25, FILEGROWTH = 5 ) Резюме Через некоторое время после установки и эксплуатации SQL Server 2000 вам потребу ется дополнительное пространство для размещения файлов данных и журналов тран закций. Вы можете разрешить SQL Server 2000 увеличивать размер файлов автомати чески, но в большинстве сред эта мера используется только как временная. Для по вышения производительности системы нужно постоянно следить за размером фай лов данных, чтобы по мере заполнения файлов данными увеличивать их размер вруч ную. Увеличение вручную позволяет выполнять эту операцию в то время, когда на грузка на систему минимальна. Если в журналах транзакций остается мало свободно го места, вы можете либо увеличить их размер, либо чаще их архивировать. Помимо увеличения размера существующих файлов БД, вы можете добавлять дополнительные файлы данных и журналы транзакций. Размер файлов БД также можно уменьшать ав томатически или вручную. Для повышения производительности рекомендуется второй способ.
^ gg Создание и конфигурирование пользовательских баз данных Глава Занятие 4 Размещение файлов БД на нескольких дисках В большинстве систем на основе SQL Server 2000 для повышения отказоустойчивости и производительности системы в целом, а также для снижения времени на восстанов ление после сбоев для размещения файлов БД и журналов транзакций используются несколько физических дисков. Это также упрощает техническое обслуживание систе мы. На этом занятии вы познакомитесь с различными способами размещения файлов на нескольких дисках Ч от просто размещения каждого из файлов данных и журналов транзакций на отдельном диске до совместного использования RAID и групп файлов для очень больших БД.
.
Изучив материал этого занятия, вы сможете:
^ определить подходящий уровень RAID для вашей системы, чтобы оптимизировать производительность и обеспечить отказоустойчивость;
S рассказать об использовании RAID и групп файлов в очень больших БД;
^ оптимизировать способ размещения файлов для обеспечения высокой производительности и минимизации времени восстановления системы после сбоев;
^ переместить файлы БД.
Продолжительность занятия Ч около 30 минут Общие сведения о RAID Система RAID состоит из двух или более дисков, которые обеспечивают более высо кую производительность и отказоустойчивость по сравнению с отдельным диском большого объема за меньшие деньги. Поддержка RAID обеспечивается специальным программным обеспечением, установленным на одном из дисков в специальном раз деле (эти RAID относятся к так называемым аппаратным RAID) или самой Windows (эти RAID относятся к программным RAID). Поскольку при применении программ ных RAID SQL Server 2000 использует ресурсы процессора совместно с RAID, для достижения максимальной производительности системы рекомендуется использовать аппаратные RAID. Однако эффективность систем с программными RAID все же выше, чем систем, где RAID вообще не используется.
Примечание Аппаратные RAID поддерживают горячую замену дисков (hot swappable drives), благодаря чему можно заменить испорченный диск без остановки системы. Не которые из них поддерживают также горячее резервирование дисков (hot standby drives), при этом дополнительные диски уже установлены. Следует отметить, что уровень RAID 10 реализован только в аппаратных RAID.
Как правило, в системах на основе SQL Server 2000 используются следующие уров ни RAID: О, 1, 5, и 10 (также иногда называемый 1+0). SQL Server интерпретирует систему RAID как отдельный диск, где можно разместить один файл. В табл. 6-2 пе речислены все уровни RAID и кратко перечислены их рабочие характеристики.
Замятие 4 Размещение файлов БД т нескольких дисках Табл. 6-2. Уровни RAID и их производительность Уровень RAID Характеристики Преимущества и недостатки Состоит из двух или Обеспечивает самую высокую производитель О более дисков. Запись ность операций чтения и записи;
а также данных на диск осуще- использует 100% объема диска. Однако ствляется блоками, рав- RAID 0 не защишен от аппаратных сбоев.
номерно распределенны- Поломка одного диска приведет к потере всех данных в дисковом массиве. Поэтому ми между всеми дисками.
Этот уровень также назы- не рекомендуется использовать уровень О вают дисками с чередова- для хранения данных в тех средах, где критически важна возможность хранения нием записи. Это самый простой для реализации данных без потерь уровень Производительность операций чтения Как правило, состоит из двух дисков. Данные за- повышается почти вдвое по сравнению с системами, где используется один диск.
писываются на оба диска.
Этот уровень называют Кроме того, обеспечивается зашита от также зеркализацией (или аппаратных сбоев. Относительно низка производительность операций записи, сдвоенными дисками, если используются два и используется только 50% объема диска контроллера) Производительность операций чтения Состоит из трех или более высока, эффективно используется дисковое дисков. Запись данных пространство и обеспечивается защита на диск осуществляется от аппаратных сбоев. Однако производи блоками, равномерно распределенными между тельность операций записи сравнительно низка. Некоторая доля общего объема всеми дисками;
кроме диска используется для записи данных блока того, добавлен дополни тельный блок четности, четности (доля этого блока от общего объема диска составляет 1/п, где п Ч общее используемый для восста число дисков в массиве) новления данных Обеспечивает самую высокую Состоит из четырех или 10(1+0) производительность опера более дисков. Запись дан ций чтения и записи и ных на два или более дис ков осуществляется рав- защиту от аппаратных сбоев.
Использует только 50% номерно размешенными блоками, для которых объема диска затем создаются зеркаль ные копии на том же числе дисков, которое используется для разме щения данных оригинала.
Этот уровень называют также зеркализацией с чередованием записи Создание и конфигурирование пользовательских баз данных 170 Глава б Основные сведения о группах файлов Группы файлов бывают трех типов Ч основные, пользовательские и по умолчанию.
Каждая БД может иметь не более 256 групп файлов. SQL Server 2000 всегда содержит по крайней мере одну группу файлов Ч основную. Группа файлов может состоять из нескольких файлов данных, размещенных на нескольких дисках. Журналы транзак ций не могут входить в группы фактов. Основная группа файлов содержит основной файл данных, где хранятся системные таблицы. Когда вы создаете в БД дополнитель ные файлы данных, по умолчанию они помещаются в основную группу файлов. Ког да вы создаете объекты БД (например таблицы и индексы) и размешаете данные в этих объектах, SQL Server 2000 использует свободное место в каждом из этих файлов данных, входящих в одну группу файлов, распределяя данные между файлами равно мерно, а не заполняет до конца сначала один файл данных, затем Ч второй и т. д.
Такой равномерный метод заполнения позволяет разместить БД на нескольких дис ках, что приводит к повышению производительности системы. Например, если в си стеме на основе SQL Server 2000 имеется четыре диска, вы можете на одном размес тить файл журнала транзакций, а на оставшихся трех Ч файлы данных (по одному на каждом диске).
Вы можете не размещать все дополнительные файлы данных в одной группе фай лов, а создать пользовательские группы файлов, где будут размешены дополнитель ные файлы. В системе, где создано несколько групп файлов, можно указать группу файлов, где будут размещаться новые объекты БД. Такой подход может обеспечить некоторый выигрыш в производительности, однако требует со стороны администра тора больших усилий и большего опыта в оптимизации производительности системы.
Если при создании объекта БД не была выбрана группа файлов, этот объект будет по мещен в группу файлов, выбранную по умолчанию. Выбранной по умолчанию после установки и первого запуска сервера является основная группа файлов. Этот параметр впоследствии можно изменить при помощи оператора Transact-SQL ALTER DATA BASE, выбрав в качестве группы но умолчанию другую группу файлов.
Примечание Выбор в качестве группы по умолчанию пользовательской группы фай лов позволяет избежать переполнения основного файла данных, которое приводит к невозможности добавлять новые з:шиси в системные таблицы.
Конфигурирование дисковой подсистемы SQL Server Чтобы обеспечить максимальную производительность, отказоустойчивость и мини мизировать время восстановления данных при сбое, вы должны стремиться достичь лучших показателей производительности операций чтения и записи для файла жур нала транзакций, файлов данных -л БД tempdb, не жертвуя при этом такими парамет рами, как отказоустойчивость или время, необходимое для восстановления системы после сбоя.
Примечание Для повышения производительности рекомендуется использовать контрол лер диска системы Small Computer System Interface (SCSI), а не Integrated Drive Elect ronics (IDE) или Enhanced Integrated Drive Electronics (EIDE). Контроллеры SCSI более интеллектуальны, они могут работать с несколькими потоками ввода Ч вывода одно временно, и их быстродействие не зависит от более медленных устройств ввода вывода, участвующих в обмене данными (например CD-ROM).
Занятие 4 Размещение файлов БД на нескольких дисках Журнал транзакций Необходимо правильно выбрать дисковую подсистему для журнала транзакций с уче том того, что основная его функция Ч обеспечение возможности восстановления данных в случае поломки одного или нескольких дисков, где размещены файлы дан ных. При выборе и конфигурировании дисковой подсистемы должно учитываться также и то, что операции записи в журнал транзакций выполняются последовательно и синхронно, но операции чтения выполняются только при создании резервных ко пий БД, при отмене транзакций, а также в процессе восстановлении БД при запуске SQL Server 2000. При чтении журнала транзакций для создания резервной копии на грузка на систему может существенно возрасти.
В соответствии со своей основной функцией журналы транзакций не должны раз мещаться на тех же дисках, что и файлы данных. Очевидно, что, если файлы данных и журналы транзакций размещены на одном физическом диске, вы не сможете вос становить данные при сбое диска. Если на SQL Server 2000 имеется несколько БД, то в качестве минимальных мер по предотвращению потери данных вам следует разме стить файлы одной БД на том же физическом диске, что и журнал транзакций другой БД. Это обеспечит возможность восстановления каждой из БД при поломке одного из дисков.
Следующий шаг в оптимизации производительности журнала транзакций Ч это раз мещение журналов транзакций на отдельных дисках для каждой БД. Если вы выделя ете для журнала транзакций отдельный диск, головка диска остается на месте в пери од от одной операции записи в журнал до следующей. Операции чтения данных из журнала также будут выполняться быстрее, поскольку данные будут размещаться на диске последовательно. Таким образом, выделение отдельных дисков для файлов дан ных и журналов транзакций позволяет существенно повысить производительность, а также уменьшить время восстановления после сбоя.
Дальнейшие меры по оптимизации производительности журнала транзакций это использование системы RAID 1. Такое решение обходится дороже (поскольку для такой системы требуется вдвое больше дискового пространства), однако оно обеспе чивает существенный выигрыш в производительности и защиту от аппаратных сбоев.
Использование RAID 1 почти удваивает производительность операций чтения дан ных с диска (это особенно важно при выполнении резервного копирования данных) и сокращает время простоя (если один диск испорчен, то считываются данные с дру гого диска). Операции записи данных на диск выполняются несколько медленнее, хотя и быстрее чем для RAID 5. Вы можете уменьшить стоимость этого решения, со кращая размер журнала транзакций (а следовательно, объем и стоимость дисков) и чаше архивируя журнал транзакций.
Файлы данных При выборе дисковой подсистемы для файлов данных критическими факторами, ко торые необходимо учитывать, являются недопустимость потери данных и минимиза ция времени простоя. Как уже упоминалось, первая мера по повышению производи тельности, отказоустойчивости и снижению времени простоя БД Ч это размещение файлов данных и журналов транзакций на разных дисковых подсистемах, а также использование отдельных дисков для размещения файлов данных. При размещении файлов данных на отдельных дисках запросы на чтение и запись данных (операции дискового ввода Ч вывода) не конкурируют с запросами на чтение и запись данных на других дисках.
172 Создание и конфигурирование пользовательских баз данных Глава Следующий шаг к оптимизации дисковой системы Ч это повышение производи тельности операций ввода Ч вывода. Хотя объем данных, которые могут храниться на одном большом и на нескольких маленьких дисках, может быть одинаковым, раз мещение данных на нескольких дисках повышает производительность ввода Ч выво да (особенно когда используется несколько контроллеров). Это обеспечивается бла годаря специальным возможностям SQL Server 2000, выполняющего параллельный просмотр данных за счет использования отдельных потоков операционной системы, по одному на каждый диск. Существуют разные способы размещения данных на не скольких дисках.
Первый способ Ч это использонание нескольких размещенных на отдельных дис ках файлов данных, объединенных в одну группу файлов. Можно также использовать RAID, чтобы разместить один файл (и хранящиеся в нем данные) на нескольких фи зических дисках, что позволяет достичь большего выигрыша в производительности.
Использование RAID также может обеспечивать отказоустойчивость. Если вы обла даете информацией о том, как выполняется доступ к данным в вашей системе, то можете также использовать несколько групп файлов вместо RAID (или как дополне ние к нему), размещая на отдельных дисках те таблицы и индексы, к которым доступ осуществляется наиболее часто. Эго позволяет повысить производительность ввода Ч вывода. Однако в большинстве случаев RAID обеспечивает преимущества, которые вы получаете при использовании групп файлов, не создавая дополнительной нагрузки для администратора системы. При работе с очень большими БД (VLDB) может пона добиться использовать несколько систем RAID и объединять файлы RAID в группы файлов для повышения производительности и упрощения поддержки БД.
Учитывая сложности администрирования групп файлов, администраторы БД обычно предпочитают решения на основе RAID и избегают использовать группы файлов. Как правило, большинство администраторов выбирает RAID 5, эффективно использующий дисковое пространство и обеспечивающий отказоустойчивость. Не достатком является относительно низкая производительность операций записи. RAID 5 подходит для систем, где требуется высокая производительность операций чтения и средняя производительность операций записи. Если же нужна высокая производи тельность записи, следует выбрать RAID 0 или RAID 10.
Примечание Если вы используете RAID 5, то в случае поломки одного диска в то время, когда для восстановления потерянных данных используется блок четности, про изводительность очень низка (хотя система продолжает работать во время восстановле ния данных).
При выборе между RAID 0 и RAID 10 предпочтительнее выбрать RAID 10 из-за менее высокой надежности и медленного восстановления данных в RAID 0. Однако RAID 0 значительно дешевле, и этот фактор может сыграть решающую роль при вы боре. Но при планировании долгосрочных вложений следует учесть, что RAID обеспечивает производительность, аналогичную RAID 0 и надежность, аналогичную RAID 1, и при достаточно продолжительной эксплуатаиии системы суммарная сто имость времени простоя из-за сбоев и поломок диска может сравниться со стоимос тью RAID 10.
Если нужно использовать несколько систем RAID, можно разместить каждый файл данных в одной группе файлов или использовать несколько групп файлов (как правило, каждая система RAID вообще представляет собой отдельный файл данных).
Если вы уже определили, к каким объектам и как будут осуществлять доступ пользо ватели системы, и у вас имеется достаточный опыт в оптимизации производительно Занятие 4 Размещение файлов БД на нескольких дисках сти, вы можете добиться некоторого повышения производительности, используя не сколько групп файлов. В среде VLDB использование отдельных групп файлов из со ображений снижения затрат на поддержку БД может определять некоторые параметры файлов данных. Например, вам может понадобиться создавать архивные копии фай лов или групп файлов VLDB по отдельности, чтобы оптимизировать процесс резервно го копирования и восстановления данных. О создании резервных копий и восстанов лении данных Ч в главе 8.
База данных tempdb При выборе дисковой подсистемы для БД tempdb следует принимать во внимание, что эта БД используется только для временного хранения рабочих файлов (например промежуточных наборов результатов, используемых в сложных запросах и операциях DBCC). Оптимизация производительности БД tempdb означает возможность обраба тывать большое число операций чтения и записи. Обеспечивать отказоустойчивость и оптимальное время восстановления данных в этой базе не нужно, так как tempdb создается заново при каждом запуске SQL Server 2000.
Первым шагом к оптимизации производительности является размещение файла данных tempdb на отдельном диске, чтобы исключить возможность конкуренции с другими объектами БД при доступе к диску. Второй шаг Ч использование отдельного дискового контроллера для этого диска. Наконец, если производительность tempdb низка и это является фактором, влияющим на снижение производительности всей системы, используйте RAID 0. Обеспечивать отказоустойчивость для tempdb не тре буется, поскольку в этой базе данные не хранятся постоянно.
Перемещение файлов данных и журналов транзакций Вы можете отсоединять файлы данных и журналы транзакций от экземпляра SQL Server 2000 и снова присоединять их к тому же самому или к другому экземпляру.
Отсоединение используется при перемещении БД на другой экземпляр SQL Server или на другой сервер. Оно также используется для перемещения данных и журналов транзакций на другие физические диски. Отсоединить и снова присоединить БД и связанные с ней физические файлы можно с помощью SQL Server Enterprise Manager или оператора Transact-SQL.
Примечание При перемещении или размещении файлов данных и журналов тран закций на разделе диска с файловой системой NTFS проверьте права доступа учетной записи, используемой службой SQL Server. Она должна иметь полный доступ к этим файлам.
Отсоединение и присоединение баз данных при помощи SQL Server Enterprise Manager Чтобы отсоединить БД с помощью SQL Server Enterprise Manager, щелкните ее пра вой кнопкой и выберите All Tasks\Detach Database (рис. 6-18).
Если с этой БД работают пользователи, щелкните кнопку Clear, чтобы разорвать пользовательские соединения и завершать процесс отсоединения БД. Вы можете так же обновить статистические сведения о БД перед ее отсоединением. Рекомендуется выполнять обновление статистических сведений о БД перед ее перемещением на но сители, предназначенные только для чтения данных (например на компакт-диск).
Создание и конфигурирование пользовательских баз данных Рис. 6-18. Окно Detach Database После отсоединения БД вы можете переместить один или несколько физических файлов в другое место, например на жесткий диск или дисковую матрицу RAID. Что бы снова присоединить БД с помощью SQL Server Enterprise Manager, щелкните Databases правой кнопкой и выберите All Tasks\Attach Database. Откроется окно, по зволяющее присоединить БД (рис. 6-19).
ШШ Рис. 6-19. Окно Attach Database В диалоговом окне Attach Database введите полное имя и путь к основному файлу данных (чтобы выбрать путь, щелкните кнопку Browse). Основной файл данных со держит информацию о местоположении других файлов, входящих в состав БД. SQL Server 2000 использует информацию, хранящуюся в основном файле и отображает все дополнительные файлы данных и файлы журнала транзакций, которые необходимо присоединить. Если SQL Server 2000 не удается обнаружить какой-либо из этих фай лов, он будет помечен в списке красным крестиком (рис. 6-20). Файлы будут помече ны как ненайденные, если вы переместите дополнительные файлы данных или фай лы журнала транзакций не в тот же самый каталог, куда был перемещен основной файл данных. В этом случае нужно указать путь самостоятельно. В поле Current File Location для каждого перемещенного файла укажите текущее место его расположе ния. В этом диалоговом окне вы можете также изменить имя перемещенной БД.
В поле Specify database owner укажите владельца присоединяемой БД и щелкните ОК.
Примечание Если нужно присоединить более 16 файлов, вы должны использовать оператор Transact-SQL CREATE DATABASE с инструкцией FOR ATTACH.
Занятие 4 Размещение файлов БД на нескольких дисках f Рис. 6-20. Присоединение БД с помощью SQL Server Enterprise Manager Отсоединение и присоединение БД при помощи операторов Transact-SQL Чтобы отсоединить БД при помощи оператора Transact-SQL, используйте системную хранимую процедуру sp_detach_db. Например, чтобы отсоединить БД TSQLDB без обновления статистических сведений о БД, выполните следующий оператор:
Sp_detach_db TSQLDB, TRUE Чтобы присоединить БД при помощи оператора Transact-SQL, используйте сис темную хранимую процедуру sp_attach_db. При этом можно указать до 16 имен фай лов, включаемых в БД, которую вы хотите присоединить. В списке имен файлов обя зательно должно быть указано имя основного файла данных, так как этот файл со держит системные таблицы, где хранится информация о других файлах, входящих в состав БД. Список имен файлов должен также содержать все файлы, перемещенные после отсоединения БД. Например, чтобы присоединить БД TSQLDB, выполните следующий оператор:
sp_attach_db TSQLDB1, йfilename"! = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\TSQLDB.mdf' Резюме При использовании нескольких дисков для оптимизации производительности систе мы, основанной на SQL Server 2000, самое простое решение Ч разместить журналы транзакций и файлы данных на разных дисках. Этот способ гарантирует возможность восстановления данных в случае поломки диска. По возможности следует размешать каждый из файлов SQL Server 2000 на отдельном диске. Более удачное (но и более дорогостоящее) решение Ч использовать RAID I для размещения журнала транзак ций. Для размещения файлов данных лучше всего использовать RAID 10. Если ис пользовать RAID 10 окажется слишком дорого, рассмотрите возможность использо вания RAID 0 или RAID 5.
176 Создание и конфигурирование пользовательских баз данных Глава Закреплениематериала ?j Приведенные ниже вопросы помогут вам лучше усвоить основные темы дан ной главы. Если вы не сумеете ответить на вопрос, повторите материал соот ветствующего занятия. Правильные ответы приведены в приложении Воп росы и ответы в конце книги.
1. Вы хотите создать пользовательскую БД, включающую в себя несколько файлов данных, размешенных на разных физических дисках. У вас мало опыта в составле нии операторов Transact-SQL. Как проще всего создать такую БД?
2. Опишите два метода, с помощью которых можно изменить модель восстановле ния БД.
3. Вы создали новую БД, используя установленные по умолчанию свойства для жур нала транзакций. Вы создали резервные копии новой БД и БД master. После того как вы загрузили большой объем данных в новую базу, размер журнала транзак ций стал очень большим. Почему размер журнала настолько увеличился и как его уменьшить?
4. Вы администрируете небольшую систему БД на платформе Windows 2000 Server.
Хотя объем БД составляет менее 1 Гб, нагрузка на систему достаточно велика (прежде всего при выполнении операций записи данных), и вы хотите повысить ее производительность. У вас не хватает бюджета для приобретения аппаратного RAID, и вы не знаете, к каким таблицам и объектам БД чаще всего обращаются пользователи. Вы уже разместили журнал транзакций и файл данных на разных физических дисках. Какие сраннительно недорогие способы дальнейшего повы шения производительности системы можно использовать?
ГЛАВА Заполнение базы данных Занятие 1. Импорт и преобразование данных Занятие 2. Обзор служб DTS Занятие 3. Обработка данных графическими средствами DTS Занятие 4. Работа с пакетами DTS Занятие 5. Утилита Ьср и оператор Transact-SQL BULK INSERT В этой главе Здесь рассказывается о заполнении БД из существующего источника данных. Мы обсудим проблемы преобразования, согласованности и проверки данных, возникаю щие при импорте информации из одного или нескольких источников в БД SQL Server 2000. Вы изучите основные средства заполнения БД, предоставляемые SQL Server 2000, и научитесь использовать их в соответствующих ситуациях.
Прежде всего Для изучения материалов этой главы вам потребуются:
Х компьютер, соответствующий минимальным требованиям к конфигурации, пере численным в главе 2;
Х Microsoft Windows 2000 Server, установленный на разделе диска с файловой систе мой NTFS;
Х компьютер с сетевым именем SelfPacedCPU, сконфигурированный как контрол лер домена SelfPacedSQL.MSFT;
Х один установленный по умолчанию и по крайней мере один именованный экзем пляр SQL Server 2000.
Заполнение базы данных Глава 1. Импорт и преобразование данных Созданную пользовательскую БД следует заполнить данными. Обычно это включает в себя импорт и преобразование имеющихся данных из одного или нескольких вне шних источников (например других БД) в БД SQL Server 2000. Здесь рассказывается о проблемах импорта данных из внешних источников. Вы вкратце познакомитесь с основными средствами импорта и преобразования данных Ч службами DTS, утили той Ьср и оператором Transact-SQL BULK INSERT Ч и их возможностями, Изучив материал этого занятия, вы сможете:
S рассказать об оценке качества и согласованности данных внешнего источника, предшествующей импорту данных;
s описать преобразования, выполняемые при импорте данных из существующих источников;
S рассказать о предоставляемых SQL Server 2000 средствах импорта данных.
Продолжительность занятия - около 15 минут Импорт данных Заполнение пользовательской БД SQL Server 2000 часто включает в себя импорт име ющихся данных из внешних источников. Источниками могут быть БД Microsoft и сторонних компаний, электронные таблицы и текстовые файлы. Перед импортом данных из внешнего источника следует выполнить описанные ниже подготовитель ные действия, чтобы оценить данные и определить этапы процесса импорта. Выпол няя эти действия, вы также сможете выбрать подходящее средство для импорта дан ных.
Х Оцените степень согласованности данных внешнего источника. Вполне возмож но, что при первоначальном вводе данных источник не обеспечивал их согласо ванность: например, в одних случаях названия штатов вводились в виде двухзнач ных аббревиатур, а в других Ч полностью.
Х Определите, потребуются ли дополнительные столбцы. Может случиться, что дан ные внутренне согласованы, но не содержат отдельных столбцов, значения кото рых во внешнем источнике предполагались, Ч например коды выхода на между городнюю или международную линию связи.
Х Решите, нужно ли менять существующий формат данных. Возможно, что имею щиеся данные внутренне согласованы, но хранятся не в том формате, какой вам хотелось бы использовать в конечной БД. Так, вам может потребоваться изменить формат даты или преобразовать числовые значения в более удобные строковые значения, например цифры 1, 2. и 3 в строки неудовлетворительно, лудовлетво рительно и лотлично соответственно).
Х Подумайте, следует ли менять имеющиеся столбцы данных. Возможно, что суще ствующие данные внутренне согласованы, но содержат столбцы, требующие агре гации или разделения. Так, вам может потребоваться поместить имя и фамилию в отдельные столбцы, или получить на основе ежедневных данных значение за не делю или за месяц.
Х Решите, будет ли импорт данных единовременным или периодичным. Так, вы можете однократно перенести данные с устаревшей системы, которая больше не Импорт и преобразование данных Занятие будет использоваться, или периодически (например еженедельно или ежемесяч но) переносить информацию с другой рабочей системы.
Х Определите, как будет осуществляться доступ к существующим данным. Доступен ли внешний источник напрямую, и есть ли у вас разрешения на непосредствен ный доступ к данным? (Если нет, данные будут экспортированы в формат, под держиваемый SQL Server, например в текстовый файл с символами-разделителями.) Преобразование данных средствами служб DTS Оценив данные всех внешних источников, следует решить, что делать дальше. В не которых случаях необходимые изменения данных можно выполнять непосредствен но во внешнем источнике, однако чаще всего при этом нарушается работа существу ющих приложений (например, когда вы добавляете столбцы или изменяете формат данных) или тратится слишком много времени (например, когда вы вручную реали зуете отсутствовавшую ранее согласованность данных). Все эти изменения можно осуществить как после заполнения БД SQL Server, очистив данные при помощи вре менных таблиц и операторов Transact-SQL, так и непосредственно в процессе им порта, Изменение данных в процессе их импорта/экспорта называется DTS-транс формацией (DTS tranformation). DTS-трансформация имеет место, когда данные, еще не достигшие конечной точки, обрабатываются средствами одной или нескольких операций или функций. Данные в источнике при этом не изменяются. Благодаря трансформациям в процессе импорта/экспорта данные можно легко чистить, преоб разовывать и проверять на соответствие сложным критериям, Определив тип и глубину необходимых преобразований, вы сможете выбрать под ходящее средство импорта и время трансформации данных. Зачастую на выбор сред ства переноса данных и степень автоматизации преобразований также влияет то, бу дет ли импорт данных однократной или периодически выполняемой операцией. Про цесс импорта, особенно при работе с большими объемами данных, следует тщатель но спланировать и протестировать, используя для этого подмножества данных.
Средства преобразования данных В SQL Server 2000 имеются различные средства импорта и экспорта данных, которые обладают разными возможностями по извлечению и преобразованию подмножеств данных из имеющихся источников (табл. 7-1).
Табл. 7-1. Средства преобразования данных Средство Описание Службы DTS Службы DTS Ч это графическое средство импорта, экспорта и преобра зования данных, способное напрямую работать с разнообразными источниками. DTS создают пакеты, которые можно выполнять по распи санию. Кроме того, DTS позволяют импортировать и экспортировать схему объектов БД (метаданные) между экземплярами SQL Server Утилита bcp Вер Ч это утилита командной строки, предназначенная для загрузки данных из текстового файла в таблицу или представление SQL Server 2000 (либо из таблицы или представления SQL Server 2000 в текстовый файл) с использованием ODBC. Утилита bcp обладает ограниченными возможностями трансформации данных и требует файлов неявного формата. Работа с БД Microsoft и сторонних производителей осущест вляется в два этапа 180 Заполнение базы данных Глава Табл. 7-1. (окончание) Средство Описание Оператор BULK INSERT предназначен для загрузки данных ш Оператор Transact-SQL текстового файла формата ASCII в таблицу или представление SQL BULK INSERT Server 2000 (но не наоборот) с использованием OLE DB.
Функциональность (и ограничения) этого оператора аналогичны возможностям утилиты Ьср, и его можно включать в пакеты DTS Примечание Перемещать данные между источниками можно и другими способами, которые обычно не используются для начального заполнения БД. К этим способам относятся резервное копирование и восстановление (см. главы 8 и 9), репликация (см, главу 15), сценарии ActiveX, а также операторы Transact-SQL INSERT и SELECT INTO, использующие распределенные загросы (см. главу 12).
Резюме При заполнении БД из внешнего источника следует оценить данные последнего и определить, нужны ли какие-то трансформации в процессе импорта и после него.
Решите, будет ли импорт данных одноразовой или периодически выполняемой опе рацией. Определите также, как вы будете обращаться к данным источника Ч напря мую или через экспортированный текстовый файл. Принятые вами решения помогут выбрать средство и определить время трансформации данных.
Занятие 2 Обзор служб DTS Занятие 2. Обзор служб DTS Службы DTS (Data Transformation Services) Ч это мощный набор графических утилит и программируемых объектов, предназначенный для импорта, экспорта и преобразо вания данных между разнообразными источниками и получателями. Здесь рассказы вается о компонентах пакетов DTS, включая подключения, задачи, трансформации и управление ходом обработки. Вы узнаете о вариантах хранения пакетов DTS, а также научитесь работать с утилитами DTS, включая мастер DTS Import/Export Wizard (Dtswiz.exe), конструктор DTS Designer и средства выполнения пакетов DTS.
Изучив материал этого занятия, вы сможете:
s описать структуру пакета DTS;
S перечислить источники данных, к которым службы DTS могут подключаться напрямую;
Х/ описать доступные типы трансформации данных;
S описать ход обработки пакета DTS;
S рассказать о вариантах хранения пакетов DTS;
S рассказать об утилитах DTS.
Продолжительность занятия Ч около 15 минут Пакеты DTS Пакеты DTS позволяют подключаться к источнику данных, копировать и управлять данными и объектами БД, выполнять различные задачи как отдельные задания в со ставе пакета, а также преобразовывать данные и сохранять преобразованные данные и объекты БД в тот же самый источник данных или в другое место. Об успешном или неудачном выполнении пакета можно уведомить процесс или пользователя, включая вложение файла в сообщение электронной почты. Пакет DTS состоит из отдельных этапов, называемых задачами DTS (DTS tasks);
для управления порядком выполне ния задач используются константы предшествования (например, если текущий этап выполнился успешно, выполняется одна задача, а если неудачно Ч другая). Создать пакет DTS можно средствами мастера DTS Import/Export Wizard, доступного в кон структоре DTS Designer, или программно, Типы подключений DTS Для пакета DTS следует указать действительный источник и приемник данных, к ко торым он будет подключаться. В процессе выполнения пакет может также подклю чаться к дополнительным источникам данных (например к таблицам поиска).
В табл. 7-2 перечислены типы подключений, которые можно устанавливать при со здании пакета.
Табл. 7-2. Типы подключений Тип подключения Описание Соединение с источником Подключение к стандартной БД (например БД Microsof;
данных (Data source SQL Server 2000, Microsoft Access 2000, Oracle, dBase connection) или Paradox), OLE DB-подключение к ODBC-источник;
данных, электронной таблице Microsoft Excel 2000, HTML источнику данных или любому другому OLE DB-постав Заполнение базы данных Глава Табл. 7-2. (окончание) Тип подключения Описание шику. Необходимые параметры соединения с источником данных задаются в свойствах подключения Соединение с файлом Соединение с текстовым файлом (обычно использующим символы-разделители). Формат файла определяется (File connection) свойствами подключения. Можно использовать разные форматы, например с символамиразделителями или полями фиксированной длины Канальное соединение Соединение с промежуточным файлом (.UDL-файл), (Data link connection) который содержит строку подключения для открытия OLE DB-соединения, преобразуемую во время выполнения пакета При канальном соединении параметры подключе ния можно поместить в отдельный.UDL-файл и затем редактировать строку соединения (например менять источник данных), не затрагивая пакет DTS Задачи DTS Пакет DTS может выполнять множество задач как последовательно, так и параллель но. Параллельные задачи выполняются независимо друг от друга, используя для по вышения производительности отдельные потоки ОС, а последовательные задачи Ч в определенном порядке, который зависит от успеха, ошибки или завершения выпол нения предыдущего этапа. Задача DTS Ч это отдельная единица работы, являющаяся частью процесса переноса и преобразования данных (например загрузка содержимо го текстового файла в таблицу SQL Server 2000). Задачи DTS, преобразующие дан ных, например Parallel Data Pump, Transform Data и Data Driven Query, основаны на компоненте DTS, который называется каналом данных DTS (DTS data pump). Канал данных DTS Ч это поставщик служб OLE DB, предоставляющий интерфейсы и сред ства импорта, экспорта и преобразования данных между OLE DB-источниками и приемниками.
В SQL Server 2000 имеется ряд задач DTS, которые осуществляют преобразование данных, копирование и управление данными и метаданными в виде отдельных зада ний. Практически все эти задачи доступны в конструкторе DTS Designer (табл. 7-3) и программно. Задача Parallel Data Pump доступна только программно.
Табл. 7-3. Задачи, доступные в конструкторе DTS Designer Категория Задача Описание Задачи копиро- Bulk Insert Запускает из пакета DTS оператор Transact-SQL BULK вания и управ- INSERT. Эта задача Ч самый быстрый способ загрузить ления данными данные в таблицу или представление, однако в процессе и метаданными ее выполнения не регистрируются ряды, при вставке которых произошла ошибка. Чтобы записать информацию о записях, вызвавших ошибки, в файл исключений, воспользуйтесь задачей Transform Data Execute SQL Запускает в процессе выполнения пакета операторы Transact-SQL. Позволяет выполнять ряд операций, включая удаление таблицы или запуск хранимых процедур Занятие 2 Обзор служб DTS Табл. 7-3. (окончание) Категория Описание Задача Copy SQL Копирует объекты БД SQL Server (метаданные) из одного Server экземпляра SQL Server в другой. Позволяет перемещать Objects объекты между экземплярами SQL Server 7.0, между экземплярами SQL Server 2000, а также из экземпляра SQL Server 7.0 в экземпляр SQL Server Transfer Набор задач, копирующих информацию уровня сервера Database (задача Copy SQL Server Objects копирует только сведения, Objects специфичные для конкретной БД) между экземплярами SQL Server. Эти задачи используются мастером Сору Database Wizard и включают в себя Transfer Database, Transfer Error Messages, Transfer Logins, Transfer Jobs и Transfer Master Stored Procedures Задачи, преоб- Transform Копирует, преобразует и вставляет данные из источника разующие Data в приемник. Это простейшая реализация канала данные данных в DTS Data Driven Основываясь на данных ряда, выбирает, настраивает Query и выполняет к нему один из нескольких операторов Transact-SQL (например UPDATE или DELETE).
Используйте эту задачу, если задачи Transform Data и Bulk Insert не соответствуют требованиям вашего приложения Задачи, функ- ActiveX Выполняет сценарий ActiveX. Используйте эту задачу для ционирующие Script написания кода, который будет выполнять функции, как задания недоступные в конструкторе DTS Designer Dynamic Выбирает данные из внешнето источника и задает Properties полученные значения указанным свойствам пакета.
Внешним источником может быть.INI-файл, файл данных, запрос, глобальная переменная, переменная окружающей среды или константа Execute Выполняет в ходе процесса обработки другие пакеты DTS.
Package He используйте эту задачу рекурсивно Ч может возникнуть ошибка переполнения стека, и консоль ММС закроется Execute Запускает исполняемую программу или пакетный файл.
Process Эта задача позволяет любое стандартное приложение, например Microsoft Excel, однако обычно ее используют для запуска пакетных файлов или бизнес-приложений, работающих с источником данных File Transfer Загружает данные с удаленного сервера или интернет Protocol ресурса по протоколу FTP. Задача FTP и Ftp.exe используют одинаковый метод подключения Send Mail Выполняет отправку почтового сообщения как отдельную задачу. Например, можно уведомить администратора об успехе или ошибке резервного копирования. Для использования этой задачи на экземпляре SQL Server следует предварительно установить MAPI-клиент В процессе импорта службы DTS могут трансформировать данные на уровне стол бцов. При работе с конструктором DTS Designer для такой трансформации можно Глава Заполнение базы данных воспользоваться задачей Transform Data или Data Driven Query или написать сцена рий ActiveX. В Visual Basic и Visual C++ трансформацию можно осуществить про граммно или при помощи задачи Parallel Data Pump. Для трансформации данных сна чала следует сопоставить один или несколько столбцов источника одному или не скольким столбцам приемника, и затем указать, какие именно преобразования нуж ны. В табл. 7-4 перечислены возможные типы трансформации.
Табл. 7-4. Возможные типы трансформации данных Тип трансформации Описание Копирует данные из одного столбца источника в один столбец Copy Column приемника (или из нескольких Ч в несколько).
При необходимости автоматически усекает текст, не генерируя сообщений об ошибках ActiveX Script С помощью сценария ActiveX преобразует (и усекает) данные между одним и более столбцами источника и приемника, обрабатывая данные по рядам Date Time String Переносит значение даты или времени из исходного столбца в конечный, преобразуя его в другой формат. Тип данных в обоих столбцах должгн быть строковым Lowercase String Переносит строковые данные из исходного столбца в конечный, преобразуя их в символы нижнего регистра и, при необходимости, приводя данные к нужному конечному типу и усекая их. Тип данных в обоих столбцах должен быть строковым Uppercase String Переносит строковые данные из исходного столбца в конечный, преобразуя их в символы верхнего регистра и, при необходимости, приводя данные к нужному конечному типу и усекая их. Тип данных в обоих столбцах должен быть строковым Middle of String Переносит шхщножество строки данных из исходного столбца в конечный, при необходимости усекая данные. Может также преобразовывать регистр символов. Тип данных в обоих столбцах должен быть строковым Trim String Переносит строковые данные из исходного столбца в конечный, удаляя лишние пробелы в начале, в конце и непосредственно в строке. При необходимости усекает данные;
может также преобразовывать регистр символов. Тип данных в обоих столбцах должен быть слроковым Read File Открывает и копирует содержимое файла, указанного в исходном столбце, в конечный столбец. Тип данных исходного столбца должен быть строковым, а конечного Ч строковым или двоичным t Write File Копирует содержимое исходного столбца в файл, указанный в конечному столбце. Тип данных исходного столбца должен быть строковым или двоичным Ход обработки пакета DTS Службы DTS упорядочивают задачи в пакетах DTS при помощи констант предше ствования и этапов. Этапы определяют, в какой последовательности выполняются задачи пакета. Управление этой последовательностью в конструкторе DTS Designer и Занятие 2 Обзор служб DTS на программном уровне осуществляется с помощью констант предшествования (табл. 7-5). Константы последовательно связывают все задачи пакета. Для задачи мо жет быть определено несколько констант предшествования. Задачи без таких кон стант выполняются параллельно.
Табл. 7-5. Константы предшествования и их функции Константа Описание Unconditional Если Вторая задача связана с Первой средствами константы Unconditional, она будет ожидать завершения Первой задачи и затем выполнится, независимо от успеха или неудачи Первой задачи On Success Если Третья задача связана со Второй средствами константы On Success, она будет ожидать завершения Второй задачи и выполнится, только если Вторая задача завершилась успешно On Failure Если Четвертая задача связана со Второй средствами константы On Failure, она будет ожидать завершения Второй задачи и выполнится, только если при выполнении Второй задачи произошла ошибка Рассмотрим следующий пример. Пусть Первая задача удаляет таблицу. Вторая Ч создает новую таблицу, Третья Ч заполняет ее, и Четвертая Ч восстанавливает ста рую таблицу. Если таблица не существует, Первая задача завершится с ошибкой, и Вторая задача создаст новую таблицу. Если таблица существует, Первая задача удалит ее, и затем Вторая задача создаст новую таблицу. Далее, если Вторая задача заверши лась успешно, Третья задача заполнит новую таблицу данными. Если же Вторая зада ча создать новую таблицу не смогла, Четвертая задача восстановит старую таблицу.
Варианты хранения пакетов DTS Пакет DTS можно сохранить в БД SQL Server 2000, репозиторий службы SQL Server 2000 Meta Data Services, файл Microsoft Visual Basic или структурированный файл хра нилища (табл. 7-6). Вместе с пакетом DTS сохраняются все подключения DTS, зада чи, трансформации и этапы хода обработки.
Табл. 7-6. Варианты хранения пакетов DTS Размещение пакета Описание БД SQL Server 2000 Пакет может храниться в виде таблицы БД msdb на любом экземпляре SQL Server 2000. Это вариант хранения по умолчанию;
он позволяет записывать множество пакетов и их версий. При сохранении пакета в БД SQL Server 2000 его можно защитить одним или несколькими паролями Репозиторий Meta Пакет хранится на вашем компьютере, в БД репозитория Data Services службы Meta Data Services. Этот вариант позволяет отслеживать столбцы и таблицы, задействованные в источнике и приемнике, включая происхождение данных конкретного ряда, Хранимый в репозиторий пакет можно защитить средствами Meta Data Services Файл Visual Basic Пакет хранится в коде Visual Basic, который можно открыть и отредактировать с помощью Visual Basic или Visual C++.
Защитить пакет DTS, хранящийся в файле Visual Basic, можно при помощи специальных средств типа Microsoft Visual SourceSafe 186 Заполнение базы данных Глава Табл. 7-6. (окончание) Размещение пакета Описание Структурированный Пакет хранится в файле ОС. Этот вариант позволяет хранить файл хранилища и перемещать пакеты DTS независимо от БД SQL Server.
В одном файле может храниться несколько пакетов и их версий. Пакеты в структурированном файле хранилища можно защитить одним или несколькими паролями.
Средства создания пакетов DTS Пакет DTS можно создать с помощью мастера DTS Import/Export Wizard, конструк тора DTS Designer или программно. Мастер DTS Import/Export Wizard Ч простей ший способ создания пакетов DTS для копирования данных между источниками. Но он ограничивает сложность трансформации данных и хода обработки создаваемой за дачи, не позволяет использовать несколько задач DTS. Мастер DTS Import/Export Wizard доступен в консоли SQL Server Enterprise Manager, а также в меню Start\Prog rams\Microsoft SQL Server. Параметры созданных этим мастером пакетов можно из менять в конструкторе DTS Designer, а также с помощью Visual Basic и Visual C++.
Конструктор DTS Designer позволяет изменять существующие пакеты DTS и при помощи графических объектов упрощает создание новых пакетов со сложной после довательностью обработки задач (например пакетов, открывающих несколько соеди нений, или содержащих логику, управляемую событиями). Конструктор доступен в контейнере Data Transformation Services дерева консоли SQL Server Enterprise Manager.
Пакеты DTS можно также создавать в Visual Basic и Visual C++. Это окажется по лезно разработчикам, которым требуется прямой доступ к объектной модели DTS и четкое управление операциями пакета. Созданные программно пакеты можно редак тировать в конструкторе DTS Designer. За основу собственного пакета можно взять один из стандартных шаблонов пакетов, рассчитанный на конкретную ситуацию (на пример запросы, управляемые данными).
В состав служб DTS также входят утилита DTS Run (Dtsrunui.exe) и команда Dtsrun, предназначенные для запуска и планирования выполнения пакетов DTS из командной строки. Утилита DTS Run позволяет выполнить пакет DTS (и создать па кетный файл Dtsrun) при помощи диалогового окна. Команда Dtsrun запускает пакет DTS из командной строки, используя заданные параметры (зачастую параметры вы зова Dtsrun сохраняют в пакетном файле).
Резюме Для подключения и перемещения данных между OLE DB-источниками службы DTS используют пакеты. Пакет DTS может извлекать данные из одного или нескольких источников данных OLE DB, выполнять простую или сложную трансформацию дан ных, и сохранять преобразованные данные в один или несколько приемников. Кро ме того, в пакете может содержаться логика хода обработки (константы предшество вания). Пакет DTS можно сохранить в БД SQL Server 2000, репозиторий служб Meta Data Services, файл Visual Basic или структурированный файл хранилища. Создать пакет DTS можно с помощью мастера DTS Import/Export Wizard, конструктора DTS Designer, а также Visual Basic и Visual C++.
-J QJ Занятие 3 Обработка данных графическими средствами DTS Занятие 3. Обработка данных графическими средствами DTS Службы DTS предоставляют две графических утилиты для создания пакетов DTS, перемещающих и трансформирующих данные. Здесь рассказывается о работе с каж дой из этих утилит. Мы рассмотрим создание простых трансформаций с помощью мастера DTS Import/Export Wizard, а также создание сложных трансформаций и ло гики обработки задач при помощи конструктора DTS Designer. Вы научитесь сохра нять созданные пакеты в различных форматах и узнаете, как расширить функцио нальность пакета DTS.
Изучив материал этого занятия, вы сможете:
s создавать пакеты DTS с помощью мастера DTS Import/Export Wizard и конструктора DTS Designer;
s сохранять пакеты DTS в различных форматах;
Х/ рассказать, как расширить функциональность пакета DTS.
Продолжительность занятия - около 60 минут Использование мастера DTS Import/Export Wizard Мастер DTS Import/Export Wizard доступен в консоли SQL Server Enterprise Manager, а также в меню Start\Programs\Microsoft SQL Server. Чтобы запустить его из SQL Server Enterprise Manager, выберите в меню Tools команду Wizards;
можно также щелкнуть контейнер Data Transformation Services дерева консоли правой кнопкой и выбрать ко манду All Tasks\Import Data или All Tasks\Export Data. Мастер DTS Import/Export Wizard в пошаговом режиме поможет вам импортировать или экспортировать данные из одних форматов в другие.
Сначала следует выбрать в окне Choose A Data Source нужный источник данных.
Источник по умолчанию Ч поставщик Microsoft OLE DB Provider for SQL Server, он используется для подключения к экземплярам SQL Server. В списке Data Source вы берите драйвер формата для хранилища, из которого вы собираетесь копировать дан ные (например, хранилищем может быть текстовый файл или БД Oracle database).
Прочие представленные в этом окне параметры зависят от выбранного источника данных. Так, если источник данных Ч БД SQL Server, следует указать имя сервера, базы данных и способ проверки подлинности (рис. 7-1).
Если используется другой источник данных, нужно указать другие параметры под ключения. Например, когда вы копируете данные из текстового файла, в окне Choose A Data Source следует указать имя файла, а окнах Select File Format и Specify Column Delimiter Ч формат файла (фиксированные или разделенные специальными симво лами поля, тип файла, разделители рядов и столбцов, спецификатор текста) (рис. 7-2, 7-3 и 7-4).
Глава Хj gg Заполнение базы данных Choose a Data Sauce Fiom л(-ив do you wsrl lo cony dala? You tan copy das blowing iouces Рис. 7-1. Выбор источника данных в DTS Import/Export Wizard a Data Source From wliere doygu i-anl lo copy dabs' YOU can copy data liom oiw of Ihe lolbwtig sonces.
i 3. ;
e, r, Рис. 7-2. Выбор текстового файла в качестве источника данных Select l*t foimat То mpoit Ihe data, coolЩ the sonce *s lormat Cbrtifm that tns ft e pioperSe?
^re conecdy deietied before proceedna --.5 К..Х-<, I Им Х 1 1 в k, p|^.;
3! 1л ' ' Viy-,' >iij.{!
Хт mm Рис. 7-3. Выбор формата файла, типа полей и спецификатора текста Занятие 3 Обработка данных графическими средствами DTS Specify Column Delimiter Now you must specify the so jce ie's cclumri delimter type comma. Mb. semicolon, я anotha characteF Рис. 7-4. Выбор символа-разделителя столбцов Следующий этап Ч выбор приемника данных в окне Choose A Destination. Опять же, приемником по умолчанию является поставщик Microsoft OLE DB Provider for SQL Server, для которого следует указать имя сервера и параметры подключения.
Перечень приемников данных очень разнообразен. Источником и приемником дан ных одновременно могут быть БД, отличные от SQL Server 2000 (рис. 7-5). Например, службы DTS позволяют копировать данные из БД Oracle в БД dBase. Если приемник данных Ч SQL Server 2000, можно создать новую БД на лету, она будет размещена в той же папке, что и БД master. При этом в окне Create Database можно задать только ее имя, размеры файла данных и журнала транзакций (рис. 7-6).
Choote a destination То where do you want to copy data' You can copy data la cne of the fol dettnialiBi!
Рис. 7-5. Выбор приемника данных Выбрав источник и приемник данных, укажите или отфильтруйте в окне Specify Table Copy Or Query копируемые данные (при создании новой БД это окно не выво дится). Параметры копирования и фильтрации будут зависеть от источника и прием ника. Если источник Ч БД, данные можно просто скопировать без фильтрации и упо рядочивания, установив переключатель Copy Table(s) And View(s) From The Source Database. Для более сложной операции копирования, которая с помощью запроса Глава Заполнение базы данных Transact-SQL в определенном порядке извлекает соответствующие критерию выбор ки строки, установите переключатель Use A Query To Specify The Data To Transfer.
Кроме того, если источником и прлемником являются БД SQL Server 7.0/2000, мож но копировать объекты БД (например хранимые процедуры и регистрационные за писи), установив переключатель Copy Objects And Data Between SQL Server Databases, (рис. 7-7).
Рис. 7-6. Выбор имени и свойств новой базы данных Specify ТаЫе Copy or QUBIJ whethei to copji one a more tables/vie*! и the results of i pueiji hom Ihe data 301ЛС6. ' Рис. 7-7. Выбор вида операции копирования Копирование таблиц и предстазлений Чтобы полностью скопировать какие-либо таблицы или представления, выберите их в окне Select Source Tables And Views. По умолчанию при копировании в приемник имена таблиц и представлений не, меняются. Можно указать новое имя или выбрать другую конечную таблицу или представление (рис, 7-8).
Если не указано иное, содержимое всех выбранных таблиц или представлений будет скопировано в приемник денных без изменений. Если конечная таблица суще ствует, копируемые данные по умолчанию будут добавлены к уже имеющимся. В про тивном случае конечная таблица будет создана, и ей будет задано указанное вами имя, Чтобы изменить эти параметры го умолчанию, щелкните в окне Select Source Tables And Views кнопку (...) в столбце Transform напротив нужных таблиц и представлений.
На вкладке Column Mappings диалогового окна Column Mappings And Transformations можно задать сопоставления ио.одных и конечных столбцов, создать новую коне чную таблицу, изменить оператор Transact-SQL CREATE TABLE (если создается но вая таблица или представление), указать, что делать с рядами существующей конеч Занятие 3 Обработка данных графическими средствами DTS ной таблицы Ч удалить или дополнить, разрешить вставку значений IDENTITY (для таблиц с полем IDENTITY), а также изменить тип данных, когда это допустимо (рис. 7-9).
Setecl Source Tablet and View.
Vou can choose on* я пив (Ales or views lo copy. Vou can cow l(l> schema and data at it appear i h Ihe source a dick (..) to Iwwlam Ihe data unrig Рис. 7-8. Выбор таблиц и представлений в качестве источника данных Рис. 7-9. Редактирование параметров трансформации данных и сопоставлений столбцов На вкладке Transformations диалогового окна Column Mappings And Transforma tions, используя язык сценариев VBScript (по умолчанию) или JScript, можно опреде лить специальные трансформации. Наберите в области ввода сценарий, который пре образует содержимое столбцов в процессе копирования данных (рис. 7-10). Не стандартные трансформации можно выполнять и с помощью мастера DTS Import/ Export Wizard, однако конструктор DTS Designer предоставляет больше возможнос тей в этом плане, так как позволяет использовать сложные сценарии ActiveX.
Глава 192 Заполнение базы данных sual Baac I ramformaliai Sciipt P* each souce cdurm to the slinalion column F-mc lion Man[] DISDtelinion|'Cu!tome(IDlT-OTSSajrcel"CJj!U)merlD"] Рис. 7-10. Выбор варианта трансформации данных Выбор копируемых данных с помощью запросов Если вы решили отобрать данные для копирования при помощи запроса, нведите его в поле Query statement окна Type SQL Statement. Можно также импортировать имею щийся сценарий или создать запрос в графическом режиме с помощью конструктора DTS Query Designer, щелкнув кнопку Query Builder. Чтобы проверить синтаксис сце нария, щелкните Parse (рис. 7-11).
Введя текст сценария Transact-SQL и щелкнув Next, можно просмотреть результа ты его выполнения Ч для этого в окне Select Source Tables And Views (рис. 7-8} щел кните Preview. Если не указано иное, результаты запроса будут скопированы в новую таблицу с именем Results (его можно изменить). Чтобы отредактировать сопоставле ния исходных и конечных столбцсв и определить нестандартные преобразования дан ных, щелкните в столбце Transform окна Select Source Tables and Views (рис. 7-12) кнопку (..,).
Type SOL Statement Type Ida SOL query statement (bat I gereale data dan the le< ted database ELECT PioducWameAS Product Х?ХХ ~Wo UnitPliee ' Quantity] AS [Total Value] FROM [Crdei Cetefe] о INNER;
' Х Х Products p Х!
' ON aPtodubUD,0 ProduttlD GRGUF 6V FroduclNfline ИДИ J~ "[' \^Pmt> fj'wri. Eutdn.Seal Рис. 7-И. Создание сценария с помощью конструктора DTS Query Designer Занятие 3 Обработки данных графическими средствами DTS | QO Рис. 7-12. Редактирование параметров трансформации данных и сопоставлений столбцов при выборке данных запросом Копирование данных и объектов между БД SQL Server Чтобы скопировать данные и объекты из одной БД SQL Server в другую, пометьте их в окне Select Objects To Copy. Возможно копирование между экземплярами SQL Server 7.0, SQL Server 2000, а также из экземпляра SQL Server 7.0 в экземпляр SQL Server 2000 (скопировать объекты и данные из экземпляра SQL Server 2000 в экземпляр SQL Server 7.0 нельзя). По умолчанию копируются все объекты БД, для копируемых объек тов создаются новые конечные объекты, перед их созданием соответствующие ста рые конечные объекты (если таковые имеются) удаляются, вместе с копируемыми объектами перемещаются все зависимые объекты. Кроме того, по умолчанию копи руются все данные источника, и все данные приемника удаляются (рис. 7-13). Спи сок копируемых объектов можно ограничить (например переместить только конк ретные таблицы и сохраненные процедуры, и не копировать индексы).
tecl Object! (о Сору You 0*1 сор> tables, dale, sloped pracedujf j. itt&crtitl inleecfci conslraintj.
iecjit>. and indeKes Choose the оЫесВД to copy.
Рис. 7-13. Выбор копируемых объектов Глава Заполнение базы данных Сохранение и планирование выполнения пакетов Заключительный этап работы с мастером DTS Import/Export Wizard Ч запустить па кет для немедленного выполнения или в диалоговом окне Save, Schedule, And Repli cate Package в окне Save, Schedule. And Replicate Package сохранить его и спланиро вать его выполнение. По умолчанию мастер предлагает запустить пакет, не сохраняя его и не создавая расписание его ныполнения. Можно создать расписание, по кото рому пакет DTS будет выполняться как задание под управлением службы SQL Server Agent. Подробнее о заданиях и планировании их выполнения Ч в главе 13. Можно также сохранить пакет в любом из поддерживаемых форматов (подробнее Ч в заня тии 4 этой главы).
Save, ichedule. and replicate package Scity (you want to save ibis DTS package You may atoo lepfcste Ine dala or schedule the package ID Бе executed at a late time.
Рис. 7-14. Сохранение и планирование выполнения пакета Упражнение 1. Перенос таблиц и данных из БД Northwind средствами мастера OTS Import/Export Wizard В этом упражнении вы с помощью мастера DTS Import/Export Wizard переме стите таблицы и данные БД Northwind в новую базу. Затем вы отредактируете таблицы и получите сводку данных БД NorthwindReportData.
/ Чтобы перенести таблицы и данные из БД Northwind средствами > мастера DTS Import/Export Wizard 1. Убедитесь, что вы зарегистрировались на контроллере домена SeifPacedSQL.MSFT под учетной записью Administrator.
2. Раскройте меню Start\Programs\Microsoft SQL Server и выберите Import And Export Data.
Запустится мастер DTS Import/Export Wizard.
3. Щелкните Next.
Откроется окно Choose A Data Source.
4. Убедитесь, что в раскрывающемся списке Data Source выбран источник данных Microsoft OLE DB Provider for SQL Server, 5. В раскрывающемся списке Server выберите SelfPacedCPU\MyNamedlnstance.
6. Убедитесь, что переключатель находится в положении Use Windows Authentication.
7. В раскрывающемся списке Database выберите Northwind и щелкните Next.
Занятие 3 Обработка данных графическими средствами DTS Откроется окно Choose A Destination.
8. Убедитесь, что в раскрывающемся списке Destination выбран приемник данных Microsoft OLE DB Provider for SQL Server.
9. В раскрывающемся списке Server выберите SelfPacedCPU.
10. Убедитесь, что переключатель находится в положении Use Windows Authentication.
11. В раскрывающемся списке Database выберите
Откроется окно Create Database.
12. В поле Name введите North wind Report Data и щелкните ОК.
Откроется окно Choose A Destination, где будет показана новая БД.
13. Щелкните Next.
Откроется окно Specify Table Copy Or Query.
14. Убедитесь, что переключатель находится в положении Copy Table(s) And View(s) From The Source Database, и щелкните Next.
Откроется окно Select Source Tables And Views.
15. Щелкните Select All.
Заметьте: именам конечных таблиц будут автоматически заданы имена исходных.
16. Щелкните Next.
Откроется окно Save, Schedule, And Replicate Package.
17. Убедитесь, что помечен флажок Run Immediately.
18. Пометьте флажок Save DTS Package, убедитесь, что переключатель находится в положении SQL Server, и щелкните Next.
Откроется окно Save DTS Package.
19. В поле Name введите NorthwindTableCopy и щелкните Next.
Откроется окно Completing The DTS Import/Export Wizard.
20. Щелкните кнопку Finish.
Откроется окно Executing Package с информацией о состоянии выполнения эта пов пакета. По завершении выполнения появится окно мастера DTS Import/Export Wizard с сообщением об успешном копировании 29 таблиц из одного экземпляра Microsoft SQL Server в другой.
21. Щелкните ОК.
22. В окне Executing Package просмотрите информацию об успешно выполненных эта пах, и щелкните Done.
23. В дереве консоли SQL Server Enterprise Manager раскройте контейнер экземпляра SQL Server по умолчанию, и затем Ч контейнер Databases.
Заметьте: появилась БД NorthwindReportData (возможно, вам потребуется обно вить дерево консоли, нажав клавишу F5).
Чтобы отредактировать таблицы и получить сводку данных БД > NorthwindReportData средствами мастера DTS Import/Export Wizard 1. Щелкните значок NorthwindReportData правой кнопкой и выберите All Tasks\Im port Data.
Откроется окно DTS Import/Export Wizard.
2. Щелкните Next.
Откроется окно Choose A Data Source.
3. Убедитесь, что в раскрывающемся списке Data Source выбран источник данных Microsoft OLE DB Provider for SQL Server.
8- 196 Заполнение базы данных Глава 4. В раскрывающемся списке Server выберите SelfPacedCPU.
5. Убедитесь, что переключатель находится в положении Use Windows Authentication.
6. В раскрывающемся списке Database выберите Northwind и щелкните Next.
Откроется окно Choose A Destination.
7. Убедитесь, что в раскрывающемся списке Destination выбран источник данных Microsoft OLE DB Provider for SQL Server.
8. В раскрывающемся списке Server выберите SelfPacedCPU.
9. Убедитесь, что переключатель находится в положении Use Windows Authentication.
10. В раскрывающемся списке Dai abase выберите NorthwindReportData и щелкните Next.
Откроется окно Specify Table Copy Or Query.
П. Установите переключатель в положение Use A Query To Specify The Data To Transfer и щелкните Next.
Откроется окно Type SQL Statement.
12. Щелкните кнопку Browse.
Откроется диалоговое окно Open.
13. В раскрывающемся списке Look In выберите путь C:\SelfPacedSQL\CH_7 и от кройте сценарий Query.sql.
Текст сценария Query.sql появится в поле Query Statement.
14. Щелкните Next.
Откроется окно Select Source Tables And Views.
15. Щелкните ячейку Results столбца Destination и введите TotalValue.
Убедитесь, что TotalValue набрано одним словом, без пробелов.
16. Щелкните кнопку Preview.
Откроется окно View Data с результатами выполнения запроса.
17. Щелкните ОК.
Откроется окно Select Source Tables And Views.
18. Щелкните кнопку (...) в столбце Transform.
Откроется окно Column Mappings And Transformations.
19. Щелкните кнопку Edit SQL.
Откроется окно Create Table SQL Statement.
20. Просмотрите оператор Transact-SQL.
Обратите внимание: поле TotalValue допускает значения NULL.
21. Щелкните OK, чтобы закрыть окно Create Table SQL Statement.
Откроется окно Column Mappings And Transformations.
22. В группе Mappings снимите для записи TotalValue флажок Nullable.
23. Щелкните Edit SQL чтобы просмотреть оператор Transact-SQL.
Откроется окно Create Table SQL Statement. Заметьте: теперь поле TotalValue не допускает значений NULL.
24. Щелкните ОК, чтобы закрыть окно Create Table SQL Statement.
25. Щелкните ОК, чтобы закрыть окно Column Mappings And Transformations.
Откроется окно Select Source Tables And Views.
26. Щелкните Next.
Откроется окно Save, Schedule, And Replicate Package.
27. Убедитесь, что переключатель находится в положении Run Immediately.
Занятие 3 Обработка данных графическими средствами DTS 28. Пометьте флажок Save DTS Package.
29. Установите переключатель в положение SQL Server Meta Data Services и щелкните Next.
Откроется окно Save DTS Package.
30. В поле Name введите NorthwindTableTransform и щелкните Next.
Откроется окно Completing The DTS Import/Export Wizard.
31. Щелкните Finish.
Откроется окно Executing Package с информацией о состоянии выполнения эта пов пакета. По завершении выполнения появится окно мастера DTS Import/Export Wizard с сообщением об успешном копировании таблицы из одного экземпляра Microsoft SQL Server в другой.
32. Щелкните ОК.
33. В окне Executing Package просмотрите информацию об успешно выполненных эта пах и щелкните Done.
34. В дереве консоли SQL Server Enterprise Manager раскройте контейнер экземпляра SQL Server по умолчанию, затем Ч контейнеры Databases и NorthwindReportData и щелкните Tables.
35. В правой панели щелкните значок TotalValue правой кнопкой и выберите Open Table\Return All Rows.
Откроется окно Data In Table TotalValue' In 'NorthwindReportData' On 'SelfPa cedCPU' с содержимым только что созданной и заполненной БД.
36. Закройте окно Data In Table 'TotaiValue' In 'NorthwindReportData' On 'SelfPa cedCPU'.
37. Консоль SQL Server Enterprise Manager оставьте открытой.
Использование конструктора DTS Designer Чтобы создать новый пакет DTS средствами конструктора DTS Designer, щелкните в дереве консоли SQL Server Enterprise Manager контейнер Data Transformation Services правой кнопкой и выберите New Package. Способ открытия существующего пакета DTS зависит от того, где тот хранится. Если пакет находится в структурированном файле хранилища, щелкните контейнер Data Transformation Services правой кнопкой и выберите Open Package, чтобы открыть файл на диске. Если пакет DTS находится в БД SQL Server, раскройте в дереве консоли контейнер Data Services\Local Packages и затем дважды щелкните в правой панели значок нужного пакета. Если пакет DTS находится в репозитории SQL Server Meta Data Services, раскройте в дереве консоли контейнер Data Services\Meta Data Services и затем дважды щелкните в правой пане ли значок нужного пакета.
Конструктор DTS Designer позволяет графически создавать подключения к источ никам и приемникам данных, конфигурировать задачи DTS, задавать параметры DTS-трансформаций и определять константы предшествования. Для создания паке та перетащите нужные объекты в область проектирования и задайте их свойства в соответствующих диалоговых окнах. Пользовательский интерфейс конструктора DTS Designer показан на рис. 7-15.
Первый этап создания пакета DTS средствами DTS Designer Ч выбор источника данных. Можно перетащить объект-источник данных с панели инструментов Connec tion в область проектирования, или выбрать источник в меню Connection. Откроется диалоговое окно Connection Properties, в котором следует задать параметры выбран Глава Заполнение базы данных ного источника данных. Это окно аналогично диалоговому окну, выводимому масте ром DTS Import/Export Wizard. На рис. 7-16 показано диалоговое окно свойств под ключения к SQL Server, использующего поставщик Microsoft OLE DB Provider for SQL Server.
Панель инструментов Панель подключения Панель задач Рис. 7-15. Пользовательский интерфейс конструктора DTS Designer Рис. 7-16. Диалоговое окно Connection Второй этап Ч выбор и конфигурирование приемника данных, как описывалось выше. На рис. 7-17 показана область проектирования, включающая три источника данных: два подключения к поставщику Microsoft OLE DB Provider for SQL Server и одно подключение к текстовому файлу [Text File (Source)].
Примечание Подключение к текстовому файлу означает, что он является источником данных или канальным соединением.
Занятие 3 Обработка данных графическими средствами DTS J Рис. 7-17. Настройка приемника данных Затем нужно при помощи меню Task или одноименной панели инструментов Task определить задачи, выполняемые пакетом. При выборе задачи Transform Data нам предложат выбрать источник и приемник данных. При выборе любой другой задачи откроется окно настройки ее свойств (например, можно настроить задачу Execute SQL для создания таблицы через имеющееся подключение к БД). Для задач Transform Data в области проектирования отображаются темно-серые стрелки, направленные от ис точника данных к приемнику. Другие задачи отображаются в области проектирова ния в виде обычного значка. На рис. 7-18, помимо трех подключений к источникам данных, показаны две задачи Transform Data и одна задача Execute SQL, которая со здает таблицу.
Рис. 7-18. Пакет DTS Package без логики управления ходом выполнения Чтобы изменить параметры задачи Data Transformation, дважды щелкните темно серую стрелку между источником и приемником данных. Откроется окно свойств задачи. Если источник данных Ч БД, на вкладке Source можно отфильтровать копи руемые данные, пометив нужные таблицы и представления или воспользовавшись запросом Transact-SQL. На рис. 7-19 показан запрос Transact-SQL для фильтрации импортируемых данных.
Глава Заполнение базы данных -JM-oUntPrice Qj*%iAS Town/due ;
RQM (Ordei Delate) 0INfoER jCVN Praducls p ONoProducaD.pPioductlO RQUP BY PraduclNane Рис. 7-19. Фильтрация импортируемых данных с помощью запроса На вкладке Destination можно описать импортируемые данных (допустим, задать определения столбцов). Параметры импорта зависят от приемника данных. Если при емник Ч БД, для всех импортируемых таблиц можно создать и определить новые или выбрать существующие таблицы.
На вкладке Transformations можно создавать нестандартные трансформации дан ных. По умолчанию исходные столбцы копируются в конечные без изменений. Что бы преобразовать данные при копировании между источником и приемником, выбе рите нужный столбец в списке Name или щелкните стрелку, направленную от источ ника данных к приемнику (стрелка для выбранного столбца будет отображаться чер ным цветом). Затем щелкните кнопку New или Edit, чтобы создать новую или отре дактировать имеющуюся трансформацию (для редактирования можно также дважды щелкнуть черную стрелку). Щелкнув New, вы сможете выбрать нужный тип транс формации из списка в диалоговом окне Create New Transformation (рис. 7-20).
Рис. 7-20. Создание нового преобразования Занятие 3 Обработка данных графическими средствами OTS Если выбрать в окне Create New Transformation пункт ActiveX Script, можно со здать новый сценарий, осуществляющий сложные преобразования данных (рис. 7-21).
На вкладке Lookups можно ввести запрос на поиск. Он, помимо источника и при емника, требует подключения к данным, по которому будут выполняться запросы и хранимые процедуры. Запросы на поиск позволяют находить информацию в табли цах, параллельно обновлять данные нескольких СУБД, оценивать вводимые данные перед загрузкой их в БД, вызывать хранимые процедуры в зависимости от условий ввода, а также передавать значения глобальных переменных в качестве параметров запросов.
Copy each source counri lo the destination с "unction ManQ Ate - DTSSou AiM Main - DTSTr*nsfomSlat_OK i '.
End Function Х Atn СЛ Cm Саи Eke CBool CBtfe CCur CDate СОЫ Chr Clnt _J CLng 'Х"->,,Д Рис. 7-21. Создание сценария для преобразования данных На вкладке Options можно настроить ряд дополнительных параметров трансфор мации. Укажите один или несколько файлов, куда будут заноситься записи об исклю чениях, возникших при выполнении пакета. Файлы исключений могут размещаться на локальных и сетевых дисках;
для преемственной совместимости их можно запи сывать в формате SQL Server 7.0. Ошибки источника и приемника можно заносить в отдельные файлах;
можно также указать максимальное число ошибок для прекраще ния выполнения пакета. Если подключение к приемнику осуществляется через по ставщик Microsoft OLE DB Provider для SQL Server, можно задать специфические па раметры выполнения, включая высокоскоростную обработку загрузки данных, про верку ограничений при выполнении пакета, тип блокировки, размер пакета и пара метры вставки значений IDENTITY.
Сконфигурировав задачу Data Transformation и другие задачи пакета DTS, задайте константы предшествования. В нашем примере данные из двух разных источников копируются в один приемник, и выполняется задача Create Table. Задать порядок выполнения этапов пакета можно с помощью констант предшествования. Чтобы оп ределить ход обработки, выберите две или более задачи в том порядке, в каком они должны выполняться, и затем в меню Workflow выберите нужную константу предше ствования. Например, если задача Create Table должна выполняться раньше копиро вания данных в приемник, выберите в меню Workflow команду On Success (рис. 7-22).
Глава Заполнение базы данных Рис. 7-22. Выбор константы предшествования On Success Можно создать задачу Send Mail и определить между задачей Create Table и зада чей Send Mail константу предшествования On Failure. При этом, в случае ошибки со здания таблицы администратор получит уведомление по электронной почте. Если для работы с БД используются полностью автоматизированные и выполняющиеся по за данному расписанию пакеты DTS, администратора следует обязательно уведомлять о любых ошибках.
Упражнение 2. Создание пакета, импортирующего и преобразующего данные, средствами конструктора DTS Designer В этом упражнении вы с помощью конструктора DTS Designer создадите па кет, импортирующий и преобразующий данные.
Чтобы создать пакет, импортирующий и преобразующий данные, средствами конструктора DTS Designer Убедитесь, что вы зарегистрировались на контроллере домена SelfPacedSQL.MSFT под учетной записью Administrator, В дереве консоли SQL Server Enterprise Manager раскройте контейнеры Microsoft SQL Servers и SQL Server Group, а затем Ч контейнер экземпляра SQL Server no умолчанию.
Щелкните контейнер Data Transformation Services правой кнопкой и выберите New Package.
Откроется окно DTS Designer.
В меню Connection выберите Text File (Source).
Откроется окно Connection Properties.
В поле New Connection введите New Products.
В поле File Name введите C:\SeIfPacedSQL\CH_7\NewData.txt и щелкните ОК.
Откроется диалоговое окно Text Files Properties с выбранной вкладкой Select File Format.
Занятие 3 Обработка данных графическими средствами DTS 7. Убедитесь, что переключатель находится в положении Delimited Columns Are Separated By Character(s), и щелкните Next.
Откроется окно Specify Column Delimiter.
8. Убедитесь в том, что установлен переключатель Comma и щелкните Finish.
Снова откроется окно Connection Properties.
9. Щелкните ОК.
В области проектирования появится значок New Products.
10. В меню Connection выберите команду Microsoft OLE DB Provider for SQL Server.
Откроется окно Connection Properties.
11. В поле New Connection введите North wind Report Data.
12. В раскрывающемся списке Server выберите SelfPacedCPU.
13. В раскрывающемся списке Database выберите NorthwindReportData и щелкните ОК.
В области проектирования появятся значки New Products и Northwind Report Data.
14. В меню Task выберите команду Transform Data Task.
Значок указателя мыши изменится и примет форму стрелки с надписью Select Source Connection.
15. Щелкните значок New Products.
Значок указателя мыши изменится и примет форму стрелки с надписью Select Destination Connection.
16. Щелкните значок Northwind Report Data.
В области проектирования появится темно-серая стрелка, направленная от знач ка New Products к значку Northwind Report Data.
17. Дважды щелкните темно-серую стрелку.
Откроется диалоговое окно Transform Data Task Properties с выбранной вкладкой Source.
18. В поле Description введите Adding New Products.
19. Перейдите на вкладку Destination.
20. Щелкните кнопку Create, чтобы создать новую таблицу, в которую будут загруже ны данные.
Откроется диалоговое окно Create Destination Table с оператором CREATE TABLE.
21. Удалите текст оператора CREATE TABLE.
22. Запустите Notepad и откройте файл NewProducts.sql из лапки C:\SelfPaced SQL\CH_7.
23. Скопируйте содержимое файла и вставьте его в поле SQL Statement. Затем щелк ните ОК.
Откроется окно Transform Data Task Properties.
24. Перейдите на вкладку Transformations.
Просмотрите заданные по умолчанию сопоставления исходных и конечных стол бцов. В частности, обратите внимание, что конечных столбцов больше исходных.
Это обусловлено тем, что мы добавили столбец TotalValue, содержащий агрегат ное значение двух столбцов. Заметьте также, что из-за добавления нового столбца сопоставление столбцов 8Ч10 нарушилось.
25. Щелкните стрелку, направленную от столбца ColOlO к столбцу ReorderLevel, пра вой кнопкой и выберите Edit.
Откроется диалоговое окно Transformation Options.
26. Перейдите на вкладку Destination Columns.
Заполнение базы данных 204 Глава 27. В списке Selected Columns щелкните кнопку <, чтобы удалить столбец ReorderLevel из списка.
28. В списке Available Columns выберите Discontinued, щелкните кнопку > и затем Ч ОК.
Заметьте: теперь столбец СоЮЮ сопоставлен столбцу Discontinued.
29. Щелкните стрелку, направленную от столбца СоЮ09 к столбцу UnitsOnOrder, пра вой кнопкой и выберите Edit.
Откроется диалоговое окно Transformation Options.
30. Перейдите на вкладку Destination Columns.
31. В списке Selected Columns щелкните кнопку <, чтобы удалить столбец Units OnOrder из списка.
32. В списке Available Columns выберите ReorderLevel, щелкните кнопку > и затем Ч ОК.
Заметьте: теперь столбец СоЮО') сопоставлен столбцу ReorderLevel.
33. Щелкните стрелку, направленную от столбца ColOOS к столбцу TotalValue, правой кнопкой и выберите Edit.
Откроется диалоговое окно Transformation Options.
34. Перейдите на вкладку Destination Columns.
35. В списке Selected Columns щелкните кнопку <, чтобы удалить столбец TotalValue из списка.
36. В списке Available Columns выберите UnitsOnOrder, щелкните кнопку > и затем Ч ОК.
Заметьте: теперь столбец СоЮОИ сопоставлен со столбцом UnitsOnOrder.
37. В списке Source щелкните СоЮОб и затем, удерживая клавишу Ctrl, щелкните Со1007.
Обратите внимание: теперь выделены два столбца Ч СоЮОб и Со1007.
38. В столбце Destination щелкните TotalValue.
39. Щелкните кнопку New.
Откроется окно Create New Transformation.
40. Щелкните ActiveX Script и затем Ч ОК.
Откроется окно Transformation Options.
41. На вкладке General щелкните кнопку Properties.
Откроется окно ActiveX Script Transformation Properties.
42. Замените строку кода DTSDestination( "TotalValue" }=DTSSource( "СоЮОб") на DTSDestination( "TotalValue" )=CTSSource( "СоЮОб-)*DTSSource("Col007") 43. Щелкните кнопку Parse.
Конструктор DTS Designer сообщит, что сценарий ActiveX не содержит синтакси ческих ошибок.
44. Щелкните ОК.
45. Щелкните кнопку Test.
Откроется окно Testing Transformation, в котором осуществляется проверка транс формации. Затем появится окно Package Execution Results с сообщением об ус пешном выполнении пакета.
46. Щелкните ОК.
47. В окне Testing Transformation щелкните Done.
Обработка данных графическими средствами DTS Занятие 3 48. В окне ActiveX Script Transformation Properties щелкните ОК.
Откроется окно Transformation Options.
49. Щелкните ОК.
Заметьте: столбцы СоШОб и CoIOO? сопоставлены со столбцом TotalValue.
50. В окне Transform Data Task Properties щелкните ОК.
51. В меню Task выберите Execute SQL Task.
Откроется окно Execute SQL Task Properties.
52. В поле Description Drop Table.
53. В раскрывающемся списке Existing Connection выберите подключение Northwind Report Data.
54. В поле SQL Statement введите DROP TABLE NewProdiicts и щелкните ОК.
В области проектирования появится значок задачи Drop Table, 55. В меню Task выберите Execute SQL Task.
Откроется окно Execute SQL Task Properties.
56. В поле Description введите Create Table.
57. В раскрывающемся списке Existing Connection выберите Northwind Report Data.
58. Щелкните кнопку Browse.
Откроется диалоговое окно Select File.
59. В списке Look In выберите путь C:\SelfPacedSQL\CH_7 и откройте сценарий NewProducts.sql.
В поле SQL Statement появится код оператора CREATE TABLE.
60. Щелкните ОК.
В области проектирования появится значок задачи Create Table.
61. В меню Task выберите команду Execute SQL Task.
Откроется окно Execute SQL Task Properties.
62. В поле Description введите Backup Northwind Report Data.
63. В раскрывающемся списке Existing Connection выберите Northwind Report Data.
64. Щелкните Browse и откройте файл BackupNorthwindReportData.sql из папки C:\SelfPacedSQL\CHJ7.
В поле SQL Statement появится код оператора BACKUP DATABASE. Если SQL Server 2000 и учебные файлы установлены на диске, отличном от С, в операторе следует отредактировать пути.
65. Щелкните ОК.
В области проектирования появится значок задачи Backup Northwind Report Data.
66. Щелкните значок задачи Drop Table и затем, удерживая клавишу Ctrl, Ч значок задачи Create Table.
Заметьте: выбраны оба значка.
67. В меню Workflow выберите команду On Completion.
Между значками задач Drop Tabie и Create Table появится стрелка с белыми и си ними полосками. Задача Create Table ожидает завершения задачи Drop Table и выполняется независимо от успеха или неудачи последней. Если таблица NewPro ducts не существует, при выполнении задачи Drop Table произойдет ошибка и за дача Create Table создаст одноименную таблицу.
68. Щелкните значок задачи Create Table и затем, удерживая клавишу Ctrl, Ч значок задачи New Products.
69. В меню Workflow выберите команду On Success.
206 Заполнение базы данных Глава Между значками задач Create Table и New Products to Northwind Report Data по явится стрелка с белыми и зелеными полосками. Задача New Products to Northwind Report Data выполняется, только если задача Create Table успешно создаст табли цу New Products.
70. Щелкните значок Northwind Report Data и затем, удерживая клавишу Ctrl, Ч зна чок Backup Northwind Report Data.
71. В меню Workflow выберите команду On Success.
Между значками задач New Products to Northwind Report Data и Backup Northwind Report Data появится стрелка с белыми и зелеными полосками. Задача Backup Northwind Report Data выполняется только в случае успешного выполнения зада чи New Products to Northwind Report Data.
72. В меню Package выберите команду Save.
Откроется окно Save DTS Package.
73. В поле Package Name введите Lesson 2 и щелкните ОК.
74. Щелкните кнопку Execute в панели инструментов.
Откроется окно Executing DTS Package: Lesson 2, сообщающее о ходе выполнения пакета DTS. По завершении всех операций появится окно Package Execution Results с сообщением об успешном завершении выполнения пакета.
75. Щелкните ОК и затем Ч Done.
76. В окне DTS Designer щелкните Close, чтобы закрыть окно DTS Package: Lesson 2.
77. В дереве консоли SQL Server Enterprise Manager последовательно раскройте кон тейнеры Microsoft SQL Servers, SQL Server Group, экземпляра SQL Server по умол чанию, Databases, Northwind Re port Data и щелкните контейнер Tables.
78. В правой панели щелкните значок New Products правой кнопкой и выберите Open Table\Return All Rows. При необходимости обновите содержимое панели, нажав клавишу F5.
Откроется окно Data In Table 'NewProducts' In 'NorthwindReportData' On 'Self PacedCPU'. Заметьте: новая таблица заполнена данными и включает столбец с аг регатным значением Ч TotalValue.
79. Закройте окно Data In Table 'NewProducts' In 'NorthwindReportData' On 'Self PacedCPlF.
Консоль SQL Server Enterprise Manager оставьте открытой.
Расширение функциональности пакетов DTS Полный обзор средств и возможностей расширения функциональности пакетов DTS выходит за рамки этой книги. Мы рассмотрим лишь некоторые из них.
Поддержка транзакций Благодаря функциям, которые предоставляет координатор Microsoft Distributed Tran saction Coordinator (DTC), пакеты DTS могут использовать распределенные транзак ции. При этом на компьютере, выполняющем пакет DTS, должен быть запущен DTC.
Распределенные транзакции гарантируют успешное завершение или откат сразу всех параллельно выполняющихся задач пакета DTS. Это особенно полезно, когда пакет DTS работает с несколькими серверами БД, и когда один пакет DTS управляет вы полнением нескольких.
Занятие 3 Обработка данных графическими средствами DTS Задача Message Queue Позволяет организовать очередь для приема и отправки сообщений между пакетами DTS. Сообщение может представлять собой текст, файл, глобальную переменную и ее значение. Очередь позволяет обмениваться сообщениями, даже если конечный пакет DTS недоступен (например, когда несколько ноутбуков отключено от обшей сети). Когда конечный пакет DTS становится доступным, пакет DTS, отправивший сообщение, возобновляет работу и выполняется до завершения. Кроме того, очередь сообщений позволяет отправлять файлы данных на компьютеры, отвечающие за их обработку, распространять файлы в сети предприятия, а также делить большое зада ние на несколько частей и передавать их на выполнение разным компьютерам сети.
Примечание Есть два вида очереди сообщений Ч транзакционная и нетранзакцион ная. Транзакционная очередь сообщений исключает многократную доставку одного и того же сообщения адресату.
Для использования задачи Message Queue вам потребуется установить в локальной сети серверное ПО Message Queuing, а также клиентское ПО Message Queuing на ком пьютере, выполняющем задачу Message Queue. В сетях Windows 2000 серверное ПО Message Queuing устанавливается на контроллер домена Windows 2000, где выполня ется служба Active Directory. Затем следует установить клиентское ПО Message Queuing на своем компьютере. В случае с Windows NT необходимо установить компонент MSMQ из пакета Windows NT 4.0 Option Pack.
Задача Send Mail Позволяет пакету DTS уведомлять администратора о ходе своего выполнения (на пример об успехе или неудаче резервного копирования). Задача Send Mail также по зволяет отправлять вложения, которые могут состоять из динамически обновляемых файлов. Для использования задачи Send Mail установите на локальном компьютере службу Messaging Application Programming Interface (MAPI) и сконфигурируйте про филь MAPI для доменной учетной записи службы SQL Server.
Шаблоны программ В комплект поставки SQL Server 2000 входит множество примеров кода на Visual C++ и Visual Basic. Они записаны в папке \DevTools\ Samples\DTS компакт-диска SQL Server 2000 и находятся в самораскрывающемся ZIP-архиве. Их можно скопировать на диск, выбрав при установке SQL Server вариант установки Custom. По умолчанию файлы примеров копируются в папку C:\Program Files\Microsoft SQL Server\80\To o!s\DevTools\Samples\Dts.
Резюме Копировать и преобразовывать объекты БД и собственно данные можно с помощью мастера DTS Import/Export Wizard. Он наиболее удобен для копирования данных и объектов БД с минимальными преобразованиями из одного источника в один при емник данных. Проектировать пакеты, осуществляющие более сложные трансфор мации, следует в конструкторе DTS Designer. Он позволяет создавать пакеты для об мена данными между несколькими источниками, включающие логику управления выполнением, а также использующие очереди сообщений и логику управления тран закциями. Расширить функциональность и более точно управлять ходом выполнения пакетов DTS можно с помощью Visual C++ и Visual Basic.
Заполнение базы данных Глава Занятие 4. Работа с пакетами DTS Здесь рассказывается о вариантах хранения и способах защиты пакетов DTS. Мы рас смотрим дополнительные методы выполнения сохраненного пакета DTS и устране ние проблем с выполнением при помощи журналов пакетов DTS. Вы также научи тесь редактировать пакеты DTS, когда источник и приемник данных недоступны, и узнаете, как просмотреть и предоставить в совместное пользование метаданные, от носящиеся к пакетам DTS.
Изучив материал этого занятия, вы сможете:
^ описать варианты хранения пакетов DTS;
защитить пакет DTS;
ХS выполнить пакет DTS из командной строки;
Х/ отредактировать пакет DTS, когда источник и приемник данных недоступны.
s Продолжительность занятии Ч около 15 минут Обзор вариантов хранения пакетов DTS Как уже говорилось, пакет DTS можно хранить в БД SQL Server 2000, репозиторий SQL Server Meta Data Services, структурированном файле хранилища или файле Visual Basic. Каждый из перечисленные форматов хранилища имеет свои отличительные особенности и предназначен для различных целей.
SQL Server Пакет DTS можно хранить в виде двоичного объекта таблицы sysdtspackages БД msdb на любом экземпляре SQL Server 2000, благодаря чему возможно централизованное хранение описи пакетов. Сохраняется каждая версия пакета DTS, т. е. создается сво его рода журнал разработки. Это позволяет выбирать и редактировать любую, а не только самую последнюю версию нужного вам пакета DTS.
Пакет DTS, хранимый в БД SQL Server 2000, можно защитить паролем владельца и паролем пользователя. Эти пароли дополняют средства проверки подлинности Windows/SQL Server, применяемые для подключения к SQL Server. Лица, знающие пользовательский пароль, могут только выполнять пакет DTS. Чтобы открыть или отредактировать его, необходим пароль владельца. Настоятельно рекомендуем вам реализовать этот дополнительный уровень защиты, особенно в экземплярах SQL Server 2000, использующих средства проверки подлинности SQL Server 2000.
Репозиторий службы Meta Data Services Позволяет отслеживать версии хранящихся в нем пакетов DTS, их метаданные, а так же происхождение данных (оригинальный источник данных и выполненные преоб разования). В SQL Server Enterprise Manager можно просматривать журнал версий пакетов, хранящихся в репозиторий служб Meta Data Services, и открывать нужные версии. Для записи метаданных о трансформациях в репозиторий Meta Data Services службы DTS используют информационную модель DTS Information Model. Она опи сывает преобразования, группировку и типы доступа к данным. Метаданные из репо зитория можно хранить, просматривать и повторно использовать.
Поместив пакет DTS в репозичорий служб Meta Data Services, вы сможете отсле живать происхождение данных двумя способами: на уровне ряда и на уровне столбца.
В первом случае фиксируется источник данных каждого ряда и выполнявшиеся пре Работа с пакетами DTS Занятие образования. Это позволяет вести аудит выполнения пакета и трансформаций уровня ряда. Для сбора информации о происхождении данных уровня ряда необходимо до бавить в каждый ряд поле, содержащее идентификатор происхождения. Сведения о происхождении данных уровня столбца предоставляют информацию о версии паке та, а также об использовавшихся таблицах БД и их столбцах. Чтобы определить, ис пользует ли пакет из репозитория Meta Data Services конкретную таблицу или стол бец, просмотрите его версии. Это особенно полезно в случаях, когда после создания пакета ценность источника данных ставится под сомнение (например, источник со держит поврежденные или неточные данные). Чтобы отслеживать происхождение данных на уровне столбца, после записи пакета в репозиторий Meta Data Services сле дует регулярно сканировать и сохранять метаданные.
Примечание Службы Meta Data Services не поддерживают защиту на уровне пакета.
Структурированный файл хранилища Позволяет копировать, перемещать и передавать пакет по сети (например в почтовом сообщении), не сохраняя его в БД или репозиторий. В одном файле может храниться несколько версий нескольких пакетов. Пакеты в структурированном файле хранили ща можно защитить паролем владельца и паролем пользователя. Выполнять пакеты DTS, записанные в структурированном файле хранилища, можно при помощи спе циальных утилит командной строки. Пакету DTS, сохраненному в структурироьан ном файле, присваивается расширение.dts.
Файл Visual Basic Сохранив пакет DTS в файл Visual Basic, вы сможете редактировать его с помощью Visual C++ или Visual Basic. Это позволяет разработчикам включать пакеты DTS в программы Visual Basic, а также использовать в качестве прототипов для ссылки на компоненты объектной модели DTS. Пакет DTS, сохраненный в файл Visual Basic, нельзя открыть и отредактировать в конструкторе DTS Designer.
Утилиты выполнения пакетов DTS В состав служб DTS входит две утилиты выполнения пакетов, запускаемые из коман дной строки, Ч DTS Run и Dtsrun. Они позволяют выполнить пакет DTS, не откры вая его. Если для пакета DTS определен пароль пользователя или владельца, его по требуется указать. Если пакет DTS находится в структурированном файле хранили ща, следует указать имя файла. Если пакет DTS размешается в БД SQL Server, укажи те параметры подключения к соответствующему экземпляру SQL Server.
Утилита DTS Run Интерактивная утилита DTS Run позволяет подключиться к серверу или указать файл с пакетом, задать расписание выполнения, включить ведение и указать имя журнала событий, добавить новые глобальные переменные и изменить свойства имеющихся переменных, а также создать для последующего использования команду Dtsrun с не зашифрованными или зашифрованными параметрами. Чтобы запустить DTS Run, введите в командной строке Dtsrunui и нажмите клавишу Enter.
Команда Dtsrun Позволяет запустить пакет DTS из командной строки, используя указанные аргумен ты, и создать вызывающий ее пакетный файл. Например, чтобы выполнить пикет 210 Заполнение базы данных Глава MyPackage из папки C:\DTSPackages\DTSl.dts, для которого определен пароль вла дельца Password, введите следующую команду:
dtsrun /FC:\DTSPackages\DTS1.dts /NMyPackage /MPassword Примечание Обычно пакет DTS выполняется в контексте зашиты запустившего его пользователя. Однако в случае выполнения по расписанию пакет DTS запускается в контексте зашиты пользователя, которому принадлежит выполняющее пакет задание SQL Server Agent. Подробнее о заданиях Ч в главе 13.
Файлы журнала и файлы исключений пакета DTS Службы DTS записывают информацию об успехе или неудаче каждого этапа, вклю чая время начала, конца и продолжительность выполнения, в журнал пакета DTS.
Если этап не выполнялся, в журнал также заносится соответствующая запись. Веде ние журнала возможно, только когда SQL Server 2000 выполняется на компьютере под управлением Windows 2000. В файлы исключений записываются сведения о ря дах, которые не удалось скопировать, и сохраняются исходный и конечный ряд, выз вавшие ошибку.
Автономное редактирование пакета Конструктор DTS Designer позволяет открывать и редактировать существующие па кеты DTS. Во избежание указания неверных параметров соединения обычно необхо димы подключения ко всем источникам и приемникам данных. Но иногда подклю читься к некоторым из них невозможно. Конструктор DTS Designer поддерживает ав тономное редактирование (Disconnected Edit), позволяющее редактировать пакет DTS, когда оригинальный источник или приемник данных недоступен (например, если пакет создавался в тестовой среде и предназначен для использования на другом узле).
Кроме того, автономное редактирование позволяет просматривать и изменять пара метры, недоступные DTS Designer (имена задач, этапов и подключений и т. д.).
Примечание При автономном редактировании значения свойств изменяются непос редственно в пакете, и поэтому данный режим должен применяться только опытными пользователями и только в случаях, когда изменить значения свойств пакета другими способами нельзя.
Резюме Есть несколько вариантов хранения пакетов DTS. Пакет, находящийся в БД SQL Server 2000 или в структурированном файле хранилища, можно защитить паролем владельца и паролем пользователя. При этом пользователь сможет запускать пакет DTS из командной строки с помощью специальных утилит, но не сможет просмот реть или изменить его. Для пакетов, хранящихся в репозитории SQL Server Mela Data Services, можно отслеживать информацию о происхождении данных уровня строки и уровня столбца, а также метаданные о выполнявшихся трансформациях. Это особен но полезно при импорте данных из большого числа источников. Конструктор DTS Designer поддерживает режим автономного редактирования, позволяющий изменять свойства пакета DTS при недоступности источников и приемников данных.
Pages: | 1 | ... | 2 | 3 | 4 | 5 | 6 | ... | 10 | Книги, научные публикации