Tools for Upgrade SQL Server 升級SQL Server的工具

Tools for Upgrade SQL Server

升級SQL Server的工具:

1.Data Migration Assistant
Microsoft Data Migration Assistant v5.2
https://www.microsoft.com/en-us/download/details.aspx?id=53595
System Requirements:
Supported Operating System
Windows Server 2016, Windows 10, Windows Server 2012, Windows 7, Windows 8, Windows 8.1
Windows 7 64-bit (or Windows Server 2012) and above
.NET Framework 4.7.2 64-bit or above

Supported source and target versions

• Source: SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017, SQL Server 2019 and, RDS SQL Server
• 
  
• Target: SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017, SQL Server 2019, Azure SQL Database, Azure SQL Database Managed Instance

What's new in Data Migration Assistant
https://docs.microsoft.com/en-us/sql/dma/dma-whatsnew?view=sql-server-ver15
Data Migration Assistant v4.5開始支援SQL Server Integration Service (SSIS)

Upgrade SQL Server using the Data Migration Assistant
https://docs.microsoft.com/en-us/sql/dma/dma-migrateonpremsql?view=sql-server-ver15


2.SQL Server 2014 Upgrade Advisor
SQL Server 2014 Upgrade Advisor
https://docs.microsoft.com/en-us/sql/sql-server/install/sql-server-2014-upgrade-advisor?view=sql-server-2014

Upgrade Advisor supports remote analysis of all SQL Server components except Reporting Services.

• Upgrade Advisor does not analyze remote instances of Reporting Services. To analyze an instance of SSRS, Upgrade Advisor must be installed on the computer where SSRS is installed.
• To analyze SQL Server Integration Services, you must have the SQL Server Database Engine installed and Integration Services installed on the same computer.
• 
  
• Servers that are running SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, or SQL Server 2012 can be upgraded to SQL Server 2014. Whereas some SQL Server components can be upgraded in place, others must be migrated, and still others have been superseded by new components. For example, starting with SQL Server 2005, Integration Services (SSIS) supersedes Data Transformation Services (DTS), and DTS is no longer supported in SQL Server 2014. When you formulate your upgrade plan, you might need to plan for updating your current DTS packages to SSIS packages.

The prerequisites for installing and running Upgrade Advisor are as follows:

• Windows Vista SP1, Windows Server 2008 beginning with SP2, Windows 7, or Windows Server 2008 R2.
• Windows Installer 4.5. You can install Windows Installer from the Windows Installer Web site.
• The .NET Framework, beginning with .NET Framework 4. The .NET Framework is available on the SQL Server 2014 product media, and from the SDK, redistributable, and service pack download Web site.
  • To install the .NET Framework 4 from the SQL Server 2014 media, locate the root of the disk drive. Then double-click the \redist folder, double-click the DotNetFrameworks folder, and run dotNetFx40_Full_x86_x64.exe (for both 32-bit and 64-bit operating systems).
• The Microsoft SQL Server Transact-SQL ScriptDom is a prerequisite for installing SQL Server 2014 Upgrade Advisor, and is not installed by Upgrade Advisor Setup. The Setup requires you to download and install the MicrosoftSQL ServerTransact-SQL ScriptDom from the SQL Server 2014 Feature Pack.

Use the following steps to complete the upgrade:

1. Review Hardware and Software Requirements for Installing SQL Server 2014.
2. Back up data and system settings.
3. Run Upgrade Advisor.
   Upgrade Advisor does not modify your data or change settings on your computer.
4. Review the issues identified in the Upgrade Advisor report.
5. Resolve any blocking issues that would prevent you from upgrading to SQL Server 2014.
6. Resolve any other pre-upgrade issues.
7. Run Upgrade Advisor to verify that all known issues have been addressed.
8. Run SQL Server 2014 Setup.
9. Resolve any post-upgrade and migration issues.

SQL Server support lifecycle 技術支援 產品週期

SQL Server support lifecycle 技術支援 產品週期

