Wednesday, June 29, 2011

ACID Properties:

ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties that guarantee database transactions are processed reliably.
Transaction: A transaction is one or more actions that are defined as single unit of work.
Atomicity: All or nothing execution.
In a sequence of steps either all actions occur or none. Atomicity means that users do not have to worry about the effect of incomplete transactions. If an action fails half-way through a transaction, then all previous actions in the transaction must be rolled back as if they never happened.
Example: Moving money from savings to checking accounts , withdrawing money from bank accounts
Consistency : Execution of transaction results in consistent database.
The database is transformed from one valid state to another valid state.
Example: If someone moves 5,000$ from their bank account that information is logged. If it can't be logged then the money is not transferred.
Isolation : Many transactions may execute concurrently but each is unaware of the others.
The results of a transaction are invisible to other transactions until the transaction is complete.
Example:  If someone querying balance amount at 12.00 AM , now at 12.01 AM user Bunty deposits 250$. The query results even if it finishshes at 12.04AM will not show the changes made by Bunty.
Durability : Persistence.
Once transaction is committed (completed), the results of a transaction are permanent and survive future system and media failures.
Example: Once your seat is reserved on an airplane, even if the system crashes the moment it finishes writing that information in the database your seat will still be reserved.

Monday, June 27, 2011

SQL Server : List all failover clustered instance nodes on which an instance of SQL Server can run and Find active node query

SELECT * FROM ::fn_virtualservernodes()

Above query returns the list of Nodes on which SQL Server can run. If the current server instance is not a clustered server, fun_virtualservernodes returns empty rowset.

User must have VIEW SERVER STATE permission to execute above query successfully. 

Note : if you have SQL 2000 , Microsoft recommending to use
Select * from sys.dm_os_cluster_nodes

How to determine the active node of a SQL Active-Passive Failover Cluster programmatically from T-SQL?
Select ServerProperty('ComputerNamePhysicalNetBIOS')

SQL Server : Last Backup / Restore date of the database.

Please find below two methods to know last backup date of SQL database.

1.       In SSMS , right click on database and check the properties. Refer  Last database backup which is available under General  tab.

2.       Query : you can query msdb..backupset table.
SELECT  database_name ,
        MAX(backup_finish_date) AS backup_finish_date
FROM    msdb..backupset
WHERE   database_name = 'Database_Name'
GROUP BY database_name

Last Restore date of database Query
                SELECT  destination_database_name ,
        MAX(restore_date) AS restore_date
FROM    msdb..restorehistory
WHERE   destination_database_name = 'Database_Name'
GROUP BY destination_database_name
Find the all databases restore date with SourceDB , SourceFile and Backup Date

USE msdb
SELECT  DBRestored = destination_database_name ,
        RestoreDate = restore_date ,
        SourceDB = b.database_name ,
        SourceFile = physical_name ,
        BackupDate = backup_start_date
FROM    RestoreHistory h
        INNER JOIN BackupSet b ON h.backup_set_id = b.backup_set_id
        INNER JOIN BackupFile f ON f.backup_set_id = b.backup_set_id
ORDER BY RestoreDate

Monday, June 20, 2011

SQL Server : sa account locked out / FIX Error : 18486 Login Failed for user ‘sa’ because the account is currently locked out. The system administrator can unlock it. Unlock SA login

There may be multiple situations which end up with SA password locked. 

Brute force attack: attempt to guess a password using different possible combination,if you have password policy setting enable which locked the account after certain attempts.
Multiple unsuccessful attempts to guess ‘SA’ password. Etc...

Login with other SQL user which has sysadmin role.
Unlocked ‘SA’ login WITH ALTER command
ALTER LOGIN sa WITH PASSWORD = 'currentpassword' UNLOCK ;

2. Unlocked with SSMS.
Microsoft SQL Server management studio-->Logins-->sa-->Properties-->Status-->unchecked the Login is locked out check box

3. Sometime because of Enforce password policy enable , SQL server not giving you specific message when you trying to unlocked the account. In this case first you need to unchecked the ‘Enforced password policy’ , unchecked the Login is locked press OK and again the set the the ‘Enforced Password policy’ option once account is unlocked.

It is good practice to disable the sa user to prevent SQL server from hacking. Almost everyone is aware of the sa account. This can be the potential security risk. Even if you provide strong password hackers can lock the account by providing the wrong password. As a best practice you can disable the sa account and use another account with same this case you have different user should have same access as ‘SA’

it is also possible that to not have a user with sytem admin rights in your database and you only have activated SQL Server authentication.

It is possible to change the Server authentication with change value of the registry key "HKEY_LOCAL_MACHINE\Software\Microsoft\MSSqlserver\MSSqlServer\LoginMode" to 2 for Mixed mode. Then restart the SQL Server service. Now you can login to SQL server with windows authentication and change the password with the procedure discuss in above steps.

Wednesday, June 15, 2011

