3 December 2015

SQL Server System Databases

1.Master
The master database stores basic configuration information for the server. This includes information about the file locations of the user databases, as well as logon accounts, server configuration settings, and a number of other items such as linked servers and start up stored procedures.
Database ID of Master database is 1.

2.TempDB
The tempdb system databases is a shared temporary storage resource used by a number of features of SQL Server, and made available to all users.  Tempdb is used for temporary objects, worktables, online index operations, cursors, table variables, and the snapshot isolation version store, among other things.  It is recreated every time that the server is restarted, which means that no objects in tempdb are permanently stored. 
Database ID of  Tempdb database is 2.

3. Model 
The model database is a template database that is copied into a new database whenever it is created on the instance.
Database ID of Model database is 3.

4. MSDB
The msdb database is used to support a number of technologies within SQL Server, including the SQL Server Agent, SQL Server Management Studio, Database Mail, and Service Broker. It is also maintain the information about backup, SQL jobs.


Database ID of MSDB database is 4.

-- By Shivasagar V

Editions of SQL Server:


Depends on version Microsoft released different types of editions.
SQL Server offers the right edition to meet your needs, including Enterprise for mission critical applications, enterprise business intelligence and data warehousing; Business Intelligence for self-service and corporate business intelligence; and Standard for basic database, reporting and analytics capabilities.
In SQL Server 2012 there are several types of editions are released.

  • ·         Enterprise Edition
  • ·         Standard Edition.
  • ·         Business Edition.
  • ·         Express Edition.
  • ·         Web and Developer Editions.
Enterprise Edition:
SQL Server 2012 Enterprise Edition is the high end of the SQL Server 2012 product line up. It supports the maximum number of cores and RAM in the host OS and provides the complete SQL Server feature set, including support for all of the advanced availability and BI features.
The Enterprise edition supports up to 16-node AlwaysOn Failover Clusters as well as AlwaysOn Availability Groups, online operations, PowerPivot, Power View, Master Data Services, advanced auditing, transparent data encryption, the Column Store index, and more. The Enterprise edition is licensed per core.

Standard Edition:
SQL Server 2012 Standard Edition is limited to 16 cores and 64GB of RAM. It provides the core relational database engine and basic business intelligence (BI) capabilities. It doesn't include support for the advanced availability features or the more powerful BI features such as PowerPivot, Power View, and Master Data Services. The Standard Edition does include support for two-node AlwaysOn Failover Clusters, and it's licensed either per core or per server.
Business Edition:
SQL Server 2012 Business Intelligence Edition is a new member of the SQL Server family. Like the Standard edition, the Business Intelligence edition is limited to 16 cores for the database engine and 64GB of RAM. However, it can use the maximum number of cores supported by the OS for Analysis Services and Reporting Services. The Business Intelligence edition includes all of the features in the Standard edition and support for advanced BI features such as Power View and PowerPivot, but it lacks support for the advanced availability features like AlwaysOn Availability Groups and other online operations. The Business Intelligence edition supports two-node AlwaysOn Failover Clusters, and it's licensed per server.
Express Edition:
SQL Server 2012 will continue to offer three versions of the free SQL Server Express Edition: Express (Database Only), Express with Tools, and Express with Advanced Services. Microsoft will also continue to offer a download of SQL Server Management Studio Express. The Express editions are limited to support for one CPU and 1GB of RAM. Databases are limited to 10GB per database. In addition, a new option called LocalDB will also be available.

Web and Developer Edition:
SQL Server 2012 Web Edition and SQL Server 2012 Developer Edition will continue to be part of the SQL Server 2012 family. The Developer edition provides the same feature set as the Enterprise edition. However, it's licensed per developer and can't be used for production work. The Web edition is licensed only to hosting companies with a Services Provider License Agreement (SLPA).


There are two types of databases in SQL server they are system databases and User databases.


--By Shivasagar V

DBMS and RDBMS

