Interview Question - DB Mirroring vs Clustering

Database mirroring and Clustering – both can serve the purpose of high availability and data protection. However Database mirroring is at database level only. Clustering is at server level, all databases on the instance are covered. In case of clustering there is a single copy of your data and log files where in case of mirroring you have multiple copies of files – on primary and secondary server both,  which increases your chances of recovery in case of disaster. In case of mirroring you cannot mirror the system databases viz. master, msdb etc. For mirroring to work database recovery model has to be set to "full recovery". I would see mirroring as a sophisticated log shipping mechanism with automatic failover capability.

SQL server - version question

Most of the people who deal with SQL Server know how to get the current SQL Server version (build number or information in other words). Let me give the command once again

select @@version

The output would look something like,

----------------------------------------------------------------- ---------------------------------------------------------------
2008-11-23 19:13:25.29 Server Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

Now, looking at the build number or the version details “10.0.1600.22” you really cannot make out at what patch level or service pack level your SQL Server instance is at. So, here is a handy web page by SQL Security.

http://www.sqlsecurity.com/FAQs/SQLServerVersionDatabase/tabid/63/Default.aspx

It lists all the version/build number with respective patch number along the side. In this case “10.0.1600.22” refers to SQL Server version 10 i.e. 2008 RTM. Once you apply some service pack this number will increase to something else and sometime its listed in either KB article or in this SQL Security website.

This should help you on - how to figure out the patch level of your SQL Server instance without asking anyone.

How to connect SQL SERVER instance ?

This article is for those who are new to SQL Server or for those who are new to windows platform.

Quiet a lot of people go to Google and search for these key words “SQL Server”+FREE. Top in the results listed is the Microsoft SQL Server Express edition. Yes- Microsoft SQL Server Express edition is free to user and it comes with certain limitations please read the documentation before you start using it. Curious to learn the SQL Server quickly most of us download and install it. Next is how do I connect and start writing and testing some queries, as it does not come with standard client connectivity GUI tools

There are two methods of user authentication in SQL Server unlike most of the RDBMS products like Oracle, MySQL, Sybase etc. The two authentication methods are know are

Window Authentication and

SQL Server Authentication

In case of “Window Authentication” your connection request is authenticated against the windows operating system. if you are using SQL Server Management studio or another other GUI’s tools and if you are the local admin of your system (which mostly we are all) you need to make following selections in your connection screen >> SQL Server Name and Authentication = “Window Authentication” (or integrated authentication in some cases) no need of password anywhere.

If you are using OSQL or SQLCMD – the command line client utility to manage SQL Server, which is mostly installed when you install SQL Server (any edition). You just need to type following command at command prompt

http://www.paulkovacs.com/InstallSQLServer2005Express/image005.jpg

{Third party GUI Tools}

http://www.sqlfindandreplace.com/images/SQL-Find-And-Replace-Net-Database-Management-Utility-DOC-002.gif

osql -E -S computer_name\instance_name

Where computer_name = your desktop/pc name, no need of keying in any PASSWORD as you have specified –E option i.e. to use window authentication. If you (logged in user) are a valid windows user and have a local admin privileges you will be successfully connecting to SQL Server instance.

In case of “SQL Server Authentication” your connection request is authenticated by the SQL Server itself. In this case you need to pass one more value i.e. password along with user name and also you need to opt for “SQL Server authentication” if there is any in you GUI tool. In this case the OSQL command would be,

osql -S computer_name\instance_name –U <SQL_userId> -P <respective password>

{command line utils}

http://decipherinfosys.files.wordpress.com/2007/12/sqlcmd_1.jpg