Экспертные советы по переходу на новую версию Microsoft SQL Server 2005 Дмитрий Артемов консультант dimaa@Microsoft.com Одиннадцатая техническая конференция. - презентация

1 Экспертные советы по переходу на новую версию Microsoft SQL Server 2005 Дмитрий Артемов консультант Одиннадцатая техническая конференция «Корпоративные базы данных-2006» На примере Microsoft.com

2 План Немного о Microsoft.com Планирование перехода Upgrade Advisor Анализ скриптов Анализ показателей производительности до и после перехода Реализация / Уроки

3 Видение, Стратегия & Инициативы Видение: Обеспечить наивысшую доступность ресурсов при демонстрации технологий Microsoft Стратегия: Использовать продукты Microsoft, обеспечить обратную связь при демонстрации возможностей операционной поддержки

4 Internet Hosting Немного фактов 3 Internet центра обработки данных 2 Сети доставки контента Наши партнеры Akamai Technologies, Inc & SAVVIS, Inc 1750 серверов в центрах обработки данных 508+ серверов в лабораториях (Разработка опытная эксплуатация) 109 Web сайтов, 2516 DBs, 1000и Приложений 80+ Gigabit/sec Bandwidth

5 Доступность по информации от Keynote Systems Ежедневный мониторинг В: А кто это такие Keynote? A: Компания, занимающаяся глобальным мониторингом В: Как мы используем данные? A: Анализ в реальном времени & Тренд * За 3 года наблюдений Microsoft.com демонстрирует наивысшую доступность 5/30/05

6 Архитектура высокой доступности Web & Database Hosting Network Load Balancing (NLB) кластеры Размер кластера: 3 – 8 Серверов на кластер Плюсы: Легко управлять и не требует закупки компонентов (встроено в Windows) Проблемы: Накладные расходы на переключение & Обеспечение Connection Affinity Конфигурации серверов Идентичные базовые конфигурации O/S, IIS, ASP.NET Код приложения и контент уникальны для каждого сайта Стандартные хостинг модели Гибкость = Легко переместить приложение между системами Эффективность = Нужно меньше персонала и оборудования Однородные конфигурации Повторяемая архитектура

7 Database Driven Sites Windows Update v Download Center – MSDN – Communities MS-I (Customer database) – ICP (Internet Content Providers) – Subsidiary Sites Other Sites – PENS (Personalized Electronic Newsletter Service) Электронный письма (security bulletin, executive , marketing и т.п.). Не виден снаружи Использует SQL Server Notification Services Windows Metadata Internet Services – Многие другие

8 Рабочие БД As of Jan Основные БД 1188 Клоны Реплицированные Реплицированные Вторичные (Failover) Вторичные (Failover)1328 Всего2516 *(не включая административные БД)

9 Производственные БД ПодсистемаОсновныеКлоныВсего Assistance Platform BI Communities CRM & Marketing Downloads134 Learning Platform224 MSCOM MSDN Operations Support Windows459 Windows Media Windows Update As of Jan

10 Стандарты БД Кодирование & конфигурация серверов Конфигурация серверов В соответствии со стандартами MSN Datacenter (гибкость) Microsoft IT SQL IPAK (стандартная конфигурация) Все серверы устанавливаются скриптами Стандарты Microsoft.com Инструменты и мониторинг (MOM, Cluster Sentinel, SQL Litespeed) Стандарты кодирования БД Короткий перечень требований и длинный перечень Best Practices Тестирование и выпуск через Release Management перед развертыванием Весь код БД проходит анализ администраторами перед выпуском Медленный код может привести к недоступности приложения

11 Database Server Hardware Практически только HP Стандартная конфигурация «железа» Все серверы имеют сходную конфигурацию дисков (в зависимости от размера сервера) Серверы под SQL Server строятся с высоким уровнем избыточности Компоненты могут использоваться повторно даже после окончания гарантии на сервер Исключения Для особо крупных инсталляций SQL Servers используется SAN (обычно direct attach storage)

12 Hardware Platform Сравнение: 32 и 64 bit Основные положения Стоимость оборудования – x64 стоит дешевле (если считать по серверам) Оборудование способно исполнять x86 и x64 O/S Большие возможности CPU и памяти облегчают консолидацию или перемещение оборудования 64-bit H/W и Windows дают несомненное преимущество x86x HTTP Req/Sec: 65 % CPU 216 HTTP Req/Sec: 35 % CPU 1200 Trans/sec: 90%CPU 1200 Trans/sec: 50% CPU

13 Интегрированный мониторинг

14 План Немного о Microsoft.com Планирование перехода Upgrade Advisor Анализ скриптов Анализ показателей производительности до и после перехода Реализация / Уроки

15 Upgrade Advisor (UA) Microsoft IT использует UA для анализа обновляемой инсталляции SQL Server Если используется «отмершая» функциональность можно скорректировать приложение перед обновлением UA способен определить и отобразить изменения в конфигурации, которые будут сделаны в процессе обновления UA анализирует сервер, на котором исполняется SQL Server и предоставляет отчет о проблемах в следующих компонентах: Database Engine Analysis Services DTS SQL Server Agent Full-Text Search Notification Services Replication Reporting Services UA отмечает проблемные места и предоставляет ссылку на документацию

16 Стратегия обновления Анализ результатов использования Upgrade Advisor Для заказчиков Облегчает процесс принятия решения Позволяет определить наименее болезненный путь обновления Иные ситуации Новые приложения Разработка и выпуск приложений – уточнение графиков Прямое обновление – высокая степень обратной совместимости

17 План Немного о Microsoft.com Планирование перехода Upgrade Advisor Анализ скриптов Анализ показателей производительности до и после перехода Реализация / Уроки

18 Тестирование приложений Варианты Развертывание SS2k5 в тестовой среде Процесс управляется группами разработки и тестирования Как обеспечить нагрузку? Совместная работа с группой тестирования При соблюдении их интересов Тестирование на воспроизведении рабочей нагрузки Trace for Replay Profiler и Sysmon

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

20 Уроки: тестирование Правильно выбранный момент очень важен: для воспроизведения следует выбирать критические уровни нагрузки БД следует восстанавливать на соответствующий момент времени Графическое представление отличий не так точно, но все равно полезно Результаты профилирования/ мониторинга следует хранить в таблице для облегчения анализа

21 План Немного о Microsoft.com Планирование перехода Upgrade Advisor Анализ скриптов Анализ показателей производительности до и после перехода Реализация / Уроки

22 Стандарты архитектуры Мы по-прежнему используем разработанные ранее архитектуры «от добра добра не ищут» Имея такое количество серверов не уйти от стандартизации Стандартные архитектуры подтвердили свое качество и с ними проще обеспечить доступность услуг Позволяют сфокусироваться на приложении Нестандартные конфигурации есть, но тщательно задокументированы

23 Сценарий 1: Одиночный сервер Где это возможно Некритичные данные Данные изменчивы и легко восстанавливаются Где применяется Некоторые приложения, для внутреннего использования Внутренняя система отчетности

24 Сценарий 2: Резервный сервер (Log Shipping) За Вторичный сервер полностью независим от основного Может располагаться на значительном удалении (желательно иметь быструю связь) Против Переход на вторичный сервер требует переименования сервера –ИЛИ - клиент должен уметь менять строку подключения При неожиданном падении основного сервера возможна потеря данных Отдельные мероприятия по синхронизации учетных записей Где применяется Microsoft Search БД ICP (Internet Content Providers), открытые на запись Microsoft Product Information Catalog Microsoft Shop

25 Сценарий 3: Log Shipping & NLB За VIP (Virtual IP) выставляет в сеть единственное имя Проще обслуживать серверы Подходит для систем с модификаций денных Против NLB ничего не знает о SQL Server Избыточность при создании центра обработки данных невозможна Не все приложения умеют общаться с VIP или виртуальным именем Чтобы серверы видели друг друга нужны две сетевые карты Выделение VLAN Где используется PENS PubWiz (внутренний инструмент публикаций MS.com)

26 Сценарий 4: NLB & Replication для данных только на чтение За С точки зрения конечного пользователя все прекрасно (сервис доступен) Maintenance can occur on any system without impact to the end user Очень хорошо масштабируется Против Задержка между издателем и подписчиком. Теоретически повторный запрос может вывести другие данные (если попасть на другой сервер). Для крупных публикаций ресинхронизация - проблема Издатель - single point of failure Где используется DownloadsICPCMS

