Home » SQL Server

Category Archives: SQL Server

How to install SQL Server Failover Cluster from SQL Server 2008 onwards ?


Steps to install SQL Server Failover Cluster from SQL Server 2008 onwards :-

1) Open SQL Server Installation Center > Go to Installation from Left > Click on New SQL Server Failover installation from right

2) Click RUN to start setup

3) Click ok after validation check. In case of any failure, you need to clear that first

4) Click INSTALL, to install setup files

5) Click Next to cont.

6) Enter Product Key & click Next

7) Accept the EULA & Click Next

8) Select Features, you want to install & Click Next

9) Specify SQL Server instance name & Click Next

10) Check Disk space requirement & Click Next

11) Mention SQL Server Cluster Group Name & Click Next

12) Select Cluster Disk, You want to use for installation & Click Next

13) Provide SQL Server Cluster VIP & Click Next

14) Check your security policy & Click Next

15) Specify Service accounts & Click Next

16) Add User to work as SYSADMIN & Click Next

17) Set error reporting options & click Next

18) Click Next (In case of any failure, you need to fix that)

19) Check all configurations & Click Next

20) Installation Started

21) Installation Completed successfully

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

SQL Server 2000 || Disable sa login


Question : How to disable sa user in SQL Server 2000?

Reason : Due to Security requirement of audit, We have to disable sa user for all SQL instances in environment having SQL 2000 \2005\2008 & 2008 R2.

Answer : We can disable sa user from SQL Server 2005 onwards but this is not possible for SQL Server 2000.

We have below system store procedure available in SQL Server 2000 but below is applicable for windows login only.

EXEC sp_denylogin ‘exampleuser’

EXEC sp_revokelogin ‘exampleuser’

The only solution, We can do

1. Change the password to very strong

2. Clear Server Roles

3. Clear all Database Access

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

Antivirus Exclusion Policy for SQL Server


Anti-virus & SQL Server on one system together are friends not enemies, if configured properly.

Anti-virus are very useful programs from security, audit & venerability detection & removal point of view. But if team managing anti-virus server did not configure anti-virus policies properly then your SQL Server is going to face the problem.

Here, we will discuss the file types that must be in exclusion list of anti-virus scanning policy. In other words, Let anti-virus programs deal with what they do best, and let SQL Server handle what it does best and avoid, at all possible costs, any interaction between the two

1. Binaries: Or the the paths to the actual executable for any of your running SQL Server Services (MSSQL, SQL Server Agent, SSAS, etc). Typically these are found, by default, in the C:\Program Files\Microsoft SQL Server folder – though this could easily be a different path on many production machines. (And, note, you’ll likely want to make sure that C:\Program Files (x86)\Microsoft SQL Server is included in any exclusions as well on x64 machines).

2. SQL Server Error Logs : Not your database log files, but the text files that SQL Server uses to keep its own ‘event logs’ running or up-to-date. (Which, in turn is also different than Windows’ system event logs as well.) By default the path to these files is, in turn, covered in the paths outlined above – or it’s part of the ‘program files’ data associated with your binaries – though you CAN move the location of these logs if desired (as an advanced operation via the startup parameters).)

3. Data And Log Files: Your actual .mdf, .ndf, and .ldf files – or the locations of your data files and log files. (Which you’ll want to make sure get excluded from anything that anti-virus monitors – otherwise creation of new databases, file-growth operations, and other normal ‘stuff’ can/will get blocked by anti-virus operations – which would be fatal in many cases.)

4. Backups: Yes, the path to any of your backups – or backup locations is also something you’ll want to make sure that anti-virus doesn’t monitor.

5. Others: Any other files related to SQL server & for its proper working. Like .TUF, .SS, .TRC etc.

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

Wishing You a Very Happy Holi


Steps for Windows Cluster Installation on Windows Server 2008


Here, You have steps with screenshot to install 2 Nodes windows cluster on Windows Server 2008.

Step 1: Validate Cluster Configuration

a) Open Cluster Administrator & click on “Validate a Configuration”

b) Wizard for cluster validation appears, click Next

c) Add Node to create cluster

d) Select Type of test want to perform

e) Confirmation Wizard for Test

f) Test Completed, You can view report by clicking on View Report button

Step 2 : Create Cluster

a) Open Cluster Administrator and click on “Create a Cluster”

