SQL Server with Mr. Denny


January 14, 2015  5:00 PM

Monitoring SQL Replication Latency

Denny Cherry Denny Cherry Profile: Denny Cherry
Database replication, SQL Server, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server Distributor

When working with SQL Server replication, you don’t have a whole lot of options for monitoring the latency of the replication. You’ve got the SQL Server Replication Monitor, and that’s basically it.

Well there is another option. You can manually run the code that the replication monitor uses to get those values that it displays.

To run it for a single publication, which is how the replication monitor uses the procedure you’d run it like this.

exec [distribution].sys.sp_replmonitorhelpsubscription @publisher = N’MyPublisher’, @publisher_db = N’MyDatabase’, @publication = ‘MyPublication’, @mode = 0, @exclude_anonymous = 0, @refreshpolicy = N’0′

But that only gives you data for the single publication for the single database. Any (or all) of the @Publisher, @publisher_db and @publication values can be null making them wildcard values. When this is done it’ll give you the information for all the publications, publishers and databases that match the wildcard. Below you can see that I’ll pulling the values for every publication which is monitored by the distributor.

exec [distribution].sys.sp_replmonitorhelpsubscription @publisher = NULL, @publisher_db = NULL, @publication = NULL, @mode = 0, @exclude_anonymous = 0, @refreshpolicy = N’0′, @publication_type=0

Now if I had a monitoring application I could have it run this stored procedure and look for values where the latency column is more than some value, say 120 seconds.

Denny

January 9, 2015  4:00 PM

Recommended reading from mrdenny for January 09, 2015

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL, SQL Server

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

This weeks SQL Server person to follow on Twitter is: CrespiDB also known as Rodrigo Crespi

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny.

Denny


January 7, 2015  5:00 PM

If I have a filtered index, what do I need to have in my query to make SQL use it?

Denny Cherry Denny Cherry Profile: Denny Cherry
Database index strategy, SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014

Filtered indexes are great, because they let you exclude rows from the index. But what do you need to include in your where clause to use the filtered index?

The answer here is surprisingly straight forward. You need to use the exact same where clause value in your queries where clause as is in the definition of the index. You also need that part of the where clause to be a text string in the query, not a parameter.

So for example “WHERE Active=1″ will work, where “WHERE Active=@Active” won’t. The reason for this is that at compile time the SQL Server won’t know what the value within the @Active parameter is, so it can’t use the filtered index. It has to do this because you could pass in 0 to the @Active parameter and then the plan with the filtered index wouldn’t be valid.

Denny


January 2, 2015  10:08 PM

Recommended reading from mrdenny for January 02, 2015

Denny Cherry Denny Cherry Profile: Denny Cherry
Reporting Services, SQL, SQL Server, Windows 10

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

This weeks SQL Server person to follow on Twitter is: PASS_RM_Canada also known as PASS RM Canada

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny.

Denny


December 31, 2014  4:00 PM

2014 By The Numbers

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL, SQL Server

2014 was another great year for my blog. This year I passed by 1000th blog post which is a pretty impressive milestone if I do say so.

My top 10 blog posts for 2014 are:
How to configure DTC on Windows 2008 – 5915 Views
What exactly is MSDTC, any when do I need it? – 5385 Views
SQL Server 2014 Licensing – 4687 Views
Any Little Thing Can Kill a SQL Server Upgrade – 1821 Views
Recommended reading from mrdenny for May 23, 2014 – 1804 Views
Fixing TempDB database problems when starting SQL using a minimum config – 1594 Views
PASS Summit 2014 Speaker Idol Competition – 1548 Views
How I Setup TempDB – 1548 Views
How Much Is That Query Costing? – 1511 Views
SQL 2012 Cluster, Windows 2012 R2 OS, Windows 2008 R2 Domains – 1461 Views

So for my top 10 posts for the year that’s about 27274 page views this year.  All in all, not bad.  Hopefully next year the numbers will go up again.  Based on these numbers I know that in 2015 I should be writing about MS-DTC, Licensing, TempDB and the SQL PASS Speaker Idol.  I think I can handle this.

See you in 2015.

Denny


December 26, 2014  7:09 PM

Recommended reading from mrdenny for December 26, 2014

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL Server

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

This weeks SQL Server person to follow on Twitter is: sqlpass also known as PASS

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny.

Denny


December 24, 2014  5:00 PM

What’s the Best Upgrade Path from SQL 2005 to SQL 2014?

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL, SQL Server, SQL Server 2005, SQL Server 2008, SQL Server 2012, SQL Server 2014, SQL Server upgrades, Upgrades

I get this question a decent amount in my Intro to SQL Server Management Session (called “I’ve got a SQL Database Now What?” or “SQL Server Management for the non Database Administrator”).

Frankly it doesn’t really matter. As long as you attach the database to a SQL Instance that is somewhere between SQL Server 2005 and SQL Server 2014 you’ll be fine. So why do we need to do this? Because Microsoft in their infinite wisdom has decided that they will only support upgrades from SQL Server 2008 and higher into SQL Server 2014. This presents a problem for those running SQL Server 2000 or SQL Server 2005 that want to jump directly into SQL Server 2014.

In order to make this upgrade happen, you need to attach the databases to a supported server. In the case of upgrading to SQL Server 2014 that means that you need to attach the database to a database instance running SQL Server 2008 through SQL Server 2012. If you are upgrading from SQL 2005 you can attach to any of those instances. If you are upgrading from SQL Server 2000 you’ll need to attach the databases to a SQL Server 2008 or SQL Server 2008 R2 instance. This is because SQL Server 2012 doesn’t support a direct upgrade from SQL Server 2000.

It makes no difference which version you attach to on the way to SQL Server 2014. The upgrade steps which need to be done between versions will be done in either location. And you don’t have to leave the database attached for very long. Just attach it, then detach it, then move it to the SQL 2014 server and attach it there for the final upgrade.

Just keep in mind that once you take that SQL 2005 instance and attach it to a SQL 2008 or higher instance you are committed and there’s no rolling back.

Denny


December 19, 2014  6:13 PM

Recommended reading from mrdenny for December 19, 2014

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL, SQL Server

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

This weeks SQL Server person to follow on Twitter is: PASSAppDev also known as PASS AppDev VC

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny.

Denny


December 17, 2014  5:00 PM

Farewell TechEd

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL Server, TechEd

Last month I had the privilege of speaking at what is supposed to be the last Microsoft TechEd conference at TechEd Europe 2014. I’ve been lucky enough to speak at both the North American and European Microsoft TechEd events for a few years now, having spoken at the 4 most recent TechEd North America events as well as the most recent 3 TechEd Europe events.

Speaking at TechEd has been a very interesting experience as it is typically a different audience than the other events which I’ve spoken at in the past. Events such as the SQL PASS Summit and SQL Saturdays, and even SQL Connections are mostly focused on Microsoft SQL Server while the audience at TechEd is made up mostly of developers as systems administrators.

I’m excited to see what the Ignite event looks like when it happens in Chicago in 2015. It is expected to be a larger event than TechEd has been recently due to the fact that many events are being rolled into a single event.

It has been a privilege to speak at TechEd the last few years, and I hope to continue the trend at the Ignite conference for years to come.

Denny


December 12, 2014  4:58 PM

Recommended reading from mrdenny for December 12, 2014

Denny Cherry Denny Cherry Profile: Denny Cherry
Database migration, SQL Server

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

This weeks SQL Server person to follow on Twitter is: morzhov_m also known as Maxim

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny.

Denny


Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: