Understanding SQL Server related Windows Registries

SQL Server related information from Windows registries

The Windows Registry is a hierarchical database that stores configuration settings and options on Microsoft Windows operating systems. It contains settings for low-level operating system components and for applications running on the platform that have opted to use the registry. The kernel, device drivers, services, SAM, user interface and third party applications can all make use of the registry. The registry also provides a means to access counters for profiling system performance.

See Wikipedia Article Windows Registry.

To View SQL Server related registries.

Go to Run and type regedit

HKLU=>Software=>Microsoft=>Microsoft SQL Server

To find valuable information about edition, version, patch label etc. Traverse as below pic

 

To Change  Default authentication mode 1-window authentication 2-SQL server Authentication

Edit Login Mode registry value.

Value= 1 for Window Authentication

Value=2 for SQL server Authentication

Restart SQL server and agent services your default SQL server authentication mode has been changed. You can change for each instance of SQL server.

Note: Serious problems might occur if you modify the registry incorrectly. So do not modify any registry if you do not understand it.

However you can achieve same using xp_instance_regwrite stored procedure to edit registries.as

The below query is used to change authentication mode from mixed to Windows only.

EXEC xp_instance_regwrite
N'HKEY_LOCAL_MACHINE', 
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'LoginMode', 
REG_DWORD, 
1;

The below query is used to change authentication mode from windows only to Mixed back.

EXEC xp_instance_regwrite 
N'HKEY_LOCAL_MACHINE', 
N'Software\Microsoft\MSSQLServer\MSSQLServer', 
N'LoginMode', 
REG_DWORD,
2;

Each time you execute query,restart the server and check.

To find installed instances on current machine, traverse as below pic

9 points before backing up SQL Server databases

Backing up SQL Server Databases

 Backing up your SQL Server database is essential for protecting your data. The word Backup refers to “Copies the data or log records from a SQL Server database or its transaction log to a backup device, such as a disk, to create a data backup or log backup.”

SQL server backup.

You should consider some below points also when making planning for database backups.

1.   Firstly set your database backup plan, In plan do not only include backup databases but also include backups of other things like reporting services, analysis services ,integration services and other third party tools. You should back up complete SQL server environment.you can use third party tools to achieve the same.

2.   After that you should include your physical backup storage location protection policy, since this storage has all data of your databases. It may be very dangerous if it is getting in wrong hands, because the can easily restore this and access your confidential data like Credit card nos. , passwords etc. Also it will be better policy if you restricts from being copied your database backups to another location. However there are some third party database backup tools that allow backup encryption.  

3.   Store database backup another location [some other drive] from database location.so that if in any chance to corruption of files on database drives your back will be secure.

4.   Do not write multiple backups in one physical file so create one backup for one database so that if a backup file for single database has been corrupted it does not affect other backups.

5.   Do not allow multiple users to access on creating backups but if you allow then monitor who is creating backups and where they are being created.

6.   Also backup system databases at least master and msdb since the master database stores security information as well as metadata about the other databases. Msdb stores information about jobs, operators, and alerts etc.

7.   Monitor backup failure, backup size and backup time regularly, if you scheduled backup using Job Scheduling then create an alert to your mail id if job fails.

8.   Testing the entire restore process to verify backup periodically.

9.    Remove older backups time to time to free disk space.

To create and automate backup using SSMS, follow below steps

1.       Go to databases in object explorer and select your database to backup.

        

    

2.       Right click on database =>Backups

3.       In destination, you must select backup device that can be any drive or tape.

4.       Go to options

5.       In options you can set over all existing back up options to overwrite however ignore this rule because if backup corrupted you lost all of backups.

Choose Verify backup when finished.

You also can select Backup compression option from here.

6.       Click ok.The backup file is created on your defined destination.

7.       You can also schedule this task using Script

8.       Set all option as your requirement in job scheduling

9.       Test your job by running once. If it successfully runs, you have successfully automated your backup.

SQL Server Database Backup Resources.
Backup SQL Server

SQL Server Maintenance Solution’s stored procedure for backing up databases

A White paper on top SQL server Backup mistakes

Red gate SQL Backup Pro

NetVault LiteSpeed for SQL Server

How to find SQL Server Version,Edition,Server Name?

How to find sql server version,edition,Server Name?

SQL Server provides a System Defined function SERVERPROPERTY(propertyname) .

By using this function you can find a number of things

Property Name	Description	syntax
Edition	Return SQL Server edition installed on machine.	select ServerProperty('edition')
EditionID	return Edition ID	select ServerProperty('editionid')
InstanceName	Return instance name if it is not default.In case of default return Null.	select ServerProperty('InstanceName')
ProductVersion	return Product version	select ServerProperty('ProductVersion')
BuildClrVersion	return version of the .NET framework Common Language Runtime (CLR)	select ServerProperty('BuildClrVersion')
EngineEdition	return 1 = Desktop 2 = Standard 3 = Enterprise 4 = Express 5 = SQL Azure	select ServerProperty('EngineEdition')
IsClustered	Server instance is configured in a failover cluster. 1 = Clustered. 0 = Not Clustered. NULL = Input is not valid, or an error.	select ServerProperty('IsClustered')
MachineName	Return machine name	select ServerProperty('MachineName')
ResourceLastUpdateDateTime	Returns the date and time that the Resource database was last updated	select ServerProperty('ResourceLastUpdateDateTime')
ProductLevel	Returns Level of the version of SQL Server instance 'RTM' = Original release version 'SPn' = Service pack version 'CTP', = Community Technology Preview version	select ServerProperty('ProductLevel')

SQL Server FAQ on COMPUTE,STUFF,REPLACE,ISNULL,COLLEASE and temporary Tables

SQL Server FAQ 2:

1.      What are COMPUTE and COMPUTE BY  in SQL Server

COMPUTE can be used to generate an addition summary column at the end of result set followed by aggregate functions like SUM, AVG, COUNT, MAX, MIN, STDEV, STDEVP, VAR, and VARP.

Syntax:

COMPUTE    { { AVG | COUNT | MAX | MIN | STDEV | STDEVP | VAR | VARP | SUM }  ( expression ) } [ ,...n ]
[ BY expression [ ,...n ] ]  

     Example:

select a.cust_id,a.amount from dbo.tblPaymentDetails a where cust_id='1751'
compute SUM(a.amount).

    

     COMPUTE BY create groups of result set based on Column in BY clause

select a.cust_id,a.amount from dbo.tblPaymentDetails a order by cust_id  compute SUM(a.amount) by a.cust_id

2.      Where vs. Having clause

Where is used with search condition upon rows whereas having is used with search condition for group or aggregate.

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name HAVING searchcondition;

Having can be used without group by but your query should have aggregate functions. You cannot be able to select any column without aggregation function

SELECT aggregate_function(column_name) FROM tablename HAVING  aggregate_function(column_name)=value

Example:

SELECT SUM(qty)FROM dbo.Orders HAVING SUM(salesprice) > 10
SELECT  * FROM dbo.Orders Where salesprice > 10

3.      STUFF vs. REPLACE

Replace function replace all occurrences of a specified string value with another string value.

REPLACE ( string_expression , string_pattern , string_replacement )

Example:

SELECT  REPLACE('queryingsql','q','A')

Result:  AueryingsAl.

q is replaced by A in whole expression for each occurrence. Now if you want to replace a part of string expression. then you need to use STUFF.

STUFF ( character_expression , start , length , replaceWith_expression )

STUFF deletes all character (specified in length parameter) from specified start position and insert replaceWith_expression string.

SELECT STUFF('queryingsql',9, 5,' SQL SERVER')

Result:  querying SQL SERVER

And on other hand it is length and location based so it not replaces all occurrence of specified pattern.

4.      Local Temporary table vs. Global Temporary table

Local Temporary table: It is created with single ‘#’ as prefix of table name and is available for the connection for which it was created. It is automatically dropped when this connection closed or user drop explicitly by using drop query.

CREATE TABLE #temptest
( ID int NOT Null ,Name varchar(250))

Global Temporary table: It is created with ‘##’ as prefix of table name and is available for all connections or any connection created after. These tables dropped when all connections that are referencing the table disconnected from the instance of SQL Server.

CREATE TABLE ##gobaltest
( ID int NOT Null ,Name varchar(250)) 

5.      COLLEASE vs. ISNULL

·         COALESCE and ISNULL   both can be used in NULL handling. But there are some differences in both of them.

·         COALESCE allows multiple parameters while ISNULL allow only two parameters.      

SELECT ISNULL(null,0) –0
 SELECT COALESCE(null,null,0) --0

·         COALESCE returns type of value with high precedence from list of parameters. While ISNULL return first parameter type.

DECLARE @intval INT, @floatval FLOAT
SELECT  @floatval= 8.25, @intval = 10
SELECT COALESCE(@floatval, @intval) AS Value

Result: 8.25

Since float have higher precedence then int.You can see data type precedence here http://msdn.microsoft.com/en-us/library/ms190309.aspx.

·         COALESCE is ANSI SQL Standard but ISNULL is T-SQL function.

Remove duplicate records from a table in SQL server

Here I create a table and inserted some duplicate records for testing purpose.

create table temp(cust_id varchar(50))

insert into temp(cust_id)

values

('oms1'),

('oms2'),

('oms1'),

('oms2'),

('oms3')

select *from temp

Method 1[Using ROWCOUNT]: 
You can simply use ROWCOUNT to delete duplicate record.

      --deleting 1 by 1

set ROWCOUNT 1

DELETE from temp where cust_id='oms1'

SET ROWCOUNT 0

select *from temp

                  --cust_id

                  --oms2

                  --oms1

                  --oms2

                  --oms3

          

insert into temp (cust_id) values('oms1')

Deleting all duplicate rows 

SET NOCOUNT ON

SET ROWCOUNT 1

WHILE 1 = 1

   BEGIN

        DELETE   FROM temp WHERE cust_id IN (SELECT  cust_id FROM temp GROUP BY          cust_id HAVING  COUNT(*) > 1)

   IF @@Rowcount = 0

         BREAK ;

   END

SET ROWCOUNT 0   

select *from temp

Output:

                  --cust_id

                  --oms1

                  --oms2

                  --oms3

Method 2[Using TOP Clause]: 

--Remove 1 by 1

delete top(1) from temp where cust_id='oms1'   

select *from temp

Output:

                  --cust_id

                  --oms2

                  --oms1

                  --oms2

                  --oms3

                 

Method 3[With the help of Another Table]: 

If your data is small then you can use this way.Here I created a table with distinct records.Drop first one and rename second one.

SELECT DISTINCT cust_id

INTO    temp1

FROM    temp

GO

DROP TABLE temp

exec sp_rename 'temp1', 'temp'

select *from temp

Output:

                  --cust_id

                  --oms1

                  --oms2

                  --oms3

Method 4[Using ROW_NUMBER]:
It is very efficient way to achieve the same.]  

WITH  TEST

          AS (
             SELECT   cust_id

             , row_number() OVER ( PARTITION BY cust_id ORDER BY cust_id )                     AS rowcnt

             FROM  temp

             )

          DELETE  FROM TEST

          WHERE  rowcnt  > 1

   

select *from temp

Output:

                  --cust_id

                  --oms2

                  --oms1

                  --oms3