Replication features in various editions of SQL Server 2005/2008
Reference Link:
Crisp information on SQL Server Replication Features availibility and limitations when using SQL Server 2005 and SQL Server 2008. I am sure this information is still useful in environments using older versions like SQL 2005 and 2008.
Snapshot from the above link shows the following… happy reading!
| Feature | Express | Express Advanced | Workgroup | Standard | Enterprise | Web | Evaluation | Developer |
|
Merge Replication |
Subscriber only | Subscriber only | <= 25 subscribers | Y | Y | Subscriber only | Y | Y |
|
Transactional / Snapshot Replication |
Subscriber only | Subscriber only | <= 5 subscribers | Y | Y | Subscriber only | Y | Y |
|
P-P Transactional Replication |
N | N | N | N | Y | N | Y | Y |
|
Oracle Publishing |
N | N | N | N | Y | N | Y | Y |
SQL Server 2012 – New Features
There has been lot of talk about SQL Server 2012 and its features – out of which there are few which are definitely a big leap. One among them is CONTAINED DATABASES –
What is Contained Database?
Any Database which is INSTANCE INDEPENDENT with no external dependencies and has self-contained mechanism of authenticating users is referred to as Contained DB.
The advantage of having such database is that they can be moved easily to another server and we can start working on it instantly without the need of any additional configuration since they do not have any external dependencies.
Frequently used terminologies when using contained databases:
- Application Boundary – It is the boundary between the server instance and the application code
- Application Model – The application model is the programming surface of a contained database to which SQL Server applications are written. This surface area differs from non-contained databases in that it clearly separates the application from the instance. This separation is made by the application boundary.
Contained database functionality can be broken into three classifications:
- Unchanged and available for use within the application model. This classification includes most SQL Server objects, CREATE and ALTER statements, procedures, and so on.
- Not available within the application model. This classification includes purely instance-level functions such as instance settings, processor affinity, resource governance, or the creation of databases themselves.
- Available within the application model with certain changes or restrictions. This classification includes collations and authentication.
The management model includes everything that lies outside of the application model on the instance level. The management model concerns the maintenance and operation of the instance as a whole as it resides outside of a contained database.
- Contained
It is a user entity that resides entirely within the application boundary.
- Uncontained
It is a user entity that crosses the application boundary.
- Non-contained database
Database whose containment is set to NONE.
- Fully contained database
Database that does not allow any objects or functions to cross the application boundary.
- Partially contained database
Database that allows features to cross the application boundary. It is available in RTM
- Contained user
There are two types of users for contained databases.
(a) Contained database user with password which are authenticated by the database.
(b) Windows principals that can directly connect to the database and does not need any login in the master database.
What is the purpose of Contained DBs?
Is there a way to avoid any loss of information during Database movement?
Is there a way to avoid checking for orphan users when we do a DB restoration and end up fixing the same one by one? Saving time J
Is there a way to move all the jobs related to a DB (agent job information) along with the Database movement? Etc.
Answer to all the above questions is CONTAINED Databases feature in DENALI (SQL Server 2012)
What are the contents of Contained DB?
The contained database, keeps all necessary information and objects in the database, for example
- Tables
- Functions
- Constraints
- Schemas
- Types.
It also stores all application-level objects in the database, including
- Logins
- Application-level agent jobs
- Persisted error messages
- Linked server information
- System settings
Implementing\Creating Contained DBs
There are 2 ways of implementing contained DB
- Using SQL Server Management Studio (SSMS)
- Using system stored procedures\scripts
Using SQL Server Management Studio (SSMS)
Step 1: Enable “Contained Database Authentication” property on the SQL Server instance Level.
Right click on the instance and select properties and then select ‘Advanced’ option to set the Containment property to True.
Step 2: Create a database and set its CONTAINMENT property to Partial.
NOTE: The current RTM version only supports PARTIAL containment
- Once the DB is created either using SSMS or scripts – set the DB containment property to ‘Partial’
Step 3: Create a Contained User within the newly created contained database.
Step 4: Login to the contained database using the user that exists in Contained Database.
Using system stored procedure
–Enabled Advanced options
sp_configure ‘show advanced’, 1;
RECONFIGURE WITH OVERRIDE;
go
–Enabled Database Containment
sp_configure ‘contained database authentication’, 1;
RECONFIGURE WITH OVERRIDE;
go
Changing Non Contained DB to Contained DB:
A non-contained DB can be set as contained but with few things to take care (mentioned below)
Step 1: To identify the NonContained objects
SELECT
CLASS_DESC
,FEATURE_NAME
,FEATURE_TYPE_NAME
FROM SYS.DM_DB_UNCONTAINED_ENTITIES
Step 2: To change the DB property to Partial containment
USE MASTER
GO
ALTER DATABASE NONCONTAINEDDB SET CONTAINMENT=PARTIAL;
GO
Step 3: To migrate users associated with Sql server logins
USE NONCONTAINEDDB
GO
EXEC SP_MIGRATE_USER_TO_CONTAINED @USERNAME = N’testuser’,
@RENAME = N’testusercontained’,
@DISABLE_LOGIN = N’DISABLE_LOGIN’
The sp_migrate_user_to_contained stored procedure is needed in order to contain the users that are associated with Sql Server logins. It will convert the Sql Server logins to users with password.
Step 4: We can repeat STEP 1 if there are any more non contained DB objects
How do we backup and restore contained DB?
The normal process of backup and restore will also apply in using contained DB as well. However, if we are restoring the backed up contained DB on any other instance we need to Enable “Contained Database Authentication” property on the SQL Server instance Level which is turned off by Default.
Else we get to see the following error –
Msg 12824, Level 16, State 1, Line 1
The sp_configure value ‘contained database authentication’ must be set to 1 in order to restore a contained database. You may need to use RECONFIGURE to set the value_in_use.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Using scripts:
–Enabled Advanced options
sp_configure ‘show advanced’, 1;
RECONFIGURE WITH OVERRIDE;
go
–Enabled Database Containment
sp_configure ‘contained database authentication’, 1;
RECONFIGURE WITH OVERRIDE;
Go
Can we use CREATE\ALTER Database Statement on Contained DBs?
The normal ALTER command doesn’t work on contained DB. Instead, a new option called ‘CURRENT ‘has been added which ensures that if we move the database to a new instance or change the database name, the command will still work.
ALTER DATABASE CURRENT
Benefits of using contained DBs
There are both Pros and Cons in using contained DBs –
Pros
Database Movement
One of the problems facing the existing database model is data being left behind when an application is moved from one instance to another. Ex: Orphan Users
Some data connected with an application, (for example, login information and agent job information) is currently stored within the instance instead of in the database. When you move a non-contained database application from one instance to another instance of SQL Server, this data is left behind. Then you must identify the data left behind and move it with your application to the new instance of SQL Server. This process can be time consuming and difficult.
Initial Application Development and administration
In a contained database, all elements of the application will reside within the database itself, thereby limiting instance-level impacts on the application and instance-level concerns for the developer.
A contained database stores data within the database itself, the lack of a disconnect between the application and the information previously held at an instance level means that the application administrator can easily verify production environment settings that may affect the application, access the necessary permissions to perform required duties, have the ability to backup and restore the database, and so on.
Threats of using contained DBs
There are disadvantages on using contained DBs
Related to Users
The major disadvantage of using contained DBs is to do with users –
- Db_owner and Db_securityadmin roles– Users in a contained database that have the ALTER ANY USER permission, such as members of the db_owner and db_securityadmin fixed database roles, can grant access to the database without the knowledge or permission if the SQL Server administrator. Granting users access to a contained database increases the potential attack surface area against the whole SQL Server instance.
- Guest Account – Database owners and database users with the ALTER ANY USER permission can create contained database users. After connecting to a contained database on an instance of SQL Server, a contained database user can access other databases on the Database Engine, if the other databases have enabled the guest account.
- Keep away from duplication user and login ids – If a contained database user with password is created, using the same name as a SQL Server login, and if the SQL Server login connects specifying the contained database as the initial catalog, then the SQL Server login will be unable to connect.
For more we can refer to Books Online.
Where can we have contained DBs?
Two instances wherein contained DB would be good are (there can be many more – but have listed few)
- We have two instances which are being used by different set of team and we have given different set of SQL Logins to each of the team.
- The same can be implemented using contained DBs and creating users and not logins (reducing the dependency on logins)
- If we need to create a copy of any of the existing DBs – the process would be to Take backup, restore and verify all the orphan users
- The same can be reduced to just backup and restore – no need to verify the users (if the same set of users need access to the newly created DB)
- In addition we can create users if any new needed with no login needed at instance level.
References:
Features WITHIN the application model (Contained)
http://msdn.microsoft.com/en-us/library/ff929188(v=sql.110).aspx
Features OUTSIDE the application model (Not Contained)
http://msdn.microsoft.com/en-us/library/ff929118(v=sql.110).aspx
Benefits of using contained DBs
http://msdn.microsoft.com/en-us/library/ff929071(v=sql.110).aspx#benefits
Threats against contained DBs
http://msdn.microsoft.com/en-us/library/ff929055(v=sql.110).aspx
http://www.codeproject.com/KB/database/ContainedDB.aspx#8
http://www.brentozar.com/archive/2010/11/sql-server-denali-database-mirroring-rocks/
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!
Experience… Life!
Sometimes I do ask myself… What is Life? I understand we do have the ability of defining the term ‘Life’ in our own ways or just google it to understand more technically 😉
For me ‘Life’ is a precious gift… gifted to us with the only intention of experiencing it to the fullest! So, Why do we waste time hating someone or be selfish? All we need to do is… be happy with whatever we have been gifted with and experience it… at the end of the day. we have only one life! precious and beautiful term referred to as ‘Life’.
So.. be good do good! 🙂
Understanding .TUF file in log shipping
What is .TUF file? What is the significance of the same? Any implications if the file is deleted?
.TUF file is the Transaction Undo File, which is created when performing log shipping to a server in Standby mode.
When the database is in Standby mode the database recovery is done when the log is restored; and this mode also creates a file on destination server with .TUF extension which is the transaction undo file.
This file contains information on all the modifications performed at the time backup is taken.
The file plays a important role in Standby mode… the reason being very obvious while restoring the log backup all uncommited transactions are recorded to the undo file with only commited transactions written to disk which enables the users to read the database. So when we restore next transaction log backup; SQL server will fetch all the uncommited transactions from undo file and check with the new transaction log backup whether commited or not.
If found to be commited the transactions will be written to disk else it will be stored in undo file until it gets commited or rolledback.
So… thats it for now! Happy Reading!!
Deadlock – Implicit Conversion
Do we see deadlocks because of ‘Implicit Conversions’?
Let me start defining deadlocks and then the reasons for deadlocks –
Deadlock – refers to a specific condition when two or more processes are waiting for the other to release a resource, or more than two processes are waiting for resources in a circular chain.
There can be number of reasons for deadlocks – such one reason can be ‘Implicit Conversions’
What is Implicit Conversion?
A system where in some tables has columns defined as a particular data type and a procedure is written with the wrong data type with reference to the same column. Say for example that our table actually has ID defined as an Int, but the developer knowingly or unknowingly wrote the procedure as if the ID column was a VarChar(20) data type.
So, now every time SQL Server has to look for ID it has to convert @ID from Varchar to INT. This is an implicit conversion of the data type.
How does implicit conversion cause performance bottleneck leading to deadlock?
SQL server internally uses a function ‘CONVERT_IMPLICIT’ to do conversion and when the conversion happens the INDEXES are not used effectively due to the uncertainty involved in the result of the function – which means it has to convert the value for each and every row. This results in SQL Server scanning the entire table looking for the value. This takes time and, under default locking modes, places a share lock on the entire table preventing other processes from updating records while the scan is taking place.
This hold on the entire set of rows in a table might lead to a deadlock when explicit DML operations are required to be performed on the same set of rows.
Solution?
To make sure to have the correct Data type defined in all definitions.
The following execution plan depicts the usage of Implicit Conversion function
Perform Counter Poster
As DBAs we are for sure to work on performance tuning. Performance Tuning requires proper planning with all the needed tools and execution to nail the TRUE culprit.
To make our life simpler Quest has released a poster which has comprehensive list of perform counters. I would say this should be part of the DBA tool kit.
MSSQL Server & Interoperability
I am sure we all know that MS SQL server is specific to MS tool set only!… till this happened 😉
Here is the change… we all have been waiting for.
MS has provided SQL Server ODBC driver for LINUX (Red Hat) – allows native application developers (C/C++) running on Linux to connect to SQL Server.
For more reading –
Announcement by Microsoft on the release and FAQ’s surrounding the same.
Data Modeling
Friends,
Another year has passed by and no blogs or updates from me! why… I should ask myself!? no answer 🙂
I have started exploring some Data Modeling tools from the last few days… and came across a web link which lists many of the tools widely used. The tools are detailed out with prices and also the purpose they serve.
We can refer to the link Data Modeling Tools
I will do my best to be active.
Happy Reading!
MS SQL Server CODE NAMES – Updated
I was wondering “where and how does Microsoft get all the code names”… Off course I did some googling and found one interesting web link shared by like minded DBA: SQL Server code names are named after National Parks!! Wow… Amazing!
