Over the past few months, I have discussed the feasibility of enabling transaction replication for customer databases on various occasions. Every time I end up writing queries to answer certain questions about the database… the most common one being if the tables that need to be replicated have primary keys.
So I finally decided to write a T-SQL script which will help me answer the most common questions asked about a database while deciding on the feasibility of enabling transaction replication.
The script doesn’t capture information like workload, performance metrics etc. to decide if the replication workload (snapshot and distribution agent) can be supported on the existing hardware and resources available in the environment.
My take on the matter is that this information is required only once we have figured out if transactional replication can be enabled on the database or not. Eg. If the main tables that need to be replicated do not have primary keys, then the question of resource availability and hardware capability is moot point!
The script below checks the following:
1. Existing of primary keys on the tables in the database. Objects (articles) without primary keys cannot be replicated as part of a transactional replication publication.
2. If the database has transparent database encryption enabled. The subscriber database is not automatically enabled for TDE in such a scenario.
3. Constraints, primary keys, triggers and identify columns which have NOT FOR REPLICATION bit set and which objects do not. You might choose to replicate or not replicate some of these objects. However, you need to be aware of what you are replicating.
4. Tables having ntext, text and image columns as there are special considerations for handling DMLs on such columns.
5. XML schema collections present in the database. Modifications to the XML Schema collection are not replicated.
6. Tables with sparse column sets as they cannot be replicated.
7. Objects created using WITH ENCRYPTION option. Such objects cannot be replicated either.
As always, in case you think that there are additional checks that could be included in the script, then please leave a comment on my blog and I will add the same into the script.
Read the rest of this entry »
As part of my work, I very frequently have to collect information about the various database engine features that are currently being used on a particular SQL Server instance. Sometimes, this requires me to write T-SQL scripts to fetch the required information. I had updated my initial data collection script some time back and this gave me the idea to write up another set of T-SQL queries to fetch the information for the database engine features in use.
The script collects a bunch of information which are categorized under the following headings:
1. General Server Configuration
Non-default sp_configure settings
Active Trace Flags
2. Replication Configuration
Merge Replication Publishers
3. Full-text enabled databases
4. Linked Servers
5. SQL Agent information
Database file information
7. Server Triggers
8. Policy Based Management
9. Resource Governor
10. Database Mail
11. Log Shipping
12. Database Mirroring
13. SQL CLR Assemblies
14. sp_OA* procedures
- Download the script using the link given at the bottom of the page and save it to a file named SQL_DISCOVERY.SQL. Open the script file in a SSMS Query Window.
- Press CTRL+SHIFT+F so that the output results are put into a file. Doing this will not produce a message or any other notification.
- Execute the script and specify SQL_DISCOVERY.html as the output file name so that we can get the output in the require HTML format.
- Once the script is completed, open the HTML file.
If you have any feedback about the script or feel any new additions to the existing data that is being captured, please feel free to leave a comment!
SQL Server 2012 CTP 3, formerly known as SQL Server Code Name “Denali”, introduces a new set of T-SQL functions called Analytic functions. Analytic functions now open up a new vista for business intelligence where in you can calculate moving averages, running totals, percentages or top-N results within a group. I find this very useful while analyzing performance issues while traversing information present in a SQL Server trace file.
I was looking into a performance issue where in an application module executing a series of T-SQL functions was taking a long time to complete it’s operation. When I looked into the total duration of the T-SQL queries executed by the application, I couldn’t account for the total duration that the application was reporting. On tracking some of the statement executions done by the SPID which was being used by the application to execute the queries, I found a difference between the start time of a batch and the completed time of the previous batch. Now I needed to see the complete time difference between two subsequent query completion and start accounted for the difference in duration that I was seeing between the duration reported by the application and sum of duration of all the queries executed by the application. And BINGO… I was finally able to make the co-relation. Till SQL Server 2008 R2, I would have to write a query which involved a self-join to get the comparative analysis that I required:
;WITH cte AS
(SELECT b.name, a.starttime, a.endtime, a.transactionid, a.EventSequence, ROW_NUMBER() OVER(ORDER BY eventsequence) AS RowIDs
FROM trace a
INNER JOIN sys.trace_events b
ON a.eventclass = b.trace_event_id
WHERE spid = 83
AND b.name IN ('RPC:Starting','RPC:Completed','SQL:BatchStarting','SQL:BatchCompleted'))
SELECT TOP 1000 b.name, b.starttime, b.endtime, b.transactionid, DATEDIFF(S,a.endtime,b.starttime) as time_diff_seconds
FROM cte a
LEFT OUTER cte b
ON a.RowIDs = b.RowIDs-1
The output of the above query is shown in the screen shot below:
As you can see that there is a 4-second delay between the endtime of the statement in Row# 783 and the next execution shown in Row# 784. With the help of Analytic functions, I can simply use the LEAD function to get the above result and avoid a self-join.
SELECT TOP 1000 a.name,b.StartTime,b.EndTime,b.TransactionID,
DATEDIFF(s,(LEAD(b.EndTime,1,0) OVER (ORDER BY EventSequence DESC)),b.StartTime) as TimeDiff
FROM sys.trace_events a
INNER JOIN dbo.trace b
on a.trace_event_id = b.EventClass
WHERE b.SPID = 83
and a.name in ('RPC:Starting','RPC:Completed','SQL:BatchStarting','SQL:BatchCompleted')
The output as you can see is the same the previous query:
I had imported the data from the profiler trace into a SQL Server database table using the function: fn_trace_gettable. Let’s see what the query plans look like. For the first query which uses the common table expression and a self-join, the graphical query plan is as follows:
Now let’s see what the query plan looks like with the new LEAD function in action:
As you can see above a new Window Spool operator is the one which performs the analytical operation to calculate the time difference between the subsequent rows using the EventSequence number. As you can see that I have eliminated the need for a self-join with a temporary table or a common table expression and therefore simplifying my query in the process.
In the above example I am using the LEAD function to get value that I am interested in the following row. If you are interested in the values from a preceding row then you can use LAG function.
One gotcha that you need to remember here is that if you don’t take care of the start and end values of the dataset which you are grouping, you could run into the following error due to an overflow or underflow condition.
Msg 535, Level 16, State 0, Line 1
The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.
This is a small example of how analytic functions can help reduce T-SQL complexity when calculating averages, percentiles for grouped data. Happy coding!!
Disclaimer: This information is based on the SQL Server 2012 CTP 3 (Build 11.0.1440), formerly known as SQL Server Code Name “Denali” documentation provided on MSDN which is subject to change in later releases.