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 