Products Released	Lifecycle Start Date	Mainstream Support End Date	Extended Support End Date	Service Pack Support End Date
SQL Server 2000 Service Pack 4	5/6/2005	4/8/2008	4/9/2013
SQL Server 2005	1/14/2006	Not Applicable	Not Applicable	7/10/2007
SQL Server 2005 Service Pack 4	12/13/2010	4/12/2011	4/12/2016
SQL Server 2008	11/7/2008	Not Applicable	Not Applicable	4/13/2010
SQL Server 2008 Service Pack 1	3/31/2009	Not Applicable	Not Applicable	10/11/2011
SQL Server 2008 Service Pack 2	9/24/2010	Not Applicable	Not Applicable	10/9/2012
SQL Server 2008 Service Pack 3	10/6/2011	Not Applicable	Not Applicable	10/13/2015
SQL Server 2008 Service Pack 4	7/7/2014	7/8/2014	7/9/2019
SQL Server 2008 R2	7/20/2010	Not Applicable	Not Applicable	7/10/2012
SQL Server 2008 R2 Service Pack 1	7/12/2011	Not Applicable	Not Applicable	10/8/2013
SQL Server 2008 R2 Service Pack 2	7/26/2012	Not Applicable	Not Applicable	10/13/2015
SQL Server 2008 R2 Service Pack 3	Review Note	7/8/2014	7/9/2019
SQL Server 2012	5/20/2012	Not Applicable	Not Applicable	1/14/2014
SQL Server 2012 Service Pack 1	11/7/2012	Not Applicable	Not Applicable	7/14/2015
SQL Server 2012 Service Pack 2	6/10/2014	Not Applicable	Not Applicable	1/10/2017
SQL Server 2012 Service Pack 3	12/1/2015	Not Applicable	Not Applicable	10/9/2018
SQL Server 2012 Service Pack 4	Review Note	7/11/2017	7/12/2022
SQL Server 2014	6/5/2014	Not Applicable	Not Applicable	7/12/2016
SQL Server 2014 Service Pack 1	4/14/2015	Not Applicable	Not Applicable	10/10/2017
SQL Server 2014 Service Pack 2	7/14/2016	Not Applicable	Not Applicable	1/14/2020
SQL Server 2014 Service Pack 3	10/30/2018	7/9/2019	7/9/2024
SQL Server 2016	6/1/2016	Not Applicable	Not Applicable	1/9/2018
SQL Server 2016 Service Pack 2	4/24/2018	7/13/2021	7/14/2026

SQL Server Support Lifecycle
https://support.microsoft.com/en-us/lifecycle/search/1044

SSH into Azure Linux Virtual Machine using SSH public-private key pair with Putty (使用Putty採用SSH private key連線到Azure Linux Virtual Machine)

SSH into Azure Linux Virtual Machine using SSH public-private key pair with Putty
使用Putty採用SSH private key連線到Azure Linux Virtual Machine

1.下載private key
如果使用Azure Cloud Shell(Bash)建立的Linux VM

az vm create --resource-group myRG1 --name myVM2 --image UbuntuLTS --admin-username azureuser --generate-ssh-keys

SSH public-private key會儲存在~/.ssh/目錄下
~/.ssh/id_rsa是private key
~/.ssh/id_rsa.pub是public key

2.下載id_rsa (private key)

輸入/id_rsa，然後按下Download

3.use the PuTTYgen to convert the key from the OpenSSH format to the PuTTY format.
將private key轉換為Putty可讀取的格式

按下Load，選擇C:\Temp\Azure\id_rsa 檔案

然後按下Save private key，將檔案儲存為C:\Temp\Azure\id_rsa.ppk

4.Configure SSH Keys Authentication for PuTTY
使用Pageant設定SSH KEY授權

啟動Pagent後，在系統右下角常駐程式區，在Pageant圖示按下右鍵選擇Add Key

選擇上一個步驟所產生的 id_rsa.ppk

5.Connect to Azure Linux VM with Putty

使用Putty連線到Azure Linux VM
Host Name輸入 使用者帳號@VM的外部IP，例如 azureuser@13.67.65.46

Pageant does not load SSH-2 key generated with GitBash
https://stackoverflow.com/questions/27129904/pageant-does-not-load-ssh-2-key-generated-with-gitbash
How To Configure SSH Keys Authentication With PuTTY And Linux Server
https://putty.org.ru/articles/putty-ssh-key-auth.html

Microsoft.ACE.OLEDB.12.0 provider is not registered

SQL Server Import and Export Wizard
The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine

1.當SSMS是在x64的主機，且主機有安裝64bit Office，此時只能下載安裝AccessDatabaseEngine_X64.exe

Microsoft Access Database Engine 2010 Redistributable
https://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=13255

然後，使用64 bit的SQL Server Import and Export Wizard
C:\Program Files\Microsoft SQL Server\150\DTS\Binn\DTSWizard.exe

2.當SSMS是在x64的主機，且主機有安裝32bit Office，此時只能下載安裝AccessDatabaseEngine.exe

Microsoft Access Database Engine 2010 Redistributable
https://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=13255

然後，使用32 bit的SQL Server Import and Export Wizard
C:\Program Files (x86)\Microsoft SQL Server\150\DTS\Binn\DTSWizard.exe