This is one of those fresh ideas I wish I had but in reality it did not occur to me until it came down to me for execution from above me. Good job managers! Kudos. Sorry, I did not come-up with this! Execution: “I can do really well and you have got the best”.
I do save money in the range of millions of Dollars every year for my organization in one form or another though; this year is no different from that respect! I am not kidding and my managers are free to refute this fact if they feel otherwise.
By downgrading editions, I mean switching from Enterprise Edition to Standard Edition. The version of the SQL Server software and the service pack level will remain the same. If done right, there should be no practical reason to retest all of your applications although it would not hurt to do so.
“SQL 2016 Standard” has what was enterprise features of prior versions
If you did not hear the news, what comprises “Enterprise” has changed from versions prior. In 2016, a lot of what used to be “Enterprise-only” features are now “Standard”. If you can afford to upgrade to SQL Server 2016, you should make use of the opportunity. The procedure I show below would still be needed to convert the version you have on hand to “Standard” edition and then you can do an in-place upgrade to SQL Server 2016 Standard edition!
Why downgrade edition?
There are two reasons –
- Save money
Save moneyYou mostly only need Standard Edition!Save moneyThere is practically no difference between Standard & Enterprise
Standard edition is limited to lesser of 4 sockets or 24 cores with a maximum memory of 128 GB plus a few truly Enterprise level features like Compression, Availability Groups, Partitioning, ColumnStore indexes etc are off limits. I would say most places would fall under this threshold for “Standard” but feel inferior to say they run “Standard”! I don’t, especially when money matters.
But, all kidding aside, most shops don’t even realize that they do not use any Enterprise features on 90% of their instances but pay Enterprise price anyway! If you don’t trust me, go check for yourself at your place – we did, on hundred’s of SQL Server instances! I painfully built the infrastructure to do this type of thing using PowerShell in seconds if not a few minutes, for scanning hundreds of servers/instances.
You could easily check each database on your instances by running this query to see if any enterprise features are in use and if it returned nothing, it is a perfect candidate for a downgrade!
SELECT feature_name FROM sys.dm_db_persisted_sku_features
Microsoft makes everything easy but when it comes to downgrading your SQL Server edition from say Enterprise to Standard, there isn’t a switch. For Microsoft that would have been easy but they made it hard for a reason! They don’t want you to downgrade your edition. You will understand why when you look at the pricing differences
| SQL Server 2016 Enterprise Edition (per core) | SQL Server 2016 Standard Edition (per core) | SQL Server 2016 Standard Edition (CAL) |
| $ 14,256 | $3,717 | $931 |
This is for volume licensing.
Savings on 50 instances with 4 cores each
Standard is almost 1/4 th the cost of Enterprise edition!
| SQL Server 2016 Enterprise Edition (50 instances with 4 cores each) | SQL Server 2016 Standard Edition (50 instances with 4 cores each) | Savings |
| $ 2,851,200 | $ 743,400 | $ 2,107,800 |
I could only wish that the savings number was my bonus this year!
Hard vs. Easy way to downgrade:
Obviously, the hard way to downgrade is to make backups of [E V E R Y T H I N G] imaginable on SQL Server, SSRS, SSAS and SSIS in addition to logins, jobs, linked servers, credentials, security, proxies etc., and restore them perfectly. This is in addition to the numerous instance level settings/configuration. The very thought of missing something is the reason enough for most people to not attempt this and continue to waste money.
Fortunately, there is an easier way and it turns out to be really simple – The Jonathan Kehayias method. Thanks Jonathan.
The over-simplified steps are:
- Make backups of everything (optional but is good for safety)
- Shutdown
- Make a physical file backup of the system databases master, msdb, model
- Uninstall the enterprise edition
- Remove system database files if they are still left over (user db’s will remain as is)
- Install standard edition using the same folder locations/settings
- Get up to the same service pack level as before
- Shutdown standard edition
- Copy back the system database files
- You are done…the user databases, security settings, jobs and everything will reappear!
**WARNING**
This method DOES NOT seem to work for SQL Server versions 2014 and above. However, I can personally vouch for versions 2008, 2008R2 and 2012 where I know that it works.
Why automate such a simple process?
It is so simple that you could do it in your sleep. However, there are reasons you want to at least partially automate this
- You have a ton of instances where you need to downgrade edition
- You don’t want to check all the details for every instance
- You want your commands ready to go and not make stupid mistakes
- You want a clean and consistent record of your activity/backups
- Save time and be efficient
- You don’t want to forget a step and lose everything in the process
I chose PowerShell to generate the steps as PowerShell commands with text comments/instructions so that I can run through them to perform the downgrade.
Note: This only generates a set of helpful commands to do the downgrade and not produce a script that does the whole thing for you! i.e., semi-automate.
Are there any prerequisites to automate?
Besides PowerShell, you really don’t need much but life is easier with some of the modules below. It makes the commands very precise. They don’t have to be on the host being downgraded. It could be on a jump-box where you usually do admin stuff.
PowerShell SQL Server related modules:
- dbatools
- SQLTranscriptase
- SQLPS or SQLSERVER PowerShell module
Yes. I have started to fall in love with dbatools and use it quite a bit. Thank you dbatools team. Also, I love the PowerShell documentor for SQL instances – SQLTranscriptase. Thanks to George Walkey. It is very thorough with its documentation and script generation.
dbatools is a general purpose swiss army knife for the DBA and SQLTranscriptase is a must have documentation module for documenting your instance. Both of these are used. The latter is optional. SQLPS is the native PowerShell module for older versions of SQL Server and SQLSERVER is the PowerShell module for newer versions but is backwards compatible. You should be able to google and find out how to install these.
You also need
Again, this could be on a network share somewhere so that you are not having to deal with constantly copying it around.
- The software installation files location to the edition of SQL Server installed (need this to uninstall via command line)
- The software installation files location which has the Standard edition setup.exe
- The software installation location to the service pack equal to the service pack that was in place before switching editions.
How the “generated” instructions look?
Basically, the instructions try to do everything outlined in Jonathan’s method but in addition in also uses SQLTranscriptase to document the instance. When instructions are followed and commands are run, all backups and documentation will be stored under an common folder (UNC share, perhaps).
The instructions only include the components that were already installed. So, if SSAS is not installed already, in the generated instructions, SSAS will not be included.
For obvious reasons, I have switched out names and locations but you get the idea..
----------------------------
General-Outline
----------------------------
The function will generate instructions which can then be used to do the downgrade.
Below link has the procedure we will follow to downgrade edition. Please read and follow along
https://www.mssqltips.com/sqlservertip/3079/downgrade-from-sql-server-enterprise-edition-to-standard-edition/
Commands from the following PowerShell modules used and hence required
1) SQLPS/SQLServer PowerShell module should already be installed
https://docs.microsoft.com/en-us/sql/relational-databases/scripting/import-the-sqlps-module
https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-ps-module
This is the native module for PowerShell from MS. SQLPS is the older one and SQLServer the newer.
2) dbatools should be installed and loaded or the path to it is needed
https://dbatools.io/
This module is a must-have for all SQL Server DBA's and command-line junkies!
3) SQLTranscriptase (optional) should be installed and loaded or the path to it is needed
https://github.com/gwalkey/SQLTranscriptase
This module is a great documentation module that I find useful. Thanks to George Walkey!
*****WARNING*****: This is just 'help' and is not a program or even the exact procedure to follow!
Run the generation script twice or as many times as you want to re-generate the instructions
For example, after you find the service account username, you may want to run with the right parameter value for that!
The following are not handled and you will have to handle them manually
1) You would have to pass in the right parameters, especially for setup files for right versions!
2) You have to note the right service pack of the installed version and pass the right location of the noted SP when reinstalling
3) Some commands have to be run on the MY_JUMP_SERVER machine from PoSh and other commands like file-copy/uninstall/install on the SQL Server host from PoSh
4) INSTANCEDIR parameter for setup.exe has to still be verified as working correctly. This is the folder that has the binaries and log folders.
Eg. D:\MSSQL10.MYINST\MSSQL\Backup|Binn|DATA|Install|JOBS|Log|Upgrade
In the case of 'D:\MSSQL10.MYINST\MSSQL\Log\ERRORLOG', D:\ is the INSTANCEDIR.
In the case of 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\Log\ERRORLOG', the INSTACEDIR is 'D:\Program Files\Microsoft SQL Server\'
5) You have to manually check if the system files are at the same location after the standard edition is installed by checking sys.master_files system view
6) The service account user names for SQL Services are automatically filled in. You need to note and pass them as parameters.
----------------------------
Import-PowerShellModules
----------------------------
if(-not (Get-Module dbatools))
{
Import-Module "c:\github\dbatools\dbatools.psd1" -Force
}
if(-not (Get-Module SQLPS))
{
Import-Module SQLPS
}
----------------------------
SQL-InstalledVersion
----------------------------
Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64)
Aug 19 2014 12:21:34
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
----------------------------
Install-LatestServicePack
----------------------------
Look at the Service Pack number for SQL Server.
Before the edition is changed, it may be a good idea to
get the instance(s) to the latest service pack so that
we dont have to hunt down the exact service pack later!
The commands to use are down below - search for 'Install-ServicePack'.
----------------------------
SQL-FeaturesInstalled
----------------------------
SystemName : MYDBSERVER
Name : MSOLAP$MYINST
StartName : myorg\svc_account
State : Running
StartMode : Auto
DisplayName : SQL Server Analysis Services (MYINST)
SystemName : MYDBSERVER
Name : MSSQL$MYINST
StartName : myorg\svc_account
State : Running
StartMode : Auto
DisplayName : SQL Server (MYINST)
SystemName : MYDBSERVER
Name : SQLAgent$MYINST
StartName : myorg\svc_account
State : Stopped
StartMode : Manual
DisplayName : SQL Server Agent (MYINST)
SystemName : MYDBSERVER
Name : SQLBrowser
StartName : NT AUTHORITY\LOCALSERVICE
State : Running
StartMode : Auto
DisplayName : SQL Server Browser
SystemName : MYDBSERVER
Name : SQLWriter
StartName : LocalSystem
State : Running
StartMode : Auto
DisplayName : SQL Server VSS Writer
----------------------------
SQL-ServiceAccountsUsed
----------------------------
myorg\svc_account
NT AUTHORITY\LOCALSERVICE
LocalSystem
----------------------------
SQL-DiskUsageInfo
----------------------------
Server Name Label SizeInGB FreeInGB PercentFree BlockSize IsSqlDisk FileSystem DriveType
------ ---- ----- -------- -------- ----------- --------- --------- ---------- ---------
MYDBSERVER \\?\Volume{1612ce56-baf8-11e1-b520-806e6f6e6963}\ System Reserved 0.1 0.07 70.36 4096 False NTFS Local Disk
MYDBSERVER E:\ DATA_VOLM 50 35.35 70.71 4096 True NTFS Local Disk
MYDBSERVER F:\ DATA_VOLM 50 36.36 72.72 4096 True NTFS Local Disk
MYDBSERVER H:\ DATA_VOLM 50 36.3 72.61 4096 True NTFS Local Disk
MYDBSERVER T:\ SCOR_TEMP 50 47.87 95.74 4096 True NTFS Local Disk
MYDBSERVER G:\ DATA_VOLM 50 36.36 72.72 4096 True NTFS Local Disk
MYDBSERVER D:\ ORGS_APP 500 155.14 31.03 4096 True NTFS Local Disk
MYDBSERVER L:\ ORGS_LOG 500 487.97 97.59 4096 True NTFS Local Disk
MYDBSERVER C:\ MY_WINSYS 99.9 48.34 48.39 4096 False NTFS Local Disk
MYDBSERVER X:\ 0 0 0 False Compact Disk
----------------------------
SQL-FileSizeInfo
----------------------------
ComputerName InstanceName SqlInstance Database FileName FileGroup PhysicalName FileType UsedSpaceMB FreeSpaceMB
------------ ------------ ----------- -------- -------- --------- ------------ -------- ----------- -----------
MYDBSERVER MYINST MYDBSERVER\MYINST OrgServer OrgServer PRIMARY D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\DATA\OrgServer.mdf ROWS 1978 905
MYDBSERVER MYINST MYDBSERVER\MYINST OrgServer OrgServer_log D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\DATA\OrgServer_log.LDF LOG 11 390
MYDBSERVER MYINST MYDBSERVER\MYINST ITShell ITShell PRIMARY D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\DATA\ITShell.mdf ROWS 16 2
MYDBSERVER MYINST MYDBSERVER\MYINST ITShell ITShell_Log D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\DATA\ITShell_Log.ldf LOG 0 1
MYDBSERVER MYINST MYDBSERVER\MYINST ITShellExtra ITShellExtra PRIMARY D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\DATA\ITShellExtra.MDF ROWS 2 1
MYDBSERVER MYINST MYDBSERVER\MYINST ITShellExtra ITShellExt... D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\DATA\ITShellExtra_l... LOG 0 1
MYDBSERVER MYINST MYDBSERVER\MYINST master master PRIMARY D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\DATA\master.mdf ROWS 3 1
MYDBSERVER MYINST MYDBSERVER\MYINST master mastlog D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\DATA\mastlog.ldf LOG 0 1
MYDBSERVER MYINST MYDBSERVER\MYINST model modeldev PRIMARY D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\DATA\model.mdf ROWS 1 1
MYDBSERVER MYINST MYDBSERVER\MYINST model modellog D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\DATA\modellog.ldf LOG 0 0
MYDBSERVER MYINST MYDBSERVER\MYINST msdb MSDBData PRIMARY D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\DATA\MSDBData.mdf ROWS 336 223
MYDBSERVER MYINST MYDBSERVER\MYINST msdb MSDBLog D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\DATA\MSDBLog.ldf LOG 4 78
MYDBSERVER MYINST MYDBSERVER\MYINST ReportServer ReportServer PRIMARY D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\DATA\ReportServer.mdf ROWS 16 4
MYDBSERVER MYINST MYDBSERVER\MYINST ReportServer ReportServ... D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\DATA\ReportServer_l... LOG 1 1
MYDBSERVER MYINST MYDBSERVER\MYINST ReportSer... ReportServ... PRIMARY D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\DATA\ReportServerTe... ROWS 2 1
MYDBSERVER MYINST MYDBSERVER\MYINST ReportSer... ReportServ... D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\DATA\ReportServerTe... LOG 0 0
MYDBSERVER MYINST MYDBSERVER\MYINST ORGPlanni... ORGPlannin... PRIMARY D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\DATA\ORGPlanningToo... ROWS 20136 12689
MYDBSERVER MYINST MYDBSERVER\MYINST ORGPlanni... ORGPlannin... D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\DATA\ORGPlanningToo... LOG 69 10171
MYDBSERVER MYINST MYDBSERVER\MYINST ORGPlanni... ORGPlannin... PRIMARY D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\DATA\ORGPlanningToo... ROWS 3 1
MYDBSERVER MYINST MYDBSERVER\MYINST ORGPlanni... ORGPlannin... D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\DATA\ORGPlanningToo... LOG 0 1
MYDBSERVER MYINST MYDBSERVER\MYINST KUTETechB... KUTETechBugDB PRIMARY D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\DATA\KUTETechBugDB.mdf ROWS 2 0
MYDBSERVER MYINST MYDBSERVER\MYINST KUTETechB... KUTETechBu... D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\DATA\KUTETechBugDB_... LOG 0 0
MYDBSERVER MYINST MYDBSERVER\MYINST tempdb tempdev PRIMARY T:\MSSQL10_50.MYINST\MSSQL\DATA\tempdb.mdf ROWS 2 1022
MYDBSERVER MYINST MYDBSERVER\MYINST tempdb templog H:\MSSQL10_50.MYINST\MSSQL\DATA\templog.ldf LOG 151 873
MYDBSERVER MYINST MYDBSERVER\MYINST tempdb tempdev1 PRIMARY E:\MSSQL10_50.MYINST\MSSQL\DATA\tempdb1.ndf ROWS 1 1023
MYDBSERVER MYINST MYDBSERVER\MYINST tempdb tempdev2 PRIMARY F:\MSSQL10_50.MYINST\MSSQL\DATA\tempdb2.ndf ROWS 1 1023
MYDBSERVER MYINST MYDBSERVER\MYINST tempdb tempdev3 PRIMARY G:\MSSQL10_50.MYINST\MSSQL\DATA\tempdb3.ndf ROWS 0 1024
----------------------------
SQL-SSASDatabases
----------------------------
ITShell
FinPlanner
CorpPlanner
CostPlanner
AssetPlanner
WeatherPlanner
TaxPlanner
----------------------------
Do-SSASBackup
----------------------------
------------------------------------------------------------------
WARNING: These SSAS database backups are purely for safety reasons.
If you have huge databases (see disk/file usage info above), and
if you already have backups that can be used in case of failure, you can skip this step.
If you run into errors, check to see if service account has permissions to backup to UNC share.
------------------------------------------------------------------
Backup-DbaDatabase -SqlInstance MYDBSERVER\MYINST -CopyOnly -BackupDirectory \\myorgcifs\sqlstuff\SQLServerEditionDowngrade\MYDBSERVER_MYINST\SSASBackups -Type Database -CompressBackup
Write-Verbose "Backup SSAS database [TaxPlanner] to [\\myorgcifs\sqlstuff\SQLServerEditionDowngrade\MYDBSERVER_MYINST\SSASBackups\TaxPlanner.abf]"
Backup-ASDatabase -BackupFile '\\myorgcifs\sqlstuff\SQLServerEditionDowngrade\MYDBSERVER_MYINST\SSASBackups\TaxPlanner.abf' -ApplyCompression -Server 'MYDBSERVER\MYINST' -Name TaxPlanner -AllowOverwrite -Confirm
----------------------------
Affected-Databases
----------------------------
The following databases will be affected
master
tempdb
model
msdb
ReportServer
ReportServerTempDB
.....
..... cleared to conserve space
----------------------------
Connected-Users
----------------------------
The following users are currently connected (idle and non-idle)
\
myorg\myuserid
----------------------------
Email-Users
----------------------------
Send out an email to owners on the host (may contain other SQL instances and application servers)
Suggested text is (please replace as necessary):
Hi team,
We are getting ready to downgrade the SQL Server edition on [MYDBSERVER\MYINST] from Enterprise to Standard edition for managing our licensing costs.
The maintenance is planned for later today unless you propose an alternate time. The SQL Server databases on the instance will be unavailable for approximately 8 hours.
Please let me know if you have any objections to the proposed maintenance today. I will notify when the downgrade is complete and the application services may have to be restarted to reconnect.
Thanks,
DBA Team
----------------------------
Do-SQLBackups
----------------------------
------------------------------------------------------------------
WARNING: These copy-only database backups are purely for safety reasons.
If you have huge databases (see disk/file usage info above), and
if you already have backups that can be used in case of failure, you can skip this step.
If you run into errors, check to see if service account has permissions to backup to UNC share.
------------------------------------------------------------------
New-Item -Path '\\myorgcifs\sqlstuff\SQLServerEditionDowngrade\MYDBSERVER_MYINST\Backups' -ItemType Container -Force | Out-Null
Backup-DbaDatabase -SqlInstance MYDBSERVER\MYINST -CopyOnly -BackupDirectory \\myorgcifs\sqlstuff\SQLServerEditionDowngrade\MYDBSERVER_MYINST\Backups -Type Database -CompressBackup
----------------------------
Export-SQLTranscriptaseScripts
----------------------------
Push-Location
Set-Location \\myorgcifs\tools\SQLTranscriptase
.\00_RunAllScripts.ps1 -SQLInstance 'MYDBSERVER\MYINST'
Pop-Location
New-Item '\\myorgcifs\sqlstuff\SQLServerEditionDowngrade\MYDBSERVER_MYINST\SQLTranscriptase' -ItemType Directory | Out-Null
Copy-Item '\\myorgcifs\tools\SQLTranscriptase\MYDBSERVER' '\\myorgcifs\sqlstuff\SQLServerEditionDowngrade\MYDBSERVER_MYINST\SQLTranscriptase' -Container -Recurse
Remove-Item '\\myorgcifs\tools\SQLTranscriptase\MYDBSERVER' -Force -Recurse -Confirm:$false
----------------------------
SQL-SoftwareLocation
----------------------------
D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL
----------------------------
SQL-SoftwareDrive
----------------------------
D
----------------------------
SQL-InstanceName
----------------------------
MYINST
----------------------------
SQL-ServerCOllation
----------------------------
SQL_Latin1_General_CP1_CI_AS
----------------------------
SQL-DefaultDataDir
----------------------------
D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\DATA
----------------------------
SQL-DefaultLogDir
----------------------------
D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\DATA
----------------------------
SQL-DefaultBackupDir
----------------------------
D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\Backup
----------------------------
SQL-InstanceDrive
----------------------------
D:\
----------------------------
SQL-ErrorLogFileName
----------------------------
D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\Log\ERRORLOG
----------------------------
SQL-InstanceDir
----------------------------
D:\Program Files\Microsoft SQL Server
----------------------------
SQL-TempDBDataDir
----------------------------
T:\MSSQL10_50.MYINST\MSSQL\DATA
----------------------------
SQL-TempDBLogDir
----------------------------
H:\MSSQL10_50.MYINST\MSSQL\DATA
----------------------------
Stop-SQLServicesForFileBackup
----------------------------
Stop-DbaSqlService -ComputerName MYDBSERVER
----------------------------
Make-SystemFileBackupsDir
----------------------------
New-Item \\myorgcifs\sqlstuff\SQLServerEditionDowngrade\MYDBSERVER_MYINST\FileBackups -ItemType Directory | Out-Null
----------------------------
Copy-SystemDBFiles(!!--AndOptionallyNonSys--!!)
----------------------------
------------------------------------------------------------------
WARNING: There is no need to make a file-copy of non-system DB files
The copy commands for non-system DB's are here only for convenience!
Definetely make a copy of the system-DB files and ensure there are no errors.
Without the physical copy of system DB files, it is game-over.
------------------------------------------------------------------
copy 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\DATA\master.mdf' '\\myorgcifs\sqlstuff\SQLServerEditionDowngrade\MYDBSERVER_MYINST\FileBackups\'
copy 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\DATA\mastlog.ldf' '\\myorgcifs\sqlstuff\SQLServerEditionDowngrade\MYDBSERVER_MYINST\FileBackups\'
copy 'T:\MSSQL10_50.MYINST\MSSQL\DATA\tempdb.mdf' '\\myorgcifs\sqlstuff\SQLServerEditionDowngrade\MYDBSERVER_MYINST\FileBackups\'
copy 'H:\MSSQL10_50.MYINST\MSSQL\DATA\templog.ldf' '\\myorgcifs\sqlstuff\SQLServerEditionDowngrade\MYDBSERVER_MYINST\FileBackups\'
copy 'E:\MSSQL10_50.MYINST\MSSQL\DATA\tempdb1.ndf' '\\myorgcifs\sqlstuff\SQLServerEditionDowngrade\MYDBSERVER_MYINST\FileBackups\'
copy 'F:\MSSQL10_50.MYINST\MSSQL\DATA\tempdb2.ndf' '\\myorgcifs\sqlstuff\SQLServerEditionDowngrade\MYDBSERVER_MYINST\FileBackups\'
copy 'G:\MSSQL10_50.MYINST\MSSQL\DATA\tempdb3.ndf' '\\myorgcifs\sqlstuff\SQLServerEditionDowngrade\MYDBSERVER_MYINST\FileBackups\'
copy 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\DATA\model.mdf' '\\myorgcifs\sqlstuff\SQLServerEditionDowngrade\MYDBSERVER_MYINST\FileBackups\'
copy 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\DATA\modellog.ldf' '\\myorgcifs\sqlstuff\SQLServerEditionDowngrade\MYDBSERVER_MYINST\FileBackups\'
copy 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\DATA\MSDBData.mdf' '\\myorgcifs\sqlstuff\SQLServerEditionDowngrade\MYDBSERVER_MYINST\FileBackups\'
copy 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\DATA\MSDBLog.ldf' '\\myorgcifs\sqlstuff\SQLServerEditionDowngrade\MYDBSERVER_MYINST\FileBackups\'
copy 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\DATA\ReportServer.mdf' '\\myorgcifs\sqlstuff\SQLServerEditionDowngrade\MYDBSERVER_MYINST\FileBackups\'
copy 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\DATA\ReportServer_log.LDF' '\\myorgcifs\sqlstuff\SQLServerEditionDowngrade\MYDBSERVER_MYINST\FileBackups\'
copy 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\DATA\ReportServerTempDB.mdf' '\\myorgcifs\sqlstuff\SQLServerEditionDowngrade\MYDBSERVER_MYINST\FileBackups\'
copy 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\DATA\ReportServerTempDB_log.LDF' '\\myorgcifs\sqlstuff\SQLServerEditionDowngrade\MYDBSERVER_MYINST\FileBackups\'
...
...cleared user DB copies to conserve space
----------------------------
Uninstall-EnterpriseEdition
----------------------------
\\myorgsoftware\install\SqlServer\2008\EE\setup.exe /ACTION="unInstall" /INDICATEPROGRESS="TRUE" /FEATURES=SQLENGINE,REPLICATION,BIDS,CONN,IS,BC,SDK,BOL,SSMS,ADV_SSMS,OCS,AS /INSTANCENAME="MYINST"
----------------------------
Remove-SystemDBFiles
----------------------------
------------------------------------------------------------------
WARNING: The system DB files (.mdf and .ldf) will be removed by the uninstaller.
The instructions to remove are here just in case, to make sure.
The user DB files will not be removed by uninstall and should be left alone.
Verify that all system DB files were copied before running the below delete!
------------------------------------------------------------------
<span data-mce-type="bookmark" id="mce_SELREST_start" data-mce-style="overflow:hidden;line-height:0" style="overflow:hidden;line-height:0" ></span>Remove-Item 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\DATA\master.mdf' -Force -Confirm:$false
Remove-Item 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\DATA\mastlog.ldf' -Force -Confirm:$false
Remove-Item 'T:\MSSQL10_50.MYINST\MSSQL\DATA\tempdb.mdf' -Force -Confirm:$false
Remove-Item 'H:\MSSQL10_50.MYINST\MSSQL\DATA\templog.ldf' -Force -Confirm:$false
Remove-Item 'E:\MSSQL10_50.MYINST\MSSQL\DATA\tempdb1.ndf' -Force -Confirm:$false
Remove-Item 'F:\MSSQL10_50.MYINST\MSSQL\DATA\tempdb2.ndf' -Force -Confirm:$false
Remove-Item 'G:\MSSQL10_50.MYINST\MSSQL\DATA\tempdb3.ndf' -Force -Confirm:$false
Remove-Item 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\DATA\model.mdf' -Force -Confirm:$false
Remove-Item 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\DATA\modellog.ldf' -Force -Confirm:$false
Remove-Item 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\DATA\MSDBData.mdf' -Force -Confirm:$false
Remove-Item 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\DATA\MSDBLog.ldf' -Force -Confirm:$false
----------------------------
Restart-Computer
----------------------------
Restart-Computer -ComputerName MYDBSERVER
----------------------------
Install-StandardEdition
----------------------------
\\myorgsoftware\install\SqlServer\2008\SE\setup.exe /ACTION="Install" /ERRORREPORTING=0 /FEATURES=SQLEngine,BC,BOL,Conn,ADV_SSMS,AS /QS /AGTSVCACCOUNT="myorg\svc_account" /AGTSVCPASSWORD="svc_sql_password" /AGTSVCSTARTUPTYPE="Manual" /SQLSVCACCOUNT="myorg\svc_account" /SQLSVCPASSWORD="svc_sql_password" /SQLSVCSTARTUPTYPE="Automatic" /INSTALLSHAREDDIR="D:\Program Files\Microsoft SQL Server" /INSTALLSHAREDWOWDIR="D:\Program Files (x86)\Microsoft SQL Server" /INSTANCEDIR="D:\Program Files\Microsoft SQL Server" /INSTANCENAME="MYINST" /INSTANCEID="MYINST" /SQMREPORTING="False" /BROWSERSVCSTARTUPTYPE="Automatic" /SAPWD="sa_password" /SECURITYMODE="SQL" /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS /SQLSYSADMINACCOUNTS="myorg\dba_admin_ad_group" /TCPENABLED="1" /SQLUSERDBDIR="D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\DATA" /SQLUSERDBLOGDIR="D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\DATA" /SQLTEMPDBDIR="T:\MSSQL10_50.MYINST\MSSQL\DATA" /SQLTEMPDBLOGDIR="H:\MSSQL10_50.MYINST\MSSQL\DATA" /ASSVCACCOUNT="myorg\svc_file_services" /ASSVCPASSWORD="svc_filenetp8_tst_password" /ASSYSADMINACCOUNTS="myorg\dba_admin_ad_group" /IAcceptSQLServerLicenseTerms
----------------------------
Verify-InstalledVersion
----------------------------
(Invoke-DbaSqlcmd -ServerInstance 'MYDBSERVER\MYINST' -Database master -Query 'SELECT @@Version as Ver' -As DataTable).Rows[0] |
Select-Object -ExpandProperty 'Ver' | Out-String
#If the SQL Instance [MYDBSERVER\MYINST] is still Enterprise edition, there is a problem!
----------------------------
Install-ServicePack
----------------------------
(Invoke-DbaSqlcmd -ServerInstance 'MYDBSERVER\MYINST' -Database master -Query 'SELECT @@Version as Ver' -As DataTable).Rows[0] |
Select-Object -ExpandProperty 'Ver' | Out-String
#If the SQL Instance [MYDBSERVER\MYINST] is still Enterprise edition, there is a problem!
------------------------------------------------------------------
WARNING: The Service pack level should be the same as before.
Review the SP information at the top and match the service pack in the 'after'.
If there was no service pack before, there should not be any now.
You may have to create the right SP folder with setup files in it if necessary.
Sometimes, setup.exe might be 'SQLServer2008R2SP3-KB2979597-x64-ENU.exe' instead. Just replace as necessary!
Also, the /quiet parameter is optional. Remove it if you want to watch progress!
setup.exe may not run straight from a share...hence the new-psdrive!
------------------------------------------------------------------
Push-Location
New-PSDrive -Name 'SP' -PSProvider 'FileSystem' -Root '\\myorgsoftware\install\SqlServer\2008\SP\SP4' -Description 'SQL Server Service Pack'
Set-Location SP:\
.\setup.exe /instancename=MYINST /quiet
Pop-Location
Remove-PSDrive -Name 'SP'
----------------------------
Stop-SQLServicesForSystemFilesOverwrite
----------------------------
Stop-DbaSqlService -ComputerName MYDBSERVER
----------------------------
CopyBack-SystemDBFiles
----------------------------
copy '\\myorgcifs\sqlstuff\SQLServerEditionDowngrade\MYDBSERVER_MYINST\FileBackups\master.mdf' 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\DATA\master.mdf'
copy '\\myorgcifs\sqlstuff\SQLServerEditionDowngrade\MYDBSERVER_MYINST\FileBackups\mastlog.ldf' 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\DATA\mastlog.ldf'
copy '\\myorgcifs\sqlstuff\SQLServerEditionDowngrade\MYDBSERVER_MYINST\FileBackups\tempdb.mdf' 'T:\MSSQL10_50.MYINST\MSSQL\DATA\tempdb.mdf'
copy '\\myorgcifs\sqlstuff\SQLServerEditionDowngrade\MYDBSERVER_MYINST\FileBackups\templog.ldf' 'H:\MSSQL10_50.MYINST\MSSQL\DATA\templog.ldf'
copy '\\myorgcifs\sqlstuff\SQLServerEditionDowngrade\MYDBSERVER_MYINST\FileBackups\tempdb1.ndf' 'E:\MSSQL10_50.MYINST\MSSQL\DATA\tempdb1.ndf'
copy '\\myorgcifs\sqlstuff\SQLServerEditionDowngrade\MYDBSERVER_MYINST\FileBackups\tempdb2.ndf' 'F:\MSSQL10_50.MYINST\MSSQL\DATA\tempdb2.ndf'
copy '\\myorgcifs\sqlstuff\SQLServerEditionDowngrade\MYDBSERVER_MYINST\FileBackups\tempdb3.ndf' 'G:\MSSQL10_50.MYINST\MSSQL\DATA\tempdb3.ndf'
copy '\\myorgcifs\sqlstuff\SQLServerEditionDowngrade\MYDBSERVER_MYINST\FileBackups\model.mdf' 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\DATA\model.mdf'
copy '\\myorgcifs\sqlstuff\SQLServerEditionDowngrade\MYDBSERVER_MYINST\FileBackups\modellog.ldf' 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\DATA\modellog.ldf'
copy '\\myorgcifs\sqlstuff\SQLServerEditionDowngrade\MYDBSERVER_MYINST\FileBackups\MSDBData.mdf' 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\DATA\MSDBData.mdf'
copy '\\myorgcifs\sqlstuff\SQLServerEditionDowngrade\MYDBSERVER_MYINST\FileBackups\MSDBLog.ldf' 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\DATA\MSDBLog.ldf'
----------------------------
Start-SQLServices
----------------------------
Start-DbaSqlService -ComputerName MYDBSERVER
----------------------------
Match-ServiceStartupMode
----------------------------
Compare startup mode of SQL related services
before the edition was changed to now,
and match to mode (manual/automatic)
----------------------------
Replace-Passwords
----------------------------
If the sa password did not exist in my organization password manager,
please add it now with title MYDBSERVER.MYINST.sa
In this instructions file, manually replace all occurances of passwords 'svc_sql_password' and 'sa_password' with '*****'
....passwords in plain text file on a network share = very bad idea!
----------------------------
Rename-MirgrationShare
----------------------------
Rename-Item -Path '\\myorgcifs\sqlstuff\SQLServerEditionDowngrade\MYDBSERVER_MYINST' -NewName '\\myorgcifs\sqlstuff\SQLServerEditionDowngrade\MYDBSERVER_MYINST_Archive_20170922_135517'
----------------------------
Other-Instructions
----------------------------
Please re-read the exclusions that need to be verified/handled manually that are noted at the beginning of this file!
----------------------------
Migration-HomeShare
----------------------------
\\myorgcifs\sqlstuff\SQLServerEditionDowngrade\MYDBSERVER_MYINST
If you see the above, you can see how many places there is scope for making unintended mistakes.
Can I upgrade from Standard/Developer to Enterprise? Yes!
For those of you who are currently using Standard/Developer editions but want to move up to Enterprise, the procedure is the same. In fact, the same code can be used with some very minor tweaks to do so.
The main function – Get-EditionDowngradeInfo
This is the main function that returns a hash-table which has all the instructions in the form of key-value pairs. The keys are the headings you see above and the values are the actual instructions/commands.
#######################
(BEGIN PowerShellBlockComment)
.SYNOPSIS
Given a sql server instance, gets all the information necessary to perform an edition downgrade!
.DESCRIPTION
There is lots of useful information to avoid mistakes that is returned which can then be used to drive logic or perform manually!
The instructions generated are based on the downgrade procedure outlined in the below article:
https://www.mssqltips.com/sqlservertip/3079/downgrade-from-sql-server-enterprise-edition-to-standard-edition/
.INPUTS
Basic stuff...
.OUTPUTS
Operations and their commands as key value pairs
.EXAMPLE
$rslt = Get-EditionDowngradeInfo -ServerInstance 'MYSQLHOST\MYINST'
$rslt
.NOTES
Created this to assist with edition downgrades from EE to SE
Version History
v1.0 - Jana Sattainathan - Sep.08.2017
.LINK
https://sqljana.wordpress.com/
https://www.mssqltips.com/sqlservertip/3079/downgrade-from-sql-server-enterprise-edition-to-standard-edition/
https://docs.microsoft.com/en-us/sql/relational-databases/scripting/import-the-sqlps-module
https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-ps-module
https://dbatools.io/
https://github.com/gwalkey/SQLTranscriptase
(END PowerShellBlockComment)
WordPress has issues with PowerShell block code comments…so, I separated the comments and the code.
function Get-EditionDowngradeInfo
{
[CmdletBinding()]
param
(
[Parameter(Mandatory=$true)]
[string] $ServerInstance,
[Parameter(Mandatory=$false)]
[ValidateSet('SQLPS','SQLSERVER')]
[string] $SQLPowerShellModuleToUse = 'SQLPS',
[Parameter(Mandatory=$false)]
[ValidateScript({Test-Path $_ -PathType Container})]
[string] $DbatoolsBaseFolder = 'c:\github\dbatools',
[Parameter(Mandatory=$false)]
[ValidateScript({Test-Path $_ -PathType Container})]
[string] $SqlTranscriptaseBaseFolder = '\\myorgcifs\tools\SQLTranscriptase',
[Parameter(Mandatory=$false)]
[ValidateScript({Test-Path $_ -PathType Container})]
[string] $MigrationShareBaseFolder = '\\myorgcifs\sqlstuff\SQLServerEditionDowngrade',
[Parameter(Mandatory=$true)]
[ValidateSet('\\myorgsoftware\install\SqlServer\2008\EE',
'\\myorgsoftware\install\SqlServer\2008_r2\EE')]
[string] $SQLEntEdSoftwareBaseFolder, #Need this setup.exe to do command-line Uninstall
[Parameter(Mandatory=$true)]
[ValidateSet('\\myorgsoftware\install\SqlServer\2008\SE',
'\\myorgsoftware\install\SqlServer\2008_r2\SE')]
[string] $SQLStdEdSoftwareBaseFolder, #Need this setup.exe to do command-line install
[Parameter(Mandatory=$true)]
[ValidateSet('\\myorgsoftware\install\SqlServer\2008\SP\SP4',
'\\myorgsoftware\install\SqlServer\2008_r2\SP\SP3')]
[string] $SQLStdEdServicePackBaseFolder, #Need this setup.exe to do service pack install
[Parameter(Mandatory=$true)]
[string] $SQLServiceAccount,
[Parameter(Mandatory=$true)]
[string] $SQLServiceAccountPassword,
[Parameter(Mandatory=$false)]
[string] $SSASServiceAccount = '',
[Parameter(Mandatory=$false)]
[string] $SSASServiceAccountPassword = '',
[Parameter(Mandatory=$false)]
[string] $SSRSServiceAccount = '',
[Parameter(Mandatory=$false)]
[string] $SSRSServiceAccountPassword = '',
[Parameter(Mandatory=$true)]
[string] $SQLSAAccountPassword,
[Parameter(Mandatory=$false)]
[string] $SQLSysAdminAccounts = 'myorg\dba_admin_ad_group'
)
[string] $fn = $MyInvocation.MyCommand
[string] $stepName = "Begin [$fn]"
[System.Collections.Specialized.OrderedDictionary]$returnValuesHashTable = @{}
[System.Collections.Specialized.OrderedDictionary]$returnValuesDescHashTable = @{}
[string] $computerName = $ServerInstance.Split('\')[0]
[string] $instanceName = $ServerInstance.Split('\')[1]; if($instanceName.Trim().Length -eq 0){$instanceName = 'DEFAULT'}
[string] $serverAndInstanceString = $ServerInstance.Replace('\', '_')
[string] $migrationFolderForInstBase = Join-Path -Path $MigrationShareBaseFolder -ChildPath $serverAndInstanceString
[string] $migrationFolderForInstBackups = Join-Path -Path $migrationFolderForInstBase -ChildPath 'Backups'
[string] $migrationFolderForFileBackups = Join-Path -Path $migrationFolderForInstBase -ChildPath 'FileBackups'
[string] $migrationFolderForSSASBackups = Join-Path -Path $migrationFolderForInstBase -ChildPath 'SSASBackups'
[string] $migrationFolderForSSRSBackups = Join-Path -Path $migrationFolderForInstBase -ChildPath 'SSRSBackups'
[string] $migrationFolderForSQLTranscriptase = Join-Path -Path $migrationFolderForInstBase -ChildPath 'SQLTranscriptase'
[string] $message = ''
[string] $command = ''
[string] $ssasRestoreCommand = ''
[bool] $Is2008R2 = $false
try
{
$stepName = "[$fn]: Validate parameters"
#--------------------------------------------
Write-Verbose $stepName
# If the drive location is a PSDrive other than the file-system from previous runs, file-system commands will fail!
Set-Location c:\
$stepName = "[$fn]: See if computer is reachable"
#--------------------------------------------
Write-Verbose $stepName
if (-not (Test-Connection -ComputerName $computerName -Count 1))
{
throw "Computer [$computerName] is not reachable!"
}
$stepName = "[$fn]: What we plan to do"
#--------------------------------------------
Write-Verbose $stepName
$message = "The function will generate instructions which can then be used to do the downgrade. `n" +
"Below link has the procedure we will follow to downgrade edition. Please read and follow along `n" +
" https://www.mssqltips.com/sqlservertip/3079/downgrade-from-sql-server-enterprise-edition-to-standard-edition/ `n`n`n" +
"Commands from the following PowerShell modules used and hence required`n" +
"1) SQLPS/SQLServer PowerShell module should already be installed `n" +
" https://docs.microsoft.com/en-us/sql/relational-databases/scripting/import-the-sqlps-module `n" +
" https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-ps-module `n" +
" This is the native module for PowerShell from MS. SQLPS is the older one and SQLServer the newer. `n" +
"2) dbatools should be installed and loaded or the path to it is needed `n" +
" https://dbatools.io/ `n" +
" This module is a must-have for all SQL Server DBA's and command-line junkies! `n" +
"3) SQLTranscriptase (optional) should be installed and loaded or the path to it is needed `n" +
" https://github.com/gwalkey/SQLTranscriptase `n" +
" This module is a great documentation module that I find useful. Thanks to George Walkey! `n`n" +
"" +
"*****WARNING*****: This is just 'help' and is not a program or even the exact procedure to follow! `n`n" +
"Run the generation script twice or as many times as you want to re-generate the instructions `n" +
" For example, after you find the service account username, you may want to run with the right parameter value for that! `n`n" +
"The following are not handled and you will have to handle them manually `n" +
" 1) You would have to pass in the right parameters, especially for setup files for right versions! `n" +
" 2) You have to note the right service pack of the installed version and pass the right location of the noted SP when reinstalling `n" +
" 3) Some commands have to be run on the a jump box from PowerShell and other commands like file-copy/uninstall/install on the SQL Server host from PoSh `n" +
" 4) INSTANCEDIR parameter for setup.exe has to still be verified as working correctly. This is the folder that has the binaries and log folders. `n" +
" Eg. D:\MSSQL10.MYINST\MSSQL\Backup|Binn|DATA|Install|JOBS|Log|Upgrade`n`n" +
" In the case of 'D:\MSSQL10.MYINST\MSSQL\Log\ERRORLOG', D:\ is the INSTANCEDIR. `n" +
" In the case of 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\Log\ERRORLOG', the INSTACEDIR is 'D:\Program Files\Microsoft SQL Server\' `n" +
" 5) You have to manually check if the system files are at the same location after the standard edition is installed by checking sys.master_files system view`n" +
" 6) The service account user names for SQL Services are automatically filled in. You need to note and pass them as parameters."
$returnValuesHashTable.Add('General-Outline', $message) | Out-Null
$stepName = "[$fn]: Import dbatools module if needed"
#--------------------------------------------
Write-Verbose $stepName
if (-not (Test-Path $DbatoolsBaseFolder -PathType Container))
{
throw "The folder [$DbatoolsBaseFolder] specified as basefolder for dbatools is not valid!"
}
else
{
if(-not (Get-Module dbatools))
{
Import-Module "$DbatoolsBaseFolder\dbatools.psd1" -Force
}
}
$command = "if(-not (Get-Module dbatools)) `n" +
"{ `n" +
" Import-Module `"$DbatoolsBaseFolder\dbatools.psd1`" -Force `n" +
"} `n" +
"if(-not (Get-Module $SQLPowerShellModuleToUse)) `n" +
"{ `n" +
" Import-Module $SQLPowerShellModuleToUse `n" +
"} `n"
$returnValuesHashTable.Add("Import-PowerShellModules", $command) | Out-Null
$stepName = "[$fn]: Check if Enterprise edition features are used in every database of the instance.."
#--------------------------------------------
Write-Verbose $stepName
$sql = 'SELECT name FROM sys.databases'
$rslt = Invoke-DbaSqlcmd -ServerInstance $ServerInstance -Database master -Query $sql -As DataTable
$dbs = $rslt | select -ExpandProperty name
foreach ($db in $dbs)
{
Write-Verbose "Checking if $db uses any Enterprise features"
$sql = 'SELECT feature_name FROM sys.dm_db_persisted_sku_features'
$rslt = Invoke-DbaSqlcmd -ServerInstance $ServerInstance -Database $db -Query $sql -As DataTable
if ($rslt.Rows.Count -gt 0)
{
Write-Warning "Enterprise features are being used by instance on database [$db]!"
Write-Warning ($rslt.Rows | Select-Object -ExpandProperty 'feature_name' | Out-String)
throw "The SQL Instance [$ServerInstance] does not qualify for downgrade since it uses enterprise edition features!"
}
}
$stepName = "[$fn]: Get SQL version/edition installed.."
#--------------------------------------------
Write-Verbose $stepName
$sql = 'SELECT @@Version as Ver'
$rslt = Invoke-DbaSqlcmd `
-ServerInstance $ServerInstance `
-Database master `
-Query $sql `
-As DataTable
$output = ($rslt.Rows[0] | Select-Object -ExpandProperty 'Ver' | Out-String)
$returnValuesHashTable.Add('SQL-InstalledVersion', $output) | Out-Null
if (-not $output.ToUpper().Contains('ENTERPRISE'))
{
$message = "The SQL Instance [$ServerInstance] is currently not Enterprise edition. There is no need to downgrade edition!"
Write-Warning $message
#throw $message
}
if ($output.Contains(' R2 '))
{
$Is2008R2 = $true
}
$stepName = "[$fn]: Get up to latest SP - message"
#--------------------------------------------
Write-Verbose $stepName
$command = "Look at the Service Pack number for SQL Server. `n" +
"Before the edition is changed, it may be a good idea to `n" +
"get the instance(s) to the latest service pack so that `n" +
"we dont have to hunt down the exact service pack later! `n" +
"The commands to use are down below - search for 'Install-ServicePack'.`n"
$returnValuesHashTable.Add('Install-LatestServicePack', $command) | Out-Null
$stepName = "[$fn]: List of SQL Server components installed"
#--------------------------------------------
Write-Verbose $stepName
#This does not return the service account user name..which is vital
#$rslt = Get-DbaSqlService -ComputerName $computerName | Out-String
#https://blogs.technet.microsoft.com/heyscriptingguy/2012/02/15/the-scripting-wife-uses-powershell-to-find-service-accounts/
$rslt = Get-WmiObject win32_service -ComputerName $computerName |
Where-Object {($_.DisplayName -match "SQL Server")}
$message = $rslt |
select SystemName, Name, StartName, State, StartMode, DisplayName |
Out-String
$returnValuesHashTable.Add('SQL-FeaturesInstalled', $message) | Out-Null
#Bring special attention to the service accounts used so that the DBA knows and can make adjustments now/later
$message = $rslt |
select -ExpandProperty StartName -Unique |
Out-String
$returnValuesHashTable.Add('SQL-ServiceAccountsUsed', $message) | Out-Null
$stepName = "[$fn]: Get disk usage info."
#--------------------------------------------
Write-Verbose $stepName
$message = (Get-DbaDiskSpace -ComputerName $computerName -Unit GB -Detailed | ft| Out-String -width 500)
$returnValuesHashTable.Add('SQL-DiskUsageInfo', $message) | Out-Null
$stepName = "[$fn]: Get file size information for reference"
#--------------------------------------------
Write-Verbose $stepName
$message = (Get-DbaDatabaseSpace -SqlInstance $ServerInstance -IncludeSystemDBs | ft | out-String -Width 500)
$returnValuesHashTable.Add('SQL-FileSizeInfo', $message) | Out-Null
$stepName = "[$fn]: SQL Server Analysis Services backups"
#--------------------------------------------
Write-Verbose $stepName
if ($returnValuesHashTable['SQL-FeaturesInstalled'].ToString().Contains('SQL Server Analysis Services'))
{
if (-not (Get-module SQLPS))
{
Import-Module SQLPS
}
#Get the list of all Analysis Services DB's
Push-Location
$asDBS = Get-ChildItem SQLServer:\SQLAS\$computerName\$instanceName\Databases | SELECT -ExpandProperty Name
Pop-Location
#Command to create a new folder for SSAS within the migration share
$command = "New-Item -Path `"$migrationFolderForSSASBackups`" -ItemType Container -Force | Out-Null `n`n`n"
$copyFilesToUNC = "`n"
$removeSSASTempBackups = "`n"
#Loop through and create the backup and restore commands
ForEach($db in $asDBs)
{
$command = "------------------------------------------------------------------`n"
$command += "WARNING: These SSAS database backups are purely for safety reasons. `n"
$command += " If you have huge databases (see disk/file usage info above), and `n"
$command += " if you already have backups that can be used in case of failure, you can skip this step.`n"
$command += " If you run into errors, check to see if service account has permissions to backup to UNC share.`n"
$command += "------------------------------------------------------------------`n"
$command += "`n"
$command += "Backup-DbaDatabase -SqlInstance $ServerInstance -CopyOnly -BackupDirectory $migrationFolderForSSASBackups -Type Database -CompressBackup `n"
$command += "Write-Verbose `"Backup SSAS database [$db] to [$migrationFolderForSSASBackups\$db.abf]`" `n"
$command += "Backup-ASDatabase -BackupFile '$migrationFolderForSSASBackups\$db.abf' -ApplyCompression -Server '$ServerInstance' -Name $db -AllowOverwrite -Confirm `n"
#Generate the restore command to make our life easier
$ssasRestoreCommand += "Write-Verbose `"Restore SSAS database [$db] from [$backupFile]`" `n"
$ssasRestoreCommand += "Restore-ASDatabase -RestoreFile '$migrationFolderForSSASBackups\$db.abf' -Server '$ServerInstance' -Name $db -AllowOverwrite -Confirm `n"
<# # This code is if your service account does not have permissions to backup to share and you want to backup local and copy manually #Backup to local file ..and copy to network (manually using another ID) $backupFile = "C:\Temp\$db.abf" $command += "Write-Verbose `"Backup SSAS database [$db] to [$backupFile]`" `n" $command += "Backup-ASDatabase -BackupFile '$backupFile' -ApplyCompression -Server '$ServerInstance' -Name $db -AllowOverwrite -Confirm `n" $command += "Copy-Item -LiteralPath '$backupFile' -Destination '$migrationFolderForSSASBackups' -Force `n" $command += "Remove-Item '$backupFile' -Force -Confirm:`$false `n" #Generate the restore command to make our life easier $ssasRestoreCommand += "Write-Verbose `"Restore SSAS database [$db] from [$backupFile]`" `n" $ssasRestoreCommand += "Restore-ASDatabase -RestoreFile '$backupFile' -Server '$ServerInstance' -Name $db -AllowOverwrite -Confirm `n" #>
}
$returnValuesHashTable.Add('SQL-SSASDatabases', $asDBs) | Out-Null
$returnValuesHashTable.Add('Do-SSASBackup', $command) | Out-Null
}
$stepName = "[$fn]: List of databases affected.."
#--------------------------------------------
Write-Verbose $stepName
$sql = 'SELECT name FROM sys.databases'
$rslt = Invoke-DbaSqlcmd `
-ServerInstance $ServerInstance `
-Database master `
-Query $sql `
-As DataTable
if ($rslt.Rows.Count -gt 0)
{
$message = "The following databases will be affected `n"
$message += ($rslt.Rows | Select-Object -ExpandProperty 'name' | Out-String)
$returnValuesHashTable.Add('Affected-Databases', $message) | Out-Null
}
$stepName = "[$fn]: List of connected users.."
#--------------------------------------------
Write-Verbose $stepName
$sql = "SELECT DISTINCT RTRIM(nt_domain) + '\' + RTRIM(nt_username) username FROM sys.sysprocesses /*WHERE LEN(RTRIM(nt_domain)) > 0*/"
$rslt = Invoke-DbaSqlcmd `
-ServerInstance $ServerInstance `
-Database master `
-Query $sql `
-As DataTable
if ($rslt.Rows.Count -gt 0)
{
$message = "The following users are currently connected (idle and non-idle) `n"
$message += ($rslt.Rows | Select-Object -ExpandProperty 'username' | Out-String)
$returnValuesHashTable.Add('Connected-Users', $message) | Out-Null
}
$stepName = "[$fn]: Send out an email seeking down time"
#--------------------------------------------
Write-Verbose $stepName
$message = "Send out an email to owners on the host (may contain other SQL as well as Oracle instances and application servers) `n" +
"Suggested text is (please replace as necessary): `n`n"
$message += "Hi team, `n`n" +
" We are getting ready to downgrade the SQL Server edition on [$ServerInstance] from Enterprise to Standard edition for managing our licensing costs.`n`n" +
" The maintenance is planned for later today unless you propose an alternate time. The SQL Server databases on the instance will be unavailable for approximately 8 hours.`n`n" +
" Please let me know if you have any objections to the proposed maintenance today. I will notify when the downgrade is complete and the application services may have to be restarted to reconnect.`n`n" +
"Thanks, `n"+
"DBA Team"
$returnValuesHashTable.Add('Email-Users', $message) | Out-Null
$stepName = "[$fn]: Make SQL Server backups (not file copy backups)"
#--------------------------------------------
Write-Verbose $stepName
$command = "------------------------------------------------------------------`n"
$command += "WARNING: These copy-only database backups are purely for safety reasons. `n"
$command += " If you have huge databases (see disk/file usage info above), and `n"
$command += " if you already have backups that can be used in case of failure, you can skip this step.`n"
$command += " If you run into errors, check to see if service account has permissions to backup to UNC share.`n"
$command += "------------------------------------------------------------------`n"
$command += "`n"
$command += "New-Item -Path '$migrationFolderForInstBackups' -ItemType Container -Force | Out-Null `n"
$command += "Backup-DbaDatabase -SqlInstance $ServerInstance -CopyOnly -BackupDirectory $migrationFolderForInstBackups -Type Database -CompressBackup "
$returnValuesHashTable.Add('Do-SQLBackups', $command) | Out-Null
$stepName = "[$fn]: Make SQLTranscriptase scripting output for future reference"
#--------------------------------------------
Write-Verbose $stepName
if ($SqlTranscriptaseBaseFolder)
{
if (-not (Test-Path $SqlTranscriptaseBaseFolder -PathType Container))
{
throw "The folder [$SqlTranscriptaseBaseFolder] specified as basefolder for SqlTranscriptase is not valid!"
}
else
{
$command = "Push-Location `n" +
"Set-Location $SqlTranscriptaseBaseFolder `n" +
".\00_RunAllScripts.ps1 -SQLInstance '$ServerInstance' `n" +
"Pop-Location `n" +
"New-Item '$migrationFolderForSQLTranscriptase' -ItemType Directory | Out-Null `n" +
"Copy-Item '$SqlTranscriptaseBaseFolder\$computerName' '$migrationFolderForSQLTranscriptase' -Container -Recurse `n" +
"Remove-Item '$SqlTranscriptaseBaseFolder\$computerName' -Force -Recurse -Confirm:`$false `n"
}
$returnValuesHashTable.Add('Export-SQLTranscriptaseScripts', $command) | Out-Null
}
$stepName = "[$fn]: Get SQL Server s/w installation location"
#--------------------------------------------
Write-Verbose $stepName
#e.g., C:\Program Files\Microsoft SQL Server\MSSQL10.MYINST\MSSQL
$sql = "DECLARE @InstanceName varchar(100),
@InstanceLocation varchar(100),
@InstancePath varchar(100)
SELECT @InstanceName = COALESCE(convert(varchar, ServerProperty('InstanceName')),'MSSQLSERVER')
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key='Software\Microsoft\Microsoft SQL Server\Instance Names\SQL',
@value_name=@InstanceName,
@value=@InstanceLocation OUTPUT
SELECT @InstanceLocation = 'Software\Microsoft\Microsoft SQL Server\'+@InstanceLocation+'\Setup'
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key=@InstanceLocation,
@value_name='SQLPath',
@value=@InstancePath OUTPUT
SELECT @InstancePath"
$rslt = Invoke-DbaSqlcmd `
-ServerInstance $ServerInstance `
-Database master `
-Query $sql `
-As DataTable
$returnValuesHashTable.Add('SQL-SoftwareLocation', $rslt.Rows[0][0]) | Out-Null
$returnValuesHashTable.Add('SQL-SoftwareDrive', $rslt.Rows[0][0].ToString().Substring(0,1)) | Out-Null
$stepName = "[$fn]: Get SQL Server default DATA/LOG/BACKUP directories"
#--------------------------------------------
Write-Verbose $stepName
#Reference: https://stackoverflow.com/questions/1883071/how-do-i-find-the-data-directory-for-a-sql-server-instance
#e.g., D:\dbBackup
$sql = "declare @DefaultData nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @DefaultData output
declare @DefaultLog nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @DefaultLog output
declare @DefaultBackup nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', @DefaultBackup output
declare @MasterData nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters', N'SqlArg0', @MasterData output
select @MasterData=substring(@MasterData, 3, 255)
select @MasterData=substring(@MasterData, 1, len(@MasterData) - charindex('\', reverse(@MasterData)))
declare @MasterLog nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters', N'SqlArg2', @MasterLog output
select @MasterLog=substring(@MasterLog, 3, 255)
select @MasterLog=substring(@MasterLog, 1, len(@MasterLog) - charindex('\', reverse(@MasterLog)))
select
/*Properties InstanceID and InstanceName in configuration.ini for silent install will be MSSQLSERVER for default instance */
COALESCE(SERVERPROPERTY ('InstanceName'), 'MSSQLSERVER') AS InstanceName,
SERVERPROPERTY ('Collation') ServerCollation,
isnull(@DefaultData, @MasterData) DefaultData,
isnull(@DefaultLog, @MasterLog) DefaultLog,
isnull(@DefaultBackup, @MasterLog) DefaultBackup,
SERVERPROPERTY('ErrorLogFileName') ErrorLogFileName"
$rslt = Invoke-DbaSqlcmd `
-ServerInstance $ServerInstance `
-Database master `
-Query $sql `
-As DataTable
$returnValuesHashTable.Add('SQL-InstanceName', $rslt.Rows[0]['InstanceName']) | Out-Null
$returnValuesHashTable.Add('SQL-ServerCOllation', $rslt.Rows[0]['ServerCollation']) | Out-Null
$returnValuesHashTable.Add('SQL-DefaultDataDir', $rslt.Rows[0]['DefaultData']) | Out-Null
$returnValuesHashTable.Add('SQL-DefaultLogDir', $rslt.Rows[0]['DefaultLog']) | Out-Null
$returnValuesHashTable.Add('SQL-DefaultBackupDir', $rslt.Rows[0]['DefaultBackup']) | Out-Null
$returnValuesHashTable.Add('SQL-InstanceDrive', $returnValuesHashTable['SQL-DefaultDataDir'].ToString().Substring(0,3))
$returnValuesHashTable.Add('SQL-ErrorLogFileName', $rslt.Rows[0]['ErrorLogFileName']) | Out-Null
#SELECT SERVERPROPERTY('ErrorLogFileName') would return a path like 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MYINST\MSSQL\Log\ERRORLOG'
# The INSTANCEDIR is 'D:\Program Files\Microsoft SQL Server' in this case..
$returnValuesHashTable.Add('SQL-InstanceDir', (Split-Path -parent (Split-Path -parent (Split-Path -parent (Split-Path -parent $rslt.Rows[0]['ErrorLogFileName']))))) | Out-Null
$stepName = "[$fn]: Get TempDB DATA and LOG locations"
#--------------------------------------------
Write-Verbose $stepName
$sql = "select
MAX(CASE WHEN name = 'tempdev' THEN physical_name ELSE NULL END) AS TempDBDataDir,
MAX(CASE WHEN name = 'templog' THEN physical_name ELSE NULL END) AS TempDBLogDir
from sys.master_files
where name in ('tempdev','templog')"
$rslt = Invoke-DbaSqlcmd `
-ServerInstance $ServerInstance `
-Database master `
-Query $sql `
-As DataTable
$returnValuesHashTable.Add('SQL-TempDBDataDir', [System.IO.Path]::GetDirectoryName($rslt.Rows[0]['TempDBDataDir'])) | Out-Null
$returnValuesHashTable.Add('SQL-TempDBLogDir', [System.IO.Path]::GetDirectoryName($rslt.Rows[0]['TempDBLogDir'])) | Out-Null
$stepName = "[$fn]: Stop SQL services"
#--------------------------------------------
Write-Verbose $stepName
[string] $command = "Stop-DbaSqlService -ComputerName $computerName"
$returnValuesHashTable.Add('Stop-SQLServicesForFileBackup', $command) | Out-Null
$stepName = "[$fn]: Backup the SSRS key"
#--------------------------------------------
Write-Verbose $stepName
if ($returnValuesHashTable['SQL-FeaturesInstalled'].ToString().Contains('SQL Server Reporting Services'))
{
$command = "Backup the SSRS key using the 'Reporting Services Configuration Manager' to network location [$migrationFolderForSSRSBackups]"
$returnValuesHashTable.Add('Backup-SSRSKey', $command) | Out-Null
}
$stepName = "[$fn]: Make SQL file copy backups of system databases"
#--------------------------------------------
Write-Verbose $stepName
$returnValuesHashTable.Add('Make-SystemFileBackupsDir', "New-Item $migrationFolderForFileBackups -ItemType Directory | Out-Null") | Out-Null
$sql = "select 'copy ''' + physical_name + ''' ''' + '$migrationFolderForFileBackups\''' AS CopyCommand from sys.master_files /*where database_id <= 4*/"
$rslt = Invoke-DbaSqlcmd `
-ServerInstance $ServerInstance `
-Database master `
-Query $sql `
-As DataTable
$command = "------------------------------------------------------------------`n"
$command += "WARNING: There is no need to make a file-copy of non-system DB files `n"
$command += " The copy commands for non-system DB's are here only for convenience! `n"
$command += " Definetely make a copy of the system-DB files and ensure there are no errors. `n"
$command += " Without the physical copy of system DB files, it is game-over.`n"
$command += "------------------------------------------------------------------`n"
$command += "`n"
$command += ($rslt.Rows | Select-Object -ExpandProperty 'CopyCommand' | Out-String)
$returnValuesHashTable.Add('Copy-SystemDBFiles(!!--AndOptionallyNonSys--!!)', $command) | Out-Null
$stepName = "[$fn]: Uninstall SQL Server Ent Edition"
#--------------------------------------------
Write-Verbose $stepName
#Reference: http://www.sqlservercentral.com/blogs/hugo/2016/06/29/uninstalling-sql-server-from-the-command-line-to-remove-unwanted-background-instances/
#This has to be the installation media location of the version that is currently installed and not the one we want to install!
if (-not (Test-Path $SQLEntEdSoftwareBaseFolder -PathType Container))
{
throw "The folder [$SQLEntEdSoftwareBaseFolder] specified as basefolder for Enterprise edition setup files location is not valid!"
}
#These are the base features we will always uninstall
$features = 'SQLENGINE,REPLICATION,BIDS,CONN,IS,BC,SDK,BOL,SSMS,ADV_SSMS,OCS'
#...we check and add others if they were installed prior
if ($returnValuesHashTable['SQL-FeaturesInstalled'].ToString().Contains('SQL Server Analysis Services'))
{
$features += ',AS'
}
if ($returnValuesHashTable['SQL-FeaturesInstalled'].ToString().Contains('SQL Server Reporting Services'))
{
$features += ',RS'
}
if ($returnValuesHashTable['SQL-FeaturesInstalled'].ToString().Contains('SQL Full-text'))
{
$features += ',FULLTEXT'
}
$command = (Join-Path -Path $SQLEntEdSoftwareBaseFolder -ChildPath 'setup.exe') +
' /ACTION="unInstall" /INDICATEPROGRESS="TRUE" ' +
' /FEATURES=' + $features +
' /INSTANCENAME="' + $returnValuesHashTable['SQL-InstanceName'] + '"'
$returnValuesHashTable.Add('Uninstall-EnterpriseEdition', $command) | Out-Null
$stepName = "[$fn]: Remove system files and TempDB files...otherwise install will fail later"
#--------------------------------------------
Write-Verbose $stepName
$sql = "select 'Remove-Item ''' + physical_name + ''' -Force -Confirm:`$false' AS RemoveCommand from sys.master_files where database_id <= 4"
$rslt = Invoke-DbaSqlcmd `
-ServerInstance $ServerInstance `
-Database master `
-Query $sql `
-As DataTable
$command = "------------------------------------------------------------------`n"
$command += "WARNING: The system DB files (.mdf and .ldf) will be removed by the uninstaller. `n"
$command += " The instructions to remove are here just in case, to make sure. `n"
$command += " The user DB files will not be removed by uninstall and should be left alone. `n"
$command += " Verify that all system DB files were copied before running the below delete! `n"
$command += "------------------------------------------------------------------`n"
$command += "<# `n" $command += ($rslt.Rows | Select-Object -ExpandProperty 'RemoveCommand' | Out-String) $command += "`n #> `n" +
$returnValuesHashTable.Add('Remove-SystemDBFiles', $command) | Out-Null
#If done manually, we would do the TempDB files like this!
#$command = "Remove-Item '" + $returnValuesHashTable.Add['SQL-TempDBDataDir'] + '\tempdb.mdf' + "' -Force -Confirm:`$false `n" +
# "Remove-Item '" + $returnValuesHashTable.Add['SQL-TempDBLogDir'] + '\tempdb.ldf' + "' -Force -Confirm:`$false `n"
$stepName = "[$fn]: Restart the computer"
#--------------------------------------------
Write-Verbose $stepName
$command = "Restart-Computer -ComputerName $computerName"
$returnValuesHashTable.Add('Restart-Computer', $command) | Out-Null
$stepName = "[$fn]: Install SQL Server Standard Edition"
#--------------------------------------------
Write-Verbose $stepName
if (-not (Test-Path $SQLStdEdSoftwareBaseFolder -PathType Container))
{
throw "The folder [$SQLStdEdSoftwareBaseFolder] specified as basefolder for Standard edition setup files location is not valid!"
}
else
{
# /IACCEPTSQLSERVERLICENSETERMS /ENU /UpdateEnabled="False"
# The above parameter values were only introduced in 2008R2 and hence will not work on 2008
#Ref: https://docs.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-from-the-command-prompt#Feature
#
#These are the base features we will always install
$features = 'SQLEngine,BC,BOL,Conn,ADV_SSMS'
#...we check and add others if they were installed prior
if ($returnValuesHashTable['SQL-FeaturesInstalled'].ToString().Contains('SQL Server Analysis Services'))
{
$features += ',AS'
}
if ($returnValuesHashTable['SQL-FeaturesInstalled'].ToString().Contains('SQL Server Reporting Services'))
{
$features += ',RS'
}
if ($returnValuesHashTable['SQL-FeaturesInstalled'].ToString().Contains('SQL Server Integration Services'))
{
$features += ',IS'
}
if ($returnValuesHashTable['SQL-FeaturesInstalled'].ToString().Contains('SQL Full-text'))
{
$features += ',FullText'
}
$command = (Join-Path -Path $SQLStdEdSoftwareBaseFolder -ChildPath 'setup.exe') +
' /ACTION="Install" /ERRORREPORTING=0 ' +
' /FEATURES=' + $features + ' /QS ' +
' /AGTSVCACCOUNT="' + $SQLServiceAccount + '" ' +
' /AGTSVCPASSWORD="' + $SQLServiceAccountPassword + '" ' +
' /AGTSVCSTARTUPTYPE="Manual" ' +
' /SQLSVCACCOUNT="' + $SQLServiceAccount + '" ' +
' /SQLSVCPASSWORD="' + $SQLServiceAccountPassword + '" ' +
' /SQLSVCSTARTUPTYPE="Automatic" ' +
' /INSTALLSHAREDDIR="' + $returnValuesHashTable['SQL-SoftwareDrive'] + ':\Program Files\Microsoft SQL Server" ' +
' /INSTALLSHAREDWOWDIR="' + $returnValuesHashTable['SQL-SoftwareDrive'] + ':\Program Files (x86)\Microsoft SQL Server" ' +
' /INSTANCEDIR="' + $returnValuesHashTable['SQL-InstanceDir'] + '" ' +
' /INSTANCENAME="' + $returnValuesHashTable['SQL-InstanceName'] + '" ' +
' /INSTANCEID="' + $returnValuesHashTable['SQL-InstanceName'] + '" ' +
' /SQMREPORTING="False" /BROWSERSVCSTARTUPTYPE="Automatic" ' +
' /SAPWD="' + $SQLSAAccountPassword + '" ' +
' /SECURITYMODE="SQL" ' +
' /SQLCOLLATION=' + $returnValuesHashTable['SQL-ServerCOllation'] +
' /SQLSYSADMINACCOUNTS="' + $SQLSysAdminAccounts + '" ' +
' /TCPENABLED="1" ' +
' /SQLUSERDBDIR="' + $returnValuesHashTable['SQL-DefaultDataDir'] + '" ' +
' /SQLUSERDBLOGDIR="' + $returnValuesHashTable['SQL-DefaultLogDir'] + '" ' +
' /SQLTEMPDBDIR="' + $returnValuesHashTable['SQL-TempDBDataDir'] + '" ' +
' /SQLTEMPDBLOGDIR="' + $returnValuesHashTable['SQL-TempDBLogDir'] + '" '
if ($returnValuesHashTable['SQL-FeaturesInstalled'].ToString().Contains('SQL Server Analysis Services'))
{
if (($SSASServiceAccount.Trim().Length -eq 0) -or
($SSASServiceAccount.Trim().Length -eq 0))
{
throw 'SSAS install requires a service account name and password for parameters SSASServiceAccount and SSASServiceAccountPassword!'
}
$command += " " +
' /ASSVCACCOUNT="' + $SSASServiceAccount + '" ' +
' /ASSVCPASSWORD="' + $SSASServiceAccountPassword + '" ' +
' /ASSYSADMINACCOUNTS="' + $SQLSysAdminAccounts + '" '
}
if ($returnValuesHashTable['SQL-FeaturesInstalled'].ToString().Contains('SQL Server Reporting Services'))
{
if (($SSRSServiceAccount.Trim().Length -eq 0) -or
($SSRSServiceAccount.Trim().Length -eq 0))
{
throw 'SSRS install requires a service account name and password for parameters SSRSServiceAccount and SSRSServiceAccountPassword!'
}
#The FilesOnlyMode option does not install the ReportServer and ReportServerTemp databases so that we can later reuse the existing ones
$command += " " +
' /RSSVCACCOUNT="' + $SSRSServiceAccount + '" ' +
' /RSSVCPASSWORD="' + $SSRSServiceAccountPassword + '" ' +
' /RSINSTALLMODE="FilesOnlyMode" '
}
#This parameter is only applicable to 2008R2 and above..but we only deal with 2008 and 2008R2
#....however it should check to see if it is 2008R2 or above!
# If these are not included, there will be errors about them being missing such as
# "IAcceptSQLServerLicenseTerms is not set"
# "You must provision the system with at least one system administrator"
#
if ($Is2008R2 -eq $true)
{
$command += " " +
' /IAcceptSQLServerLicenseTerms'
}
$returnValuesHashTable.Add('Install-StandardEdition', $command) | Out-Null
}
$stepName = "[$fn]: Verify version/edition installed.."
#--------------------------------------------
Write-Verbose $stepName
$command = "(Invoke-DbaSqlcmd -ServerInstance '$ServerInstance' -Database master -Query 'SELECT @@Version as Ver' -As DataTable).Rows[0] |
Select-Object -ExpandProperty 'Ver' | Out-String
#If the SQL Instance [$ServerInstance] is still Enterprise edition, there is a problem! `n`n"
$returnValuesHashTable.Add('Verify-InstalledVersion', $command) | Out-Null
$stepName = "[$fn]: Install Standard Edition Service Pack.."
#--------------------------------------------
Write-Verbose $stepName
#$command = "You may have to copy the service pack folder locally. Otherwise you may get a security exception. Can try mapping it to drive! `n" +
#$command += (Join-Path -Path $SQLStdEdServicePackBaseFolder -ChildPath 'setup.exe ') + ("/instancename={0} /quiet" -f $returnValuesHashTable['SQL-InstanceName'])
$command = "------------------------------------------------------------------`n"
$command += "WARNING: The Service pack level should be the same as before. `n"
$command += " Review the SP information at the top and match the service pack in the 'after'. `n"
$command += " If there was no service pack before, there should not be any now. `n"
$command += " You may have to create the right SP folder with setup files in it if necessary. `n"
$command += " Sometimes, setup.exe might be 'SQLServer2008R2SP3-KB2979597-x64-ENU.exe' instead. Just replace as necessary! `n"
$command += " Also, the /quiet parameter is optional. Remove it if you want to watch progress! `n"
$command += " setup.exe may not run straight from a share...hence the new-psdrive! `n"
$command += "------------------------------------------------------------------`n"
$command += "`n"
$command += "`n"
$command += "Push-Location `n"
$command += "New-PSDrive -Name 'SP' -PSProvider 'FileSystem' -Root '$SQLStdEdServicePackBaseFolder' -Description 'SQL Server Service Pack' `n"
$command += "Set-Location SP:\ `n"
$command += '.\setup.exe ' + ("/instancename={0} /quiet" -f $returnValuesHashTable['SQL-InstanceName']) + "`n"
$command += "Pop-Location `n"
$command += "Remove-PSDrive -Name 'SP' `n"
$returnValuesHashTable.Add('Install-ServicePack', $command) | Out-Null
$stepName = "[$fn]: Stop SQL services"
#--------------------------------------------
Write-Verbose $stepName
[string] $command = "Stop-DbaSqlService -ComputerName $computerName"
$returnValuesHashTable.Add('Stop-SQLServicesForSystemFilesOverwrite', $command) | Out-Null
$stepName = "[$fn]: Copy-back system database files after Standard edition installation"
#--------------------------------------------
Write-Verbose $stepName
$sql = "select 'copy ''$migrationFolderForFileBackups\' + reverse(left(reverse(physical_name),charindex('\',reverse(physical_name),1) - 1)) + ''' ''' + physical_name + ''''
AS CopyBackCommand from sys.master_files where database_id <= 4"
$rslt = Invoke-DbaSqlcmd `
-ServerInstance $ServerInstance `
-Database master `
-Query $sql `
-As DataTable
$command = ($rslt.Rows | Select-Object -ExpandProperty 'CopyBackCommand' | Out-String)
$returnValuesHashTable.Add('CopyBack-SystemDBFiles', $command) | Out-Null
$stepName = "[$fn]: Start SQL services"
#--------------------------------------------
Write-Verbose $stepName
$command = "Start-DbaSqlService -ComputerName $computerName"
$returnValuesHashTable.Add('Start-SQLServices', $command) | Out-Null
#Restoring the SSAS backups is also not necessary as they reappear
# auto-magically after the reinstall of the new edition!
<# $stepName = "[$fn]: Restore the SSAS databases" #-------------------------------------------- Write-Verbose $stepName if ($returnValuesHashTable['SQL-FeaturesInstalled'].ToString().Contains('SQL Server Analysis Services')) { #The commands to restore the SSAS databases # The variable $ssasRestoreCommand was already populated earlier! $returnValuesHashTable.Add('Do-SSASRestore', $ssasRestoreCommand) | Out-Null $returnValuesHashTable.Add('Remove-SSASTempBackups', $removeSSASTempBackups) | Out-Null } #>
$stepName = "[$fn]: Restore the SSRS key"
#--------------------------------------------
Write-Verbose $stepName
if ($returnValuesHashTable['SQL-FeaturesInstalled'].ToString().Contains('SQL Server Reporting Services'))
{
$command = "Restore the SSRS key in the 'Reporting Services Configuration Manager' from network location [$migrationFolderForSSRSBackups] `n"
$command += "Follow instructions at https://darrenmyher.com/2014/02/22/downgrading-reporting-services-from-sql-2012-enterprise-to-sql-2012-standard/ `n"
$returnValuesHashTable.Add('Restore-SSRSKey', $command) | Out-Null
}
$stepName = "[$fn]: Match startup mode of SQL related services"
#--------------------------------------------
Write-Verbose $stepName
$command = "Compare startup mode of SQL related services `n" +
"before the edition was changed to now, `n" +
"and match to mode (manual/automatic) `n"
$returnValuesHashTable.Add('Match-ServiceStartupMode', $command) | Out-Null
$stepName = "[$fn]: Replace password in the instructions file with *****"
#--------------------------------------------
Write-Verbose $stepName
$command = "If the sa password did not exist in the password database, `n" +
"please add it now with title $computerName.$instanceName.sa `n" +
"In this instructions file, manually replace all occurances of passwords '$SQLServiceAccountPassword' and '$SQLSAAccountPassword' with '*****' `n" +
"....passwords in plain text file on a network share = very bad idea!"
$returnValuesHashTable.Add('Replace-Passwords', $command) | Out-Null
$stepName = "[$fn]: Rename the migration folder to avoid accidental overwrites"
#--------------------------------------------
Write-Verbose $stepName
$timestamp = (get-date).ToString("yyyyMMdd_HHmmss")
$command = "Rename-Item -Path '$migrationFolderForInstBase' -NewName '$migrationFolderForInstBase`_Archive_$timestamp'"
$returnValuesHashTable.Add('Rename-MirgrationShare', $command) | Out-Null
$stepName = "[$fn]: Other instructions"
#--------------------------------------------
Write-Verbose $stepName
$command = 'Please re-read the exclusions that need to be verified/handled manually that are noted at the beginning of this file!'
$returnValuesHashTable.Add('Other-Instructions', $command) | Out-Null
$stepName = "[$fn]: Return the migration home share for reference"
#--------------------------------------------
Write-Verbose $stepName
$returnValuesHashTable.Add('Migration-HomeShare', $migrationFolderForInstBase) | Out-Null
#Return
$returnValuesHashTable
}
catch
{
[Exception]$ex = $_.Exception
Throw "Unable to get downgrade info . Error in step: `"{0}]`" `n{1}" -f `
$stepName, $ex.Message
}
finally
{
#Return value if any
}
}
How to create the instructions?
A call like this should give you a hash-table that you can then save off. Make sure you declare each of the variables with appropriate values first..
$ServerInstance = 'MYDBHOSTNAME\MYINST'
$MigrationShareBaseFolder = '\\myorgcifs\sqlstuff\SQLServerEditionDowngrade'
$SQLEntEdSoftwareBaseFolder' = '\\myorgsoftware\install\SqlServer\2008\EE' #Need this setup.exe to do command-line Uninstall
$SQLStdEdSoftwareBaseFolder' = '\\myorgsoftware\install\SqlServer\2008\SE' #Need this setup.exe to do command-line Uninstall
$SQLStdEdServicePackBaseFolder' = '\\myorgsoftware\install\SqlServer\2008\SP\SP4' #Need this setup.exe to do service pack install
$SQLServiceAccount' = 'myorg\svc_account'
$SQLServiceAccountPassword' = 'svc_account_password'
$SSASServiceAccount = 'myorg\svc_account'
$SSASServiceAccountPassword' = 'svc_account_password'
$SSRSServiceAccount = 'myorg\svc_account'
$SSRSServiceAccountPassword' = 'svc_account_password'
$SQLSAAccountPassword = 'sa_password'
$rslt = Get-EditionDowngradeInfo `
-ServerInstance $ServerInstance `
-DbatoolsBaseFolder $DbatoolsBaseFolder `
-SqlTranscriptaseBaseFolder $SqlTranscriptaseBaseFolder `
-MigrationShareBaseFolder $MigrationShareBaseFolder `
-SQLEntEdSoftwareBaseFolder $SQLEntEdSoftwareBaseFolder `
-SQLStdEdSoftwareBaseFolder $SQLStdEdSoftwareBaseFolder `
-SQLStdEdServicePackBaseFolder $SQLStdEdServicePackBaseFolder `
-SQLServiceAccount $SQLServiceAccount `
-SQLServiceAccountPassword $SQLServiceAccountPassword `
-SSASServiceAccount $SSASServiceAccount `
-SSASServiceAccountPassword $SSASServiceAccountPassword `
-SSRSServiceAccount $SSRSServiceAccount `
-SSRSServiceAccountPassword $SSRSServiceAccountPassword `
-SQLSAAccountPassword $SQLSAAccountPassword `
-SQLSysAdminAccounts $SQLSysAdminAccounts
foreach($key in $rslt.Keys)
{
Write-Output '----------------------------'
Write-Output $key
Write-Output '----------------------------'
Write-Output ''
Write-Output $rslt[$key]
Write-Output ''
Write-Output ''
Write-Output ''
}
Screenshot of the Output/Info Folders
This is how the network share folder looks after everything is said and done.
What happens if you have more than one instance on the host?
If you have more than one instance, you just have to follow the same process, but twice (with some minor differences). Let us say you have a default instance and a named instance. You would script out the instructions for both instances, do the backups and the other things you need to do. Then, you would uninstall the named instance first, restart the computer, then uninstall the default instance next and restart again. Then, you would install the default instance first, then the named instance. Then, you would install service pack for both instances in a single pass (not done twice). After that, you would shutdown both instances and replace the system database files to make all the databases magically appear again in both instances.
Conclusion
As shown above, a solution does not have to be fully automated before it can be used. It can be partially automated and still be efficient with a lot of manual control.
I was able to knock out 2 instances in a typical day while I also take care of other tasks. You mileage may vary. It is a function, so you wont hurt anything by trying out the generation and then if you like it, you can tweak it as necessary. I used it primarily for downgrading editions of SQL Server 2008 and 2008R2. For other versions, you can tweak the parameters easily.
You can always reach out to me via comments if you need help or clarification. I really mean it when I say that you can reach out to me. It does not have to be about this but it can be anything related to PowerShell based automation using databases.
Good luck!
SQL Jana 
Worth reading …!! thanks for sharing!!
Thanks for reading Deepak.
thanks for the bump!
George Walkey
Thank you for sharing your nice SQLTranscriptase module. Very useful as I mentioned!
Hello Jana,
Just a confirmation form your side: the script output is to be run locally on the machine being downgraded? I am asking since the the copy commands target the local path and not a network path.
I am trying to perform the operations from my admin machine rather than the SQL machine.
BTW, some dbatools command are outdated and replaced. I am trying to update them and will post or send you the amendments if they work.
Regards,
Hi Salim, The local path references are to locations of SQL Server data/transaction log files and not the backup location. You could still use a UNC path for that but it has been a while since I did the above which can be used as a good starting point.
An easier method that I recommend if you have small databases and a spare host with same version is to
1) Use the below dbatools command to move everything to another host (databases, logins, etc): This one command does it all:
https://docs.dbatools.io/#Start-DbaMigration
2) Uninstall existing version and install downgraded edition
3) Do the Start-DbaMigration again to move back from the spare box to the original