SQL Server System Databases

SQL Server 2008/2005 contains five system databases.

Master, Model, Msdb, tempdb  and Mssqlsystemresource (aka resource). Other than this ReportServer and ReportServerTempDB we can consider as system databases if reporting services installed. You may count distributor as system database if replication configure.

The master database contains all of the system level information for SQL Server – all of the logins, linked servers, endpoints, and other system-wide configuration settings.
The master database is also where SQL Server stores information about the other databases on this instance and the location of their files.

The first database in the SQL Server startup process, If the master database is not present, SQL Server cannot start. needs to reside in the same directory as the Resource database
Always take regular backups of the master database.

Good Practice: 
Do not create users objects in the master. Otherwise, master must be backed up more frequently. Normally when you open the SQL server and firing any query without proper USE statement it will create all object in default master database so keep practicing of always uses USE statement at top of any queries.
The msdb database is used by SQL Server Agent for scheduling alerts and jobs, Operators, Alerts and by other features such as Service Broker and Database Mail.
MSDB database is used to store the information related to the database backups and restore information. You need to make sure of purging old back up history from the msdb.
The model database is used as the template for all databases created on an instance of SQL Server. Because tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system.

You can change most database properties, create users, stored procedures, tables, views, etc. – whatever you do will be applied to any new databases.

The database configurations such as the recovery model for the Model database are applied to future user defined databases.

Outside of its role as a template, model doesn’t do anything else.
Is a workspace for holding temporary objects or intermediate result sets. Purpose of temporary database to store temporary tables, table variables, cursors, create or rebuilding indexes sorted in Tempdb etc.

Tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database, so permanent objects cannot be crated in tempdb database.
Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down. Therefore, there is never anything in tempdb to be saved from one session of SQL Server to another.

Backup and restore operations are not allowed on tempdb.

The size of tempdb can affect the performance of a system. For example, if the tempdb size is too small, the system processing could be too occupied with auto growing the database to support your workload requirement every time that you start SQL Server. You can avoid this overhead by increasing the size of tempdb.

Tempdb is the workhorse of the system databases. It is the workspace that SQL Server uses to store the intermediate results of query processing and sorting.

Is a read-only database that contains system objects that are included with SQL Server. System objects are physically persisted in the Resource database, but they logically appear in the sys schema of every database.

The Resource database makes upgrading to a new version of SQL Server an easier and faster procedure. When a service pack / hot fix are installed the resource database is updated.
The physical file names of the Resource database are mssqlsystemresource.mdf and mssqlsystemresource.ldf. These files are located in <drive>:\Program Files\Microsoft SQL Server\MSSQL10_50.<instance name>\MSSQL\Binn\. Each instance of SQL Server has one and only one associated mssqlsystemresource.mdf file, and instances do not share this file.

SQL Server cannot back up the Resource database. You can perform your own file-based or a disk-based backup by treating the mssqlsystemresource.mdf file as if it were a binary (.EXE) file, rather than a database file, but you cannot use SQL Server to restore your backups. 

Restoring a backup copy of mssqlsystemresource.mdf can only be done manually, and you must be careful not to overwrite the current Resource database with an out-of-date or potentially insecure version.

Read-only database that is not accessible via the SQL Server 2005 tool set
The database ID for the Resource database is 32767
The Resource database does not have an entry in master.sys.databases
To determine the version number of the Resource database, use:

To determine when the Resource database was last updated, use:
SELECT SERVERPROPERTY('ResourceLastUpdateDateTime');

Tuesday, June 14, 2011

How to find out how long a SQL Server database backup took?

SET @dbname = 'RealDB'
 --set this to be whatever dbname you want
SELECT  bup.user_name AS [User] ,
        bup.database_name AS [Database] ,
        bup.server_name AS [Server] ,
        bup.backup_start_date AS [Backup Started] ,
        bup.backup_finish_date AS [Backup Finished] ,
        CAST(( CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS INT) )
        / 3600 AS VARCHAR) + ' hours, '
        + CAST(( CAST(DATEDIFF(s, bup.backup_start_date,
                               bup.backup_finish_date) AS INT) ) / 60 AS VARCHAR)
        + ' minutes, '
        + CAST(( CAST(DATEDIFF(s, bup.backup_start_date,
                               bup.backup_finish_date) AS INT) ) % 60 AS VARCHAR)
        + ' seconds' AS [Total Time]
FROM    msdb.dbo.backupset bup
WHERE   bup.backup_set_id IN (
        SELECT  MAX(backup_set_id)
        FROM    msdb.dbo.backupset
        WHERE   database_name = ISNULL(@dbname, database_name) --if no dbname, then return all
                AND type = 'D' --only interested in the time of last full backup
        GROUP BY database_name )
        AND bup.database_name IN ( SELECT   name
                                   FROM     master.dbo.sysdatabases )
ORDER BY bup.database_name

 ref :