Migration Graylite To Azure

Migrasi Database Server To Azure




[sql]
/*---- remove orphaned user using dbo.sp_Drop_OrphanedUsers stored procedure ----*/
USE [db_name]
GO
EXEC [sp_Drop_OrphanedUsers]

/*---- remove Stored Procedure: [HR].[Attendance.Activities] ----*/

/*---- remove a trigger from table inventory.[item.stock] ----*/

/*---- create table system.email.queue ----*/
CREATE TABLE [System].[Email.Queue]
(
[trans_id] [INT] IDENTITY(1,1)
NOT NULL
,[trans_recipients] [NVARCHAR](1000) NULL
,[trans_copy_recipients] [NVARCHAR](1000) NULL
,[trans_blind_copy_recipients] [NVARCHAR](1000) NULL
,[trans_subject] [NVARCHAR](1000) NULL
,[trans_body] [NVARCHAR](MAX) NULL
,[trans_sensitivity] [NVARCHAR](50) NOT NULL
CONSTRAINT [DF_Email.Queue_trans_sensitivity] DEFAULT('Personal')
,[trans_importance] [NVARCHAR](50) NOT NULL
CONSTRAINT [DF_Email.Queue_trans_importance] DEFAULT('High')
,[trans_body_format] [NVARCHAR](50) NOT NULL
CONSTRAINT [DF_Email.Queue_trans_body_format] DEFAULT(N'HTML')
,[trans_profile_name] [NVARCHAR](100) NOT NULL
CONSTRAINT [DF_Email.Queue_trans_profile_name] DEFAULT('System Notification')
,[trans_fetched] [BIT] NOT NULL
CONSTRAINT [DF_Email.Queue_trans_fetched] DEFAULT((0))
,[reply_to] [NVARCHAR](50) NULL
,CONSTRAINT [PK_Email.Queue] PRIMARY KEY CLUSTERED([trans_id] ASC)
WITH(PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF,IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON)
)

/*---- convert :Trigger [System].[Send.Notification]
:SP [System].[User.SetPassword]
:SP [System].[Schedule.Execute] and others sp which using db_send_email ----*/
INSERT INTO system.[email.queue]
(
trans_recipients
,trans_body
,trans_subject
)
SELECT
'alamatemail@gmail.com'
,@body
,@subject

/*---- [System].[Send.Notification] ----*/

INSERT INTO system.[email.queue]
(
trans_recipients
,trans_body
,trans_subject
)
SELECT
@NOTICE_EMAIL
,@body
,@Notice_Subject

/*---- [System].[User.SetPassword] ----*/
INSERT INTO system.[email.queue]
(
trans_recipients
,trans_body
,trans_subject
,trans_profile_name
)
SELECT
@Email
,@body
,'New Password'
,'System Notification'
/*---- [System].[Schedule.Execute] ----*/
INSERT INTO system.[email.queue]
(
trans_recipients
,trans_body
,trans_subject
)
SELECT
'loeischandra@gmail.com'
,@ERR
,'Error Graylite Job Scheduler'

/*---- create sp getlocaldate ----*/
CREATE FUNCTION [dbo].[GetLocalDate]
(
)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(Hh,7,GETUTCDATE())
END;
CREATE DEFAULT [dbo].[LocalTime] AS DATEADD(Hh,7,GETUTCDATE());
/*---- update password from asymetrickey to sha1 ----*/
ALTER TABLE system.[user.management]
ADD
old_password VARBINARY(256)
,new_password VARBINARY(256)

UPDATE system.[user.management]
SET old_password = user_password
SELECT
'update system.[user.management] set new_password = hashbytes(''sha1'','''+CONVERT( NVARCHAR,DECRYPTBYASYMKEY(ASYMKEY_ID('UserPassword'),user_password,N'c4t3rp1ll4r'))+''') where user_login='''+user_login+''';'
FROM system.[user.management]

UPDATE system.[user.management]
SET user_password = new_password

/*---- remove asymetric key ----*/

/*---- update printout connection to new server database ----*/
DECLARE
@connstring NVARCHAR(200)
SET @connstring = '<ConnectionString>Server=graylite1.database.windows.net, 1433;Database=DB_NAME;User Id=graylite1;Password=b00m3r4ng!;</ConnectionString>'
UPDATE [System].[Form.Headers]
SET hdr_expr = CASE
WHEN STUFF(hdr_expr,PATINDEX('%<ConnectionStringEncrypted>%',hdr_expr),PATINDEX('%</ConnectionStringEncrypted>%',hdr_expr)+28-PATINDEX('%<ConnectionStringEncrypted>%',hdr_expr),@connstring) IS NULL
THEN hdr_expr
ELSE STUFF(hdr_expr,PATINDEX('%<ConnectionStringEncrypted>%',hdr_expr),PATINDEX('%</ConnectionStringEncrypted>%',hdr_expr)+28-PATINDEX('%<ConnectionStringEncrypted>%',hdr_expr),@connstring)
END
WHERE
hdr_type = 'REPORTSTI'
UPDATE [System].[Form.Headers]
SET hdr_expr = CASE
WHEN STUFF(hdr_expr,PATINDEX('%<ConnectionString>%',hdr_expr),PATINDEX('%</ConnectionString>%',hdr_expr)+19-PATINDEX('%<ConnectionString>%',hdr_expr),@connstring) IS NULL
THEN hdr_expr
ELSE STUFF(hdr_expr,PATINDEX('%<ConnectionString>%',hdr_expr),PATINDEX('%</ConnectionString>%',hdr_expr)+19-PATINDEX('%<ConnectionString>%',hdr_expr),@connstring)
END
WHERE
hdr_type = 'REPORTSTI'
/*---- update sql server agent jobs :AZUREDAILYSCHEDULE on i7PC ----*/

/*---- update FETCH EMAIL stored procedure [System].[Fetch.Email.Azure] on DB_Controller i7PC ----*/

/*---- remove job on local i7 ----*/

/*---- replace semua penggunaan getdate() ke dbo.getlocaldate() ----*/

/*---- All stored procedure and function ----*/
SELECT DISTINCT
o.name AS Object_Name
,o.type_desc
,LEFT(m.definition,500) AS def
FROM sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id = o.object_id
WHERE m.definition LIKE '%GETDATE%' ESCAPE '\'

/*---- Default value on tables ----*/
SELECT
table_schema
,table_name
,COLUMN_NAME
,column_default
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_default LIKE '%GETDATE%' ESCAPE '\'
ORDER BY
table_schema
,table_name
/*---- Computed column formula on tables ----*/

---------------------------------

SELECT
a.definition
,a.name
,b.name
FROM sys.computed_columns a
INNER JOIN sys.tables b ON a.object_id = b.object_id
WHERE definition LIKE '%GETDATE%' ESCAPE '\'
--

/*---- create report session ----*/
CREATE TABLE [System].[Report.Session]
(
[trans_id] [INT] IDENTITY(1,1)
NOT NULL
,[trans_code] [NVARCHAR](100) NOT NULL
,[trans_status] [TINYINT] NOT NULL
CONSTRAINT [DF_Report.Session_trans_status] DEFAULT((0))
,[trans_date] [DATETIME] NOT NULL
CONSTRAINT [DF_Report.Session_trans_date] DEFAULT([dbo].[getlocaldate]())
,[trans_report] [NVARCHAR](100) NOT NULL
,[trans_user] [NVARCHAR](50) NOT NULL
,[trans_type] [BIT] NOT NULL
CONSTRAINT [DF_Report.Session_trans_type] DEFAULT((0))
,CONSTRAINT [PK_Report.Session] PRIMARY KEY CLUSTERED([trans_id] ASC)
WITH(PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF,IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON)
)
[/sql]