The Ji Village News

季庄新闻–Haidong Ji’s Blog

The script below is available in MSDN. I used it a few months ago but it took me some time to find it again, so I decided to put it here for my own reference.

Purpose: find out information on distributor and publisher databases, articles, and publications in Sql Server replication.

–********** Execute at the Distributor in the master database **********–

use master
go

–Is the current server a Distributor?
–Is the distribution database installed?
–Are there other Publishers using this Distributor?
exec sp_get_distributor

–Is the current server a Distributor?
select is_distributor from sys.servers where name=’repl_distributor’ and data_source=@@servername

–Which databases on the Distributor are distribution databases?
select name from sys.databases where is_distributor = 1

–What are the Distributor and distribution database properties?
exec sp_helpdistributor
exec sp_helpdistributiondb
exec sp_helpdistpublisher

–********** Execute at the Publisher in the master database **********–

–Which databases are published for replication and what type of replication?
exec sp_helpreplicationdboption

–Which databases are published using snapshot replication or transactional replication?
select name as tran_published_db from sys.databases where is_published = 1
–Which databases are published using merge replication?
select name as merge_published_db from sys.databases where is_merge_published = 1

–What are the properties for Subscribers that subscribe to publications at this Publisher?
exec sp_helpsubscriberinfo

–********** Execute at the Publisher in the publication database **********–

use AdventureWorks
go

–What are the snapshot and transactional publications in this database?
exec sp_helppublication
–What are the articles in snapshot and transactional publications in this database?
–REMOVE COMMENTS FROM NEXT LINE AND REPLACE
with the name of a publication
–exec sp_helparticle @publication=’ ‘

–What are the merge publications in this database?
exec sp_helpmergepublication
–What are the articles in merge publications in this database?
exec sp_helpmergearticle — to return information on articles for a single publication, specify @publication=’ ‘

–Which objects in the database are published?
select name as published_object, schema_id, is_published as is_tran_published, is_merge_published, is_schema_published
from sys.tables where is_published = 1 or is_merge_published = 1 or is_schema_published = 1
union
select name as published_object, schema_id, 0, 0, is_schema_published
from sys.procedures where is_schema_published = 1
union
select name as published_object, schema_id, 0, 0, is_schema_published
from sys.views where is_schema_published = 1

–Which columns are published in snapshot or transactional publications in this database?
select object_name(object_id) as tran_published_table, name as published_column from sys.columns where is_replicated = 1

–Which columns are published in merge publications in this database?
select object_name(object_id) as merge_published_table, name as published_column from sys.columns where is_merge_published = 1

use msdb
go
select name, suser_sname(sysjobs.owner_sid) as owner from msdb..sysjobs
go

select sj.name,sjh.run_status,case sjh.run_status
when 1 then ‘Sucess’
when 2 then ‘Retry’
when 3 then ‘Cancelled’
when 4 then ‘In Progress’
when 0 then ‘Failed’
End
as ‘Run Status’,sjh.run_date as ‘Run Date’,
CASE len(run_time)
WHEN 1 THEN cast(’00:00:0′
+ cast(run_time as char) as char (8))
WHEN 2 THEN cast(’00:00:’
+ cast(run_time as char) as char (8))
WHEN 3 THEN cast(’00:0′
+ Left(right(run_time,3),1)
+’:’ + right(run_time,2) as char (8))
WHEN 4 THEN cast(’00:’
+ Left(right(run_time,4),2)
+’:’ + right(run_time,2) as char (8))
WHEN 5 THEN cast(’0′
+ Left(right(run_time,5),1)
+’:’ + Left(right(run_time,4),2)
+’:’ + right(run_time,2) as char (8))
WHEN 6 THEN cast(Left(right(run_time,6),2)
+’:’ + Left(right(run_time,4),2)
+’:’ + right(run_time,2) as char (8))
END as ‘End Time’,
CASE len(run_duration)
WHEN 1 THEN cast(’00:00:0′
+ cast(run_duration as char) as char (8))
WHEN 2 THEN cast(’00:00:’
+ cast(run_duration as char) as char (8))
WHEN 3 THEN cast(’00:0′
+ Left(right(run_duration,3),1)
+’:’ + right(run_duration,2) as char (8))
WHEN 4 THEN cast(’00:’
+ Left(right(run_duration,4),2)
+’:’ + right(run_duration,2) as char (8))
WHEN 5 THEN cast(’0′
+ Left(right(run_duration,5),1)
+’:’ + Left(right(run_duration,4),2)
+’:’ + right(run_duration,2) as char (8))
WHEN 6 THEN cast(Left(right(run_duration,6),2)
+’:’ + Left(right(run_duration,4),2)
+’:’ + right(run_duration,2) as char (8))
END as ‘Duration’ from msdb..sysjobs sj
join sysjobhistory sjh on sj.job_id=sjh.job_id
where name like ‘NameString%’
and sjh.run_date>=’yyyymmdd’
order by run_duration

