How to hide databases in SQL Server Management Studio from unauthorised users?

Question:

If a user is not authorized to see a database can I exclude that database from even appearing in SQL Server Management Studio for that specified user or group?

Answer:

Until SQL Server version 2000 it was not possible to hide the database information from being displayed on SQL Server Enterprise Manager. In SQL Server 2005 it is possible with a new server side role that has been created. VIEW ANY DATABASE permission is a new, server-level permission. A login that is granted with this permission can see metadata that describes all databases, regardless of whether the login owns or can actually use a particular database. Please note By default, the VIEW ANY DATABASE permission is granted to the public role. Therefore, by default, every user that connects to an instance of SQL Server 2005 can see all databases in the instance.

An interesting point to note is that being a member of db_owner is not sufficient to see the database if “view any database” was denied. In this regard SQL Server Development team are working on new features in order to make this more affective in future releases.

How to disable database mirroring for a particular database?

Database mirroring is a primarily software solution for increasing database availability. Mirroring is implemented on a per-database basis and works only with databases that use the full recovery model.  There may be a requirement to disable database mirroring for a database that is no longer required.

To achieve this, delete the ENDPOINT for the database and there is an additional step needed to remove that particular database from Database Mirroring. To perform that execute ALTER DATABASE SET PARTNER OFF. The ::= in ALTER DATABASE statement controls mirroring for a database. Values specified with the database mirroring options apply to both copies of the database and to the database mirroring session as a whole. Only one is permitted per ALTER DATABASE statement.

Is there a difference between fill factor 0 and 100

When creating indexes in SQL Server, if you do not specify an index fill factor, the fill factor will be 0 (effectively the same as 100%). You can specify an index’s fill factor percentage in a number of different ways. Some of these methods include: The Create Index statement, DBCC DBReindex and Rebuild.
ALTER INDEX ALL ON dbo.Data
REBUILD WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON)
GO
DBCC DBREINDEX (Data, ”, 0)
A few caveats:

• In the create statement you cannot specify 0 for fill factor.
  
• In the DBCC DBReindex statement, if you specify the fill factor as zero, this means Reindex the index with the existing index, not 100.
  
• You cannot specify 0 for the fill factor in the Rebuild statement.
  

SQL Server Service Accounts

SQL Server Service Accounts

While installation of SQL Server, it will install different services like

1. SQL Server

2. SQL Server Agent

3. Integration Services

4. Analysis Services

5. Browser Service Etc.,

If any service wants to interact with domain resources, network resources like file shares, linked servers, we need use domain account. Many server-to-server activities can be performed only with a domain account.

Below are the steps to create domain account and required privileges required for domain account.

1. Domain account was created by windows administrator

2. Below are rights and privileges required for domain account to access SQL Server.

a. Add domain account to Logon as Service

b. Add domain account to Replace a process-level token

c. Add domain account to Bypass traverse checking

d. Add domain account to Adjust memory quotas for a process

e. Add account to security of MSSQL Install folder and provide full control.

\MSSQL\

f. Create domain account with password never expiry. If not please change password based on the age of the password.

No Special privileges are required for Domain account.

Post Configuration of SQL Server -

1. Disable the xp_cmdshell

2. Hide the Instance from SQL Server Configuration Manager

3. Change the SQL Server Port Number from default to customize

4. Add service account to Lock Pages in Memory

5. Disable the guest account

6. Disable the Built In Administrator (for older versions)

7. Assign Min and Max Memory as per the memory in your machine.

8. Create multiple tempdb files based on the number of processors.

9. Follow the Nomenclature for DBA jobs

10. Add DBA group into Administrator

11. Enable TCP IP / Named Pipes from SQL Server configuration Manager.

12. Allow Remote Connections.

13. Enable DAC

14. Make sure alerting in place etc.,

How to Manage SQL Server Failover Cluster using Command Line an Overview

How to list all cluster nodes of SQL Server Failover Cluster Using Command Line (CMD)

Open Command Line and Type “cluster node” and press enter to list all the node of SQL Server Failover Cluster as shown in the snippet below. This command will list the Nodes Names, Node ID and Status of each node.

How to list all Cluster Groups of SQL Server Failover Cluster Using Command Line (CMD)

Open Command Line and Type “cluster group” and press enter to list all the available resource groups of SQL Server Failover Cluster as shown in the snippet below. This command will list the Group, Node and Status of each group.

How to list all Cluster Network of SQL Server Failover Cluster Using Command Line (CMD)

