Service Pack on AVG

First upgrade secondary put manual fail-over for aov
disable monitoring alerts disable replication on avg dbs

Change synchronous commit to asynchronous commit
change automatic failover to manual failover
make sure ag group is on primary replica
upgrade secondary replica
failover to secondary replica
upgrade primary replicat
failback ag to primary replica
set synchronous commit and automatic failover as it was before upgrade.

put synchronous mode to asynchronous mode
put automatice to manual failover

1. Open your Windows Fail-over Cluster Manager .
2. Check which node owns the Role (A or B).
3. Pause the Secondary node (so that the Primary knows is down)
4. Patch the Secondary node.
5. Reboot if required.
6. Check for more patches or updates (reboot if required)
7. If none needed then RESUME the secondary node.
8. Failover using High Availability Failover Wizard (SQL AlwaysON Dashboard)
9. Pause the other node (which should be Secondary) on Windows Cluster Manager
10. Apply Patches and updates
11. Reboot if required
12. Check for additional patches (reboot if required)
13. RESUME node again in Windows Failover Cluster Manager
14. Fail-over back to original node if want to keep in perspective (A being primary B Secondary)

Advertisements

Check High availability solutions configured on server.

 

For Clustering use the following query:
–To Check if Database are configured for Clustering
SELECT SERVERPROPERTY(‘IsClustered’) as ‘Clusteredserver’

For Mirroring use the following query:
–To Check if Database are configured for Mirroring
SELECT A.name,
CASE WHEN B.mirroring_state is NULL THEN ‘Mirroring is not configured’
ELSE ‘Mirroring is configured’
END as MirroringState FROM sys.databases A
INNER JOIN sys.database_mirroring B ON A.database_id=B.database_id WHERE a.database_id > 4 ORDER BY A.NAME

For Replication use the following query:
–To Check if Database are configured for Replication
SELECT name as [Database name],CASE is_published
WHEN 0 THEN ‘No’ ELSE ‘Yes’END AS [Is Published],CASE is_merge_published WHEN 0 THEN ‘No’ ELSE ‘Yes’END AS [Is Merge Published],
CASE is_distributor WHEN 0 THEN ‘No’ELSE ‘Yes’END AS [Is Distributor],
CASE is_subscribed WHEN 0 THEN ‘No’ELSE ‘Yes’END AS [Is Subscribed]
FROM sys.databases
WHERE database_id > 4

For Log shipping use the following query:
–To Check if Database are configured for Log shipping
select lp.primary_server, sd.name, case when lp.primary_database IS not null then ‘Yes’ else ‘No’ end LSConfigured
From Master.dbo.sysDatabases SD
left join msdb.dbo.log_shipping_monitor_primary LP on lp.primary_database=SD.name

 

–To determine whether AlwaysOn Availability Groups is enabled
SELECT SERVERPROPERTY (‘IsHadrEnabled’);

Reading the SQL Server log files using TSQL

sp_readerrorlog.
Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc…
Log file type: 1 or NULL = error log, 2 = SQL Agent log
Search string 1: String one you want to search for
Search string 2: String two you want to search for to further refine the results

 

EXEC sp_readerrorlog 6, 1, ‘2005’, ‘exec’

xp_readerrrorlog

Even though sp_readerrolog accepts only 4 parameters, the extended stored procedure accepts at least 7 parameters.

If this extended stored procedure is called directly the parameters are as follows:

Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc…
Log file type: 1 or NULL = error log, 2 = SQL Agent log
Search string 1: String one you want to search for
Search string 2: String two you want to search for to further refine the results
Search from start time
Search to end time
Sort order for results: N’asc’ = ascending, N’desc’ = descending
EXEC master.dbo.xp_readerrorlog 6, 1, ‘2005’, ‘exec’, NULL, NULL, N’desc’
EXEC master.dbo.xp_readerrorlog 6, 1, ‘2005’, ‘exec’, NULL, NULL, N’asc’

Find all logins which have sys admin permission.

Find all logins which have sys admin permission.

 

USE master
GO

SELECT p.name AS [loginname] ,
p.type ,
p.type_desc ,
p.is_disabled,
CONVERT(VARCHAR(10),p.create_date ,101) AS [created],
CONVERT(VARCHAR(10),p.modify_date , 101) AS [update]
FROM sys.server_principals p
JOIN sys.syslogins s ON p.sid = s.sid
WHERE p.type_desc IN (‘SQL_LOGIN’, ‘WINDOWS_LOGIN’, ‘WINDOWS_GROUP’)
— Logins that are not process logins
AND p.name NOT LIKE ‘##%’
— Logins that are sysadmins
AND s.sysadmin = 1
GO

—————————————————————————————————————————————

exec sp_helpsrvrolemember ‘sysadmin’
exec sp_helpsrvrolemember ‘serveradmin’

Check version of sql you are running on server:

Step 1:By using SQL Server Management Studio right clicking on properties of instance name. Here you can see the “Product version” or “Version”.

Step 2:Using T-SQL command.
SELECT SERVERPROPERTY(‘productversion’), SERVERPROPERTY (‘productlevel’),SERVERPROPERTY (‘edition’)
GO
OR
select @@version

Step 3:Using configuration manager
Open configuration manager expand sql server services right click on instance name. Go to “Advanced Tab” go to to “Stock Keeping Unit Name” and “Version”
First Digit of version:

8.0 for SQL Server 2000
9.0 for SQL Server 2005
10.0 for SQL Server 2008
10.5 for SQL Server 2008 R2
11.0 for SQL Server 2012
12.0 for SQL Server 2014
13.0 for SQL Server 2016

Find owner of maintenance plan

To view the owner of maintenance plan execute the following select statement.

–Above 2008
select [ownersid] , SUSER_SNAME([ownersid]) from msdb.dbo.sysssispackages
where [name] like ‘<mainatenanceplanname>’

To update owner of maintenance plan execute the below command:
update msdb.dbo.sysssispackages
set [ownersid] = suser_sid(‘<loginname>’)
where [name] like ‘<mainatenanceplanname>’

–For 2005
select name,owner from dbo.sysmaintplan_plans

UPDATE msdb.dbo.sysdtspackages90
SET OWNERSID = SUSER_SID(‘<loginname>’)
WHERE [Name] like ‘<mainatenanceplanname>’

–For 2000
select plan_name,owner from dbo.sysdbmaintplans

update dbo.sysdbmaintplans set owner =’LoginName’ where owner =’LoginName’

To change job owner expand SQL Server Agent, expand Jobs, right-click the job, and then click Properties.In the Owner list, select a login.
You must be a system administrator to change the owner of a job.