Script below. This one works better for Sql Server 2000. I will do a version for Sql Server 2005, when I get a chance. It didn’t work very well on Sql Server 2005 because Ad Hoc Distributed Queries is disabled by default.

set nocount on
IF OBJECT_ID(’tempdb..#TmpJobs’) IS NOT NULL
DROP TABLE #TmpJobs

SELECT *
INTO #TmpJobs
FROM OPENROWSET(’sqloledb’
, ’server=(local);trusted_connection=yes’
, ’set fmtonly off exec msdb.dbo.sp_help_job @enabled = 1′)

select ‘Successful run’

SELECT cast(name as varchar(59)),
LastRunDatetime = CASE last_run_date
WHEN 0 THEN ‘Never’
ELSE
CONVERT(varchar(10), CONVERT(smalldatetime, CAST(last_run_date as varchar), 120), 120)+’ ‘+
RIGHT(’0′+CAST((last_run_time/10000) AS VARCHAR), 2)+’:'+
RIGHT(’0′+CAST((last_run_time-((last_run_time/10000)*10000))/100 AS VARCHAR), 2)+’:'+
RIGHT(’0′+CAST((last_run_time-((last_run_time/10000)*10000)-((last_run_time-((last_run_time/10000)*10000))/100*100)) AS VARCHAR), 2)
END
FROM #TmpJobs where last_run_outcome = 1

select ‘Failed run’

SELECT cast(name as varchar(59)),
LastRunDatetime = CASE last_run_date
WHEN 0 THEN ‘Never’
ELSE
CONVERT(varchar(10), CONVERT(smalldatetime, CAST(last_run_date as varchar), 120), 120)+’ ‘+
RIGHT(’0′+CAST((last_run_time/10000) AS VARCHAR), 2)+’:'+
RIGHT(’0′+CAST((last_run_time-((last_run_time/10000)*10000))/100 AS VARCHAR), 2)+’:'+
RIGHT(’0′+CAST((last_run_time-((last_run_time/10000)*10000)-((last_run_time-((last_run_time/10000)*10000))/100*100)) AS VARCHAR), 2)
END
FROM #TmpJobs where last_run_outcome = 0

This is mainly for my own reference. Save it as ListSysadmin.pl. Run ListSysadmin.pl SqlServerList.txt on command line. It lists all logins that has sa (sysadmin) rights on Sql Server instance, including individual domain members in a domain group that has sysadmin rights.

SqlServerList.txt should be a text file, with each individual line contains a Sql Server instance.

This script is an improvement over Linchi’s original scrip from his Real World SQL Server Administration with Perl. The original script only takes one instance as input parameter. Note: with my current site style sheet on my web site, I think it ignores the angle brackets, so you need to fix the script when you do copy and paste.

use strict;
use Win32::ODBC;

# Import the function from the module SQLDBA::Utility
use SQLDBA::Utility qw( dbaRemoveDuplicates );

# Import functions from the module Win32::NetAdmin
use Win32::NetAdmin qw( GetDomainController
GroupGetMembers
LocalGroupGetMembersWithDomain
UsersExist );

Main: {
my $log = shift or die “***Err: $0 expects a file name.\n”;
open(LOG, “$log”) or die “***Err: couldn’t open $log.\n”;
while () {
my $line = $_;
if ($line =~ /\[?(\w+)/ix) {
my $serverConn = $1;

print “Instance: $1\n”;

# get all the accounts/logins with sysadmin role from SQL Server
my $loginRef = getSQLSysadmins($serverConn);
# in case the server name is followed by an instance name
my ($server) = $serverConn =~ /([^\\]+)(\\.+)?/;

# replace BUILTIN with the server name
foreach (@{$loginRef->{groupSet}}) {
s/BUILTIN\\/uc($server) . “\\”/e; # evaluate the right side before replace
}

# remove all local groups and get their members
$loginRef = removeLocalGroups($loginRef);

# get global group members
$loginRef = getGlobalGroupMembers($loginRef);

# now print out all the NT/SQL logins that are SQL sysadmin
print “Listing all NT/SQL logins with the sysadmin rights …\n”;
foreach (sort @{$loginRef->{resultSet}}) { print “\t$_\n”; }
}
}
close(LOG);

} # Main

#########################
sub getSQLSysadmins {
my $serverConn = shift or
die “***Err: getSQLSysadmins() expects a server.”;

my $connStr = “Driver={SQL Server};Server=$serverConn;” .
“Trusted_Connection=Yes;Database=master”;
my $conn = new Win32::ODBC ($connStr) or
die “***Err: Can’t connect to $serverConn. ” . Win32::ODBC::Error();

# get sysadmin logins (NT and SQL)
my @logins = ();
my $sql = q/SELECT loginname FROM master.dbo.syslogins
WHERE sysadmin = 1
AND isntgroup = 0/;

unless ($conn->Sql( $sql )) {
@logins = (@logins, $conn->Data) while ($conn->FetchRow());
} else {
print “***Err: executing $sql. “, Win32::ODBC::Error(), “\n”;
}

# get sysadmin nt groups
my @groups = ();
my $sql = q/select loginname from master.dbo.syslogins
where sysadmin = 1
and isntgroup = 1/;

unless ($conn->Sql( $sql )) {
@groups = (@groups, $conn->Data) while ($conn->FetchRow());
} else {
print “***Err: executing $sql. “, Win32::ODBC::Error(), “\n”;
}
$conn->Close();

return { resultSet => \@logins,
groupSet => \@groups,
userSet => undef };
} # getSQLSysadmins

###########################
sub removeLocalGroups {
my $loginRef = shift or
die “***Err: removeLocalGroups() expects a reference.”;

foreach my $group (@{$loginRef->{groupSet}}) {
if (my $rc = getLocalGroupMembers($group)) {
push @{$loginRef->{userSet}}, @{$rc};
}
else {
push @{$loginRef->{userSet}}, $group;
}
}
delete $loginRef->{groupSet};
return $loginRef;
} # removeLocalGroups

##############################
sub getGlobalGroupMembers {
my $loginRef = shift or
die “***Err: getGlobalGroupMembers() expects a reference.\n”;
my @logins = ();

foreach my $user (@{$loginRef->{userSet}}) {
if ($user =~ /^NT\s+AUTHORITY\\/) {
push @logins, $user;
next;
}

my ($domain, $name) = $user =~ /^(.+?)\\(.+)$/;
if ( UsersExist($domain, $name ) ) { # a user account
push @logins, $user;
}
else {
my $pdc;
my @users;
if (GetDomainController(undef, $domain, $pdc)) {
$pdc =~ s/\\//g;

if ( UsersExist($pdc, $name ) ) {
push @logins, $user;
}
else { # $name is a global group account
# get users in the global group
if (GroupGetMembers($pdc, $name, \@users)) {
# prefix the users with the domain name
push @logins, map { “$domain\\$_”; } @users;
}
else { # you shouldn’t reach here
print “***Err: can’t retrieve users for $domain\\$name.\n”;
next;
}
}
}
else {
print “***Err: can’t find domain controller for $domain.\n”;
next;
}
}
}

push @logins, @{$loginRef->{resultSet}};
$loginRef->{resultSet} = dbaRemoveDuplicates(\@logins);
return $loginRef;
} # getGlobalGroupMembers

################################
sub getLocalGroupMembers {
my $group = shift or
die “***Err: getLocalGroupMember() expects a group name.”;

my ($domain, $group_name) = $group =~ /^(.+?)\\(.+)$/ or
die “***Err: $group must be qualified with a domain/machine name.”;

my @users = ();
if (LocalGroupGetMembersWithDomain($domain, $group_name, \@users)) {
return \@users;
}
else {
return undef;
}
} # getLocalGroupMembers

One of the first things you want to do as a DBA is to check when was the last time a full backup was taken for all databases you manage. For Sql Server, here is a script you can run:

set nocount on
select cast(a.name as char(30)), max(b.backup_finish_date) from master..sysdatabases a left outer join msdb..backupset b on a.name = b.database_name
where a.name not in (’model’, ‘tempdb’, ‘pubs’, ‘northwind’, ‘adventureworks’)
group by a.name

Suppose we have a list of instances we manage, I cannot emphasize enough how convenient it is to be able to blast the above statement to all instances we manage. That way we get all full backup information on all instances in one fell swoop. Fortunately, Linchi Shea wrote a couple of utilities just for that: one in C#, one in Perl. Check ‘em out here.

I will take MySQL certification exams, therefore I am going through MySQL 5.0 Certification Study Guide. It is a pretty cool book in that it goes to the point right away without many wasted words. Too many technical books dance around the topic, fill the volume with screen shots, but do not deliver the goods in the end. I am reading the Safari online version. With so many pages, I can see the physical book can be bulky.

Anyway, some comments and observations:

1. Sybase, MySQL, and Sql Server’s definition and implementation of database and schema are somewhat close. I dislike Microsoft’s definition and usage of schema, especially in Sql Server 2005. It is very confusing to new or DBAs from other platforms.

Sybase, MySQL, and Sql Server all support the USE Database statement;

2. I like mysql command line tool and how the result set is displayed. It looks clean and neat to me, although, like command line tools from other database platforms, when the result set gets wider, it gets messy;

3. Similar to Sql Server, MySQL also has the system and status variables that starts with @@. And, like Sql Server, you can just do SELECT @@Variable to retrieve its value, for example, SELECT @@sql_mode;

4. Played with MySQL Query Browser a little bit. Ctrl - E is the keyboard shortcut for statement execution, the same as Sql Server. However, F5 does not execute the statement in MySQL Query Browser.

If you are running Sql Server 2005 on Windows Server 2003 SP2, you may get this message from Sql Server native client connection:

An existing connection was forcibly closed by the remote host

This is most likely caused by a “feature” called TCP Chimney offloading. On the server, run:

Netsh int ip set chimney DISABLED

should fix your problem. It seems Visual Basic 6 (VB6) based applications and Heat Call Logging are particularly susceptible to this problem.

KB article here: http://support.microsoft.com/default.aspx/kb/945977

As an independent consultant and trainer , I found myself doing a lot of existing code analysis and enhancement, mostly for stored procedures and ad-hoc SQL statements. I suspect a lot of people do the same thing as well, so I am really interested in learning how you do it. That’s the main purpose of this post. This post is tagged with Oracle, Sql Server, and MySQL, as the principals should be the same for all platforms.

Let me share with you how I do it. Notice that I look at table/column statistics and indexes in almost all the steps below. Therefore I purposely left them out in the discussion.

1. I always talk to the original author or the current owner of the code, asking him/her to walk me through it. I listen mostly, trying to understand why s/he writes code this way. I may ask a few questions, just to help me understand. I almost never do any lecturing at this stage. If the code is a stored procedure, I ask the author or stakeholder to give me sample execution statements, with parameters filled with typical values for production load. Sometime it is not possible to talk to the original author, because s/he may have left the company;

2. After that, I will get the database in question and restore it on my own workstation, with the data as close to production as possible. I will start running the code, and gather IO statistics and execution plan, and save the results for benchmark comparison later on;

3. Here is the raw code analysis stage, where code is put into an editor for analysis. In my case, it is VI or VIM. I find myself using the * command on all variable names and tables (make sure you are doing case insensitive search for Sql Server code), to see where they are used. This is also where I weed out the historical garbage code that is there but never being used anymore, such as unused variables, tables, etc. In the case of Sql Server, I mostly look for cursors, temp tables, and user defined functions;

4. Code simplification stage. I found a lot of bad performing code is unwieldy and unnecessarily complex. At this stage, I look for redundant WHERE clauses, convoluted AND and/or OR operators. This can be a tedious and time-consuming process. At this point, it is important to talk to the author or owner of the code, as a good understanding of data and entity relationship is crucial. Also, since I have gained some understanding from the steps above, it is easier to have an intelligent conversation with the code owner, with fresh perspective and momentum. Usually something good will come out of the conversation, and we will have something concrete to work on next;

5. I will make the changes discussed in step 4, once again, collect statistics and execution plan for comparison;

6. Repeat Step 4 and 5 a few times, when necessary. I usually start talking about better database design, code/design refactoring, set based operations, etc., as hopefully I have some credibility and gained the customer’s trust.

So what do you do to break down complex, low performance SQL code? What methodology, tools, tips, tricks you can share with me?

One important method for performance tuning is to decrease number of logical reads, by adding, updating, and/or refreshing indexes and statistics. To see if those changes make a difference for you, it is important to look at query statistics.

Here is a sample output after set statistics io on:

Table ‘Table1′. Scan count 0, logical reads 1673, physical reads 4, read-ahead reads 8, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Table2′. Scan count 0, logical reads 1673, physical reads 10, read-ahead reads 8, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Table3′. Scan count 1, logical reads 11871, physical reads 1, read-ahead reads 11865, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘#TempTable_____________________________________________________________________________________________________________000000000022′. Scan count 2, logical reads 276, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Table2′. Scan count 156, logical reads 1723, physical reads 1, read-ahead reads 8, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

I used to tally those numbers by hand. This is error prone, and can be time-consuming, especially if the output is big. So I cooked up a Perl script below for this:

#!/usr/bin/perl
use strict;

my $LogicalReads = 0;

Main: {
my $log = shift or die "***Err: $0 expects a file name.n";
open(LOG, "$log") or die "***Err: couldn't open $log.n";
while () {
findPattern($_);
}
close(LOG);
print “Total logical read is $LogicalReadsn”;
} # Main

#######################
sub findPattern {
my $line = shift or die “***Err: function findPattern() expects a string.n”;
if ($line =~ /logicalsreadss+(d+)/ix) {
$LogicalReads = $LogicalReads + $1;
}
} # findPattern

Thanks Linchi and Tom for ideas!

Here is some of my thoughts on migrating MySQL to Sql Server. It came out of an email discussion. I’d love to hear your thoughts on migrating to a different database platform, not just MySQL to Sql Server.

I actually thought about writing a white paper or even a course on migrating from MySQL to Sql Server, but never got the time to do it. Sometimes a project doing similar things can serve as a launchpad for this endeavour, but that never came along, at least not yet. I am very interested in database interoperability field though. I’ve done MySQL and Oracle admin in the past and have published some MySQL and Oracle stuff in blogs. I have much better technical skills on Sql Server than any other RDBMS platforms, primarily because I’ve worked on it longer.

Here are some of my thoughts. I think most of it applies equally on migration from Oracle, DB2, Sybase, Postgresql, etc., to Sql Server, or the other way around. It might be slightly easier to migrate from Sybase to Sql Server, considering their common root.

1. It is not easy to migrate existing app, unless the app is a simple one. Even for that, there are enough quirks that can throw people off and cause enough frustration to derail the whole projects. I’ve seen that happening twice, having engaged in moving 2 apps from Sql Server to Oracle;

2. Therefore, the best way to migrating to a new database RDBMS, in my opinion, is to start from a new initiative, probably not big initially. When you start things from a clean slate, you don’t have the historical garbage to worry about. Furthermore, you will give the team enough time to learn the new platform, and prepare the team for future migration, if you choose to do so;

3. Having open-minded team members is crucial to a migration project’s success. Too often people have emotional attachments to the platform they are familiar with, possibly out of job security concerns and lack of general curiosity toward new things.

I generally adopt a platform agnostic attitude, and don’t get religious and too carried away on the platform I work on. Having said that, I think these are points that marketing people can spin for persuasion purposes:

1. MySQL has too many storage platforms: MyISAM, InnoDB, MaxDb, and the newly introduced Maria. This can be viewed as a plus, as it provides choice. The downside of it is that it causes confusion for end users;

2. MySQL’s support for relational model is fairly recent. For example, for a long time, MySQL didn’t support Stored Procedures, Views, Triggers, Foreign Keys, etc. One could argue that MySQL is not mature in this area since it is new for them, but I think it is difficult to find evidence to substantiate that claim. Also, running the risk of offending some people, I think the importance of relational model got overblown a bit;

3. Sql Server offers the CLR integration. This can be a great selling point;

4. Sql Server offers tight integration with Visual Studio, Windows network, and all other things Microsoft. This is a huge advantage.

5. Too many people find *nix environment intimidating. Although MySQL works on Windows, but the perception in the marketplace is MySQL works better on *nix.

As far as migrating MySQL to Sql Server in a hosting web environment, my honest opinion is Sql Server will be fighting an uphill battle, because MySQL excels in this arena, especially for small and medium-sized, or departmental organizations, with the proliferation of such LAMP app like blogs, wikies, discussion boards, etc. I believe Microsoft’s weapon of choice in this arena should be SharePoint. Given Microsoft’s clout, it is certainly a battle worth fighting.