Script to Check List of SQL Jobs, their Status and owner

Problem:   SQL Script to get list of SQL Jobs running under SQL Agent, their Status and Job Owner Details

 

Solution:   Below is the script.

 

SQL Script:

_______________________________________

USE msdb

SELECT j.[name] AS ‘JobName’,
Enabled = CASE
WHEN j.enabled = 0 THEN ‘No’
ELSE ‘Yes’
END,
l.[name] AS ‘OwnerName’
FROM msdb.dbo.sysjobs j
INNER JOIN master.dbo.syslogins l
ON j.owner_sid = l.sid
ORDER BY j.[name]

go

_______________________________________

 

ScreenShot:

 

jobdetails

Installing SQL Server 2008 R2 Cluster

Microsoft Cluster

A Microsoft cluster is a high-availability system designed to keep the database up as much as possible. It does this by resuming processing on the secondary server in the event of a failure on the principal server. Since the data resides on a shared disk, a Microsoft cluster will not survive a data corruption.  This technology is high availability, not disaster recovery. All data resides in the same data center and on the same disk. A cluster can be used with other technologies, such as database mirroring.

Below is the SQL Cluster Architechture:

Architechture

 

 

 

In order to Install SQL Server Cluster, we need to have Windows cluster ready as a base with some other pre-requisites as well. [ Installing Windows Cluster is out of scope of this article.]

1- Windows Cluster

2- DTC

3- SQL

Below IPs needs to be reserved well in advance to save yourself some time. IP Addresses and Hostnames can be of your choice.

Prerequisites

 

We then need to setup 2 Network Interfaces to be used in production and the other as a heartbeat.

Once the setup is ready to install SQL Server Cluster. Please follow the below steps to configure 2 Node Active/Passive Cluster[SQL Server 2008 R2].

 

Installation:

Steps to be performed on Node 1.

  1. Run setup.exe from the installation media to launch SQL Server Installation Center. Click on the Installationlink on the left-hand side1
  2. Click the New SQL Server failover cluster installation link. This will run the SQL Server 2008 R2 Setup wizard2

 

3. In the Setup Support Rules dialog box, validate that the checks return successful results and click Next.

3

4. In the Product Key dialog box, enter the product key and click Next.

4

5. In the License Terms dialog box, click the I accept the license terms check box and click Next.

5

6. In the Setup Support Rules dialog box, click Install. Validate that the checks return successful results. If the checks returned a few warnings, make sure you fix them before proceeding with the installation.

8

7. Select the features to install and click Next.

9

 

8. Configure the Network Name ( this is going to be the DB cluster name ) and instances

10

 

9. In the Disk Space Requirements dialog box, check that you have enough space on your local disks to install the SQL Server binaries and click Next.

11

10. Choose your Cluster Resource Group ( or leave to default )

12

 

11. In the Cluster Disk Selection dialog box, select the available disk groups that are on the cluster for SQL Server to use.

13

 

12. In the Cluster Network Configuration dialog box, enter the IP address and subnet mask.

14

 

13. In the Cluster Security Policy dialog box, accept the default value of Use service SIDs (recommended).

15

14. In the Server Configuration dialog box, enter the credentials that you will use for your SQL Server service accounts in the Service Accounts tab. Configure the Service accounts Users and Password as per best practices.

16

 

15.  Select the appropriate Authentication Mode. If you want to add the currently logged on user to be a part of the SQL Server administrators group, click the Add Current User button.

17

16. On the Data Directories tab, enter the path where your system and user database files will be created.

18

17. Install, But do not configure is the only option available as we haven’t installed the pre-requisites for Reporting Service.

19

 

18. Make Sure everything passes on the Cluster installation Rules

21

 

19. Review your install Summary and Click Install

22

 

20. Now wait for the installation to finish

23

 

21. In the Complete dialog box, click Close. Cluster Installation on Node 1 is finished.

24

 

 

Now, start installation on Node 2.

1. Run setup.exe and Click the Add node to a SQL Server failover cluster link

25

2.  Install Support rules and Make Sure all passes.  Few steps are similar to Node 1 Installation

26

27

28

 

30

 

 

31

 

3. You will see that setup has detected the current Single node cluster from above, click Next

 

32

 

4. Enter the same credentials and click Next.

 

33

 

5. Make sure everything is green.

 

 

35

 

6. In the Ready to Update dialog box, verify that all configurations are correct and click Install.

 

36

 

7. In the Complete dialog box, click Close. This concludes installing the passive node of a SQL Server 2008 R2 Failover Cluster

 

39

 

Verifying The Cluster

 

FailoverCluster

 

 

Hope this was helpful.

SQL Server Best Practices – Part 1

 

  • Use a dedicated server for your database [it will prevent security breaches and leaks. Manageability and Availability will be enhanced]

 

  • Install only required components. [It is good practice to install only needed features and by doing so it will reduce the attack surface and better resource utilization]

 

  • Do not install sample databases and sample codes on SQL servers in the production environment. [If needed, install the samples only in the development and test environments]

 

  • Windows Authentication mode is more secure than SQL Authentication. [Windows Authentication mode is the default and recommended authentication mode]

 

  • If there is still a need to use SQL Authentication – enforce strong password policy.

 

  • Disable the SA account and rename it. Do not use this account for SQL server management. [Before Disabling/renaming the SA account, verify that another account with administrative privileges exists in order to have access to the SQL Server.]

 

  • Keep minimum administrators as it can be.

 

  • Allocate different administrative accounts if there is more than one administrator. [This will set accountability]

 

  • Remove BUILDIN\Administrators group from the SQL Server Logins. [Because it gives SQL Server System Administrator rights to any account in the Windows Local Administrators group].

 

  • Change default SQL Server ports associated with the SQL Server installation to keep hackers from port scanning the server. [Default port (i.e 1433) is well known and is common target for hackers. Therefore it is recommended to change default port associated with the SQL Server installation]

 

  • Enable logging SQL Server login attempts (failed & successful). [The default mode is:Failed Logins Only. Thus, it is recommended to set the auditing mode to be Both Failed and Successful Logins.]

 

  • Hide SQL Server Instance from Broadcasting Information [The SQL Server Browser service enumerates SQL Server information on the network. In such way attackers can use SQL Server clients to browse the current infrastructure and retrieve a list of running SQL Server instances. Thus it is strongly recommended to hide SQL instances from being shown in the network]

 

 

  • Recent service packs and critical fixes should be installed for SQL Server and Windows. [Service Packs contain important fixes for a product and are tested even more rigorously than incremental releases like cumulative updates, so they’re pretty important to install. But like everything else, they may contain something that has unexpected side effects– so you need to test your application with the service pack before you change it in production.]