27 Сценарий 5: Комбинированная схема За Высокий уровень доступности для Web серверов, приложение все время видно Высокий уровень доступности для сотрудников MSFT, обновляющих контент Горизонтальное масштабирование Против Практически невозможно выполнять обновления на front end серверах Если издатель переходит на резервный сервер сотрудники MSFT не могут работать Проблемы ресинхронизации Не обеспечивается географически избыточная конфигурация Где используется MSI (Profile) Communities

28 Сценарий 6 – Избыточность для центра обработки данных За Высокий уровень доступности для Web серверов, приложение все время видно Высокий уровень доступности для сотрудников MSFT, обновляющих контент Горизонтальное масштабирование Против Практически невозможно выполнять обновления на front end серверах Если издатель переходит на резервный сервер сотрудники MSFT не могут работать Сотрудники MSFT должны перенастроить приложения Ресинхронизация Больше компонентов Где используется Windows Update v5 (три центра)

29 Планирование для SS2k5 Требования по доступности (возможная длительность перерыва) Размещение различных связанных приложений Выработка альтернативных решений Стандартизированные планы Архитектурные решения

30 Реализация аппаратной конфигурации Обновление на месте потребует больше времени чем альтернативные решения Переход на резервный сервер (log shipped instance) Позволяет обновление оборудования и ОС Позволяет перейти в другой центр обработки данных Установка именного экземпляра SQL Server 2005 на одиночный сервер Подключение БД Attach/Detach Внимание: БД будут подключены с уровнем совместимости 8.0

31 Peer to Peer Replication Data Center #1 Data Center #2 Для получения наилучших результатов приложение должно быть спроектировано с учетом задержек передачи

32 Peer to Peer Replication Использование NLB совместно с Peer to Peer Replication позволяет получить интересные возможности по масштабируемости и доступности

33 Database Mirroring Для получения наилучших результатов приложение должно понимать что работает с зеркалированным сервером, особенно, если используется автоматический переход Настройка: Создание полной копии основной БД в определенное место Создание копии журнала основной БД в то же место Восстановление зеркалируемой БД на зеркальном сервере с опцией NORECOVERY Восстановление журнала на зеркальном сервере с опцией NORECOVERY (это важно)

34 Репликация высокой интенсивности У БД Distribution отключают autostats; обновление статистики делают вручную Особая настройка параметров для обеспечения высокой интенсивности репликации

35 Репликация высокой интенсивности Параметры, которые мы используем Snapshot Agent Увеличиваем query timeout с 1800 до более высокого значения Distribution Agent Для размещения снимка: BcpBatchSize от 0 до 100,000. Эффективный размер пакета. MaxBcpThreads от 1 до 2x числа CPU. Если потоки начинают блокировать друг друга, можно снизить этот параметр. Использование UseInprocLoader значительно повышает производительность, но может дать ошибки. Использовать для больших транзакций (множество команд в транзакции) CommitBatchSize с 100 до 1. CommitBatchThreshold с 1000 до 100. MaxDevliveredTransactions с 0 до 10. Обеспечивает более оперативное взаимодействие при обработке больших транзакций. QueryTimeout с 1800 до 0. Log Reader Agent QueryTimeout 1800 до более высокого значения. Использовать для больших транзакций (множество команд в транзакции) MaxCmdsInTran ReadBatchSize 500 до 10.

36 Репликация высокой интенсивности Проверка значений параметров sp_help_agent_profile – получает ID профиля sp_help_agent_parameter – показывает параметры для этого ID *Скрипты в самом конце презентации

37 Репликация высокой интенсивности Distribution agent / Log reader должны доставлять транзакции с малой (~ 10 минут) задержкой Occasional blocking between replication agents and clean job. You may need to turn off clean job Run either log reader or distribution agent for a period of time, but not both at once Replication Check Job Checks for history logging every 10 minutes. Marks agent suspect if no logging. It is safe to disable this job if it causes problems.

