SQL Server Fixed Server Roles

Have we ever wondered why does SQL Server provide many fixed server roles? What all“Server roles” are available for grabs? What of this each help us in? And why do we need these many?

Well the simple answer is ‘there are ‘N’ number of users accessing and viewing data from the database’ which makes ‘SECURITY’ a major concern area for any Database administrator.

There are different levels of ROLES defined in SQL Server - Server roles and Database roles.

For now let us consider ‘Server roles’ and understand more on the same. From SQL Server Management Studio – Under ‘Security’, and then under ‘Server Roles’ we have the following roles listed. Each of this role helps manage the permissions on a server. There are in total ‘NINE’ fixed server roles. These are system level server roles and cannot be changed at any point in time. Until SQL Server 2008 we did not have the flexibility of creating user defined server roles, SQL Server 2012 does provide the functionality of user defined server roles.

Each of the fixed server role and description is as explained below: (From MS Books online)

Fixed server-level role	Description
sysadmin	Members of the sysadmin fixed server role can perform any activity in the server.
serveradmin	Members of the serveradmin fixed server role can change server-wide configuration options and shut down the server.
securityadmin	Members of the securityadmin fixed server role manage logins and their properties. They can GRANT, DENY, and REVOKE server-level permissions. They can also GRANT, DENY, and REVOKE database-level permissions if they have access to a database. Additionally, they can reset passwords for SQL Server logins. Security Note The ability to grant access to the Database Engine and to configure user permissions allows the security admin to assign most server permissions. The securityadmin role should be treated as equivalent to the sysadmin role.
processadmin	Members of the processadmin fixed server role can end processes that are running in an instance of SQL Server.
setupadmin	Members of the setupadmin fixed server role can add and remove linked servers.
bulkadmin	Members of the bulkadmin fixed server role can run the BULK INSERT statement.
diskadmin	The diskadmin fixed server role is used for managing disk files.
dbcreator	Members of the dbcreator fixed server role can create, alter, drop, and restore any database.
public	Every SQL Server login belongs to the public server role. When a server principal has not been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object. Only assign public permissions on any object when you want the object to be available to all users. You cannot change membership in public. Note public is implemented differently than other roles. However, permissions can be granted, denied, or revoked from public.

For more reading – we can refer to Brian Kelley article on MSSQL Tips
ReferenceLink

Replication Architecture - Part 2

Let me get straight into the topic "Replication Architecture - Part 1"discussed last time; about the working of replication and then the entities involved in any replication model for that matter. We also did a understanding on publishing industry in comparison with replication which helped us in identifying the entities.

Following are the same:

• Publisher
• Distributor
• Agent
• Subscriber
• Articles
• Publications
• Subscriptions

So, let us get into each of this with respect to Replication.
A SQL server instance can be configured as Publisher or Distributor or subscriber.


Publisher: In simple terms, Publisher is the server on which the "Source" database resides. It is the main source for data and also identifies what data should be distributed across.

Distributor: gathers all the published data and holds the same until it sends it across to all the registered subscribers. It is also referred to as "Bridge" between publisher-subscriber and can support multiple publisher and subscriber concept.

Subscriber: Is the final destination on which data has to be transmitted.

Articles: This is the basic unit of replication. An article identifies a database object that is included in a publication. A publication can contain different types of articles, including tables, views, stored procedures, and other objects.

Publication: A publication is a collection of one or more articles from one database. The grouping of multiple articles into a publication makes it easier to specify a logically related set of database objects and data that are replicated as a unit.

Subscription: A subscription is a request for a copy of a publication to be delivered to a Subscriber. The subscription defines what publication will be received, where, and when. There are two types of subscriptions, push and pull.

Agent: An agent is a median which identifies the changes at publisher and transfers those changes to subscriber.

There are different agents in replication,

• Distribution Agent
  
• Log Reader Agent
  
• Merge Agent
  
• Queue Reader Agent
  
• Snapshot Agent

Replication Architecture - Part 1

Replication was something which I always wanted to explore... luckily had the opportunity of doing just that. To start off with anything that is new, we need to understand the "Architecture" and also working.

I have tried to put my understanding in words on the "Replication Architecture".

First of all, What is Replication?
In simple terms: Replication is the process of sharing data between databases in different locations. Using replication, we can create copies of a database and share the copy with different users.  This allows to make changes to local copy of the database and later synchronize the changes to the source database.

Next Question would be... Why do we opt for Replication?
Again, there can be many reasons for this; I have tried to capture few very valid reasons.
The primary concern for any organization is often the protection and availability of its data. Without reliable access to secure and relevant data, the smooth operation of a company comes to painful, unprofitable halt. Data should not only be secure, but also be accessible after a system or catastrophic failure. Replication - offers this accessibility.
In addition, Database replication can also supplement disaster-recovery plans by duplicating the data from a local database server to a remote database server. If the primary server fails, applications can switch to the replicated copy of the data and continue operations.

