SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
PRINT N'Dropping foreign keys from [EventCollection].[MonitoringServers]'
GO
ALTER TABLE [EventCollection].[MonitoringServers] DROP CONSTRAINT[FK_MonitoringServers_Farms]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating types'
GO
CREATE TYPE [SQL].[DatabaseIDsType] AS TABLE
(
[DatabaseID] [int] NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [SQL].[GetLoadedProgrammParameters]'
GO
CREATE PROCEDURE [SQL].[GetLoadedProgrammParameters]
	@LoadingInfoID int,
	@SQLInstanceName nvarchar(max),
	@DatabaseIDs [SQL].[DatabaseIDsType] READONLY

AS
BEGIN
	SELECT ld.LoadedDatabaseID,
		   ld.SQLInstanceName,
		   ld.DatabaseName,
		   lp.LoadedObjectsID,
		   lo.ObjectName,
		   lo.SchemaName,
		   lps.TypeName,
		   lps.ParameterName,
		   lps.IsOutput,
		   lp.ParameterId,
		   lps.HasDefaultValue,
		   lo.ObjectType
	FROM [SQL].LoadedProgrammParameters lp
	JOIN [SQL].LoadedObjects lo ON lp.LoadedObjectsID = lo.LoadedObjectsID
	JOIN [SQL].LoadedDatabases ld ON lo.LoadedDatabaseID = ld.LoadedDatabaseID
	JOIN [SQL].LoadedProgrammParamSettings lps ON lps.ParametersSettingsID = lp.ParametersSettingsID
	JOIN @DatabaseIDs db ON db.DatabaseID = ld.DatabaseId
	WHERE ld.LoadingInfoID = @LoadingInfoID AND ld.SQLInstanceName = @SQLInstanceName
END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [SQL].[GetLoadedTableKeys]'
GO
CREATE PROCEDURE [SQL].[GetLoadedTableKeys]
	@LoadingInfoID int,
	@SQLInstanceName nvarchar(max),
	@DatabaseIDs [SQL].[DatabaseIDsType] READONLY

AS
BEGIN
	SELECT ld.LoadedDatabaseID,
		   ld.SQLInstanceName,
		   ld.DatabaseName,
		   ltk.LoadedObjectId,
		   lo.ObjectName,
		   lo.SchemaName,
		   ltk.ColumnName,
		   ltk.DeleteAction,
		   ltk.UpdateAction,
		   ltk.KeyName,
		   ltk.KeyType,
		   lto.ObjectName,
		   lto.SchemaName,
		   ltk.TargetTableColumn,
		   lto.LoadedObjectsID,
		   lo.ObjectType
	FROM [SQL].LoadedTableKeys ltk
	JOIN [SQL].LoadedObjects lo ON ltk.LoadedObjectId = lo.LoadedObjectsID
	JOIN [SQL].LoadedDatabases ld ON lo.LoadedDatabaseID = ld.LoadedDatabaseID
	LEFT JOIN [SQL].LoadedObjects lto ON ltk.TargetTableObjectId = lto.LoadedObjectsID
	WHERE ld.LoadingInfoID = @LoadingInfoID AND ld.SQLInstanceName = @SQLInstanceName AND EXISTS (SELECT * FROM @DatabaseIDs WHERE DatabaseID = ld.DatabaseId)
END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [EventCollection].[MonitoringServersFarmRoles]'
GO
CREATE TABLE [EventCollection].[MonitoringServersFarmRoles]
(
[FarmID] [int] NOT NULL,
[ServerID] [int] NOT NULL,
[ServerRoleFlags] [int] NOT NULL CONSTRAINT [DF_MonitoredServersFarmRoles_ServerRoleFlags] DEFAULT ((1)),
[IsActive] [bit] NOT NULL CONSTRAINT [DF_MonitoredServersFarmRoles_IsActive] DEFAULT ((1))
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_MonitoredServersFarmRoles] on [EventCollection].[MonitoringServersFarmRoles]'
GO
ALTER TABLE [EventCollection].[MonitoringServersFarmRoles] ADD CONSTRAINT [PK_MonitoredServersFarmRoles] PRIMARY KEY CLUSTERED  ([FarmID], [ServerID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [SQL].[GetLoadedColumnSettings]'
GO
CREATE PROCEDURE [SQL].[GetLoadedColumnSettings]
	@LoadingInfoID int,
	@SQLInstanceName nvarchar(max),
	@DatabaseIDs [SQL].[DatabaseIDsType] READONLY

AS
BEGIN
	SELECT ld.LoadedDatabaseID,
	   ld.SQLInstanceName,
	   ld.DatabaseName,
	   lc.LoadedObjectsID,
	   lo.ObjectName,
	   lo.SchemaName,
	   lcs.Type,
	   lcs.ColumnName,
	   lcs.MaxLength,
	   lcs.IsNullable,
	   lcs.IsIdentity,
	   lcs.CollationName,
	   lcs.Precision,
	   lcs.KeyType,
	   lo.ObjectType
	FROM [SQL].LoadedColumns lc
	JOIN [SQL].LoadedObjects lo ON lc.LoadedObjectsID = lo.LoadedObjectsID
	JOIN [SQL].LoadedDatabases ld ON lo.LoadedDatabaseID = ld.LoadedDatabaseID
	JOIN [SQL].[LoadedColumnSettings] lcs ON lc.ColumnSettingsID = lcs.ColumnSettingsID
	JOIN @DatabaseIDs db ON db.DatabaseID = ld.DatabaseId
	WHERE ld.LoadingInfoID = @LoadingInfoID AND ld.SQLInstanceName = @SQLInstanceName
END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [EventCollection].[MonitoringServersFarmRoles]'
GO
ALTER TABLE [EventCollection].[MonitoringServersFarmRoles] ADD CONSTRAINT [FK_MonitoredServersFarmRoles_Farms] FOREIGN KEY ([FarmID]) REFERENCES [dbo].[Farms] ([ID]) ON DELETE CASCADE
ALTER TABLE [EventCollection].[MonitoringServersFarmRoles] ADD CONSTRAINT [FK_MonitoredServersFarmRoles_MonitoringServers] FOREIGN KEY ([ServerID]) REFERENCES [EventCollection].[MonitoringServers] ([ID]) ON DELETE CASCADE
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
UPDATE Options SET OptionValue = '7.4.0' WHERE OptionID = 14
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
UPDATE Options SET OptionValue = '[7.2,7.3)' WHERE OptionID = 1003
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
IF OBJECT_ID('tempdb..#duplicateMappings') IS NOT NULL
    DROP TABLE #duplicateMappings

CREATE TABLE #duplicateMappings (
 ActualID int NOT NULL,
 DuplicateID int NOT NULL,
 Name nvarchar(255) NOT NULL
); WITH dupdata AS 
(
	select ID, Name, FarmID, RANK() OVER(Partition by Name order by ID) AS OrderInserted  FROM EventCollection.MonitoringServers
),
duplicateMappings AS
(
select d1.ID AS ActualID, d2.ID AS DuplicateID, d1.Name FROM dupdata d1
JOIN dupdata d2 ON d1.OrderInserted = 1 AND d2.ID <> d1.ID AND d2.Name = d1.Name
)
INSERT INTO #duplicateMappings
SELECT ActualID, DuplicateID, Name FROM duplicateMappings

DELETE FROM EventCollection.MonitoringServersFarmRoles
INSERT INTO EventCollection.MonitoringServersFarmRoles
SELECT s.FarmID, ISNULL(dup.ActualID, s.ID) AS ServerID, s.ServerRoleFlags, s.IsActive FROM EventCollection.MonitoringServers s
LEFT JOIN #duplicateMappings dup ON dup.DuplicateID = s.ID

DELETE d
FROM EventCollection.CollectionHistory d 
INNER JOIN #duplicateMappings mapping ON d.ServerID = mapping.DuplicateID

DELETE d
FROM PerformanceCounters.CounterValues d 
INNER JOIN #duplicateMappings mapping ON d.TerminalServerID = mapping.DuplicateID

DELETE d
FROM PerformanceCounters.DiskQueue d 
INNER JOIN #duplicateMappings mapping ON d.TerminalServerID = mapping.DuplicateID

DELETE d
FROM PerformanceCounters.DiskRead d 
INNER JOIN #duplicateMappings mapping ON d.TerminalServerID = mapping.DuplicateID

DELETE d
FROM PerformanceCounters.DiskTransfer d 
INNER JOIN #duplicateMappings mapping ON d.TerminalServerID = mapping.DuplicateID

DELETE d
FROM PerformanceCounters.DiskWrite d 
INNER JOIN #duplicateMappings mapping ON d.TerminalServerID = mapping.DuplicateID

DELETE d
FROM PerformanceCounters.DiskFreeSpace d 
INNER JOIN #duplicateMappings mapping ON d.TerminalServerID = mapping.DuplicateID


DELETE d
FROM PerformanceCounters.CPU d 
INNER JOIN #duplicateMappings mapping ON d.TerminalServerID = mapping.DuplicateID

DELETE d
FROM PerformanceCounters.Memory d 
INNER JOIN #duplicateMappings mapping ON d.TerminalServerID = mapping.DuplicateID

DELETE d
FROM PerformanceCounters.NetworkReceived d 
INNER JOIN #duplicateMappings mapping ON d.TerminalServerID = mapping.DuplicateID

DELETE d
FROM PerformanceCounters.NetworkSent d 
INNER JOIN #duplicateMappings mapping ON d.TerminalServerID = mapping.DuplicateID

DELETE d
FROM dbo.PerformanceCountersAlerts d 
INNER JOIN #duplicateMappings mapping ON d.TerminalServerID = mapping.DuplicateID

DELETE d
FROM PerformanceCounters.ServerCounterStates d 
INNER JOIN #duplicateMappings mapping ON d.TerminalServerID = mapping.DuplicateID

DELETE d
FROM EventCollection.ServerEventSourceCollectionStates d 
INNER JOIN #duplicateMappings mapping ON d.ServerID = mapping.DuplicateID

DELETE d
FROM PerformanceCounters.ServerInstances d 
INNER JOIN #duplicateMappings mapping ON d.TerminalServerID = mapping.DuplicateID


DELETE d
FROM Template.ServerSubscriptions d 
INNER JOIN #duplicateMappings mapping ON d.TerminalServerID = mapping.DuplicateID

DELETE d
FROM dbo.Discs d 
INNER JOIN #duplicateMappings mapping ON d.TerminalServerID = mapping.DuplicateID

DELETE d
FROM dbo.NetworkAdapters d 
INNER JOIN #duplicateMappings mapping ON d.TerminalServerID = mapping.DuplicateID


DELETE d
FROM EventCollection.MonitoringServers d 
INNER JOIN #duplicateMappings mapping ON d.ID = mapping.DuplicateID

UPDATE EventCollection.MonitoringServers SET PerformanceCountersEnabled = 1 WHERE PerformanceCountersEnabled = NULL


IF OBJECT_ID('tempdb..#duplicateMappings') IS NOT NULL
    DROP TABLE #duplicateMappings

GO

IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO

