Windows server 2008 r2 sql server 2016

Best Method to Upgrade SQL Server 2008 to 2016 – Step by Step

Are you looking for a reliable solution to Upgrade SQL Server 2008 to 2016 ? Do you find this task very challenging? Well, do not despair as the solution to the problem is very straightforward. All you need to do is keep reading this post till the end. Here, you will get the answer of the SQL Server 2008 / 2008 R2 to 2016 migration.

Let us understand the reasons why a user need to upgrade Microsoft SQL Server 2008 to 2016 database.

Reasons for Upgrading from SQL 2008

There are some factors which lead to SQL Server 2008 to 2016 Migration. All the factors are discussed in detail here.

SQL 2008 has reached its extended end of life that means it is not a good thing to keep using it. It is when the extended support ends and you need to choose the right SQL version. This results in the following danger:

• SQL Server 2008 will then no longer receive any security updates
• For security exploits, you have to put yourself at risk
• It may fall out of compliance with industry regulations

Some of the highlighted advantages of upgrading SQL Server 2008 to 2016 are listed below:

• Performance increases
• Could eliminate the weekend analytic loads
• Increased data security and advanced data encryption
• Easier upgrades by using data migration assistant

Why Migrate SQL Server 2008 to SQL Server 2016?

The main challenge standing in front of users is which version to choose for upgrading. Here, the answer is given. Some of the new features of SQL server 2016 which are not present in its previous versions are stated as below:

• Encrypt data
• Dynamic data masking
• Multiple TempDB database files during install
• Polybase
• JSON support
• Query store
• Row Level Security
• Stretch database
• Temporal Table

Upgrade SQL Server 2008 to 2016 – Step by Step Method

In the following section, we are going to provide the method for the same. Users need to follow these steps for SQL Server 2008 to 2016 Migration:

1. Install the SQL Server installation media. From the root folder, click on setup.exe. To insert from the network share, navigate to the root folder on the share and then, double-click on Setup.exe.
2. The installation wizard will open up the SQL Server installation center. To update a pre-existing version of SQL Server, hit on Installation from the left-hand side. Then, choose the option of Upgrade From and select the required option.
3. In the Product Key page, select an alternative to show if you are switching to free version of SQL server or you possess a PID key for the production version.
4. The License Term page will appear on the screen. View the license agreement and if you agree, choose the checkbox corresponding to I accept the license term and then hit on Next. You can also send reports to Microsoft to improve SQL Server.
5. In the Global Rules window, the setup procedure will open to the Product Updates window, in case no rule error occurs.
6. If the Microsoft update checkbox is not checked in Control PanelAll Control Panel ItemsWindows UpdateChange Settings, Microsoft Update page will open up on the screen. On selecting this checkbox in the Microsoft Update page, there will be a change in computer settings to include the latest updates.
7. On Products Update page, the latest available SQL server products will be displayed. In case, you do not want to include the updates, deselect the checkbox of Include SQL Server product update. If no product updates come into notice, then SQL server setup will not open up this page and auto advances to install setup files.
8. When the page of Install Setup Files open up, setup will give the progress report of downloading, extracting, and installing the setup files. If any update of SQL Server is present and is mentioned to be included, the update will also get installed.
9. In the Upgrade Rules window, the complete setup process will automatically migrate to Select instance window, in case no rule errors occur.
10. From the Select Instance page, mention the instance of SQL Server to be upgraded. In case, users want to upgrade shared features and management tools, choose the option of Upgrade shared features only.
11. A Select Features page will open up on the screen. The features to upgrade will be selected. After you will select a feature name, a description for each component will appear on the right-hand side of the window.
12. The prerequisites for a chosen feature will be present on the right–hand side column. The SQL server setup will insert all the prerequisites, which are not pre-installed.
13. On the page named Instance Configuration page, mention Instance ID for instance of SQL server.
14. Instance ID: By default, the instance name is utilized as Instance ID. This is used to examine registry keys and installation directories for the instance of SQL Server. This is the case specifically for named instance and default instance. In case of default instance, the name of the instance and instance ID will be MSSQLSERVER, whereas in case of non-default instance ID, give a value for the Instance ID toolbox.
15. All SQL Server service upgrades and packs will be applicable to every element of an SQL Server instance.
16. Installed Instances: The network will display instances of the SQL Server present on the computer where Setup is running. If a default instance of SQL Server is already installed on the system, then you should install a SQL Server named instance.
17. Workflow for the further steps is based on the selection of features during installation. You may not see all of the pages, varying according to the selections.
18. On the Server Configuration: All the default servcie accounts are appeared on the screen for the SQL Server services. The proper services which will be configured on this page, depending on the features you are upgrading.
19. Authentication and login information will be continued from the previous situation of SQL Server. A user can assign the same sign in an account to all the SQL server services, or a user can configure each service account separately. Another point to specify is whether services will start automatically, manually or are disabled. Microsoft suggests that users should configure each service account separately so that SQL server is equipped with minimum permissions.
20. To mention only login account for overall service accounts in this example of SQL server gives credentials at the bottom of the page.
21. On the page of Full-Text Search Upgrade Options, mention the upgrade alternatives for the database that is to be upgraded.
22. If all the rules will be passed, the Features Rules window will advance automatically.
23. Ready to Upgrade will show a treelike structure of alternatives that were mentioned during setup. To continue with the procedure, click on Install. At first step, SQL server setup will install the desired prerequisites for chosen features followed by installation of features.
24. As the process of installation will be going on, the progress page will give status so that you can manage installation progress.
25. After installation, the Complete page will give a link to the summary log file for installation and some other crucial notes. At last, click on Close to complete SQL Server installation.
26. If it is needed to restart the system, do it

In this way, users can upgrade SQL server 2008 to 2016 manually.

Quick Way to Upgrade Microsoft SQL Server 2008 to 2016

If the manual solution does not work well to migrate SQL Server 2008 to 2016. To make the process easy, you can go for an automated solution that is SQL Database Migration Tool for upgrading SQL Server 2008 to 2016. You can migrate SQL Server 2008 to the latest SQL version 2017, & its below version. The tool can easily set up on Windows 10, 8.1, 8 and 7 and perform the SQL Server database migration effortlessly.

Download Now
100% Safe & Secure

Final Words!!

In the upcoming time, MS SQL server 2008 will be of no use as Microsoft has announced its end of extended support. The performance factor will decline and users will upgrade their versions to upper version. Hence, this write-up explains step-by-step method & an automated solution to upgrade SQL server 2008 to 2016. Depends on users requirement, one can choose any of the above method that suits the best.

Frequently Asked Questions –

На глаза попалась уже вторая новость на Хабре о том, что скоро Microsoft «подружит» SQL Server и Linux. Но ни слова не сказано про SQL Server 2016 Release Candidate, который стал доступен для загрузки буквально на днях.

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

Начнем с установки экземпляра SQL Server 2016. Сам инсталлятор претерпел изменения по сравнению с предыдущей версией:

• Для установки доступна только x64 версия SQL Server-а (последний x86 билд вырезали еще в CTP2.3). Официально все звучит более лаконичнее: «SQL Server 2016 Release Candidate (RC0) is a 64-bit application. 32-bit installation is discontinued, though some elements run as 32-bit components.»
• Установка SQL Server 2016 на Windows 7 и Windows Server 2008 не поддерживается. Официальный список систем куда можно установить SQL Server: все x64 редакции Windows 8/8.1/10 и Windows Server 2012
• SSMS теперь не поставляется вместе с SQL Server и развивается отдельно. Скачать standalone редакцию SSMS можно по этой ссылке. Новая редакция SSMS поддерживает работу с SQL Server 2005..2016, поэтому теперь не нужно держать целый парк студий для каждой версии.
• Добавились два новых компонента, которые реализуют поддержку языка R и PolyBase (мост между SQL Server и Hadoop):

