2019-02-19

Installing SQL Server 2017 on Windows 10

Installing SQL Server Database Engine 2017 together with other major versions can give an error like this:


The entire installation will fail, and looking into the summary log file reveils an issue installing Microsoft Visual C++ 2015 Redistributable. This is thoroughly described in a StackExchange question (link), but the solution details differ in various situations according to the comments. The solution given in the Microsoft CSS SQL Server Engineers blog entry (link) gives another solution on repairing the Visual C++ 2015 Redistributable that did not work in my case as the component was not installed.

The existing Visual C++ 2017 Redistributable was version 14.16.27012.6 where the link in the StackOverflow answer goes to 14.11.25325.0. The latest version can be found through the support article 2977003 (link). Today the given version is 14.16.27027.1. The version is shown in the file details:


The name of the installation file is always "VC_redist.x64.exe".

After removing the two Visual C++ 2017 Redistributable installations the SQL Server installation completes with success - partly.

I am actually not using the failed components, but this will be fixed later in this text with a update.

After the SQL Server 2017 installation Visual C++ 2015 Redistributable is on the computer in both x86 and x64.
Installing Visual C++ 2017 Redistributable requires a computer restart on both x64 and x86 installations. This gives two restarts.

After all this SQL Server 2017 runs nice.

And then the latest CU can be installed. I notice that the CU installation also updates the two shared features that failed in the SQL Server 2017 installation above.


The update completes with success. Please notice that a final reboot is required as mentioned in the summary log file:
Instance SSDB2017 overall summary:
  Final result:                  Passed but reboot required, see logs for details
  Exit code (Decimal):           3010
...

This is not raised in the GUI.

2019-02-03

Installing Ola Hallengren Maintenance Solution


Ola Hallengren Maintenance Solution (OHMS) is with good reason a well-known and widely used solution for SQL Server database backups and maintenance of indexes and statistics together with database integrity check. The solution is recommended by many great DBAs and SQL Server MVPs. Personally I have used the solution with great success in several financial organisations.

Installing OHMS is quite simple. In this text I will look into some details when installing OHMS in a scalable SQL Server infrastructure. The installation is here done i five steps:
  1. Create DBA database
  2. Configure OHMS installation
  3. Run OHMS installation
  4. Configure OHMS
  5. Test OHMS
With dbatools (dbatools.io) there is the PowerShell CmdLet Install-DbaMaintenanceSolution, that install or update OHMS in a given database. Check the details on the code in GitHub. I have not personally used dbatools, but they look fine and I might take a deeper look later.

Create DBA Database

Create seperate userdatabase on each instance for SQL Server administration, e.g. [sqladmin] with a prefix of the organisation. User underscore for space in the name like [nielsgrove_sqladmin].

This is a general solution that can be used for other database administration solutions.
This database does not require OHMS, but you should still do backups and maintain indexes.

Configure installation

Put OHMS in seperate filegroup to use database for other purposes now and in the future.

ALTER DATABASE [sqladmin] ADD FILEGROUP [OHMS];
GO
ALTER DATABASE [sqladmin] ADD FILE ( NAME = N'ohms_data0', FILENAME = N'C:\Data\sqladmin_ohms_data0.ndf' , SIZE = 8MB , FILEGROWTH = 64MB ) TO FILEGROUP [OHMS];
GO
ALTER DATABASE [sqladmin] MODIFY FILEGROUP [OHMS] DEFAULT;
GO


The filegroup [OHMS] is marked as default during the OHMS installation.

OHMS is using the schema [dbo] by default. You could change this, but this would require a rather detailed rewrite of the code. As the code in the scriptfile ManitenanceSolution.sql is more than 8000 lines I would not recomment changing the schema.

Download script from ola.hallengren.com. Define database name from above in the USE-statement around line 22.
Create cmd- or PowerShell-file to implement configuration. The simple solution used here is a one-line cmd-script as in the next section. You should always script even simple things, as you then are prepared to scale out with the same quality.
If you are building a automatic installation and upgrade you should get the precise URLs from the general .com-URL. That might require a more complex configuration.

If you want a longer or shorter history on backuphistory, jobhistory or CommandLog from the default 30 days you can edit the file MaintenanceSolution.sql by searching for DATEADD(dd,-30,GETDATE()) and change the value. You should change the history length before running the OHMS installation as it is more simple than alter the jobs afterwards. I like to have 42 days of history...

Run OHMS installation

sqlcmd command-line using configuration above. If you create a more complex configuration, the execution itself will have to be considered in detail.

C:\>sqlcmd.exe -S "(local)\SQLSERVER" -E -d "sqladmin" -i "C:\SysAdmin\MaintenanceSolution.sql"
Changed database context to 'sqladmin'.


And change the default filegroup back to [PRIMARY]
ALTER DATABASE [sqladmin] MODIFY FILEGROUP [PRIMARY] DEFAULT;
GO


Configure OHMS

The sql-script dowes create SQL Agent jobs, but the jobs has no schedule. Add a schedule to each job by the stored procedure [msdb].[dbo].[sp_add_jobschedule]. This is a quick example:

DECLARE @schedule_id int;
EXECUTE [msdb].[dbo].[sp_add_jobschedule]
  @job_name=N'CommandLog Cleanup',
  @name=N'OHMS CommandLog Cleanup',
  @enabled=1,
  @freq_type=8,
  @freq_interval=1,
  @freq_subday_type=1,
  @freq_subday_interval=0,
  @freq_relative_interval=0,
  @freq_recurrence_factor=1,
  @active_start_date=20190203,
  @active_end_date=99991231,
  @active_start_time=190000,
  @active_end_time=235959, @schedule_id = @schedule_id OUTPUT;
SELECT @schedule_id;
GO


OHMS create these 11 jobs in SQL Agent:
  • CommandLog Cleanup
  • DatabaseBackup - SYSTEM_DATABASES - FULL
  • DatabaseBackup - USER_DATABASES - DIFF
  • DatabaseBackup - USER_DATABASES - FULL
  • DatabaseBackup - USER_DATABASES - LOG
  • DatabaseIntegrityCheck - SYSTEM_DATABASES
  • DatabaseIntegrityCheck - USER_DATABASES
  • IndexOptimize - USER_DATABASES
  • Output File Cleanup
  • sp_delete_backuphistory
  • sp_purge_jobhistory
You can get a list of the jobs from the table [msdb].[dbo].[sysjobs]:
SELECT * FROM msdb.dbo.sysjobs
WHERE [description]=N'Source: https://ola.hallengren.com'
ORDER BY [name];


Glenn Berry has written a script that creates the jobs with a good reference schedule. The script is introduced in the blog post "Creating SQL Server Agent Job Schedules for Ola Hallengren’s Maintenance Solution".

All OHMS jobs are placed in the SQL Agent job category Database Maintenance. You could change the category with the stored procedure [msdb].[dbo].[sp_update_job], but I think that the benefits will be rather limited.

Log files are placed in SQL Server ErrorLogPath where the SQL Server ErrorLog and default traces are placed by default. If you want the OHMS log files another place, then change the job steps with the stored procedure [msdb].[dbo].[sp_update_jobstep]. You should only do this if you are forced to by stupid policies.

Put configuration in script file(-s) and make it idempotent. Also put everything in version control, with the documentation.

If you want to remove the OHMS jobs then this query on the table [msdb].[dbo].[sysjobs] can generate the statements:
SELECT N'EXECUTE [msdb].[dbo].[sp_delete_job] @job_name=N''' + [name] + N''', @delete_unused_schedule=1;'
FROM [msdb].[dbo].[sysjobs]
WHERE [description]=N'Source: https://ola.hallengren.com';


Test OHMS

Run each job starting with database integrity checks, then full data backup moving to differential backup and closing with transaction log backup spiced up with index and statistics maintenance jobs.
Check output of each job and check SQL Server errorlog after each SQL Server Agent job execution.

You can generate the statements th start the OHMS jobs refering the stored procedure [msdb].[dbo].[sp_start_job] in a query on the table [msdb].[dbo].[sysjobs] like above:
SELECT N'EXECUTE [msdb].[dbo].[sp_start_job] @job_name=N''' + [name] + N''';'
FROM [msdb].[dbo].[sysjobs]
WHERE [description]=N'Source: https://ola.hallengren.com';


Improvements

  • The installation script handles both installation and update. Some identification on which version is installed and the version about to be deployed.
  • Integrate installation with your Definitive Software Library (DSL). Put the complete installations set with Ola's and you own scripts in a combined installation set.
  • More robust errorhandling. Especially in the tests.

History

2019-02-03 : First text. 2021-06-15 : Section on changed history added.