Snapshot replication for beginners

If you want to learn a little bit about one of the types of replications, that can be used to distribute data to another database/instance/server, then you should see this video about Snapshot replication.

Imagine that you have a server in one location and a server in another location. They have both a SQL Server installed, and on the first server you have a database called CRM. From that database you want to copy one of the tables, called Customers to a database called CUSTOMERS on the other server.

You want it to be done once a week and there are only 1.000 customers.

In that situation a Snapshot replication could be a good choice. In snapshot replication you:

  1. Create a publication (Meaning: Customertable from CRM database is now an article in the publication).
  2. You send it to a distributor that will deliver it to
  3. A subscriber on the other server.

In the video you will see how a snapshot replication is created.


Instant file initialization

Some times you need to create larger databases, and it will be quite irritating to wait on the SQL Server to do all the Work. The most timeconsuming task is to wait on the databasefile to be zeroed out. So how can we avoid that?

Basically it is very simple: You just need to allow the user WHO has started the SQL Server Instance to perform volume maintenance tasks.

You do that by:

  1. Go to SQL Server Configuration manager and see what user WHO has started your instance.
  2. Go to start –> run and write GPEDIT.MSC
  3. Go to the node Computer configuration –> Windows settings –> Security settings –> Local Policies –> User Right assignment and find PERFORM VOLUME MAITENANCE TASKS.
  4. Add the user that started the sql server instance.
  5. Restart the SQL server instance in SQL Server Configuration Manager.

Now you have instant file initialization on your sql server.

Don’t you believe me??¬† Well, try to Watch this video, that I have made.


Index fragmentation

I have seen a lot of nice ways of handling index fragmentations, but in the blog I would try to give you my approach on how to setup index defragmentation on a production server.

First of all, then there is a lot of Things to take into consideration, and that is Things like:

  1. How often do we need to do defragmentation.
  2. What version do we have of SQL server.
  3. How often is our data defragmented.
  4. How large are our indexes.
  5. And so on.

and when we have used a lot of time to think about that, then we will (at least in 90%) of all situations end up with some standardrecommandations:

  1. When an index is more than 10% fragmented, then we must do defragmentation.
  2. When it is between 10% and 30%  fragmented, then we must do a reorganize.
  3. when it is over 30% fragmented, then we must do a rebuild.

This blog is not about answering the questions about when to defragment, how often and so on, this script is just about getting some code that can optimize defragmentation.

We will use three special techonologies called:

  1. Dynamic Management object.
  2. Cursors
  3. Dynamic SQL.

First we must identify fragmentation on each index, and we will use a Dynamic Management object called dm_db_index_physical_stats, and it takes the following parameters:

  1. DatabaseID
  2. ObjectID
  3. IndexID
  4. PartitionID
  5. Mode

the important thing is the mode, because it takes 5 different values, and the two most important is SAMPLED, DETAILED. Use SAMPLED if you have a big database with some heavy indexes. Use DETAILED, when you have a smaller database. DETAILED gives a more accurate view of the indexes, and SAMPLED returns the result faster.

So, if we use that, then we now can create a small TSQL script that returns fragmentation for all indexes in a database.

select DB_NAME(database_ID) asdatabasename
       , T1.object_id as [Table ID]
       , OBJECT_NAME(t1.object_ID) as [Table name]
       , t2.index_id as [Index ID]
       , as [Index name] 
       , max(avg_fragmentation_in_percent) as Fragmentation 
       , case when max(avg_fragmentation_in_percent)<10 then  
           'No action required' 
           case when max(avg_fragmentation_in_percent)<40 then 
             'Index should be REORGANIZED' 
             'Index should be REBUILD' 
         end as ActionType
from sys.dm_db_index_physical_stats(db_id('adventureworks'),null,null,null,'SAMPLED')t1 
       inner join sys.indexes t2 on t1.index_id=t2.index_id and t1.object_id=t2.object_id
group by DB_NAME(database_ID) 
         , T1.object_id
         , OBJECT_NAME(t1.object_ID)   
         , t2.index_id  ,

