Resource Database:

What is Resource Database and what is it holds ?

The resource database (mssqlsystemresource) database is the new system databases available in SQL Server 2005 and later versions.

The Resource database is a read-only, hidden system database that contains all the system objects that are included with SQL Server. 

SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. 

The Resource database does not contain user data or user metadata.

Why Resource Database is important?

 

The Resource database appears to be a critical system database as the SQL Server service is now dependent on this.

What are the Advantages of Resource Database?

1) Prior to SQL Server 2005, whenever service packs are applied all the system objects that are residing within the system 

    and user databases gets updated which makes it very difficult to rollback the changes.

2) The only way to rollback the changes is to uninstall the instance of SQL Server and reinstall SQL Server followed by applying 

     any Service Packs or Hotfixes to revert back to the previous version of the SQL Server Instance and attached all User databases.

3) In SQL Server 2005 onwards the changes will be made to the Resource Database,

     which will indeed reflect the changes in all the system and user database of the particular instance.

4) If the DBA needs to apply a Service Pack to multiple instances, then the mssqlsystemresource.mdf and mssqlsystemresource.ldf 

     needs to be copied by the DBA to the target instance.

5) If the DBA needs to rollback the changes performed by the Service Packs then they need to replace the Resource Database related MDF and LDF files 

     with that of the previous backed up version

Here is some information about the resource database. 

• This hidden database stores system stored procedures and functions
  
• This was created by Microsoft in order to facilitate faster upgrades. 
  
• If the system objects were to be upgraded in any Service Pack, just replacing the resource database is enough.
  
• This database does not list when sp_helpdb is executed.
  
• Since resource database is hidden, it cannot be backed up using the Backup Database command. The only way to backup this database is to copy the .mdf & .ldf files. Same holds good for restoring this database. If resource database is restored, all the service packs/hotfixes need to be  reapplied.
  
• The database id 32767 is reserved for resource database and cannot be changed. If in SQL Server 2000, if any database is allocated the ID 32767, the upgrade to SQL Server 2005 will fail.
  
• If the files are renamed or moved from their respective locations then SQL Server will not start
  
• The physical file names of the Resource database is mssqlsystemresource.mdf and mssqlsystemresource.ldf, which are specific to each SQL Instance.
  
• The location of mssqlsystemresource.mdf/.ldf files are different for SQL Server 2005 & SQL Server 2008.
  
• In SQL Server 2005 these files are located at C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ folder. These files must be in the same folder where the data files of master database are stored.
  
• In SQL Server 2008 the default location of these files is C:\Program Files\Microsoft SQL Server\MSSQL10.<instance_name>\MSSQL\Binn. 
• These files need not be on the same directory where master database files are located.
  

In a clustered environment, the Resource database exists in the \Data folder on a shared disk drive.

How to Backup Resource Database ?

In order to take the backup of Resource Database we need to perform a file based or a disk based backup of 

the Mssqlsystemresource.mdf and Mssqlsystemresource.ldf files, by copy paste method. 

There is no Full backup or online backup for this database.

SQL Server will allow Resource Database related MDF & LDF files to be copied even when SQL Server is running.

How to Restore Resource Database ?

Use Copy Paste Method only, overwrite the existing files from the backup location.

SELECT SERVERPROPERTY('ResourceVersion') Resource_DB_Version,

SERVERPROPERTY('ResourceLastUpdateDateTime') Resource_DB_LastUpdate_DateTime

GO

Notes:

Do not put the Resource Database files in a compressed or encrypted NTFS file system folders as it will effect performance 

and will also possibly  prevent upgrades.

Thanks!

PATAN

Fill Factor

Fill Factor:

=======================================================================================================

    

A page is the basic unit of data storage in SQL server. Its size is 8KB(8192 bytes). Data is stored in the leaf-level pages of Index.  

The percentage of space to be filled with data in a leaf level page is decided by fill factor. 

The remaining space left is used for future growth of data in the page. Fill factor is a number from 1 to 100.

Its default value is 0, which is same as 100.So when we say fill factor is 70 means, 70% of space is filled with data and remaining 30% is vacant for future use.

So higher the fill factor, more data is stored in the page. Fill factor setting is applied when we create/rebuild index.

Page Split:

Page split occurs, when there is no more space to accommodate data in leaf-level pages. 

Page split is movement of data from one page to another page, when data changes in the table.

Fill factor and its effect on performance:

From above, we have gathered some idea about fill factor. Now let see how fill factor can performance. 