Open Command Line and Type “cluster network” and press enter to list all the available networks of SQL Server Failover Cluster as shown in the snippet below. This command will list the Network Name and Status of each network.

How to list all Cluster Resources of SQL Server Failover Cluster Using Command Line (CMD)

Open Command Line and Type “cluster resource” and press enter to list all the available networks of SQL Server Failover Cluster as shown in the snippet below. This command will list all the available resources.

How to Patch SQL Server 2008 Failover Cluster??…

This would be the last part of this Cluster Installation Series! In the Previous part, I’ve shown you how to Configure an Active-Passive SQL Server 20008  Failover Cluster. So at this moment our SQL Version will be at RTM, let’s apply Service pack 2 over our RTM.

Let’s begin! FYI I’ve downloaded SQL Server 2008 Service Pack 2 from microsoft and copied the exe files onto both the Nodes of our Cluster.

Procedure:

To be very simple and precise, We’ve to Install SP2 on our Passive Node(I’ve chosen Node2 as current Passive) first. Once completed, we’ve to manually failover SQL Services to Node2(Currently SQL is running on Node1) and apply patch on Node1.

This model has been introduced starting SQL 2008. Earlier SQL patching was cluster aware where it used to automatically patch all the nodes in your Cluster. But this leads to more down time depending on number of Nodes involved in your Cluster. Let us say..we’ve 8 Nodes(A-A-P) with many SQL Instances, in this case SQL Server will not be available untill all the Nodes are successfully being patched. Starting 2008 MSFT avoided this, basically making High Availability Solution more highly available

Same logic applies to Active-Active Cluster as well. You’ve to Manually failover your SQL Services to other node(so at this point 2 instances of SQL will be running on single node) and you’ve to apply patch to idle node. Once patching is completed successfully on your idle node, you’ve to manually failover both the Instances to this node and patch the other node. Hope am not confusing!!

Let’s begin! I’ve chosen to patch my second node first. So I’ll make sure that SQL and all other dependencies are running on Node1. You can see below in the screenshot that Node1 is owning every SQL resource.

Very Important:  You should remove Node 2 as a possible owner through out the patching process of Node2, making sure that SQL is not going(allowed) to failover to Node2 for any reason throughout this process.

Also,Just to double check open your SQL Server Configuration manager on your Node2 and you should be seeing SQL Server and SQL Server Agent as Offline as shown below. you might be seeing Browser and FTS services as running since they are not clustered (SQL Cluster Patch installation will take care of those services as needed).

Last thing to do is open your SSMS and note your Current build and Version just by issuing @@Version. For me the current Output is

“Microsoft SQL Server 2008 (RTM) – 10.0.1600.22 (Intel X86)   Jul  9 2008 14:43:34   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Evaluation Edition on Windows NT 6.0 <X86> (Build 6001: Service Pack 1) (VM) “

Close your SSMS(if any) on Node2 and double click on the SQL Server 2008 SP2 executable, you should be welcomed with the below screen

Next ->Accept the License Terms ->Next and you’ll get the below screen where you’ve to choose the features which you want to apply patch to.

Next and It’ll perform a basic Check for any Files in Use which might prevent successful installation of SP2 as shown below.

Click Next -> Update  as shown below…That’s it:)

It will take few minutes to complete and you’ll get below screen.

Click Next->Close. Once Done, go to your Failover cluster Manager and add your Node2 back as a possible owner and Failover SQL Services to Node2(At this Point If you open your SQL Server Configuration manager on your Node1, SQL Server Service and SQL Agent Service should be Offline) and repeat the same procedure what we did for Node2, on Node1 to patch Node1 as well.

Note: Don’t forget to remove Node1 as possible owner now through out the process, making sure that SQL is not going(allowed) to failover to Node1 for any reason through out this process.

Once you are done with patching both the nodes successfully, add node1 back as possible Owner and now open your SSMS and issue @@Version and let’s compare with what we had prior to Installation.

As you see in the above screenshot we’ve upgraded our SQL from “Microsoft SQL Server 2008 (RTM) – 10.0.1600.22 (Intel X86)   Jul  9 2008 14:43:34″ to “Microsoft SQL Server 2008 (SP2) – 10.0.4000.0 (Intel X86)   Sep 16 2010 20:09:22″.

Perfect…That’s all for the day!

Basically we’ve covered everything right from scratch(Installing Virtual machines, Installing Windows Server 2008 Enterprise OS, Configure your DNS and Domain controller, Adding new Physical Computers to Domain, Creating your own ISCSI SAN, configuring NIC’s…Clustering Windows and creating SQL Server Cluster and finally how to Patch your SQL Servers which are configured as clusters).

