2020

June 03, 2020 1 ,
I have implemented windows server 2019 clustering by using the below methods,

  • Installed windows 2019 standard evaluation edition on 3 machines.
  • SRVRDC (will act as a domain controller), SRVR1, and SRVR2 (these two will act as nodes)
  • Disabled firewall and UAC on 3 machines.
  • Done IP configuration on all three machines as below.
Network IP configuration is done as shown below



Public IP address is configured as shown blelow 



Private IP configuration is done as shown below



IP configuration for SRVRDC 

192.168.1.110
255.255.255.0
192.168.1.110

IP configuration for SRVR1

192.168.1.111
255.255.255.0
192.168.1.110

- Private network  
192.168.2.11

IP configuration for SRVR2

192.168.1.112
255.255.255.0
192.168.1.110

Private network  
192.168.2.12

  • Need to add .net 3.5 and failover cluster feature on SRVR1 and SRVR2
  • On SRVRDC enable iSCSI initiator from the server manager file share. and create storage.
  • Once storage is created add them from the iSCSI drives. capacity is given as below.

  1. UserDB     R = 8 GB
  2. UserDBLog  S = 6 GB
  3. Tempdb     T = 2 GB
  4. MSDTC   M = 1 GB
  5. Quorum  Q = 1
  • Once storage created initiate them from node1 and node2 respectively 




Read more »

May 05, 2020 ,

 we have use below TSQL script to take all database backup jobs 


DECLARE @name VARCHAR(50) -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name
DECLARE @DeleteDate DATETIME = Dateadd(hh, -22, Getdate());
-- specify database backup directory
SET @path =
'\\servername\foldername\'
-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20), Getdate(), 112) + '_'
                   + Replace(CONVERT(VARCHAR(20), Getdate(), 108), ':', '')
DECLARE db_cursor CURSOR read_only FOR
  SELECT NAME
  FROM   master.dbo.sysdatabases
  WHERE  NAME NOT IN ( 'master', 'model', 'msdb', 'tempdb'
                     ) -- exclude these databases
OPEN db_cursor
FETCH next FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
  BEGIN
      SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
      BACKUP DATABASE @name TO DISK = @fileName WITH copy_only, noformat, noinit
      ,
      skip, norewind, nounload, compression, stats = 2
      FETCH next FROM db_cursor INTO @name
  END
EXEC master.sys.Xp_delete_file
  0,
  @path,
  'BAK',
  @DeleteDate,
  0;
CLOSE db_cursor
DEALLOCATE db_cursor 


Read more »

March 20, 2020 ,

 we can use below script to get last database name and date by executing below script

SELECT bus.database_name Org_DBName,
       restored_to_dbname,
       last_date_restored
FROM   msdb..backupset bus
       INNER JOIN (SELECT backup_set_id,
                          restored_to_dbname,
                          last_date_restored
                   FROM   msdb..restorehistory
                          INNER JOIN (SELECT rh.destination_database_name
                                             Restored_To_DBName,
                                             Max(rh.restore_date)
                                             Last_Date_Restored
                                      FROM   msdb..restorehistory rh
                                      GROUP  BY rh.destination_database_name) AS
                                     InnerRest
                                  ON destination_database_name =
                                     restored_to_dbname
                                     AND restore_date = last_date_restored) AS
                  RestData
               ON bus.backup_set_id = RestData.backup_set_id 


Read more »

February 26, 2020 , ,

 we can use the below script to check database backup restore percentage details and many more.



use master;

SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete) 
AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time], 
CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min], 
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min], 
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours], 
CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2, 
CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END) 
FROM sys.dm_exec_sql_text(sql_handle))) 
FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','RESTORE LOG','BACKUP DATABASE', 'BACKUP LOG','DbccSpaceReclaim','DbccFilesCompact')

Read more »

February 12, 2020
It is very important to take a database users and the respective privileges backup while you perform a database restore, as once the database restore complete we must replace the same access rights before so that application activities never disturb. 

to achieve this requirement we can use below script to take the specific database users and the roles. 

[code type="SQL"] Use <'database name here'> go SELECT 'IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = ' + QuoteName(dp.name, char(39)) + ') CREATE USER ' + QuoteName(dp.name) + IsNull(' FOR LOGIN ' + QuoteName(sp.name),'') + IsNull(' WITH DEFAULT_SCHEMA = ' + QuoteName(dp.default_schema_name),'') + ';' COLLATE DATABASE_DEFAULT FROM sys.database_principals dp LEFT JOIN sys.server_principals sp ON sp.sid = dp.sid WHERE dp.type in ('G','U','S') go SELECT 'exec sp_addrolemember ' + '''' + (r3.name) + '''' + ',' + '''' + (r2.name) + '''' + ';' FROM sys.database_role_members r1 inner join sys.database_principals r2 on r1.member_principal_id = r2.principal_id inner join sys.database_principals r3 on r1.role_principal_id = r3.principal_id GO [/code]



Test code given below:

Use <'database name here'>
go
SELECT 'IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = ' + 
QuoteName(dp.name, char(39)) + 
') CREATE USER ' + QuoteName(dp.name) + 
IsNull(' FOR LOGIN ' + QuoteName(sp.name),'') + 
IsNull(' WITH DEFAULT_SCHEMA = ' + QuoteName(dp.default_schema_name),'') + ';' COLLATE DATABASE_DEFAULT 
FROM sys.database_principals dp 
LEFT JOIN sys.server_principals sp 
ON sp.sid = dp.sid 
WHERE dp.type in ('G','U','S') 
go
SELECT 'exec sp_addrolemember ' + '''' + (r3.name) + '''' + ',' + '''' + (r2.name) + '''' + ';' 
FROM sys.database_role_members r1 
inner join sys.database_principals r2 
on r1.member_principal_id = r2.principal_id 
inner join sys.database_principals r3 
on r1.role_principal_id = r3.principal_id 
GO


Read more »
Subscribe to: Posts (Atom)

Author Name

Contact Form

Name

Email *

Message *

Powered by Blogger.