Для работы PolyBase требуется предварительно установить JRE7 или более свежую версию:

И не забыть потом добавить в исключения Firewall выбранный диапазон портов:

Отдельное спасибо Microsoft — теперь не нужно ковыряться в групповой политике, чтобы включить Instant File Initialization:

Также немного поменялся диалог по выбору дефолтных путей:

Для настройки tempdb сделали отдельную закладку на которой можно автоматически создать нужное число файлов и разнести их при необходимости по разным дискам. Но даже если этого не делать, радует, что при установке по умолчанию параметр Autogrowth будет не 1Mб (как раньше), а 64Mб.

При этом максимальный размер файла ограничен 256Мб. Можно задать и больше, но уже после установки:

На этом отличия в установке по сравнению с предыдущей версией заканчиваются.

Теперь посмотрим на то что еще поменялось…

Изменились настройки системной базы model, чтобы снизить число AutoGrow событий:

Почитать почему это плохо можно тут.

Также важно упомянуть, что некоторые Trace Flag-ги на новом SQL Server-е будут включены по умолчанию…

-T1118

SQL Server вычитывает данные с диска кусками по 64Кб (так называемыми экстентами). Экстент – это группа из восьми физически последовательных страниц (по 8Кб каждая) файлов базы данных.

Имеются два типа экстентов: смешанные и однородные. На смешанном экстенте могут храниться страницы с разных объектов. Такое поведение позволяет очень маленьким таблицам занимать минимальное количество места. Но чаще всего таблицы не ограничиваются размером в 64Кб и когда требуется более 8 страниц для хранения данных по одному объекту, то происходит переключение на выделение однородных экстентов.

Чтобы изначально выделять для объекта однородные экстенты был предусмотрен TF 1118, который рекомендовалось включать. И получалось, что работал он глобально для всех баз на сервере.

В 2016 версии такого уже не будет. Теперь для каждой пользовательской базы можно задать опцию MIXED_PAGE_ALLOCATION:

ALTER DATABASE test SET MIXED_PAGE_ALLOCATION OFF

Для системных баз данная опция включена по умолчанию, т.е. все остается, как и было ранее:

SELECT name, is_mixed_page_allocation_on
FROM sys.databases

Исключение сделано лишь для пользовательских баз и tempdb:

name              is_mixed_page_allocation_on
----------------- ---------------------------
master            1
tempdb            0
model             1
msdb              1
DWDiagnostics     0
DWConfiguration   0
DWQueue           0
test              0

Приведу небольшой пример:

IF OBJECT_ID('dbo.tbl') IS NOT NULL
    DROP TABLE dbo.tbl
GO
CREATE TABLE dbo.tbl (ID INT DEFAULT 1)
GO
CHECKPOINT
GO
INSERT dbo.tbl DEFAULT VALUES
GO

SELECT [Current LSN], Operation, Context, AllocUnitName, [Description]
FROM sys.fn_dblog(NULL, NULL)

MIXED_PAGE_ALLOCATION=ON:

MIXED_PAGE_ALLOCATION=OFF:

-T1117

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

Теперь предположим ситуацию: все файлы, входящие в файловую группу, имеют одинаковый размер. Создается большая временная таблица. Места в файле #1 не достаточно и разумеется происходит AutoGrow. Через время такая же таблица пересоздается, но вставка происходит в файл #2, потому что #1 временно заблокирован. Что в таком случае будет? AutoGrow для #2… и повторная задержка при выполнении запросов. Для таких случаев, был предусмотрен TF 1117. Работал он глобально и при нехватке места в одном файле вызывал AutoGrow для всех файлов в рамках одной файловой группы.

Теперь данный трейс-флаг включен по умолчанию для tempdb и может избирательно настраиваться для пользовательских баз:

ALTER DATABASE test
    MODIFY FILEGROUP [PRIMARY] AUTOGROW_ALL_FILES
GO
ALTER DATABASE test
    MODIFY FILEGROUP [PRIMARY] AUTOGROW_SINGLE_FILE
GO

Посмотрим на размер файлов:

USE tempdb
GO

SELECT
      name
    , physical_name
    , current_size_mb = ROUND(size * 8. / 1024, 0)
    , auto_grow =
        CASE WHEN is_percent_growth = 1
            THEN CAST(growth AS VARCHAR(10)) + '%'
            ELSE CAST(CAST(ROUND(growth * 8. / 1024, 0) AS INT) AS VARCHAR(10)) + 'MB'
        END
FROM sys.database_files
WHERE [type] = 0

name       physical_name                                       size_mb  auto_grow
---------- --------------------------------------------------- -------- ------------
tempdev    D:DATABASESSQL_2016RC0TEMPtempdb.mdf            8.000000 64MB
temp2      D:DATABASESSQL_2016RC0TEMPtempdb_mssql_2.ndf    8.000000 64MB
temp3      D:DATABASESSQL_2016RC0TEMPtempdb_mssql_3.ndf    8.000000 64MB
temp4      D:DATABASESSQL_2016RC0TEMPtempdb_mssql_4.ndf    8.000000 64MB

Создаем временную таблицу:

IF OBJECT_ID('#t') IS NOT NULL
    DROP TABLE #t
GO

CREATE TABLE #t (
    ID INT DEFAULT 1,
    Value CHAR(8000) DEFAULT 'X'
)
GO

INSERT INTO #t
SELECT TOP(10000) 1, 'X'
FROM [master].dbo.spt_values c1
CROSS APPLY [master].dbo.spt_values c2

Места чтобы вставить данные не хватит и произойдет AutoGrow.

AUTOGROW_SINGLE_FILE:

name       physical_name                                       size_mb     auto_grow
---------- --------------------------------------------------- ----------- ------------
tempdev    D:DATABASESSQL_2016RC0TEMPtempdb.mdf            72.000000   64MB
temp2      D:DATABASESSQL_2016RC0TEMPtempdb_mssql_2.ndf    8.000000    64MB
temp3      D:DATABASESSQL_2016RC0TEMPtempdb_mssql_3.ndf    8.000000    64MB
temp4      D:DATABASESSQL_2016RC0TEMPtempdb_mssql_4.ndf    8.000000    64MB

AUTOGROW_ALL_FILES:

name       physical_name                                       size_mb     auto_grow
---------- --------------------------------------------------- ----------- ------------
tempdev    D:DATABASESSQL_2016RC0TEMPtempdb.mdf            72.000000   64MB
temp2      D:DATABASESSQL_2016RC0TEMPtempdb_mssql_2.ndf    72.000000   64MB
temp3      D:DATABASESSQL_2016RC0TEMPtempdb_mssql_3.ndf    72.000000   64MB
temp4      D:DATABASESSQL_2016RC0TEMPtempdb_mssql_4.ndf    72.000000   64MB

-T2371

До 2016 версии для автоматического пересчета статистики использовалось магическое число «20% + 500 строк». Просто покажу на примере:

USE [master]
GO
SET NOCOUNT ON