Now, we understand What is Replication and Why do we opt for Replication... The next would be to understand "How does Replication work", "What are the common entities used in deciding the type of replication?", "What are the types of Replication available or rather provided by MS" and much more...

So, let's move on understanding "How does Replication work":

Microsoft SQL server uses publishing industry model to represent the components and processes in replication architecture. The working is also similar to how publishing industry works(we can think of "Daily Newspaper as an example).
Publishing industry publishes Magazines/Books; there are Distributors and Agents who carry these publications to the Subscribers. Subscribers of the magazine obtain copies of the publication and read the articles of interest to them; this is exactly how the SQL Server Replication model works.

We can identify the following Entities for the SQL Server replication model.

Publisher
Distributor
Agent
Subscriber
Articles
Publications
Subscriptions

We will start getting into each of the above listed entities in my next blog... till then enjoy reading and also get some kind of know-how on types of replication provided by MS SQL Server!

What extra information can we get from a backup file?

Consider a situation where you got a alert saying the "disk is almost full" and you are browsing the disk to find out what can be removed. During this process you come across a orphaned backup file with just some name specified to that file and you do not know from which database this backup file is generated, when was it taken, is it a full or differential or log backup, etc, etc...
We all know that if we have the proper backup file(s), we can recover a database fully, but without the proper information we cannot recover. In this case, we do not know the information about the backup file that was found.

So how to proceed?
One way is to restore the backup and check,

1. But restoring of a backup taken in higher version is not allowed in lower version. For example, a backup taken in SQL Server 2008 cannot be restored in  SQL Server 2005.
2. Without a full backup we cannot start a new database restore and in this case we do not know what is the backup type.

So what next?

Another way is to check without restoring. For this, SQL Server provides additional commands that can be used with the backup file using which we can get extra information from the backup file.
These commands are

1. RESTORE HEADERONLY
2. RESTORE FILELISTONLY
3. RESTORE LABELONLY

RESTORE HEADERONLY - Returns the backup header information of the specified backup.

And the header information includes information about

Column name	Description for SQL Server backup sets
BackupName	Backup set name.
BackupDescription	Backup set description.
BackupType	Backup type: 1 = Database 2 = Transaction log 4 = File 5 = Differential database 6 = Differential file 7 = Partial 8 = Differential partial
ExpirationDate	Expiration date for the backup set.
Compressed	Whether the backup set is compressed using software-based compression: 0 = No 1 = Yes
Position	Position of the backup set in the volume (for use with the FILE = option).
DeviceType	Number corresponding to the device used for the backup operation. Disk: 2 = Logical 102 = Physical Tape: 5 = Logical 105 = Physical Virtual Device: 7 = Logical 107 = Physical
UserName	User name that performed the backup operation.
ServerName	Name of the server that wrote the backup set.
DatabaseName	Name of the database that was backed up.
DatabaseVersion	Version of the database from which the backup was created.
DatabaseCreationDate	Date and time the database was created.
BackupSize	Size of the backup, in bytes.
FirstLSN	Log sequence number of the first log record in the backup set.
LastLSN	Log sequence number of the next log record after the backup set.
CheckpointLSN	Log sequence number of the most recent checkpoint at the time the backup was created.
DatabaseBackupLSN	Log sequence number of the most recent full database backup.
BackupStartDate	Date and time that the backup operation began.
BackupFinishDate	Date and time that the backup operation finished.
SortOrder	Server sort order. This column is valid for database backups only. Provided for backward compatibility.
CodePage	Server code page or character set used by the server.
UnicodeLocaleId	Server Unicode locale ID configuration option used for Unicode character data sorting. Provided for backward compatibility.
UnicodeComparisonStyle	Server Unicode comparison style configuration option, which provides additional control over the sorting of Unicode data. Provided for backward compatibility.
CompatibilityLevel	Compatibility level setting of the database from which the backup was created.
SoftwareVendorId	Software vendor identification number. For SQL Server, this number is 4608 (or hexadecimal 0x1200).
SoftwareVersionMajor	Major version number of the server that created the backup set.
SoftwareVersionMinor	Minor version number of the server that created the backup set.
SoftwareVersionBuild	Build number of the server that created the backup set.
MachineName	Name of the computer that performed the backup operation.
Flags	Individual flags bit meanings if set to 1: 1 = Log backup contains bulk-logged operations. 2 = Snapshot backup. 4 = Database was read-only when backed up. 8 = Database was in single-user mode when backed up. 16 = Backup contains backup checksums. 32 = Database was damaged when backed up, but the backup operation was requested to continue despite errors. 64 = Tail log backup. 128 = Tail log backup with incomplete metadata. 256 = Tail log backup with NORECOVERY.
BindingID	Binding ID for the database
RecoveryForkID	ID for the ending recovery fork. This column corresponds to last_recovery_fork_guid in the backupset table.
Collation	Collation used by the database.
FamilyGUID	ID of the original database when created. This value stays the same when the database is restored.
HasBulkLoggedData	1 = Log backup containing bulk-logged operations.
IsSnapshot	1 = Snapshot backup.
IsReadOnly	1 = Database was read-only when backed up.
IsSingleUser	1 = Database was single-user when backed up.
HasBackupChecksums	1 = Backup contains backup checksums.
IsDamaged	1 = Database was damaged when backed up, but the backup operation was requested to continue despite errors.
BeginsLogChain	1 = This is the first in a continuous chain of log backups. A log chain begins with the first log backup taken after the database is created or when it is switched from the Simple to the Full or Bulk-Logged Recovery Model.
HasIncompleteMetaData	1 = A tail-log backup with incomplete meta-data.
IsForceOffline	1 = Backup taken with NORECOVERY; the database was taken offline by backup.
IsCopyOnly	1 = A copy-only backup.
FirstRecoveryForkID	ID for the starting recovery fork. This column corresponds to first_recovery_fork_guid in the backupset table.
ForkPointLSN	If FirstRecoveryForkID is not equal to RecoveryForkID, this is the log sequence number of the fork point. Otherwise, this value is NULL.
RecoveryModel	Recovery model for the Database, one of: FULL BULK-LOGGED SIMPLE
DifferentialBaseLSN	For a single-based differential backup, the value equals the FirstLSN of the differential base; changes with LSNs greater than or equal to DifferentialBaseLSN are included in the differential. For non-differential backup types, the value is always NULL.
DifferentialBaseGUID	For a single-based differential backup, the value is the unique identifier of the differential base.
BackupTypeDescription	Backup type as string, one of: DATABASE TRANSACTION LOG FILE OR FILEGROUP DATABASE DIFFERENTIAL FILE DIFFERENTIAL PARTIAL PARTIAL DIFFERENTIAL
BackupSetGUID	Unique identification number of the backup set, by which it is identified on the media.
CompressedBackupSize	Byte count of the backup set. For uncompressed backups, this value is the same as BackupSize.

Example:

RESTORE FILELISTONLY - Returns the information about list of the database and log files contained in the backup. 

Column name	Description
LogicalName	Logical name of the file.
PhysicalName	Physical or operating-system name of the file.
Type	The type of file, one of: L = Microsoft SQL Server log file D = SQL Server data file F = Full Text Catalog
FileGroupName	Name of the filegroup that contains the file.
Size	Current size in bytes.
MaxSize	Maximum allowed size in bytes.
FileID	File identifier, unique within the database.
CreateLSN	Log sequence number at which the file was created.
DropLSN	The log sequence number at which the file was dropped. If the file has not been dropped, this value is NULL.
UniqueID	Globally unique identifier of the file.
ReadOnlyLSN	Log sequence number at which the filegroup containing the file changed from read-write to read-only (the most recent change).
ReadWriteLSN	Log sequence number at which the filegroup containing the file changed from read-only to read-write (the most recent change).
BackupSizeInBytes	Size of the backup for this file in bytes.
SourceBlockSize	Block size of the physical device containing the file in bytes (not the backup device).
FileGroupID	ID of the filegroup.
LogGroupGUID	NULL.
DifferentialBaseLSN	For differential backups, changes with log sequence numbers greater than or equal to DifferentialBaseLSN are included in the differential.
DifferentialBaseGUID	For differential backups, the unique identifier of the differential base. For other backup types, the value is NULL.
IsReadOnly	1 = The file is read-only.
IsPresent	1 = The file is present in the backup.

Example:

RESTORE LABELONLY - Returns the information about backup media of the given backup. 

Column name	Description
MediaName	Name of the media.
MediaSetId	Unique identification number of the media set.
FamilyCount	Number of media families in the media set.
FamilySequenceNumber	Sequence number of this family.
MediaFamilyId	Unique identification number for the media family.
MediaSequenceNumber	Sequence number of this media in the media family.
MediaLabelPresent	Whether the media description contains: 1 = Microsoft Tape Format media label 0 = Media description
MediaDescription	Media description, in free-form text, or the Tape Format media label.
SoftwareName	Name of the backup software that wrote the label.
SoftwareVendorId	Unique vendor identification number of the software vendor that wrote the backup.
MediaDate	Date and time the label was written.
Mirror_Count	Number of mirrors in the set (1-4).
IsCompressed	Whether the backup is compressed: 0 = not compressed 1 =compressed

Example: