As part of database life cycle management process organizations ensure database is patched and kept up to date to ensure database system is secured from any vulnerabilities and to stay in support life cycle.
Microsoft Support Life cycle provides a consistent and predictable guidelines for product support availability when a product releases and throughout that product’s life. By understanding the product support available, customers are will be able to maximize the management of their IT investments and strategically plan for a successful IT future.
To know the current status of Microsft life cycle is not easy, MS provides an Excel file that contains all the build versions together with their current support life cycle stage for 2005 through the current version is available.(please note as we speak MS ended support for 2005)
So put to together a powershell script that will load the excel file into SQL Server table.
- First step download the excel file.
- Second create a table and procedure in any Database to hold the SQL Lifecycle data
- load the Excel file into the Table.
Link is available on this page.(https://support.microsoft.com/en-us/kb/321185) (http://download.microsoft.com/download/7/C/1/7C1BA242-14B5-48AE-9A99-7CE4FE9DAAF9/SQL%20Server%20Builds%20V3.xlsx)
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[SQLServerBuilds]( [Version] [varchar](100) NOT NULL, [BuildNumber] [varchar](100) NOT NULL, [KBNumber] [varchar](100) NOT NULL, [ServicePackRTM] [varchar](100) NULL, [ReleaseDate] [date] NULL, [UpdateNumber] [varchar](100) NULL, [IncrementalServicing] [varchar](100) NULL, [IncludeSecurity] [varchar](1000) NULL, [LifeCycleStatus] [varchar](100) NULL, [Notes] [varchar](max) NULL, [EOS] [date] NULL, [inserted_on] [datetime] NOT NULL, [inserted_by] [varchar](100) NOT NULL, CONSTRAINT [PK_SQLServerBuilds] PRIMARY KEY CLUSTERED ( [Version] ASC, [BuildNumber] ASC, [KBNumber] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[SQLServerBuilds] ADD CONSTRAINT [DF_SQLServerBuilds_inserted_on] DEFAULT (getdate()) FOR [inserted_on] GO ALTER TABLE [dbo].[SQLServerBuilds] ADD CONSTRAINT [DF_SQLServerBuilds_inserted_by] DEFAULT (suser_sname()) FOR [inserted_by] GO
Create procedure [dbo].[wmsp_CUSQLServerBuilds] ( @version varchar(100) ,@buildNumber varchar(100) ,@Kbnumber varchar(100) ,@ServicePackRTM varchar(100) ,@ReleaseDate varchar(100) ,@UpdateNumber varchar(100) ,@IncrementalServicing varchar(100) ,@IncludeSecurity varchar(1000) ,@LifeCycleStatus Varchar(100) ,@Notes varchar(100) ) as begin declare @eos date; set @ReleaseDate = case when PATINDEX('%[0-9]%',@ReleaseDate) = 1 and PATINDEX('%[A-Z]%',@ReleaseDate) = 0 then dateadd(dd,convert(int,@ReleaseDate),'12/31/1899 00:00:00') else @ReleaseDate end set @eos = case when PATINDEX('%[0-9]%',@Notes) = 1 and PATINDEX('%[A-Z]%',@Notes) = 0 then dateadd(dd,convert(int,@Notes),'12/31/1899 00:00:00') else null end if not exists (select 1 from SQLServerBuilds where [version] = @version and BuildNumber = @buildNumber and KBNumber = @Kbnumber) Begin Insert into SQLServerBuilds( [version] ,[BuildNumber] ,[KBNumber] ,[ServicePackRTM] ,[ReleaseDate] ,[UpdateNumber] ,[IncrementalServicing] ,[IncludeSecurity] ,[LifeCycleStatus] ,[Notes] ,EOS) Values ( @version ,@buildNumber ,@Kbnumber ,@ServicePackRTM ,@ReleaseDate ,@UpdateNumber ,@IncrementalServicing ,@IncludeSecurity ,@LifeCycleStatus ,@Notes ,@eos ) end else begin update SQLServerBuilds set LifeCycleStatus= @LifeCycleStatus ,UpdateNumber = @UpdateNumber ,IncrementalServicing = @IncrementalServicing ,IncludeSecurity = @IncludeSecurity ,Notes = @Notes ,ReleaseDate = @ReleaseDate ,ServicePackRTM = @ServicePackRTM ,eos = @eos where [version] = @version and BuildNumber = @buildNumber and KBNumber = @Kbnumber end end
Powershell Script to load the excel
$srv = "Server Name" $Dbname = "Database Name" $filelocation = "Excel File Location" add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop $cscn = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $srv $files = Get-ChildItem $filelocation *.xlsx foreach ($file in $files) { Get-ExcelSheetInfo $file.FullName | ForEach-Object { $Sheet =$_.Name; $SQLserver = Import-Excel $file.FullName -Sheet $Sheet; ForEach ($SQL in $SQLserver) { $Buldnumber = $SQL.'Build number'; $kbnumber = $SQL.'KB number'; $lifecycle = if($SQL.'Lifecycle stage' -EQ $null){$SQL.'Lifecycle status (Service Pack Support End Date) '}else{$SQL.'Lifecycle stage'} $Updatenumber = $SQL.'Cumulative Update number/Security ID'; $IncrementalServicing = $SQL.'Incremental Servicing Model'; $inlcudeSecurity = $SQL.'Includes latest security fixes?'; [String]$notes = $SQL.'Notes regarding Lifecycle stage'+$SQL.'Notes regarding Lifecycle stage/ Service Pack support end date'+$SQL.'Lifecycle status (Service Pack Support End Date) '; if($notes -ne $null){ $notes=$notes.replace("'","''")} $releasedate = $SQL.'Release Date'; $servicepack = $SQL.'Service Pack/RTM'; if($Buldnumber -NE $null){ $cscn.Databases[$Dbname].ExecuteNonQuery("exec wmsp_cuSQLServerBuilds '$Sheet','$Buldnumber','$kbnumber','$servicepack','$releasedate','$Updatenumber','$IncrementalServicing','$inlcudeSecurity','$lifecycle','$notes'")} } }
Final Result
Sqljunkieshare 