IF DB_ID('test') IS NOT NULL BEGIN
    ALTER DATABASE [test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE [test]
END
GO
CREATE DATABASE [test]
GO
USE [test]
GO

IF OBJECT_ID('dbo.tbl', 'U') IS NOT NULL
    DROP TABLE dbo.tbl
GO
CREATE TABLE dbo.tbl (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Value CHAR(1)
)
GO
CREATE NONCLUSTERED INDEX ix ON dbo.tbl (Value)
GO

INSERT INTO dbo.tbl
SELECT TOP(10000) 'x'
FROM [master].dbo.spt_values c1
CROSS APPLY [master].dbo.spt_values c2

Чтобы обновилась статистика, нужно изменить:

SELECT [>=] = COUNT(1) * .20 + 500
FROM dbo.tbl
HAVING COUNT(1) >= 500

В нашем случае это 2500 строк. При этом не за один раз, а вообще… это значение кумулятивное. Выполняем сперва запрос:

UPDATE dbo.tbl 
SET Value = 'a'
WHERE ID <= 2000

Смотрим:

DBCC SHOW_STATISTICS('dbo.tbl', 'ix') WITH HISTOGRAM

RANGE_HI_KEY RANGE_ROWS    EQ_ROWS       DISTINCT_RANGE_ROWS  AVG_RANGE_ROWS
------------ ------------- ------------- -------------------- --------------
x            0             10000         0                    1

Статистика старая… Выполняем еще один запрос:

UPDATE dbo.tbl 
SET Value = 'b'
WHERE ID <= 500

Ура! Статистика обновилась:

RANGE_HI_KEY RANGE_ROWS    EQ_ROWS       DISTINCT_RANGE_ROWS  AVG_RANGE_ROWS
------------ ------------- ------------- -------------------- --------------
a            0             1500          0                    1
b            0             500           0                    1
x            0             8000          0                    1

А теперь предположим, что таблица у нас огромная… 10-20-30 миллионов строк. Чтобы пересчиталась статистика нам нужно изменить внушительный объём данных или вручную следить за обновлением статистики.

Начиная с SQL Server 2008R2 SP1 появился TF 2371, который вот тот «магический» процент занижал динамически в зависимости от общего числа строк:

< 25k    = 20%
> 30k    = 18%
> 40k    = 15%
> 100k   = 10%
> 500k   = 5%
> 1000k  = 3.2%

В SQL Server 2016 этот трейс флаг включен по умолчанию.

-T8048

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

SELECT waiting_tasks_count
FROM sys.dm_os_wait_stats
WHERE wait_type = 'CMEMTHREAD'
    AND waiting_tasks_count > 0

SELECT spins
FROM sys.dm_os_spinlock_stats
WHERE name = 'SOS_SUSPEND_QUEUE'
    AND spins > 0

то использование TF 8048 помогало избавиться от проблем с производительностью. В SQL Server 2016 данный трейс флаг включен по умолчанию.

SCOPED CONFIGURATION

На уровне базы появилась новая группа настроек:

Получить их можно из нового системного представления sys.database_scoped_configurations. Лично меня очень радует, что степень параллелизма менять можно не глобально как раньше, а настраивать персонально для каждой базы:

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0

Включать старый Cardinality Estimation (раньше приходилось включать TF 9481 либо понижать compatibility level до 2012):

ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON

Отключать Parameter Sniffing (раньше для этого включали TF 4136 или хардкодили OPTIMIZE FOR UNKNOWN)

ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF

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

ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON

Для любителей вызывать вызывать команду DBCC FREEPROCCACHE предусмотрели команду для очистки процедурного кеша:

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE

Аналог команды:

DECLARE @id INT = DB_ID()
DBCC FLUSHPROCINDB(@id)

Также думаю будет полезно добавить запрос, по которому можно отслеживать обьем процедурного кеша в разрезе баз:

SELECT db = DB_NAME(t.[dbid]), plan_cache_kb = SUM(size_in_bytes / 1024) 
FROM sys.dm_exec_cached_plans p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) t
WHERE t.[dbid] < 32767
GROUP BY t.[dbid]
ORDER BY 2 DESC

Теперь рассмотрим новые функции:

JSON_MODIFY

В RC0 добавили возможность модифицировать JSON c помощью функции JSON_MODIFY:

DECLARE @js NVARCHAR(100) = '{
    "id": 1,
    "name": "JC",
    "skills": ["T-SQL"]
}'

SET @js = JSON_MODIFY(@js, '$.name', 'Paul') -- update
SET @js = JSON_MODIFY(@js, '$.surname', 'Denton') -- insert
SET @js = JSON_MODIFY(@js, '$.id', NULL) -- delete
SET @js = JSON_MODIFY(@js, 'append $.skills', 'JSON') -- append