From above discussion we can say if we have higher value of fill factor, we can save more data in a page.

By storing more data in less space, we can save memory, resource uses(like IO, CPU).

But downside of it is page split. Page split hamper performance.

Now let us discuss about low value of fill factor. By setting low value of fill factor, page split can be avoided. 

But it will need more memory(although memory is cheap now), more resource uses.

Let say we have a table where the data was fit in 50 pages when the fill factor setting was 100 or 0.

Now let say we reduced the fill factor to 50. So it will take 100 pages. 

When we need to read all the rows, the number of read is doubled now.

How to decide fill factor?

What is correct value of fill factor, we need to set for better performance ? 

There is no specific answer. It depend upon your application. You are the best person to decide its value. 

Below is the criteria you need to consider while choosing fill factor.

1:For static/look-up table:                                                                                                                                             

This type of tables have static data means data changes very rarely in the table. So we can set high value of fill factor means 100 or 0.

2:For Dynamic table:                                                                                                                                                   

 In this type of table, data get changes(inserted/updated) frequently. So we need to set low fill factor, between 80 to 90.

3:For Table with Clustered Index on Identity Column:                                                                                              

Here the data is inserted at the end of the table always. So we can have higher value of fill factor between 95 to 100.

How to set Fill factor ?                                                                                                                                                  

We can set fill factor in 2 ways                                                                                                                                          

1) Server level:

A generic fill factor setting is applied at the server level for all table/index.                             

To see what is the current current default fill factor set at the server level, you can use below script.  

EXEC [sys].[sp_configure] 'fill factor'

GO

   

You can set a server level default fill factor by using sp_configure with a parameter as below script. 

Here are setting a fill factor value of 90.

EXEC sys.sp_configure 'fill factor', 90

GO

RECONFIGURE WITH OVERRIDE

GO

                                                                                                                  

2) At Index/Table level:

While creating/rebuilding index we can set a specific fill factor. 

We can use below script to set fill factor while rebuilding index.

USE YourDatabaseName                                                                                    

GO

ALTER INDEX YourIndexName ON [YourSchemaName].[YourTableName]                                           

REBUILD WITH (FILLFACTOR = 80);

GO

======================================================================================

 Patan

When was my database last used

When was my database last used ???

====================================================
Use <DatabaseName>

GO

SELECT DB_NAME() as DatabaseName,
s1.sql_handle,
(SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 ,
( (CASE WHEN statement_end_offset = -1
THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2)
ELSE statement_end_offset END) - statement_start_offset) / 2+1)) AS sql_statement,
execution_count,
plan_generation_num,
last_execution_time,
total_worker_time,
last_worker_time,
min_worker_time,
max_worker_time,
total_physical_reads,
last_physical_reads,
min_physical_reads,
max_physical_reads,
total_logical_writes,
last_logical_writes,
min_logical_writes,
max_logical_writes
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
WHERE s2.objectid is null
ORDER BY s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset

==========================================================================

Thanks & Regards,

ChanBasha Patan

+91-7411823445

   Think before you print. Go Green.

Hardening

HARDENING OF SQL SERVER:

=======================

Hardening:

Hardening SQL Server is nothing but Minimizing security risks to protect the data (that means, reducing its surface area and controlling access to it).     

Reducing the surface area:

To reduce the surface area of SQL Server, apply the following best practices.

• Install only the required SQL Server components.
• Don't install SQL Server Reporting Services (SSRS) on the same server as the database engine also running on that.
• Disable the SQL Server services that won't be immediately used. E.g., SQL Server browser service.
• Don't use the default TCP/IP ports. E.g., Use dynamic port: 1633
• Disable the network protocols that aren't required. E.g., Named Pipes.
• Make sure that the antivirus and firewall software versions are current and configured correctly.

 After that, you initially reduce the surface area, you need to manage its configurations.

In SQL Server 2005, Microsoft introduced the Surface Area Configuration Manager (And its command-line counterpart sac.exe) for this purpose. However, this tool performs only the most common management tasks.

            (e.g., enabling remote connections and supported protocols).

     To perform the less common tasks (e.g., designating service accounts and authentication mode), you need to use the SQL Server Configuration Manager, the system stored procedure sp_configure, or a Windows tool (e.g., Windows Management Instrumentation—WMI).

In SQL Server 2008, Microsoft replaced the Surface Area Configuration Manager with the Policy-Based Management system.With this system, you can manage the configurations for the full surface area—it's a one-stop shop for your surface area needs.                                                                            

Controlling Access:

Ø  SA (System Admin) account should be disable.

Ø  Sysadmin right should not provide to any groups and logins.

Thanks & Regards,

ChanBasha Patan

+91-7411823445

   Think before you print. Go Green.

2016, 1st June

1st June 2016: SQL Server 2016

========================

New Features:

Thanks & Regards,

ChanBasha Patan

+91-7411823445

   Think before you print. Go Green.

TAKING BACKUPS SCRIPT

àBackup all systemdb (Masster, model, msdb):

DECLARE @DB VARCHAR(20)

DECLARE @BkpFName VARCHAR(100)

DECLARE @BkpFDate VARCHAR(50)

 

SELECT @BkpFDate = REPLACE(CONVERT(VARCHAR(50), GETDATE(), 102),'.','') + REPLACE(CONVERT(VARCHAR(50), GETDATE(), 108),':','')

 

DECLARE Cursor_DBs CURSOR FOR

 

SELECT name

FROM master.sys.DATABASES

WHERE name IN ('master','model','msdb')

 

OPEN Cursor_DBs

FETCH NEXT FROM Cursor_DBs INTO @DB

 

WHILE @@FETCH_STATUS = 0

 

BEGIN

SET @BkpFName = 'C:\BackupFolder\' + @DB + '_FullBackup_' + @BkpFDate + '.bak'

BACKUP DATABASE @DB TO DISK = @BkpFName WITH COMPRESSION

FETCH NEXT FROM Cursor_DBs INTO @DB

END

 

CLOSE Cursor_DBs

DEALLOCATE Cursor_DBs

================================================================================================================================

àBackup all Userdb :

DECLARE @DB VARCHAR(20)

DECLARE @BkpFName VARCHAR(100)

DECLARE @BkpFDate VARCHAR(50)

 

SELECT @BkpFDate = REPLACE(CONVERT(VARCHAR(50), GETDATE(), 102),'.','') + REPLACE(CONVERT(VARCHAR(50), GETDATE(), 108),':','')

 

DECLARE Cursor_DBs CURSOR FOR

 

SELECT name

FROM master.sys.DATABASES

WHERE name NOT IN ('master','model','msdb', 'tempdb')

 

OPEN Cursor_DBs 

FETCH NEXT FROM Cursor_DBs INTO @DB 

 

WHILE @@FETCH_STATUS = 0 

 

BEGIN 

SET @BkpFName = 'C:\BackupFolder\' + @DB + '_FullBackup_' + @BkpFDate + '.bak'

BACKUP DATABASE @DB TO DISK =  @BkpFName  WITH COMPRESSION

FETCH NEXT FROM Cursor_DBs INTO @DB 

END 

 

CLOSE Cursor_DBs 

DEALLOCATE Cursor_DBs

 ===================================================================================

àBackup all systemdb & Userdb's:

DECLARE @DB VARCHAR(20)

DECLARE @BkpFName VARCHAR(100)

DECLARE @BkpFDate VARCHAR(50)

 

SELECT @BkpFDate = REPLACE(CONVERT(VARCHAR(50), GETDATE(), 102),'.','') + REPLACE(CONVERT(VARCHAR(50), GETDATE(), 108),':','')

 

DECLARE Cursor_DBs CURSOR FOR

 

SELECT name

FROM master.sys.DATABASES

WHERE name != 'tempdb'

 

OPEN Cursor_DBs 

FETCH NEXT FROM Cursor_DBs INTO @DB 

 

WHILE @@FETCH_STATUS = 0 

 

BEGIN 

SET @BkpFName = 'C:\BackupFolder\' + @DB + '_FullBackup_' + @BkpFDate + '.bak'

BACKUP DATABASE @DB TO DISK =  @BkpFName  WITH COMPRESSION

FETCH NEXT FROM Cursor_DBs INTO @DB 

END 

 

CLOSE Cursor_DBs 

DEALLOCATE Cursor_DBs

Thanks & Regards,

ChanBasha Patan

+91-7411823445

   Think before you print. Go Green.

How to find user databases files size with script

Disk Space Used for all databases:

====================

;WITH DataBase_Size (SqlServerInstance,DatabaseName,DatabaseSize,LogSize,TotalSize)

AS

-- Define the CTE query.

(

  SELECT      @@SERVERNAME SqlServerInstance,

            db.name AS DatabaseName,

            SUM(CASE WHEN af.groupid = 0 THEN 0 ELSE af.size / 128.0E END) AS DatabaseSize,

            SUM(CASE WHEN af.groupid = 0 THEN af.size / 128.0E ELSE 0 END) AS LogSize,

            SUM(af.size / 128.0E) AS TotalSize

FROM        master..sysdatabases AS db

INNER JOIN  master..sysaltfiles AS af ON af.[dbid] = db.[dbid]

WHERE       db.name NOT IN ('distribution', 'Resource', 'master', 'tempdb', 'model', 'msdb') -- System databases

            AND db.name NOT IN ('Northwind', 'pubs', 'AdventureWorks', 'AdventureWorksDW')   -- Sample databases

GROUP BY    db.name 

)

-- Define the outer query referencing the  name.

SELECT *FROM DataBase_Size order by TotalSize desc

Script out logins (Taking backup of the logins)

Logins script out:
===================

USE master

GO

IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL

  DROP PROCEDURE sp_hexadecimal

GO

CREATE PROCEDURE sp_hexadecimal

    @binvalue varbinary(256),

    @hexvalue varchar (514) OUTPUT

AS

DECLARE @charvalue varchar (514)

DECLARE @i int

DECLARE @length int

DECLARE @hexstring char(16)

SELECT @charvalue = '0x'

SELECT @i = 1

SELECT @length = DATALENGTH (@binvalue)

SELECT @hexstring = '0123456789ABCDEF'

WHILE (@i <= @length)

BEGIN

  DECLARE @tempint int

  DECLARE @firstint int

  DECLARE @secondint int

  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))

  SELECT @firstint = FLOOR(@tempint/16)

  SELECT @secondint = @tempint - (@firstint*16)

  SELECT @charvalue = @charvalue +

    SUBSTRING(@hexstring, @firstint+1, 1) +

    SUBSTRING(@hexstring, @secondint+1, 1)

  SELECT @i = @i + 1

END

SELECT @hexvalue = @charvalue

GO

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL

  DROP PROCEDURE sp_help_revlogin

GO

CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS

DECLARE @name sysname

DECLARE @type varchar (1)

DECLARE @hasaccess int

DECLARE @denylogin int

DECLARE @is_disabled int

DECLARE @PWD_varbinary  varbinary (256)

DECLARE @PWD_string  varchar (514)

DECLARE @SID_varbinary varbinary (85)

DECLARE @SID_string varchar (514)

DECLARE @tmpstr  varchar (1024)

DECLARE @is_policy_checked varchar (3)

DECLARE @is_expiration_checked varchar (3)

DECLARE @defaultdb sysname

IF (@login_name IS NULL)

  DECLARE login_curs CURSOR FOR

      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM

sys.server_principals p LEFT JOIN sys.syslogins l

      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'

ELSE

  DECLARE login_curs CURSOR FOR

      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM

sys.server_principals p LEFT JOIN sys.syslogins l

      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name

OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin

IF (@@fetch_status = -1)

BEGIN

  PRINT 'No login(s) found.'

  CLOSE login_curs

  DEALLOCATE login_curs

  RETURN -1

END

SET @tmpstr = '/* sp_help_revlogin script '

PRINT @tmpstr

SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'

PRINT @tmpstr

PRINT ''

WHILE (@@fetch_status <> -1)

BEGIN

  IF (@@fetch_status <> -2)

  BEGIN

    PRINT ''

    SET @tmpstr = '-- Login: ' + @name

    PRINT @tmpstr

    IF (@type IN ( 'G', 'U'))

    BEGIN -- NT authenticated account/group

      SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'

    END

    ELSE BEGIN -- SQL Server authentication

        -- obtain password and sid

            SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )

        EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT

        EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

        -- obtain password policy state

        SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

        SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

            SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

        IF ( @is_policy_checked IS NOT NULL )

        BEGIN

          SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked

        END

        IF ( @is_expiration_checked IS NOT NULL )

        BEGIN

          SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked

        END

    END

    IF (@denylogin = 1)

    BEGIN -- login is denied access

      SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )

    END

    ELSE IF (@hasaccess = 0)

    BEGIN -- login exists but does not have access

      SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )

    END

    IF (@is_disabled = 1)

    BEGIN -- login is disabled

      SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'

    END

    PRINT @tmpstr

  END

  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin

   END

CLOSE login_curs

DEALLOCATE login_curs

RETURN 0

GO

========================================================================================================