38 Уроки Планирование Тщательное планирование – залог успешной миграции. Тщательное документирование существующей системы и параметров приложения – очень важный аспект планирования. Использование Upgrade Advisor позволило обнаружить и скорректировать проблемные места до начала миграции. Разработка списка задач до начала миграции обеспечило стандартизированный подход в каждом конкретном случае и дало возможность сформулировать дальнейшие планы. На основании опыта выполненных миграций Upgrade Advisor и SCC были расширены для более обширного списка рекомендаций.

39 Уроки Внедрение Тестовое внедрение на этапе планирования. Тестовое внедрение позволило уточнить необходимо время работ (достаточно ли доступного окна) После внедрения подробное документирование процесса и принятых решений существенно облегчает работу по дальнейшим проектам. Использование новой функциональности. Четкая идентификация на ранних этапах внедрения полезной для конкретного приложения новой функциональности позволила обеспечить гораздо более плавное проведение проекта. Например, аккуратное планирование секционирования (table- partitioning) обеспечило прирост производительности и более «теплый» прием конечными пользователями.

40 Уроки Перед началом миграции следует повысить уровень знаний о продукте: новой функциональности, не поддерживаемых более функциях, закрытой в этой версии функциональности… Использование встроенной функциональности: Расширенные возможности защиты Поддержка 64-bit оборудования в SQL Server 2005 Новые возможности для разработчиков Расширенные аналитические и отчетные возможности

41 Lessons Learned Built-in new features By using enhanced support for 64-bit mode in SQL Server 2005, Microsoft IT maintains a much more scalable environment in which server consolidation may occur, and in which performance has dramatically increased. By using developer enhancements in SQL Server 2005, Microsoft IT has delivered more robust and scalable solutions to meet new requirements from the various business groups within Microsoft at a reduced total cost of ownership. By using enhanced analysis and reporting abilities in SQL Server 2005, Microsoft IT can provide real-time insights into business values to help business groups make better-informed decisions.

42 Уроки резюме Очень немного проблем при миграции Увеличение времени доступности Увеличение производительности Улучшение масштабируемости Облегчение администрирования Снижение затрат

43 Ресурсы SS2k5 Upgrade Handbook px px px Replaying Traces us/library/ms190995(en-US,SQL.90).aspx us/library/ms190995(en-US,SQL.90).aspx us/library/ms190995(en-US,SQL.90).aspx Generating List of SQL Servers Microsoft.com Standard Server Configurations erconfig_note.mspx erconfig_note.mspx erconfig_note.mspx Monitoring and Troubleshooting Microsoft.com hoot.mspx hoot.mspx hoot.mspx

44 Вопросы?

45 Scripts --Replication: update statistics dynamic set nocount on select 'update statistics ' + name + char(10) + 'go' from sysobjects where type='u' -- sys.objects for sql 2005 order by 1

46 Scripts --Replication: yukon_manageReplAgentProfiles.sql set nocount on -- skorman: 12/ First find the profile ID for each of your agent profiles -- It shows which one is the default (being used) sp_help_agent_profile 2 -- log reader -- verify the profile ID and parameters set 19-HistoryVerboseLevel2 19-LoginTimeout30 19-LogScanThreshold PollingInterval10 19-QueryTimeout ReadBatchSize10 sp_help_agent_profile 1 -- snapshot reader 17-BcpBatchSize HistoryVerboseLevel2 17-LoginTimeout30 17-QueryTimeout >> sp_help_agent_profile 3 -- distribution agent 18-BcpBatchSize CommitBatchSize10 18-CommitBatchThreshold HistoryVerboseLevel2 18-KeepAliveMessageInterval LoginTimeout30 18-MaxBcpThreads2 18-MaxDeliveredTransactions0 18-PollingInterval10 18-QueryTimeout SkipErrors 18-TransactionsPerHistory Use this to change which profile is the default outside the gui -- Must turn off one and turn on the other. select * from msdb..msagent_profiles begin tran update msdb..msagent_profiles set def_profile=0 where profile_id=4 commit

47 Scripts --Replication: yukon_rebuild_DistributionIndexes.sql USE [distribution] GO /****** Object: Index [ucMSrepl_commands] Script Date: 12/15/ :09:33 ******/ CREATE UNIQUE CLUSTERED INDEX [ucMSrepl_commands] ON [dbo].[MSrepl_commands] ( [publisher_database_id] ASC, [xact_seqno] ASC, [command_id] ASC )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = ON, IGNORE_DUP_KEY = OFF, ONLINE = ON) ON [PRIMARY] GO /****** Object: Index [ucMSrepl_transactions] Script Date: 12/15/ :09:52 ******/ CREATE UNIQUE CLUSTERED INDEX [ucMSrepl_transactions] ON [dbo].[MSrepl_transactions] ( [publisher_database_id] ASC, [xact_seqno] ASC )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = ON, IGNORE_DUP_KEY = OFF, ONLINE = ON) ON [PRIMARY]

48 Upgrade Advisor Import 1 --First create the database you intend to use. Each script references this database. --enable Full Text indexing in the database you import the data to. use DBOps2 go set nocount on -- drop TABLE [dbo].[importDE]; CREATE TABLE [dbo].[importDE]( serverName varchar(39), importdate datetime, [F1_engine] [varchar](30), [F2_phase] [varchar](30), [F3] [varchar](1000) default null, [F4] [text] default null, [F5] [varchar](1000) default null, [F6] [varchar](1000) default null, [F7] [varchar](1000) default null) GO create view DEupgradeAdvice as select distinct serverName, F2_phase as issueType, F3 as issueDesc, cast(F4 as varchar(8000)) as issueText, F5 + ' '+F6 + ' ' + F7 as issueDetail from importDE GO -- drop table codeCheck; CREATE TABLE [dbo].[codeCheck]( source varchar(50), category varchar(75), status varchar(50), feature varchar(100), FindByKeyword varchar(100), FindByQuery varchar(500), FindByOther varchar(500), notes varchar(5000), moreInfo varchar(300), entryDate datetime default getdate() ) GO -- drop table importedCode; create table importedCode (servername varchar(39), databasename varchar(20), objname sysname, objtype varchar(20), code varchar(7500), pkid int identity(1,1) not null UNIQUE NONCLUSTERED ) GO create unique index pkid_importedCode on importedCode(pkid) go create table UAfileImport (id int identity(1,1) not null, serverName varchar(39), engineCode char(2), source varchar(5), engineName varchar(30), UAxml xml, UAout varchar(7500), importdate datetime default(getdate()) ) Go create table upgradeAdvice (serverName varchar(30), source varchar(5), engine char(2), category varchar(30), comment varchar(7930) ) go

49 Upgrade Advisor Import 2 --This script imports MOST Upgrade Advisor DE*.CSV reports. --It will need modification in some cases. use DBOps go --for demo delete from importDE set nocount on char(3) = 'csv' truncate table UAfileImport table (serverName varchar(20)) table (fileName varchar(50)) varchar(15) varchar(100) varchar(120) varchar(20) varchar(20) varchar(1500) char(1) nvarchar(1500) varchar(100) table (col1 int) table (serverName varchar(39)) = 'C:\_SQLupgrade\UAoutput\' = char(39) = 'dir /AD /B /ON ' (serverName) exec where serverName is NULL WHILE exists (select * begin = (select top 1 serverName delete where serverName + '\' = 'dir /A-D /B /ON ' + (fileName) exec delete where fileName is null --for demo delete where left(filename,2) 'DE' if exists (select * where filename like 'DE%') begin = (select top 1 fileName delete where fileName --rename the files (format of De.xml.csv will cause an error, so rename to De.csv) = 'ren --discard result exec = --select 'Importing = 'Insert importDE SELECT FROM OpenRowset(' ' * from exec --some files have six columns If not exists (select * from importDE where serverName begin select 'Retry 1 for = 'Insert importDE serverName, importdate,F1,F2,F3,F4,F5,F6 SELECT FROM OpenRowset(' ' * from exec end --and, some have five columns If not exists (select * from importDE where serverName begin select 'Retry 2 for = 'Insert importDE serverName, importdate,F1,F2,F3,F4,F5 SELECT FROM OpenRowset(' ' * from exec end If not exists (select * from importDE where serverName select 'Server could not be end delete end select distinct [Successfully imported:]= serverName from importDE -- select * from DBOps.dbo.importDE -- delete from importDE

50 © 2006 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.