Hope this series will be useful to someone who is new to SQL Failover Cluster setups and also hoping you enjoyed this entire series of SQL 2008 Cluster Installation as i did:)

How to Rebuild Master Database?? AKA Rebuilding SQL Server 2008R2.

Have you ever been in a situation where you lost the complete Drive where your System Databases are residing and even SAN Admins were not able to bring the Drive back?? Fortunately I’ve never been in that situation:)  What would be our available options if that disaster strikes?? In this scenario restoring System Databases won’t work because SQL Server Service itself is completely down and your System Databases mdf and ldf files are gone!!..So what should we do?? REBUILD YOUR MASTER. Rebuilding Master will actually create brand new System databases from scratch as if you just Installed your SQL Server.  There’s a misconception that we need the Actual SQL Server media and it’s mandatory to rebuild your Master. Those days are gone..That was the case with SQL Server 2005 and earlier. Starting SQL Server 2008 it’ll rebuild based upon the TEMPLATES which it creates in your BINN Directory. (This is the one of the main Reasons,you should place your SQL Binaries and actual System Databases on completely separate Physical Drives.  Let me show you what I’m talking about…..You can see the templates folder which SQL Server created while initial Installation of this Instance on my machine.

These are not the actual system database files….I repeat, these are Just templates which SQL Server will create behind the scenes during Installation Process. My actual System Databases are located in DATA folder as shown below

Note: On My laptop I Installed every thing Just on C Drive. You should never ever do this in a real environment. There’s no single valid/good reason to do so…

Once rebuild process is complete, You’ve to Restore all the User Databases from your Backups and Restore Master and Master Databases in case if you want to get back all your Logins, Jobs, Server objects etc….Yes! I agree that sounds like a very painful process….But if you’ve all your recent healthy backups and few other dependent objects…you are the champion:) So..if you don’t have a proper Backup/Restore Strategy in place…you are gone!!..You might even loose your Job for this single most important valid reason. In this post, let’s try to rebuild our entire SQL Server/Master Database.

I’ll simulate scenario by Shutting down SQL Server Services on this Instance and physically deleting the mdf and ldf files of system databases and let’s see how it looks and what are the errors you’ll be getting in this case.

Step1: Now my Data folder looks as shown below

Step 2: I tried to Start SQL Server Service from our Configuration Manager and the error messages which i’m getting are (see below Screenshots).

In the Event Viewer I’m seeing the below

Okay…! In this case we know that the issue is not with Invalid Startup Option since we deliberately deleted our Master.mdf file.

Step 3: Let’s see how to rebuild using SETUP.EXE 

As i already mentioned above we no longer need installation media to rebuild SQL Server(you can use it as well if you want to). All you need to know is where did you choose the “Setup Bootstrap/program files” while Installing your SQL Server initially. ( standardizing all these Drives, locations and paths across the Servers in your organization greatly reduces the pain to maintaining all these important details individually at Server level). In my case it’s “C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release” as you can see below

Step 4: Open Command Prompt(Run as Admin) and navigate to this folder where you can find SETUP.EXE and we can do the “QUIET Installation” passing the required parameters. In My case the Syntax was

setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=DR /SQLSYSADMINACCOUNTS=”sreekanthPC\sreekanth” /SAPWD=”Pa$$w0rd”

If you want to explore all other options and parameters available to us….you can issue “setup.exe/?”  for help from windows as shown below.

In the above syntax every switch is Space Seperated. Here we are using Quiet Mode and the action is Rebuild master Database, Instance name is DR(For Default Instance: INSTANCENAME will be “MSSQLSERVER”), adding myself as a Local Admin and providing sa password(since mine is a mixed mode).

Very Very Important: Actually It failed for me and got below error Messages!!!! The error Messages were “The following is an exception stack listing the exceptions in outermost to innermost order  Inner exceptions are being indented  Exception type: Microsoft.SqlServer.Setup.Chainer.Workflow.NoopWorkflowException Message: The state of your SQL Server installation was not changed after the setup execution. Please review the summary.txt logs for further details.”  I’ve no idea what it is trying to convey here…Looks like a Bug( may be may be not) to me in SQL Server 2008R2( I’m using 2008R2 for this demo).
Work Around: I’ve selected “C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2\Setup.exe” instead of “C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release\setup.exe“

Even if this doesn’t work for you, please try to make use of setup.exe from Installation media. This should work. I hope this strange behavior has been fixed in DENALI.

Sooo…..the actual Syntax and Path which worked for my case is as shown below

it took around 7-10 minutes in my case and got a command prompt without any errors as you can see in the above screenshot

FYI Failed path(in my case) as per Microsoft documentation  is as shown below

Please keep this Bug in your mind and don’t just PANIC if you encounter the same error message.

Well, with all this hard work…what did we achieved?? Did SQL Server Instance “DR” got rebuilt??  Yesssssssssss as you can see below, all the System database files are back.

Also..I was able to Start SQL Services from the Configuration manager and as you can see below….everything appears to be brand new…. Tadaaaaaaaaaaaaaaaaaaaaaaa!! We did it guys

 

Now it’t time to (patch if applicable) and  restore your Databases from your healthy Backups accordingly. You can see how to restore System Databases here.

That’s it Guys!!! You should be aware of this process and these challenges and work arounds as a production DBA. I would recommend to test this in your lab at least once or twice before actual disaster strikes your Production Servers…(I hope you never need this….But you should not be surprised when it hits). Hope this helps. Cheers!

How to Add a New Disk/Drive to SQL Server Failover Cluster?

Below are the screenshots of my current SQL Server Failover Cluster which I’m going to add a new Drive.

As you can see I’ve 6 SAN Drives dedicated to this cluster. Let’s see how to add a new drive with a name aaaaahhhh……say “SQLBacks2″ to our SQL Server.  First thing is your SAN Admin should create/present a new Drive for you to be able to add to our cluster.

Okay, let me wear my SAN hat. Now am a SAN Admin and I’m going to create a new SAN Drive for my Windows/SQL team.

Creating a SAN Drive:

Note: If you didn’t followed my earlier Clustering Series, I use Starwind for all my SAN Stuff. Just refer to my previous posts in clustering series to understand this tool more in depth.

step1: Add a new Target.

Step 2:

Step3:

Step 4: placing and sizing the Drive( I chose just 1 GB for this example)

Step 5: Making it a ISCSI aware disk(Mandatory for Clusters)

Okay..now Am done with SAN guy role and now I’m wearing  a Windows Admin Hat to initialize the new disk which my SAN Admin just created for me from my Nodes.

Wearing a Windows Admin hat:

Went to ISCSI Initiator and all I have to do is Initiate this new drive as shown below. you can see our new drive as Inactive as of now in the below screenshot.

Once you click ok, you will be seeing this drive as connected in your ISCSI Initiator, but still this is not available for Windows. Now we’ve to go to Server manager->Storage and Bring it Online->Initialize->Create new Simple Volume(Format the Drive) so that you can see it physically in your My Computer. (See below Screenshot, once I did created the drive, I selected K$ as my volume)

Once, this drive is logged on both the Nodes  basically now we’ve to add this drive to our Windows Cluster using Fail Over Cluster manager(am on Node1) as shown below.

It will search for all the disks which are suitable for clustering for a while…After few seconds, You’ll see below.

Once added, you can see this Drive has been added to our Cluster as Available Storage(Note: At this Point SQL Server Service is not yet ready using this Drive).

Now…Right Click on your SQL Server and select “Add Storage” as shown below.

So…Are we done yet? Nopee…….Here comes the most Important Part and the piece which I’ve seen people missing a lot while adding a new Drive to existing SQL Server Clusters. So what are we missing here? The Answer is SQL Server Dependency. Didn’t quite understood what I’m saying? Well, see the below Screenshot where Drive 7(new drive which we just added) is floating in air with no relation to SQL Server Service.

How to verify that the Drive has not yet added successfully 100% to our SQL Server Instance from SSMS? It’s very simple, Just try to access this Drive from your SSMS. In this example I’m just trying to take a backup of one of my Databases and looking for $K to place the backup, which I can’t see(But I can see it from My Computer) unless I add the dependency Manually as you can see below.

So…What to do now?? Just make an “AND” Dependency for your New Drive. But how??

Wearing SQL Admin hat now! Well, this could be even a Windows Admin depending on your company!

Navigate to your SQL Server and Right Click and select properties and go to dependencies tab as shown below.

As you can see, there is no Drive 7(DriveK) as dependency. Now you have to add the new drive as “AND” dependent as shown below and you are all set to go

Now see below Dependency report, where Drive 7 is no longer floating in the air

and of course a happy SSMS as you would expect to locate the Drive for your Instance!

Now you are all set guys…Just try to move the Service to other node(s) and double check that everything is working as expected from all the nodes. Hope this helps! Cheers…