DBMS: A database management system (DBMS) is a software  that interacts with the user, other applications, and the database itself to capture and analyse data. A general-purpose DBMS is designed to allow the definition, creation, querying, update, and administration of databases.
RDBMS: A relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational models invented by E. F. Codd.
“RDBMS store the data into collection of tables, which might be related by common fields (database table columns). RDBMS also provide relational operators to manipulate the data stored into the database tables.
The key difference is that RDBMS applications store data in a tabular form, while DBMS applications store data as files. Does that mean there are no tables in a DBMS? There can be, but there will be no “relation” between the tables, like in a RDBMS. In DBMS, data is generally stored in either a hierarchical form or a navigational form.

Examples : SQL Server, ORACLE, DB2, MySQL, Teradata and etc…

-- By Shivasagar V

21 April 2015

Mirroring with Replication in SQL Server

In SQL server environment, we can configure combination of HA features. Like Mirroring with log-shipping or Mirroring with Replication.

If you go through with below scenario you can understanding very well.


Setup
If you followed the link above, BOL provides a basic listing of the order to setup mirroring with replication.
  1. Configure the Publisher.
  2. Configure database mirroring.
  3. Configure the mirror to use the same Distributor as the principal.
  4. Configure replication agents for failover.
  5. Add the principal and mirror to Replication Monitor.
We will follow these steps while adding in a few catches and added details that need to be configured to ensure processing flows while not in a failover situation and after a failover situation.
Mirroring and Replication Landscape
In the end, the solution shown in the following diagram will be achieved.
From the above diagram, you can see that three servers are involved. Server A acts as the principal as well as the publisher.
  • Principal: The primary server in mirroring
  • Mirror: The secondary server in mirroring
  • Publisher: The primary source of replication
  • Subscriber: The subscription to the published data in replication
To follow the setup order, the first task is to setup the publisher in transactional replication. This server also acts as the principal in mirroring (which will be setup later).

17 April 2015

SQL express schedule backup


USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_BackupDatabases] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create PROCEDURE [dbo].[sp_BackupDatabases]  
            @databaseName sysname = null,
            @backupType CHAR(1),
            @backupLocation nvarchar(200)
AS

       SET NOCOUNT ON;
           
            DECLARE @DBs TABLE
            (
                  ID int IDENTITY PRIMARY KEY,
                  DBNAME nvarchar(500)
            )
           
             -- Pick out only databases which are online in case ALL databases are chosen to be backed up
             -- If specific database is chosen to be backed up only pick that out from @DBs
            INSERT INTO @DBs (DBNAME)
            SELECT Name FROM master.sys.databases
            where state=0
            AND name=@DatabaseName
            OR @DatabaseName IS NULL
            ORDER BY Name
           
            -- Filter out databases which do not need to backed up
            IF @backupType='F'
                  BEGIN
                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','AdventureWorks')
                  END
            ELSE IF @backupType='D'
                  BEGIN
                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','model','msdb','AdventureWorks')
                  END
            ELSE IF @backupType='L'
                  BEGIN
                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','model','msdb','ReportServer$SQLEXPRESS','ReportServer$SQLEXPRESSTempDB','AdventureWorks')
                  END
            ELSE
                  BEGIN
                  RETURN
                  END
           
            -- Declare variables
            DECLARE @BackupName varchar(100)
            DECLARE @BackupFile varchar(100)
            DECLARE @DBNAME varchar(300)
            DECLARE @sqlCommand NVARCHAR(1000)
        DECLARE @dateTime NVARCHAR(20)
            DECLARE @Loop int                  
                       
            -- Loop through the databases one by one
            SELECT @Loop = min(ID) FROM @DBs

      WHILE @Loop IS NOT NULL
      BEGIN

-- Database Names have to be in [dbname] format since some have - or _ in their name
      SET @DBNAME = '['+(SELECT DBNAME FROM @DBs WHERE ID = @Loop)+']'

-- Set the current date and time n yyyyhhmmss format
      SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),'/','') + '_' +  REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')  

-- Create backup filename in path\filename.extension format for full,diff and log backups
      IF @backupType = 'F'
            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_FULL_'+ @dateTime+ '.BAK'
      ELSE IF @backupType = 'D'
            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_DIFF_'+ @dateTime+ '.BAK'
      ELSE IF @backupType = 'L'
            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_LOG_'+ @dateTime+ '.TRN'

-- Provide the backup a name for storing in the media
      IF @backupType = 'F'
            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' full backup for '+ @dateTime
      IF @backupType = 'D'
            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' differential backup for '+ @dateTime
      IF @backupType = 'L'
            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' log backup for '+ @dateTime

-- Generate the dynamic SQL command to be executed

       IF @backupType = 'F'
                  BEGIN
               SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'
                  END
       IF @backupType = 'D'
                  BEGIN
               SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH DIFFERENTIAL, INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'        
                  END
       IF @backupType = 'L'
                  BEGIN
               SET @sqlCommand = 'BACKUP LOG ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'        
                  END

-- Execute the generated SQL command
       EXEC(@sqlCommand)

-- Goto the next database
SELECT @Loop = min(ID) FROM @DBs where ID>@Loop

END

GO

use master

--====================================================================
--usage: usp_DeleteOldBackupFiles <path>, <file extention>, <age_hours>
--i.e. usp_DeleteOldBackupFiles 'D:\MSSQL_DBBackups', 'bak', 36
--usp_DeleteOldBackupFiles 'D:\MSSQL_DBBackupsLogs', 'trn', 72
--====================================================================
GO

Create PROCEDURE [dbo].[usp_DeleteOldBackupFiles]
@path nvarchar(256),
@extention nvarchar(10),
@age_hrs int
AS
BEGIN
SET NOCOUNT ON;

DECLARE @DeleteDate nvarchar(50)
DECLARE @DeleteDateTime datetime

SET @DeleteDateTime = DateAdd(hh, -@age_hrs, GetDate())

SET @DeleteDate = (Select Replace(Convert(nvarchar, @DeleteDateTime, 111), '/', '-') + 'T' + Convert(nvarchar, @DeleteDateTime, 108))

EXECUTE master.dbo.xp_delete_file 0, @path, @extention, @DeleteDate, 1
END
--============================
-- xp_delete_file information
--============================
-- xp_delete_file actually checks the file header to see what type of file it is and will only delete certain types such
-- as database and log backups. I suppose they expanded this to certain types of log files as well but as you say this is
-- not documented by MS. Just be aware that it will not delete just any file type

-- First argument is:
-- 0 - specifies a backup file
-- 1 - specifies a report file
-- (I'm not sure what the difference between a "backup file" and a "report file" is, since you specify the extension of files
-- you're deleting with the third argument.)
--
-- Fifth argument is whether to delete recursively.
-- 0 - don't delete recursively (default)
-- 1 - delete files in sub directories
--====================================================================


-- By Shivasagar V

16 April 2015

Error message when you execute a linked server query in SQL Server: "Timeout Expired"

Hi Viewers, In our production environment today we received following error.

Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'SQLOLEDB' reported an error. Execution terminated by the provider because a resource limit was reached. [OLE/DB provider returned message: Timeout expired] OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ICommandText::Execute returned 0x80040e31: Execution terminated by the provider because a resource limit was reached.].

Actually this error message received in one of the job history. Inside of the job is having the statements which will run the update or DML operation on one of the Linked server. There we got the error.

We tried a lot and finally we got the following KB article workarounds. It was very helpful.

Solution:
http://support.microsoft.com/en-us/kb/314530


-- By Shivasagar V

How to restore the missing Windows Installer cache files and resolve problems that occur during a SQL Server update

Today I have received this error while installing SQL service pack. This error usually comes due to missing windows installer cache files. Some of my windows team people are removed soome cache files due to space issue in C: drive. 

I have followed the workarounds which are there in below KB article. Now issue has been resolved. Thanks to microsoft KB article.

Solution: http://support.microsoft.com/en-us/kb/969052#Script

-- By Shivasagar V