order by case when max(avg_fragmentation_in_percent)<10 then 
           'No action required' 
           case when max(avg_fragmentation_in_percent)<40 then 
             'Index should be REORGANIZED' 
             'Index should be REBUILD' 
         end ,[Table name] , [Index ID]

Try to run that script some couple of times and then you can see how it works.

Now we can find the information we needs pr. index, and that is:

  1. Object_ID
  2. Index_ID
  3. Fragmentation in percent

And we can now create a cursor that do the appropriate action. Please not that we also use dynamic SQL to run do this task. The code looks like

Use AdventureWorks
Declare @objectid  varchar(20)
Declare @indexid  varchar(20)
Declare @fragmentation float
Declare sjCursor Cursor  for
              select t2.object_id   
              , t3.index_id   
              ,  max(avg_fragmentation_in_percent) as max_fragmentation_index
              from sys.dm_db_index_physical_stats(DB_ID(), null, null, null, 'Sampled') t1   
                     inner join sys.objects t2 on t1.object_id=t2.object_id   
                     inner join sys.indexes t3 on t1.index_id = t3.index_id and t3.object_id=t1.object_id
              group by t2.object_id   
              , t3.index_id
              having max(avg_fragmentation_in_percent)>10
open sjCursor
fetch next from sjcursor into @objectid, @indexid, @fragmentation
while @@FETCH_STATUS=0
  Declare @sqlstatement varchar(500)  
  Declare @objectnavn  Varchar(500)  
  Declare @indexnavn  varchar(500)    
  set @objectnavn=(select '[''].['']'
                   from sys.objects t1
                      inner join sys.schemas t2 on t1.schema_id=t2.schema_id
                   where t1.object_id=@objectid)  
  set @indexnavn = (select '['+name+']'
                    from sys.indexes
                    where object_id=@objectid and index_id=@indexid)  
  if @fragmentation<=30   
    set @sqlstatement='alter index '+@indexnavn+ ' on '+@objectnavn+' REORGANIZE'  
    set @sqlstatement='alter index '+@indexnavn+ ' on '+@objectnavn+' REBUILD'
  fetch next from sjcursor into @objectid, @indexid, @fragmentation
end close sjCursor
deallocate sjCursor


THATS IT. run this script on the SQL Server, and you have a smarter way of doing defragmentation.

You could improve the script in many ways, and some of them are

  1. Create another cursor so that you can do multiple databases.
  2. Add a top 100 to the select statement, so that it only defrags the most fragmented indexes.
  3. Fix code to tune performance of the script.

But anyway…… It Works and it is a good way of working.

All scripts on this blog is executed at your own risk!!!!




Microsoft¬ģ SQL Server¬ģ 2012 Pocket Consultant

This is the first time I would make a book review, where I am 100% negative about a book. I know that if you have nothing good to say, then you should not say it, but I must warn everybody about buying this book.

Especially if you also have the 2008 version of the book.

There is a lot of good knowledge in the book, but I can’t see the purpose of it, and I hate when I buy a book, then I just feel that the FrontPage has been changed from the previous version.

I must admit that there is a lot of nice information in the book, but what does that help when the physical layout of the book is very bad?

There are simply to many pages for the small format, and if I really should use it as a pocket consultant, then I would not be able to bring it onboard a plane, because my total weight would exceed the allowed weight.

I would give this book 1 out of 5 stars, and I would recommend you to use your Money buying other books instead.

Bookreview – 70-462 Administering Microsoft SQL Server 2012 Databases

This is the book for Microsoft SQL 2012 exam 70-462, and compared to the books for Microsoft sql server 2008 and earlier, then I think that Microsoft has done a great job on this one. The book is divided into 12 chapters, and the first one is the traditional “How do we install” and what versions has what functionalities, how can I upgrade from one SQL server to another and so on. When it comes to Examination it is good to know, but In real life, I think it of no use.

Chapter 2+3 is about configuration, First about the instance configuration and then how to configure components. I would wish that more dba’s would read these chapters, because there are a lot of small tricks that could improve SQL Server installations. E.g. is there a small chapter about fill-factor and a lot more about e.g. filegroups/files and recovery models. Really good to know, eventhough you can google a lot of things these days.

Chapter 4 is about migrationg, importing and export, and what I would focus on, if I should learn from this chapter, would be all about the bulk operations, and I think that I would try to get more knowledge if I could find the time. It is really interesting.

Chapter 5+6 is about security, and eventhough security is not as funny as e.g. performancetuning, then these chapters are a very good startingpoint to understand security in a SQL Server. In 2012 we also have the possibility to have user-defined serveroles. That is really a new and great thing. I could also recommend to read about auditing in chapter 6.

Chapter 7+8 is about high availability and replication. Please note that I think it is wrong to call replication a high availability feature. I don’t think it is, but anyway, AlwaysOn (a new term in Sql 2012) is introduced.

Chpater 9+10 is about performance, troubleshooting and concurrency, and you should definately read these two chapters. For me it is the most important thing when i comes to SQL Servers.

Chapter 11 is about the SQL Server Agent, backups and restores, and I have seen so many wrong configured Backups, that it is a wonder that I only know one situation where a customer has lost data. READ IT!!!!!!! UNDESTAND IT!!!!! PRACTICE IT!!!

Chapter 12 is code studies, and before taking the exam, you should really dig into this.

So, the 100 million dollar question: Can you pass the exam if you understand this book? NO! I think you can use it as a very very good beginning (better than earlier versions), but you must also practice and also use the tests that are on the CD that follows the Book.

If I should rate this book, then I would give it 3 out of 5 stars.

I want to pass 70-461 – Querying Microsoft SQL Server 2012


I just want to share some of the thoughts that I have about how you in the most easy way can Pass exam. 70-461.

Basically, the easiest way is to learn SQL, but it is my opinion that you should have been using TSQL and the SQL server for at least 3-5 years, if you want to be sure to pass the exam.

The Exam is split into the following topics.

  1. Create Database Objects (24%)
  2. Work with Data (27%)
  3. Modify Data (24%)
  4. Troubleshoot & Optimize (25%)

At present (on 2012-10-22) there is no trainingmaterial from Microsoft, so you must learn a lot of stuff your self. That stuff includes (but is not 100% perfect):

  1. What is the syntax to
    1. Create/drop/alter a table
    2. Create/alter/drop  a filetable
    3. Create /alter/drop a view
    4. Create an indexed view.
    5. What types of functions are there.
    6. How to extract data to XML format (What is the difference between e.g. FOR XML AUTO, RAW, PATH)
    7. How do you insert, update, delete records.
    8. Understand different types of constraint (Unique, Foreign key e.g.).
    9. How SELECT works, and how to use Joins (at least inner, Outer and Cross joins), APPLY.
    10. How to Insert, Update and Delete data.
    11. How MERGE is working.
  2. Dynamic management views you must underStand:
    1. Views to tell you about missing indexes.
    2. Views to tell you about index usage, including physical info.
  3. Other things that you might need to know:
    1. How collation works.
    2. New features in SQL 2012 in relation to querying.
    3. General understanding of different types of objects (e.g. views, tables, Stored Procedures, Functions) and when to use what.
    4. Data types (especially the new ones).
    5. How Transactions works (also what the difference between implicit and explict transactions are).
    6. How Executionplans works
    7. Table hints
    8. Query hints

And there are much more. I would recommend also to read the studyplan at microsoft.

So how would you prepare. Well, I would start downloading the Adventureworks databases from CODEPLEX.COM.
When they are installed (at least just choose one of them, then I would do the following:

  1. Search Books online to read about the topics.
  2. Open SQL Server Management Studio (or other tools if they are required) and then start to code.
  3. If any doubts then try searching (but be carefull. there is a lot of crap out there).

When you have learned a new topic, then you can move on to the next topic. However!!!!! What about the level of knowledge?

Well, that is always the question. Often Microsoft has this “Free shot” campaign, and if that is the situation (or if you have to much money), then try to take the exam. If you pass then you are fantastic. If you fail, you are still fantastic, but then you have learned something about the level of knowledge.

AND!!!!! At some point and will have some preparation questions ready, and Microsoft will send out the training material, and then you can use that for your preparations.

Good Luck (and please call me if you need any specific advises).



1) Print out a checklist (that could e.g. be mine, eventhough there are more topics to be covered)