PRINT @js

{
    "name": "Paul",
    "skills": ["T-SQL","JSON"],
    "surname":"Denton"
}

STRING_ESCAPE

Также появилась функция STRING_ESCAPE, которая экранирует спецсимволы в тексте:

SELECT STRING_ESCAPE(N'JS/Denton "Deus Ex"', N'JSON')

------------------------
JS/Denton "Deus Ex"

STRING_SPLIT

Срочно радоваться! Наконец появилась функция STRING_SPLIT, которая избавляет нас от прежних извращений с XML и CTE:

SELECT * FROM STRING_SPLIT(N'1,2,3,,4', N',')

value
---------
1
2
3

4

Но есть и «ложка дегтя», функция работает только с разделителем в один символ:

SELECT * FROM STRING_SPLIT(N'1--2--3--4', N'--')

Msg 214, Level 16, State 11, Line 3
Procedure expects parameter ‘separator’ of type ‘nchar(1)/nvarchar(1)’.

С точки зрения производительности сравним старые методы сплита и новые:

SET STATISTICS TIME ON

DECLARE @x VARCHAR(MAX) = 'x' + REPLICATE(CAST(',x' AS VARCHAR(MAX)), 500000)

;WITH cte AS
(
    SELECT
        s = 1,
        e = COALESCE(NULLIF(CHARINDEX(',', @x, 1), 0), LEN(@x) + 1),
        v = SUBSTRING(@x, 1, COALESCE(NULLIF(CHARINDEX(',', @x, 1), 0), LEN(@x) + 1) - 1)
    UNION ALL
    SELECT
        s = CONVERT(INT, e) + 1,
        e = COALESCE(NULLIF(CHARINDEX(',', @x, e + 1), 0), LEN(@x) + 1),
        v = SUBSTRING(@x, e + 1, COALESCE(NULLIF(CHARINDEX(',',  @x, e + 1), 0), LEN(@x) + 1)- e - 1)
    FROM cte
    WHERE e < LEN(@x) + 1
)
SELECT v
FROM cte
WHERE LEN(v) > 0
OPTION (MAXRECURSION 0)

SELECT t.c.value('(./text())[1]', 'VARCHAR(100)')
FROM 
( 
    SELECT x = CONVERT(XML, '<i>' + REPLACE(@x, ',', '</i><i>') + '</i>').query('.')
) a
CROSS APPLY x.nodes('i') t(c)

SELECT *
FROM STRING_SPLIT(@x, N',')

Результаты выполнения:

(CTE)
 SQL Server Execution Times:
   CPU time = 18719 ms,  elapsed time = 19109 ms.

(XML)
 SQL Server Execution Times:
   CPU time = 4672 ms,  elapsed time = 4958 ms.

(STRING_SPLIT)
 SQL Server Execution Times:
   CPU time = 2828 ms,  elapsed time = 2941 ms.

Live Query Statistics

Что еще понравилось… В новой версии SSMS появилась возможность отслеживать как выполняется запрос в режиме реального времени:

Данный функционал поддерживается, не только в SQL Server 2016, но и для SQL Server 2014. На уровне метаданных данный функционал реализован посредством выборки из sys.dm_exec_query_profiles:

SELECT
      p.[sql_handle]
    , s.[text]
    , p.physical_operator_name
    , p.row_count
    , p.estimate_row_count
    , percent_complete = 100 * p.row_count / p.estimate_row_count
FROM sys.dm_exec_query_profiles p
CROSS APPLY sys.dm_exec_sql_text(p.[sql_handle]) s

Чтобы сохранить читабельность, за бортом этого обзора я оставил некоторые новые возможности SQL Server (Temporal Tables, Dynamic Data Masking и улучшения в In-Memory), которые планирую добавить после того как выйдет RTM версия.

Если хотите поделиться этой статьей с англоязычной аудиторией:
SQL Server 2016 RC0