Note : You cannot create cluster until test validation completed successfully.

b) Create Cluster Wizard appears

c) Add Nodes to create cluster

d) Specify Windows Cluster Name & windows cluster VIP

e) Confirmation Wizard, check details & click Next

f) Cluster Installation Start

g) Cluster Created successfully, You can view report bu clicking on “View Report” button

Step 3: Add Quorum

a) Open Cluster Administrator, Right click on Storage & select Add a disk

b) Select the disk available in system

c) Disk successfully added in cluster storage

d) Select “Configure Cluster Quorum Settings” to create Quorum

e) Select Quorum type

f) Select the available cluster disk

g) Check confirmation & click Next

h) Configuration Started

i) Cluster Quorum Configuration completed, you can view report by clicking on “View Report” button

j) Cluster installation completed

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg(http://mssqlfun.com/)

ERROR || The query processor is unable to produce a plan because the index ‘IND_TABLE’ on table or view ‘Table’ is disabled.


Table with clustered index is totally depended on index accessibility.

ERROR : The query processor is unable to produce a plan because the index ‘IND_TABLE’ on table or view ‘Table’ is disabled.

REASON : We find that some disable the cluster index due to which issue occur. Clustered index physically sort & save data in pages. When clustered index is disable, DB engine is not able to access data although data is available with table.

SCREENSHOT :

Note :

· There is no option to ENABLE the Index. You have to REBUILD or DROP & RECREATE it.

· This is not the case with non-clustered index.

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg(http://mssqlfun.com/)

System Configuration Check – Without Starting SQL Server Installation


Question : Can we do system configuration check with starting SQL Server installation? One of my friend need to submit it to get installation approval. This is to ensure that no configuration issue occur at time of installation.

Answer : Yes, We can do it.

Step 1: Run Setup.exe

Step 2 : Go to Tool & click “System Configuration Checker”

Step 3 : You are done, You got configuration report. You can ignore the warnings But for healthy system start only on 100% green.

Support : It support SQL Server 2008/2008 R2/2012.

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

Cumulative Update – 8 for SQL Server 2012 Service Pack 1 Is Now Available !


The 8th cumulative update release for SQL Server 2012 Service Pack 1 is now available. Cumulative Update 8 contains all the hotfixes released since the initial release of SQL Server 2012 SP1.

Those who are facing severe issues with their environment, they can plan to test CU8 in test environment & then move to Production after satisfactory results.

To other, I suggest to wait for SP2 final release to deploy on your production environment, to have consolidate build.

KB Article For CU8 of SQL Server 2012 SP1

§ CU#8 KB Article: http://support.microsoft.com/kb/2917531

Previous Cumulative Update KB Articles of SQL Server 2012 SP1

§ CU#7 KB Article: http://support.microsoft.com/kb/2894115

§ CU#6 KB Article: http://support.microsoft.com/kb/2874879

§ CU#5 KB Article: http://support.microsoft.com/kb/2861107

§ CU#4 KB Article: http://support.microsoft.com/kb/2833645

§ CU#3 KB Article: http://support.microsoft.com/kb/2812412

§ CU#2 KB Article: http://support.microsoft.com/kb/2790947

§ CU#1 KB Article: http://support.microsoft.com/kb/2765331

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

Cumulative Update – 15 for SQL Server 2008 Service Pack 3 Is Now Available !


The 15th cumulative update release for SQL Server 2008 Service Pack 3 is now available for download at the Microsoft Support site. Cumulative Update 15 contains all the hotfixes released since the initial release of SQL Server 2008 SP3.

Those who are facing severe issues with their environment, they can plan to test CU15 in test environment & then move to Production after satisfactory results.

To other, I suggest to wait for SP4 final release to deploy on your production environment, to have consolidate build.

KB Article For CU15 of SQL Server 2008 SP3

· CU#15 KB Article: http://support.microsoft.com/kb/2923520

Previous Cumulative Update KB Articles:

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

2013 with You & SQL Server


The WordPress.com stats helper monkeys prepared a 2013 annual report for this blog.

Here’s an excerpt:

The concert hall at the Sydney Opera House holds 2,700 people. This blog was viewed about 41,000 times in 2013. If it were a concert at Sydney Opera House, it would take about 15 sold-out performances for that many people to see it.

Click here to see the complete report.

Follow

Get every new post delivered to your Inbox.

Join 133 other followers