tag:blogger.com,1999:blog-27795871828218714172024-03-08T03:14:40.261-05:00Rudy Panigas - Blog on Microsoft SQL ServerWho am I?http://www.blogger.com/profile/11368611239474848109noreply@blogger.comBlogger53125tag:blogger.com,1999:blog-2779587182821871417.post-21737110875574474942016-12-19T15:07:00.000-05:002016-12-19T15:07:44.599-05:00SQL Server Blogs Common SenseHello everyone,<br />
<br /><br />
It has been sometime since my last blog, however I am still here. People have asked me why hasn't my blog been updated for a while. The answer is simple. My blog is different than most. I only write about things that others have not written or ideas and solutions that I have developed because no one else has. With this outlook of my blog, the articles posted are more interesting and original. <br />
<br /><br />
With the release of SQL Server 2016 on Linux, I'm sure you will find many blogs in 2017. This is a new area for me and other DBAs and will relook at my previous blogs and see how they have changed in this new version.<br />
<br /><br />
If you are looking for information on backups, granting user access, error messages, etc. then you should look at the multitude of other blogs and sites with this information. If you are looking for common sense writing then you have found come to the right place.<br />
<br /><br />
Wishing you all the best in 2017 and let's hope it's ever a better year than last.<br />
<br /><br />
RudyWho am I?http://www.blogger.com/profile/11368611239474848109noreply@blogger.com0tag:blogger.com,1999:blog-2779587182821871417.post-54620956651058971482015-08-27T10:58:00.000-04:002015-08-27T11:32:13.075-04:00How to Upgrade SQL Server<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif; mso-bidi-font-weight: bold;"><span style="font-size: x-small;">Hello everyone,</span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif; mso-bidi-font-weight: bold;"><span style="font-size: x-small;"><br /></span></span></div>
<div class="MsoNormal">
<span style="font-size: x-small;"><span style="font-family: "Arial",sans-serif; mso-bidi-font-weight: bold;">In this blog, I will provide an </span><span style="font-family: Arial, sans-serif;">outline of recommended tasks for the upgrade to SQL Server 2014.
You can use this document to also upgrade to other versions.</span></span></div>
<div class="MsoNormal">
<span style="font-family: Arial, sans-serif;"><span style="font-size: x-small;"><br /></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;">To ensure a
successful upgrade of your SQL Server to a new version, we need to review the
following areas<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;"><br /></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;">Pre Work –
Before you start<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;">Data Loads –
Getting your data loaded<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;">Post Work –
Finalize the environment<o:p></o:p></span></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<b><u><span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;">Pre Work – Before you Start<o:p></o:p></span></span></u></b></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;"><br /></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;">1. Hardware and
Software Requirements for Installing SQL Server 2014<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-size: x-small;"><a href="https://msdn.microsoft.com/en-us/library/ms143506.aspx"><span style="font-family: "Arial",sans-serif;">https</span><span style="font-family: "Arial",sans-serif;">://</span><span style="font-family: "Arial",sans-serif;">msdn.microsoft.com/en-us/library/ms143506.aspx</span></a><span style="font-family: "Arial",sans-serif;"><o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;">Review the
minimum hardware and software requirements like editions<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;"><br /></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;">2. The SQL
Server 2014 Upgrade Advisor tool <o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-size: x-small;"><a href="https://msdn.microsoft.com/en-us/library/ee210467.aspx"><span style="font-family: "Arial",sans-serif;">https</span><span style="font-family: "Arial",sans-serif;">://</span><span style="font-family: "Arial",sans-serif;">msdn.microsoft.com/en-us/library/ee210467.aspx</span></a><span style="font-family: "Arial",sans-serif;"><o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;">Analyses the
configuration and recommends changes that need to be made<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;"><br /></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;">3. Review
Breaking Changes <o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-size: x-small;"><a href="https://msdn.microsoft.com/en-us/library/ms143179.aspx"><span style="font-family: "Arial",sans-serif;">https</span><span style="font-family: "Arial",sans-serif;">://</span><span style="font-family: "Arial",sans-serif;">msdn.microsoft.com/en-us/library/ms143179.aspx</span></a><span style="font-family: "Arial",sans-serif;"><o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;">New changes to
SQL Server may break applications, scripts and/or functions that are bases on
earlier versions of SQL Server<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;"><br /></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;">4. Review
Behavioral Changes <o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-size: x-small;"><a href="https://msdn.microsoft.com/en-us/library/ms143359.aspx"><span style="font-family: "Arial",sans-serif;">https://</span><span style="font-family: "Arial",sans-serif;">msdn.microsoft.com/en-us/library/ms143359.aspx</span></a><span style="font-family: "Arial",sans-serif;"><o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;">Behavior
changes affect how features work and/or interact in the new versions<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;"><br /></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;">5. Review
Release Notes <o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-size: x-small;"><a href="https://msdn.microsoft.com/en-us/library/dn169381(v=sql.15).aspx"><span style="font-family: "Arial",sans-serif;">https://msdn.microsoft.com/en-us/library/dn169381(v=sql.15).</span><span style="font-family: "Arial",sans-serif;">aspx</span></a><span style="font-family: "Arial",sans-serif;"><o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;">These notes
describes the known issues with current version and should be reviewed before
installation<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;">6. Document all
your findings <o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;">Record what you
have learned. Update this document as you go along with the upgrade<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;"><br /></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;">7. Database
Instant File Initialization <o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-size: x-small;"><a href="https://msdn.microsoft.com/en-us/library/ms175935.aspx"><span style="font-family: "Arial",sans-serif;">https://</span><span style="font-family: "Arial",sans-serif;">msdn.microsoft.com/en-us/library/ms175935.aspx</span></a><span style="font-family: "Arial",sans-serif;"><o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;">Turn on this
feature in the OS to help speed up the creation of databases<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;"><br /></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;">8. Start the
installation of SQL Server <o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-size: x-small;"><a href="https://technet.microsoft.com/en-us/sqlserver/ff803383.aspx"><span style="font-family: "Arial",sans-serif;">https://</span><span style="font-family: "Arial",sans-serif;">technet.microsoft.com/en-us/sqlserver/ff803383.aspx</span></a><span style="font-family: "Arial",sans-serif;"><o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;">Ensure that you
install the latest service packs and review cumulative patches<o:p></o:p></span></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<b><u><span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;">Data Loads – Getting your Data Loaded</span></span></u></b></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;"><br /></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;">1. When creating
a new database(s) place data files, partitions and logs on to different
disk <o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;"><br /></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;">2. Create
multiple TempDB files on to different disks (use SSD drives)<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-size: x-small;"><a href="https://social.msdn.microsoft.com/Forums/sqlserver/en-US/6e193115-5b6a-47d6-869b-795ee88bd4a8/tempdb-multiple-files?forum=sqlgetstarted"><span style="font-family: "Arial",sans-serif;">https://</span><span style="font-family: "Arial",sans-serif;">social.msdn.microsoft.com/Forums/sqlserver/en-US/6e193115-5b6a-47d6-869b-795ee88bd4a8/tempdb-multiple-files?forum=sqlgetstarted</span></a><span style="font-family: "Arial",sans-serif;"><o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;"><br /></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;">3. Create an
automated backup job <o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-left: .5in; mso-list: l1 level1 lfo1; tab-stops: list .5in; text-indent: -.25in;">
<br />
<ul>
<li><span style="font-family: Arial, sans-serif; font-size: x-small;">Back up all databases (include system and
empty databases)</span></li>
<li><span style="font-family: Arial, sans-serif; font-size: x-small;">Verify that backup locations are correct</span></li>
<li><span style="font-family: Arial, sans-serif; font-size: x-small;">Use built in compression</span></li>
</ul>
</div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;"><br /></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;">4. Load user
database data by restoring from ** latest full ** backups<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;"><br /></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;">5. Compatibility
level. Ensure that the restored databases are set to compatibility level of new
installation. If not set may result in possible issues and you won't be able to
take advantage of the new features<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-left: 1.0in; mso-list: l0 level2 lfo2; tab-stops: list 1.0in; text-indent: -.25in;">
<br />
<ul>
<li><span style="font-family: Arial, sans-serif; font-size: x-small;">Compatibility level for 2014 is 120</span></li>
<li><span style="font-family: Arial, sans-serif; font-size: x-small;">Compatibility level for 2012 is 110</span></li>
<li><span style="font-family: Arial, sans-serif; font-size: x-small;">Compatibility level for 2008 and 2008R2
is 100 </span></li>
<li><span style="font-family: Arial, sans-serif; font-size: x-small;">Compatibility level for 2005 is 90</span></li>
</ul>
</div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;"><br /></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;">6. Recovery Model. Document the current recovery
models and ensure that the restored database are set to the same<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;"><br /></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;">7. Database
Ownership. Change the ownership of databases once they are restored to “sa”<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;"><br /></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;">8. Collation of
all User databases. Review the collation setting for all databases are set
correctly<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-size: x-small;"><br /></span></div>
<div class="MsoNormal">
<b><u><span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;">Post Work – Finalize the Environment</span></span></u></b></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;"><br /></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;">1. DBCC CHECKDB
WITH DATA_PURITY <o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;">Causes DBCC
CHECKDB to check the database for column values that are not valid or
out-of-range <o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;">Execute this
check on “all” databases <o:p></o:p></span></span><br />
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;"><br /></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;"> </span><span style="font-size: xx-small;">DBCC CHECKDB WITH DATA_PURITY;</span><span style="font-size: x-small;"><o:p></o:p></span></span></div>
<div class="MsoNormal">
<br />
<span style="font-size: x-small;"><a href="https://msdn.microsoft.com/en-s/library/ms176064(v=sql.120).aspx"><span style="font-family: "Arial",sans-serif;">https</span><span style="font-family: "Arial",sans-serif;">://</span><span style="font-family: "Arial",sans-serif;">msdn.microsoft.com/en-s/library/ms176064(v=sql.120</span><span style="font-family: "Arial",sans-serif;">).</span><span style="font-family: "Arial",sans-serif;">aspx</span></a><span style="font-family: "Arial",sans-serif;"><o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;"><br /></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;">2. DBCC
UPDATEUSAGE <o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;">Reports and
corrects pages and row count inaccuracies in the catalog views. These
inaccuracies may cause incorrect space usage reports returned by the
sp_spaceused system stored procedure.<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;">Execute this check on “all”
databases<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;"><br /></span></span>
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;"> </span><span style="font-size: xx-small;">DBCC UPDATEUSAGE(db_name);</span><span style="font-size: x-small;"><o:p></o:p></span></span></div>
<div class="MsoNormal">
<br />
<span style="font-size: x-small;"><a href="https://msdn.microsoft.com/en-us/library/ms188414(v=sql.120).aspx"><span style="font-family: "Arial",sans-serif;">https</span><span style="font-family: "Arial",sans-serif;">://</span><span style="font-family: "Arial",sans-serif;">msdn.microsoft.com/en-us/library/ms188414(v=sql.120</span><span style="font-family: "Arial",sans-serif;">).</span><span style="font-family: "Arial",sans-serif;">aspx</span></a><span style="font-family: "Arial",sans-serif;"><o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;"><br /></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;">3. Update Statistics
<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;"> Ensure you update statistics on all your
databases<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;"><br /></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: xx-small;"> USE db_name;<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: xx-small;"> GO<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: xx-small;"> EXEC sp_MSforeachtable
@command1='UPDATE STATISTICS ? WITH FULLSCAN';</span><span style="font-size: x-small;"><o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;"><br /></span></span></div>
<div class="MsoNormal">
<span style="font-size: x-small;"><a href="https://msdn.microsoft.com/en-us/library/ms187348(v=sql.120).aspx"><span style="font-family: "Arial",sans-serif;">https://msdn.microsoft.com/en-us/library/ms187348(v=sql.120).</span><span style="font-family: "Arial",sans-serif;">aspx</span></a><span style="font-family: "Arial",sans-serif;"> <o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;"><br /></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;">4. Refresh all
Views <o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;"> Use sp_refreshview command<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-size: x-small;"><a href="https://msdn.microsoft.com/en-us/library/ms187821(v=sql.120).aspx"><span style="font-family: "Arial",sans-serif;">https://msdn.microsoft.com/en-us/library/ms187821(v=sql.120).</span><span style="font-family: "Arial",sans-serif;">aspx</span></a><span style="font-family: "Arial",sans-serif;"> <o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;"><br /></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;">5. Perform
backups on “all” databases, yes again!<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;"><br /></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;">6. Check your
compatibility levels<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-size: x-small;"><a href="https://msdn.microsoft.com/en-us/library/bb933794.aspx"><span style="font-family: "Arial",sans-serif;">https://</span><span style="font-family: "Arial",sans-serif;">msdn.microsoft.com/en-us/library/bb933794.aspx</span></a><span style="font-family: "Arial",sans-serif;"> <o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;"><br /></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;">7. Update your
finding document<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;"><br /></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;">8. Script out
your logins from source and apply to new SQL Server. Also check for orphan logins <o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-size: x-small;"><a href="https://gallery.technet.microsoft.com/scriptcenter/Get-logins-databases-816f66b2"><span style="font-family: "Arial",sans-serif;">https://</span><span style="font-family: "Arial",sans-serif;">gallery.technet.microsoft.com/scriptcenter/Get-logins-databases-816f66b2</span></a><span style="font-family: "Arial",sans-serif;"> <o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;"><br /></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;">9. Script out
and recreate link servers, SQL jobs, SSIS packages, SSAS and SSRS and any other
objects that have been missed<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;"><br /></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;">10. Automate
Backup Databases<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;">Perform full
database backups often and create automated backup jobs<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;"><br /></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;">11. Review
Error Logs<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial",sans-serif;"><span style="font-size: x-small;">Ensure your
error logs are clean before using SQL Server. <o:p></o:p></span></span></div>
<span style="font-family: Cambria, serif; line-height: 120%;"><span style="font-size: x-small;"><a href="https://msdn.microsoft.com/en-us/library/ms187109.aspx"><span style="font-family: "Arial",sans-serif;">https</span><span style="font-family: "Arial",sans-serif;">://</span><span style="font-family: Arial, sans-serif;">msdn.microsoft.com/en-us/library/ms187109.aspx</span></a></span></span><br />
<br />Who am I?http://www.blogger.com/profile/11368611239474848109noreply@blogger.com0tag:blogger.com,1999:blog-2779587182821871417.post-53929180049417695642015-04-22T09:47:00.000-04:002015-04-22T09:47:45.054-04:00SQL Server Management Studios (SSMS) Not Working Properly Exception from HRESULT: 0X8002801D Type_E_LIBNOTREGISTERED<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif; font-size: x-small;">Hello all,</span><br />
<span style="font-family: Arial, Helvetica, sans-serif; font-size: x-small;"><br /></span>
<div class="MsoNormal">
<span style="font-size: x-small;"><span style="font-family: Arial, Helvetica, sans-serif;">I have found the
issue and fix to the problem I've had experiencing with </span><span style="font-family: Arial, Helvetica, sans-serif;">SQL Server Management Studios</span><span style="font-family: Arial, Helvetica, sans-serif;"> not working on a
server. <o:p></o:p></span></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Arial, Helvetica, sans-serif; font-size: x-small;"><b><u>Issue:</u></b> SQL Server Management Studios (SSMS)
does not work properly<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Arial, Helvetica, sans-serif; font-size: x-small;">
and you get the following error: <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Arial, Helvetica, sans-serif; font-size: x-small;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: Arial, Helvetica, sans-serif; font-size: x-small;"> <b>Exception
from HRESULT: 0X8002801D Type_E_LIBNOTREGISTERED<o:p></o:p></b></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<b><u><span style="font-family: Arial, Helvetica, sans-serif;"><span style="font-size: x-small;">Resolution:</span><o:p></o:p></span></u></b></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Arial, Helvetica, sans-serif; font-size: x-small;">First check if
the following is in place.<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Arial, Helvetica, sans-serif; font-size: x-small;">1. Run
REGEDIT<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Arial, Helvetica, sans-serif; font-size: x-small;">2. Expand
- <span style="color: #385723;">HKEY_CLASSES_ROOT</span><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Arial, Helvetica, sans-serif; font-size: x-small;">3. Expand
- <span style="color: #385723;">TypeLib</span><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Arial, Helvetica, sans-serif; font-size: x-small;">4. Expand
- <span style="color: #385723;">(91A74EB0-EFA0-482B-B43C-35CFC74B275F)<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: Arial, Helvetica, sans-serif; font-size: x-small;">5. Expand
- <span style="color: #385723;">a.0</span><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Arial, Helvetica, sans-serif; font-size: x-small;">6. You
should see the following keys<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="color: #385723;"><span style="font-family: Arial, Helvetica, sans-serif; font-size: x-small;">
0<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: Arial, Helvetica, sans-serif; font-size: x-small;">
<span style="color: #385723;">FLAGS</span><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Arial, Helvetica, sans-serif; font-size: x-small;">
<span style="color: #385723;">HELPDIR</span><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Arial, Helvetica, sans-serif; font-size: x-small;">7. Expand
- the key called <span style="color: #385723;">0</span> (zero)<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Arial, Helvetica, sans-serif; font-size: x-small;">8. Expand
- <span style="color: #385723;">win32</span><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Arial, Helvetica, sans-serif; font-size: x-small;">9. Right
click on <span style="color: #385723;">(Default)<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: Arial, Helvetica, sans-serif; font-size: x-small;">10. You now
should see the following: </span></div>
<div class="MsoNormal">
<span style="color: #385723; font-family: Arial, Helvetica, sans-serif; font-size: x-small;"> </span><span style="color: #385723; font-family: Arial, Helvetica, sans-serif; font-size: xx-small;">C:\Program Files
(x86)\Microsoft Visual Studio 10.0\Common7\IDE\dteproperties.tlb</span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Arial, Helvetica, sans-serif; font-size: x-small;">Next if the
steps 6 - 10 are missing, create the keys as above and restart SSMS.<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Arial, Helvetica, sans-serif; font-size: x-small;">If the error
still continues then check the path of the "dteproperties.tlb" and
modify the above with the proper path<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Arial, Helvetica, sans-serif; font-size: x-small;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: Arial, Helvetica, sans-serif; font-size: x-small;">The issues is
now resolved.</span></div>
<br />
<div class="MsoNormal">
<span style="font-size: x-small;"><span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></span></div>
<div class="MsoNormal">
<span style="font-size: x-small;"><span style="font-family: Arial, Helvetica, sans-serif;">Thanks,</span><span style="font-family: Arial, sans-serif;"><o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: Arial, Helvetica, sans-serif; font-size: x-small;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: Arial, Helvetica, sans-serif; font-size: x-small;">Rudy</span></div>
Who am I?http://www.blogger.com/profile/11368611239474848109noreply@blogger.com0tag:blogger.com,1999:blog-2779587182821871417.post-90844789952443675862015-02-23T11:21:00.000-05:002015-03-30T11:27:34.322-04:00<h1 class="MinMargin" itemprop="name">
<span style="font-family: Arial, Helvetica, sans-serif; font-size: large;">Quick SQL Server Configuration Summary
Report</span></h1>
<span style="font-family: Arial, Helvetica, sans-serif;">Hello everyone,</span><br />
<br />
<div class="content-text" itemprop="articleBody" sizcache="0" sizset="3">
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">I have written this script to detect and display a quick summary of your SQL Server
installation/environment. </span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Download this (updated March 30, 2015) script here <a href="https://drive.google.com/file/d/0B07PMQYOhF2DU2RZWlpkcklua0U/view?usp=sharing" target="_blank">Detail Configuration Script</a></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">To view the report, open the script in SSMS and execute.There are temp tables
created and dropped at end of execution.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Here is a list of information produced in the reported. </span><br />
<div sizcache="0" sizset="3">
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></div>
<div sizcache="0" sizset="3">
<span style="font-family: Arial, Helvetica, sans-serif;">Display SQL Server name\Instance name<br />Display
Installation Date<br />Display Machine Name <br />Display Instance
Name<br />Display Edition and BIT Level<br />Display Production Service Pack
Level <br />Display Production Name <br />Display Production
Version<br />Display Logical CPU Count<br />Display Maximum Memory
(Megabytes)<br />Display Minimum Memory (Megabytes)<br />Display IP
Address<br />Display Port Number<br />Display Default Domain Name<br />Display Service
Account name<br />Display Clustered Status<br />Display Kerberos<br />Display Security
Mode<br />Display Audit Level<br />Display User Mode<br />Display Collation
Type<br />Display SQL Server Errorlog Location<br />Display SQL Server Default Trace
Location<br />Display Number of Link Servers<br />Display SysAdmin
Members<br />Display ServerAdmin Members<br />Display Configuration
setting<br />Display code that automatically executes on startup<br />Display SQL
Service Status<br />Display Location of Database files<br />Display Link
Servers<br />Display Database Collation type<br />Display Database Hard Drive Space
Available<br />Display Database Information<br />Display Database Backup
Information<br />Display SQL Job Status<br />Display SQL Mail Information<br />Display
Database Mirroring Status<br />Display Database Log Shipping Status<br />Display
Report Server (SSRS) Reports Information </span></div>
<div sizcache="0" sizset="42">
<span style="font-family: Arial, Helvetica, sans-serif;"><br />Please execute on your test/development
environment and verify results. </span></div>
<span style="font-family: Arial, Helvetica, sans-serif;">I will be adding more to this script in the future so watch for updates. </span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Please let me know you comments. </span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Thanks, </span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Rudy </span></div>
Who am I?http://www.blogger.com/profile/11368611239474848109noreply@blogger.com0tag:blogger.com,1999:blog-2779587182821871417.post-80585973993340745422015-01-13T11:22:00.002-05:002015-01-13T11:22:53.040-05:00Auto Generating of Service Principal Name (SPN) Commands<span style="font-family: Arial, Helvetica, sans-serif;">Hello All,</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Have you had to use </span><span style="font-family: Arial, Helvetica, sans-serif;">Kerberos authentication </span><span style="font-family: Arial, Helvetica, sans-serif;">and needed to create the commands for the Service Principal Name (SPN)? </span><span style="font-family: Arial, Helvetica, sans-serif;">Then the script in this blog will help. Execute the script on the SQL Server with SQL Server Reporting Services (SSRS) and it will automatically generate the commands needed. </span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">If you need more details on Kerberos have a look at Rob Carrol blog. It is very good and covers how to enable Kerberos authentication for reporting services. Here is his link.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><a href="http://blogs.technet.com/b/rob/archive/2011/11/23/enabling-kerberos-authentication-for-reporting-services.aspx">http://blogs.technet.com/b/rob/archive/2011/11/23/enabling-kerberos-authentication-for-reporting-services.aspx</a></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Now on to my script.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">First, load this script on to your server (please verify on test servers) and execute. It only creates temp tables, does not write anything to SQL Server and the temp tables are deleted once executed.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">The script will automatically detects the SQL Server settings</span><br />
<br />
<span style="color: #6aa84f; font-family: Arial, Helvetica, sans-serif; font-size: x-small;">******** Settings that have been automatically detected **************</span><br />
<br />
<span style="color: #6aa84f; font-family: Arial, Helvetica, sans-serif; font-size: x-small;">Result for detection of SQL Server name --> SrvName</span><br />
<span style="color: #6aa84f; font-family: Arial, Helvetica, sans-serif; font-size: x-small;">Result for detection of Service Account name -->MyDomain\svcAccount</span><br />
<span style="color: #6aa84f; font-family: Arial, Helvetica, sans-serif; font-size: x-small;">Result for detection of Port Number --> 1433</span><br />
<span style="color: #6aa84f; font-family: Arial, Helvetica, sans-serif; font-size: x-small;">Result for detection of default Domain Name --> MyDomain</span><br />
<span style="color: #6aa84f; font-family: Arial, Helvetica, sans-serif; font-size: x-small;">Result for detection of cluster --> SQL Server is not clustered</span><br />
<span style="color: #6aa84f; font-family: Arial, Helvetica, sans-serif; font-size: x-small;">Result for detection of Kerberos --> TCP is using Kerberos</span><br />
<span style="color: #6aa84f; font-family: Arial, Helvetica, sans-serif; font-size: x-small;">Result for detection of IP Address --> 192.168.54.1</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Next, the script will take the information found and create the SETSPN commands </span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<br />
<span style="color: #6aa84f; font-family: Arial, Helvetica, sans-serif; font-size: x-small;">cls</span><br />
<span style="color: #6aa84f; font-family: Arial, Helvetica, sans-serif; font-size: x-small;">echo '*** Automatic SQL Server Service Principal Name (SPN) ***'</span><br />
<span style="color: #6aa84f; font-family: Arial, Helvetica, sans-serif; font-size: x-small;">echo ' '</span><br />
<span style="color: #6aa84f; font-family: Arial, Helvetica, sans-serif; font-size: x-small;">echo 'Setting SQL Server Database SPN...please wait'</span><br />
<span style="color: #6aa84f; font-family: Arial, Helvetica, sans-serif; font-size: x-small;">echo ' '</span><br />
<span style="color: #6aa84f; font-family: Arial, Helvetica, sans-serif; font-size: x-small;"> </span><br />
<span style="color: #6aa84f; font-family: Arial, Helvetica, sans-serif; font-size: x-small;">SetSPN -s "MSSQLSvc/SQLServerName:1433" "MyDomain\svcAccount"</span><br />
<span style="color: #6aa84f; font-family: Arial, Helvetica, sans-serif; font-size: x-small;">SetSPN -s "MSSQLSvc/SrvName.MyDomain.com:1433" "MyDomain\svcAccount"</span><br />
<br />
<br />
<span style="color: #6aa84f; font-family: Arial, Helvetica, sans-serif; font-size: x-small;">echo ' '</span><br />
<span style="color: #6aa84f; font-family: Arial, Helvetica, sans-serif; font-size: x-small;">echo 'Setting SQL Server Reporting Services SPN...please wait'</span><br />
<span style="color: #6aa84f; font-family: Arial, Helvetica, sans-serif; font-size: x-small;">echo ' '</span><br />
<br />
<span style="color: #6aa84f; font-family: Arial, Helvetica, sans-serif; font-size: x-small;">SetSPN -s "http/SrvName" "MyDomain\svcAccount"</span><br />
<span style="color: #6aa84f; font-family: Arial, Helvetica, sans-serif; font-size: x-small;">SetSPN -s "http/SrvName.MyDomain.com" "MyDomain\svcAccount"</span><br />
<br />
<br />
<span style="color: #6aa84f; font-family: Arial, Helvetica, sans-serif; font-size: x-small;">echo ' '</span><br />
<span style="color: #6aa84f; font-family: Arial, Helvetica, sans-serif; font-size: x-small;">echo 'Setting SQL Server Analysis Services SPN...please wait'</span><br />
<span style="color: #6aa84f; font-family: Arial, Helvetica, sans-serif; font-size: x-small;">echo ' '</span><br />
<br />
<span style="color: #6aa84f; font-family: Arial, Helvetica, sans-serif; font-size: x-small;">SetSPN -s "msolapsvc.3/SrvName" "MyDomain\svcAccount"</span><br />
<span style="color: #6aa84f; font-family: Arial, Helvetica, sans-serif; font-size: x-small;">SetSPN -s "msolapsvc.3/SrvName.MyDomain.com" "MyDomain\svcAccount"</span><br />
<br />
<span style="color: #6aa84f; font-family: Arial, Helvetica, sans-serif; font-size: x-small;">echo ' '</span><br />
<span style="color: #6aa84f; font-family: Arial, Helvetica, sans-serif; font-size: x-small;">SetSPN -l MyDomain\svcAccount"</span><br />
<span style="color: #6aa84f; font-family: Arial, Helvetica, sans-serif; font-size: x-small;">echo ' '</span><br />
<br />
<span style="color: #6aa84f; font-family: Arial, Helvetica, sans-serif; font-size: x-small;"> ==> Auto SPN generation is now complete <==</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">Finally, copy the SetSPN statements and execute then with a Domain Admin account. </span><span style="font-family: Arial, Helvetica, sans-serif;"> Verify that the account "MyDomain\svcAccount" has the following check on </span><span style="font-family: Arial, Helvetica, sans-serif;">"<span style="color: #cc0000;">Trust this user for delegation to any service [Kerberos only]</span>" </span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Note that once the SetSPN command is executed it may take</span><span style="font-family: Arial, Helvetica, sans-serif;"> several hours (once mine took approx. 8) before it takes effect.</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">Here is the link for my script</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><a href="https://drive.google.com/file/d/0B07PMQYOhF2DMEJMcnpGVDg3eFE/view?usp=sharing">https://drive.google.com/file/d/0B07PMQYOhF2DMEJMcnpGVDg3eFE/view?usp=sharing</a></span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">Thanks,</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Rudy</span><br />
<br />
<br />Who am I?http://www.blogger.com/profile/11368611239474848109noreply@blogger.com0tag:blogger.com,1999:blog-2779587182821871417.post-52023518755096206162015-01-08T14:47:00.002-05:002015-01-08T14:47:57.369-05:00SQL Server Performance Survival Guide<span style="font-family: Arial, Helvetica, sans-serif;">Hello and Happy New Year!</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Today's blog is a link to the Microsoft Tech Net site that has an excellent guide regarding SQL Server performance. It is worth a look and a place in your browser's bookmarks.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;"><a href="http://social.technet.microsoft.com/wiki/contents/articles/5957.sql-server-performance-survival-guide.aspx">http://social.technet.microsoft.com/wiki/contents/articles/5957.sql-server-performance-survival-guide.aspx</a></span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">Below is the table of contents</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Learning about Troubleshooting SQL Server Performance</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">Transaction Processing (OLTP)</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">Data Warehouse Performance</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">Troubleshooting Hardware Issues</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">Monitoring and Performance Tools</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">Performance Tips and Tricks</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">Performance Topics in SQL Server Books Online</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">Getting Advice and Guidance</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">SQL Server Customer Advisory Team</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">Community Resources</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">SQL Server Performance Blogs</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">SQL Server Performance Forums</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">SQL Server Resource Centers</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">SQL Server Performance Workshops</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">SQL Server Social Networking</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">Twitter</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">SQL Server Performance on Facebook</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">SQL Server Performance Books</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Hope you find this useful,</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Rudy</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>Who am I?http://www.blogger.com/profile/11368611239474848109noreply@blogger.com0tag:blogger.com,1999:blog-2779587182821871417.post-70930687017479653632014-12-22T10:30:00.002-05:002014-12-22T10:30:49.083-05:00SSRS Subscription Status<span style="font-family: Arial, Helvetica, sans-serif;">Need to see the status of a report subscription in SSRS? Well now you can. This script, see download link below, will provide the following details.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<br />
<ul>
<li><span style="font-family: Arial, Helvetica, sans-serif;">Event type</span></li>
<li><span style="font-family: Arial, Helvetica, sans-serif;">Executed by</span></li>
<li><span style="font-family: Arial, Helvetica, sans-serif;">Inactive or active </span></li>
<li><span style="font-family: Arial, Helvetica, sans-serif;">Last status</span></li>
<li><span style="font-family: Arial, Helvetica, sans-serif;">Description</span></li>
<li><span style="font-family: Arial, Helvetica, sans-serif;">Last run status</span></li>
<li><span style="font-family: Arial, Helvetica, sans-serif;">Delivery extension</span></li>
<li><span style="font-family: Arial, Helvetica, sans-serif;">Date modified</span></li>
</ul>
<br />
<a href="https://drive.google.com/file/d/0B07PMQYOhF2DWWhER19jazFEdk0/view?usp=sharing">https://drive.google.com/file/d/0B07PMQYOhF2DWWhER19jazFEdk0/view?usp=sharing</a><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">Enjoy!</span>Who am I?http://www.blogger.com/profile/11368611239474848109noreply@blogger.com0tag:blogger.com,1999:blog-2779587182821871417.post-32252755752256981702014-12-22T10:19:00.002-05:002014-12-22T10:19:40.599-05:00View User Access to your Reports in SSRS<span style="font-family: Arial, Helvetica, sans-serif;">How to see what access users have to your reports in SQL Server Reporting Service (SSRS)? Well now you can view the access with the following script, download it here </span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;"><a href="https://drive.google.com/file/d/0B07PMQYOhF2DWWhER19jazFEdk0/view?usp=sharing">https://drive.google.com/file/d/0B07PMQYOhF2DWWhER19jazFEdk0/view?usp=sharing</a></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">The script will show the following information
<a href="https://drive.google.com/file/d/0B07PMQYOhF2DWWhER19jazFEdk0/view?usp=sharing"></a>
</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<br />
<ul>
<li><span style="font-family: Arial, Helvetica, sans-serif;">Role name</span></li>
<li><span style="font-family: Arial, Helvetica, sans-serif;">User name</span></li>
<li><span style="font-family: Arial, Helvetica, sans-serif;">Path of report</span></li>
<li><span style="font-family: Arial, Helvetica, sans-serif;">Report name</span></li>
<li><span style="font-family: Arial, Helvetica, sans-serif;">Catalog type</span></li>
<li><span style="font-family: Arial, Helvetica, sans-serif;">Description</span></li>
</ul>
<div>
<span style="font-family: Arial, Helvetica, sans-serif;">Hope you find it useful.</span></div>
<div>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></div>
<div>
<span style="font-family: Arial, Helvetica, sans-serif;">Enjoy</span></div>
Who am I?http://www.blogger.com/profile/11368611239474848109noreply@blogger.com0tag:blogger.com,1999:blog-2779587182821871417.post-42640061983268773942014-12-19T15:51:00.000-05:002014-12-19T15:51:29.203-05:00Memory Usage by All Databases<span style="font-family: Arial, Helvetica, sans-serif;">Hello,</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Do you know how much memory your databases are using and how much free memory you SQL Server has? Try this script below. </span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">The output will show the following:</span><br />
<br />
<ul>
<li><span style="font-family: Arial, Helvetica, sans-serif;">List all the databases with how much memory they are using</span></li>
<li><span style="font-family: Arial, Helvetica, sans-serif;">Total usage for all databases</span></li>
<li><span style="font-family: Arial, Helvetica, sans-serif;">Max memory set in SQL Server</span></li>
<li><span style="font-family: Arial, Helvetica, sans-serif;">Memory available by subtracting the 2 values from above.</span></li>
</ul>
<div>
<span style="font-family: Arial, Helvetica, sans-serif;">Enjoy!</span></div>
<br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif; font-size: xx-small;">DECLARE </span><br />
<span style="font-family: Arial, Helvetica, sans-serif; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span> @TotalUsedByDatabases NUMERIC</span><br />
<span style="font-family: Arial, Helvetica, sans-serif; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>,@TotalMAXMemorySQLSrv NUMERIC</span><br />
<span style="font-family: Arial, Helvetica, sans-serif; font-size: xx-small;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif; font-size: xx-small;">SELECT</span><br />
<span style="font-family: Arial, Helvetica, sans-serif; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span> isnull(db_name(database_id), 'resourcedb') [dbname]</span><br />
<span style="font-family: Arial, Helvetica, sans-serif; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>, CAST(COUNT(row_count)/128.0 as decimal(10,2)) [size]</span><br />
<span style="font-family: Arial, Helvetica, sans-serif; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>INTO #RAM</span><br />
<span style="font-family: Arial, Helvetica, sans-serif; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>FROM sys.dm_os_buffer_descriptors</span><br />
<span style="font-family: Arial, Helvetica, sans-serif; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>GROUP BY database_id</span><br />
<span style="font-family: Arial, Helvetica, sans-serif; font-size: xx-small;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif; font-size: xx-small;">SELECT dbname [Name of Databases], size [Memory Usage in MB] FROM #RAM</span><br />
<span style="font-family: Arial, Helvetica, sans-serif; font-size: xx-small;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif; font-size: xx-small;">SET @TotalUsedByDatabases = (SELECT SUM(size) FROM #RAM);</span><br />
<span style="font-family: Arial, Helvetica, sans-serif; font-size: xx-small;">SET @TotalMAXMemorySQLSrv = (SELECT CAST(value_in_use as NUMERIC) </span><br />
<span style="font-family: Arial, Helvetica, sans-serif; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>FROM sys.configurations </span><br />
<span style="font-family: Arial, Helvetica, sans-serif; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>WHERE name LIKE 'max server memory%');</span><br />
<span style="font-family: Arial, Helvetica, sans-serif; font-size: xx-small;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif; font-size: xx-small;">SELECT @TotalUsedByDatabases [Total Memory used by Databases in MB]</span><br />
<span style="font-family: Arial, Helvetica, sans-serif; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span> , @TotalMAXMemorySQLSrv [Max Memory Allocated to SQL Server in MB]</span><br />
<span style="font-family: Arial, Helvetica, sans-serif; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span> , @TotalMAXMemorySQLSrv - @TotalUsedByDatabases [ ** Memory Available ** in MB]</span><br />
<span style="font-family: Arial, Helvetica, sans-serif; font-size: xx-small;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif; font-size: xx-small;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif; font-size: xx-small;">DROP TABLE #RAM;</span><br />
<span style="font-family: Arial, Helvetica, sans-serif; font-size: xx-small;"><br /></span>Who am I?http://www.blogger.com/profile/11368611239474848109noreply@blogger.com0tag:blogger.com,1999:blog-2779587182821871417.post-39316511513352172872014-11-25T16:16:00.000-05:002014-11-25T16:16:00.026-05:00SQL Server 2014 Checklist for Performance<span style="font-family: Arial, Helvetica, sans-serif;">Hello once again :)</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Over time and discussing with other DBAs, I have compiled a checklist for performance. Here are some areas and comments for each. </span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<b style="font-family: Arial, Helvetica, sans-serif;">SQL Server 2014 Checklist for Performance</b><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">- Test your changes on your test servers</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">- Make changes incrementally - small change at a time</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">- Use 64 bit, even on a laptop</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;"><b>Memory</b></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">Set MIN and MAX values for memory. Max setting use script to calculate</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">Enable Optimize for Ad Hoc Workloads</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;"><b>CPU</b></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">Set "Cost Threshold for Parallelism" = OLTP = 45 and Reporting = 25. Default is 5 which is to low</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Set "Max Degree of Parallelism" leave it on after you have changed the cost threshold. </span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Set NUMA = number of physical processors, not cores, is a good place to start</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;"><b>Disk</b></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">TEMPDB on separate disks,</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Set TEMPDB data and logs onto separate disks</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Use multiple files wtih equal sizes, not equal to the number of processors</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Use index compression</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">More disks is better but limited to the number of controllers</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;"><b>Statistics</b></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">Enable AUTO_CREATE and AUTO_UPDATE<span class="Apple-tab-span" style="white-space: pre;"> </span></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Make plans to manual updates on statistics with full scans</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;"><b>Defragment Indexes</b></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">Number of pages matter,defrag below 300-500 pages</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Cannot defrag below 8 pages</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Defragment indexes with less then 50% fragmentation and rebuilt index if higher than 50%</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;"><b>DO NOT USE </b></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">Disable AUTO_CLOSE</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Disable AUTO_SHRINK</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">DO NOT use Profiler GUI, use extended events, even in SQL 2008</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Create server side trace using T-SQL scripts. Can you use the GUI to create but execute via T-SQL</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;"><b>Database Design</b></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">Separate log and data files onto separate disks</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Use multiple file groups even on a single disk</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Turn off AUTO_GROW (depends). If not, use fixed growth, not percentage. Do not leave defaults in place</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Normalize the data as it benefits performance</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Enforce constraints, have foreign keys, primary keys, unique indexes</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Use narrow indexes, when possible</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Indexes work better on integers - performance better</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Don't create too many indexes (depends)</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Rebuilt cluster indexes </span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;"><b>Coding</b></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">Return only use data you need</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Use stored procedures or parameterized queries</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Avoid cursors, WHILE, LOOP</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Quality all object names</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Avoid using sp_* stored procedure names</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Avoid functions on columns and LIKE command</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">SET NOCOUNT ON</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Don't nest the views and join views to views</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Don't use NOLOCK </span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Avoid recompiling execution plans</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Use table variables instead of temp tables</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Multi statement table valued functions are very bad!</span><br />
<span class="Apple-tab-span" style="white-space: pre;"><span style="font-family: Arial, Helvetica, sans-serif;"> </span></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">Let me know if you have any additional checks to add</span><br />
<span class="Apple-tab-span" style="white-space: pre;"><span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></span>
<span class="Apple-tab-span" style="white-space: pre;"><span style="font-family: Arial, Helvetica, sans-serif;">Thanks,</span></span><br />
<span class="Apple-tab-span" style="white-space: pre;"><span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></span>
<span class="Apple-tab-span" style="white-space: pre;"><span style="font-family: Arial, Helvetica, sans-serif;">Rudy </span></span><br />
<br />Who am I?http://www.blogger.com/profile/11368611239474848109noreply@blogger.com0tag:blogger.com,1999:blog-2779587182821871417.post-7851460914149291182014-11-25T15:21:00.000-05:002014-11-25T15:21:04.888-05:00Are you Ready for a New SQL Server Version?<span style="font-family: Arial, Helvetica, sans-serif;">Hello again,</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">As you are aware Microsoft like to provide DBAs and developers with more work every two years. So, are you ready for a new version of SQL Server and all the migration that goes along with it?</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">If you are not upgrading then you're lucky, for now. Most DBA/Developers I know are in the middle of upgrading to SQL Server 2012/2014.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">If you are upgrading, then I would highly recommend reading an article from Michael J. Swart called "Developers, Ready for a New SQL Server Version?" </span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;"><a href="http://michaeljswart.com/2014/11/devs_ready_for_a_new_version/">http://michaeljswart.com/2014/11/devs_ready_for_a_new_version/</a></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">It's an excellent articles for developers and DBAs.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Thanks,</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Rudy</span>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
Who am I?http://www.blogger.com/profile/11368611239474848109noreply@blogger.com0tag:blogger.com,1999:blog-2779587182821871417.post-64034230918880799242014-05-20T14:37:00.004-04:002014-05-20T14:55:58.358-04:00Antivirus SQL Server Recommended Setting<div class="MsoNormal">
<span style="font-family: Arial, sans-serif; font-size: 9pt; line-height: 107%;">Hello Again,</span></div>
<div class="MsoNormal">
<span style="font-family: "Arial","sans-serif"; font-size: 9.0pt; line-height: 107%;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: Arial, sans-serif; font-size: 9pt; line-height: 107%;">This blog will show the Microsoft Support recommended
antivirus (AV) setting for SQL Server. Each version of SQL Server will be
listed below with different setting for each version. Below are the directories
and file-name extensions that must be exclude from AV scanning.</span></div>
<div class="MsoNormal">
<span style="font-family: "Arial","sans-serif"; font-size: 9.0pt; line-height: 107%;"><br /></span></div>
<div class="MsoNormal">
<b><u><span style="font-family: "Arial","sans-serif"; font-size: 9.0pt; line-height: 107%;">Common
AV Settings for all versions of SQL Server<o:p></o:p></span></u></b></div>
<div class="MsoNormal">
<span style="font-family: "Arial","sans-serif"; font-size: 9.0pt; line-height: 107%;">Regardless of the version of SQL Sever the exclusion
below will apply.<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<b><i><span style="font-family: "Arial","sans-serif"; font-size: 9.0pt; mso-fareast-font-family: "Times New Roman";">SQL Server data files</span></i></b><span style="font-family: "Arial","sans-serif"; font-size: 9.0pt; mso-fareast-font-family: "Times New Roman";"><br />
These files usually have one of the following file-name extensions:<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br />
<ul>
<li><span style="font-family: Arial, sans-serif; font-size: 9pt;">.mdf</span></li>
<li><span style="font-family: Arial, sans-serif; font-size: 9pt;">.ldf</span></li>
<li><span style="font-family: Arial, sans-serif; font-size: 9pt;">.ndf</span></li>
</ul>
</div>
<div class="MsoNormal" style="margin: 0in 0in 0.0001pt 1.25in;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<b><i><span style="font-family: "Arial","sans-serif"; font-size: 9.0pt; mso-fareast-font-family: "Times New Roman";">SQL Server backup files</span></i></b><span style="font-family: "Arial","sans-serif"; font-size: 9.0pt; mso-fareast-font-family: "Times New Roman";"><br />
These files frequently have one of the following file-name extensions:<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br />
<ul>
<li><span style="font-family: Arial, sans-serif; font-size: 9pt;">.bak</span></li>
<li><span style="font-family: Arial, sans-serif; font-size: 9pt;">.trn</span></li>
</ul>
</div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<b><i><span style="font-family: "Arial","sans-serif"; font-size: 9.0pt; mso-fareast-font-family: "Times New Roman";">Full-Text catalog files<o:p></o:p></span></i></b></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: "Arial","sans-serif"; font-size: 9.0pt; mso-fareast-font-family: "Times New Roman";">Default instance: Program
Files\Microsoft SQL Server\MSSQL\FTDATA <o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: "Arial","sans-serif"; font-size: 9.0pt; mso-fareast-font-family: "Times New Roman";">Named instance: Program
Files\Microsoft SQL Server\MSSQL$instancename\FTDATA <o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<b><i><span style="font-family: "Arial","sans-serif"; font-size: 9.0pt; mso-fareast-font-family: "Times New Roman";">Trace files<o:p></o:p></span></i></b></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br />
<ul>
<li><span style="font-family: Arial, sans-serif; font-size: 9pt;">.trc</span></li>
</ul>
</div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: "Arial","sans-serif"; font-size: 9.0pt; mso-fareast-font-family: "Times New Roman";">These files usually have the .trc
file-name extension. These files can be generated either when you configure
profiler tracing manually or when you enable C2 auditing</span><span style="font-family: Arial, sans-serif; font-size: 9pt;">.</span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<b><i><span style="font-family: "Arial","sans-serif"; font-size: 9.0pt; mso-fareast-font-family: "Times New Roman";">SQL audit files</span></i></b><span style="font-family: "Arial","sans-serif"; font-size: 9.0pt; mso-fareast-font-family: "Times New Roman";"> (for SQL Server 2008 or later versions) <o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br />
<ul>
<li><span style="font-family: Arial, sans-serif; font-size: 9pt;">.sqlaudit</span></li>
</ul>
</div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: "Arial","sans-serif"; font-size: 9.0pt; mso-fareast-font-family: "Times New Roman";">These files have the .sqlaudit
file-name extension. For more information, see the following topic in SQL
Server Books Online<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<b><i><span style="font-family: "Arial","sans-serif"; font-size: 9.0pt; mso-fareast-font-family: "Times New Roman";">SQL query files<o:p></o:p></span></i></b></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br />
<ul>
<li><span style="font-family: Arial, sans-serif; font-size: 9pt;">.sql</span></li>
</ul>
</div>
<div class="MsoNormal">
<span style="font-family: "Arial","sans-serif"; font-size: 9.0pt; mso-fareast-font-family: "Times New Roman";">These
files typically have the .sql file-name extension and contain Transact-SQL
statements.<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial","sans-serif"; font-size: 9.0pt; line-height: 107%; mso-fareast-font-family: "Times New Roman";">The directory
that holds <b><i>Analysis Services dat</i></b>a</span><span style="font-family: "Arial","sans-serif"; font-size: 9.0pt; line-height: 107%;"><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial","sans-serif"; font-size: 9.0pt; line-height: 107%;">Default location C:\Program Files\Microsoft SQL
Server\MSSQL.X\OLAP\Data <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial","sans-serif"; font-size: 9.0pt; line-height: 107%;">For more specifics contact the DBA team for actual
locations as this can change based on the installation and requirements.<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial","sans-serif"; font-size: 9.0pt; line-height: 107%;"><br /></span></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<b><i><span style="font-family: "Arial","sans-serif"; font-size: 9.0pt; line-height: 107%;">Clustering
Setting<o:p></o:p></span></i></b></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: "Arial","sans-serif"; font-size: 9.0pt; line-height: 107%;">Exclude
the following locations from being scanned<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<br />
<ul>
<li><span style="font-family: Arial, sans-serif; font-size: 9pt; line-height: 107%;">Quorum
drive letter</span></li>
<li><span style="font-family: Arial, sans-serif; font-size: 9pt; line-height: 107%;">C:\Windows\Cluster</span></li>
<li><span style="font-family: Arial, sans-serif; font-size: 9pt; line-height: 107%;">DTC
drive letter</span></li>
</ul>
</div>
<br />
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<b><u><span style="font-family: "Arial","sans-serif"; font-size: 9.0pt; line-height: 107%;">SQL Server 2012<o:p></o:p></span></u></b></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: "Arial","sans-serif"; font-size: 9.0pt; line-height: 107%;">Apply
the recommendation mentioned in the <b><u>Common
AV Settings for all versions of SQL Server </u></b>section and the exclusions
below.</span><br />
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: "Arial","sans-serif"; font-size: 9.0pt; line-height: 107%; mso-fareast-font-family: "Times New Roman";">%ProgramFiles%\Microsoft SQL
Server\MSSQL11.<Instance Name>\MSSQL\Binn\SQLServr.exe<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: "Arial","sans-serif"; font-size: 9.0pt; line-height: 107%; mso-fareast-font-family: "Times New Roman";">%ProgramFiles%\Microsoft SQL
Server\MSRS11.<InstanceName></span><span style="font-family: "Arial","sans-serif"; font-size: 9pt; line-height: 107%;">\Reporting</span><span style="font-family: Arial, sans-serif; font-size: 9pt; line-height: 107%;">Services</span><span style="font-family: Arial, sans-serif; font-size: 9pt; line-height: 107%;">\ReportServer</span><br />
<span style="font-family: Arial, sans-serif; font-size: 9pt; line-height: 107%;">\Bin\ReportingServicesService.exe</span></div>
<span style="font-family: Arial, sans-serif; font-size: 9pt; line-height: 107%;">%ProgramFiles%\Microsoft SQL
Server\MSAS11.<Instance Name>\OLAP\Bin\MSMDSrv.exe</span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<b><u><span style="font-family: "Arial","sans-serif"; font-size: 9.0pt; mso-fareast-font-family: "Times New Roman";">SQL Server 2008 R2<o:p></o:p></span></u></b></div>
<div class="MsoNormal">
<span style="font-family: "Arial","sans-serif"; font-size: 9.0pt; line-height: 107%;">Apply the recommendation mentioned in the <b><u>Common AV Settings for all versions of
SQL Server </u></b>section and the exclusions below.</span><br />
<span style="font-family: "Arial","sans-serif"; font-size: 9pt; text-indent: -0.25in;">%ProgramFiles%\Microsoft SQL
Server\MSSQL10_50.<Instance Name>\MSSQL\Binn\SQLServr.exe</span><span style="font-family: Symbol; font-size: 10pt; text-indent: -0.25in;"><span style="font-family: 'Times New Roman'; font-size: 7pt;"> </span></span><span style="font-family: "Arial","sans-serif"; font-size: 9pt; text-indent: -0.25in;">%ProgramFiles%\Microsoft SQL
Server\MSSQL10_50.<Instance Name>\ReportingServices\ReportServer</span><br />
<span style="font-family: "Arial","sans-serif"; font-size: 9pt; text-indent: -0.25in;">\Bin\ReportingServicesService.exe</span><br />
<span style="font-family: Arial, sans-serif; font-size: 9pt; text-indent: -0.25in;">%ProgramFiles%\Microsoft SQL
Server\MSSQL10_50.<Instance Name>\OLAP\Bin\MSMDSrv.exe</span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<b><u><span style="font-family: "Arial","sans-serif"; font-size: 9.0pt; mso-fareast-font-family: "Times New Roman";">SQL Server 2008<o:p></o:p></span></u></b></div>
<div class="MsoNormal">
<span style="font-family: "Arial","sans-serif"; font-size: 9.0pt; line-height: 107%;">Apply the recommendation mentioned in the <b><u>Common AV Settings for all versions of
SQL Server </u></b>section and the exclusions below.</span><br />
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: "Arial","sans-serif"; font-size: 9.0pt; mso-fareast-font-family: "Times New Roman";">%ProgramFiles%\Microsoft SQL
Server\MSSQL10.<Instance Name>\MSSQL\Binn\SQLServr.exe<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: "Arial","sans-serif"; font-size: 9.0pt; mso-fareast-font-family: "Times New Roman";">%ProgramFiles%\Microsoft SQL
Server\MSSQL10.<Instance Name>\ReportingServices\ReportServer</span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: "Arial","sans-serif"; font-size: 9.0pt; mso-fareast-font-family: "Times New Roman";">\Bin\ReportingServicesService.exe</span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: "Arial","sans-serif"; font-size: 9.0pt; mso-fareast-font-family: "Times New Roman";">%ProgramFiles%\Microsoft SQL
Server\MSSQL10.<Instance Name>\OLAP\Bin\MSMDSrv.exe<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: "Arial","sans-serif"; font-size: 9.0pt; mso-fareast-font-family: "Times New Roman";"><br /></span></div>
</div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<b><u><span style="font-family: "Arial","sans-serif"; font-size: 9.0pt; mso-fareast-font-family: "Times New Roman";">SQL Server 2005<o:p></o:p></span></u></b></div>
<div class="MsoNormal">
<span style="font-family: "Arial","sans-serif"; font-size: 9.0pt; line-height: 107%;">Apply the recommendation mentioned in the <b><u>Common AV Settings for all versions of
SQL Server </u></b>section and the exclusions below.</span><br />
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: "Arial","sans-serif"; font-size: 9.0pt; mso-fareast-font-family: "Times New Roman";">%ProgramFiles%\Microsoft SQL
Server\MSSQL.1\MSSQL\Binn\SQLServr.exe<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: "Arial","sans-serif"; font-size: 9.0pt; mso-fareast-font-family: "Times New Roman";">%ProgramFiles%\Microsoft SQL
Server\MSSQL.3\Reporting Services\ReportServer\Bin\ReportingServicesService.exe</span><span style="font-family: "Arial","sans-serif"; font-size: 9.0pt;"><o:p></o:p></span></div>
<span style="font-family: Arial, sans-serif; font-size: 9pt;">%ProgramFiles%\Microsoft SQL Server\MSSQL.2\OLAP\Bin\MSMDSrv.exe</span><br />
<span style="font-family: Arial, sans-serif; font-size: 9pt;"><br /></span>
<span style="font-family: Arial, sans-serif;"><span style="font-size: 12px;">Please let me know if you find any additional information I should add.</span></span><br />
<span style="font-family: Arial, sans-serif;"><span style="font-size: 12px;"><br /></span></span>
<span style="font-family: Arial, sans-serif;"><span style="font-size: 12px;">Thanks,</span></span></div>
Who am I?http://www.blogger.com/profile/11368611239474848109noreply@blogger.com0tag:blogger.com,1999:blog-2779587182821871417.post-63406981736822093162014-03-26T10:01:00.000-04:002014-03-26T10:04:49.274-04:00SQL Server 2012 on a Windows 2012R2 Cluster – Things to watch out for<div class="MsoNormal">
<span style="font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 107%;">While performing a new installation of SQL Server 2012 on
a Windows 2012R2 cluster, I learned a few things along the way and I want to
share them with you. <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 107%;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 107%;">There are 3 main area to watch out when building the
cluster and SQL Server</span></div>
<div class="MsoNormal">
<span style="font-family: Arial, sans-serif; font-size: 10pt; line-height: 107%; text-indent: -0.25in;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: Arial, sans-serif; font-size: 10pt; line-height: 107%; text-indent: -0.25in;">1.</span><span style="font-size: 7pt; text-indent: -0.25in;"> </span><span style="font-family: Arial, sans-serif; font-size: 10pt; line-height: 107%; text-indent: -0.25in;">Integration
Services (SSIS) is not cluster aware. You can get SSIS working in the cluster. However,
if you have a SSIS package that is executing and the cluster fails over (from
active to passive node) the SSIS package will not continue to execute. In fact
you have to restart the SSIS package execution. Therefore you need to build
some logic in you package to detect if the SSIS package execution was completed
or not and if not, who to roll back the changes and re execute it. There is
also a fix to allow remote connection to SSIS which can break too</span></div>
<div class="MsoNormal">
<span style="font-family: Arial, sans-serif; font-size: 10pt; line-height: 107%; text-indent: -0.25in;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: Arial, sans-serif; font-size: 10pt; line-height: 107%; text-indent: -0.25in;">2.<span style="font-family: 'Times New Roman'; font-size: 7pt; line-height: normal;"> </span></span><span style="font-family: Arial, sans-serif; font-size: 10pt; line-height: 107%; text-indent: -0.25in;">Reporting
Services (SSRS) will not start after applying patches. It appears that the
patch can/does break SSRS but it’s an easy fix</span><span style="font-size: 10pt; line-height: 107%; text-indent: -0.25in;"><span style="font-family: Arial, sans-serif;"> </span></span></div>
<div class="MsoNormal">
<span style="font-size: 10pt; line-height: 107%; text-indent: -0.25in;"><span style="font-family: Arial, sans-serif;"><br /></span></span></div>
<div class="MsoNormal">
<span style="font-size: 10pt; line-height: 107%; text-indent: -0.25in;"><span style="font-family: Arial, sans-serif;">3. </span></span><span style="font-family: Arial, sans-serif; font-size: 10pt; line-height: 107%; text-indent: -0.25in;">Always
On Availability Groups does not work between clusters. That is, if you have a
cluster (say 2 nodes) and you want to replicate with Always On Availability
Groups to </span><u style="font-family: Arial, sans-serif; font-size: 10pt; line-height: 107%; text-indent: -0.25in;">another</u><span style="font-family: Arial, sans-serif; font-size: 10pt; line-height: 107%; text-indent: -0.25in;"> cluster (again say a 2 node cluster), this will not
work! There is no documentation from Microsoft support about this. </span><span style="font-family: Arial, sans-serif; font-size: 10pt; line-height: 107%; text-indent: -0.25in;">So be aware
of this limitation</span></div>
<div class="MsoNormal">
<span style="font-family: Arial, sans-serif; font-size: 10pt; line-height: 107%; text-indent: -0.25in;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 107%;">Below are the links that help with the issues notes above
and others I found useful when building a Windows 2012R2 cluster and SQL Server
2012.<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 107%;"><br /></span></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 107%;">Integration
Services (SSIS) in a Cluster is not Cluster Aware<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 107%;"><a href="http://technet.microsoft.com/en-us/library/hh213127.aspx">http://technet.microsoft.com/en-us/library/hh213127.aspx</a><o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 107%;">Configure
the Integration Services Service as a Cluster Resource <o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 107%;"><a href="http://technet.microsoft.com/en-us/library/hh231005.aspx">http://technet.microsoft.com/en-us/library/hh231005.aspx</a><o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 107%;">Grant
Access to the Integration Services Service<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 107%;"><a href="http://mssqltrek.com/2011/12/07/connecting-to-the-integration-services-service-on-the-computer-mirrorstand-failed-with-the-following-error-access-is-denied/">http://mssqltrek.com/2011/12/07/connecting-to-the-integration-services-service-on-the-computer-mirrorstand-failed-with-the-following-error-access-is-denied/<o:p></o:p></a></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 107%;">Connect
to a Remote Integration Services Server <o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 107%;"><a href="http://technet.microsoft.com/en-us/library/aa337083.aspx">http://technet.microsoft.com/en-us/library/aa337083.aspx<o:p></o:p></a></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 107%;">Cannot
Start SQL Server Reporting Services after applying an update<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 107%;"><a href="http://support.microsoft.com/kb/2745448">http://support.microsoft.com/kb/2745448</a><o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 107%;">Before
Installing Failover Clustering<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 107%;"><a href="http://msdn.microsoft.com/en-us/library/ms189910.aspx">http://msdn.microsoft.com/en-us/library/ms189910.aspx</a><o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 107%;">SQL
Server Failover Cluster Installation<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 107%;"><a href="http://technet.microsoft.com/en-us/library/hh231721.aspx">http://technet.microsoft.com/en-us/library/hh231721.aspx</a><o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 107%;">Create
a New SQL Server Failover Cluster (Setup)<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 107%;"><a href="http://technet.microsoft.com/en-us/library/ms179530.aspx">http://technet.microsoft.com/en-us/library/ms179530.aspx</a><o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 107%;">Error
during Installation of an SQL Server Failover Cluster Instance<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<span style="font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 107%;"><a href="http://blogs.msdn.com/b/psssql/archive/2013/09/30/error-during-installation-of-an-sql-server-failover-cluster-instance.aspx">http://blogs.msdn.com/b/psssql/archive/2013/09/30/error-during-installation-of-an-sql-server-failover-cluster-instance.aspx</a><o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 107%;">Please let me know if you find any other useful
information and/or links in this topic. <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 107%;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 107%;">Thanks and enjoy.<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 107%;"><br /></span></div>
<br />
<div class="MsoNormal">
<span style="font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 107%;">Rudy<o:p></o:p></span></div>
Who am I?http://www.blogger.com/profile/11368611239474848109noreply@blogger.com0tag:blogger.com,1999:blog-2779587182821871417.post-25965658778265057882014-01-23T16:12:00.000-05:002014-01-23T16:12:04.326-05:00My other blog at DELL Software - NewHello all,<div>
<br /></div>
<div>
Today I have another blog which is with DELL Software and will be covering more topics. See below for link.</div>
<div>
<br /></div>
<div>
<div class="MsoNormal">
<span style="font-family: "Arial","sans-serif"; font-size: 9.0pt;"><a href="http://www.toadworld.com/platforms/sql-server/b/weblog/archive/2014/01/21/10-sql-server-metrics-that-are-telling-you-something-by-rudy-panigas.aspx">http://www.toadworld.com/platforms/sql-server/b/weblog/archive/2014/01/21/10-sql-server-metrics-that-are-telling-you-something-by-rudy-panigas.aspx</a></span><o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
As they become available I will be reposting them here on my blog site.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Enjoy and thanks,</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Rudy</div>
</div>
Who am I?http://www.blogger.com/profile/11368611239474848109noreply@blogger.com0tag:blogger.com,1999:blog-2779587182821871417.post-25891722425046282272013-12-20T15:36:00.000-05:002013-12-20T15:38:24.041-05:00Interesting SQL Server Metric - Installation Date<span style="font-family: Arial, Helvetica, sans-serif;">Hello, </span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">Do you collect metrics about your SQL Server environment? Wait, you don't? Here are some metrics you should collect. </span><br />
<ul>
<li><span style="font-family: Arial;">How many SQL Servers are installed?</span></li>
<li><span style="font-family: Arial;">How many SQL Servers are installed?</span></li>
<li><span style="font-family: Arial;">How many SQL Servers are used as production</span></li>
<li><span style="font-family: Arial;">How many SQL Servers are used as development?</span></li>
<li><span style="font-family: Arial;">How many SQL Servers are used as QA and UAT?</span></li>
<li><span style="font-family: Arial;">How many SQL Servers are used as disaster recovery?</span></li>
<li><span style="font-family: Arial;">How many SQL Servers are at your location, cloud, other sites, etc.?</span></li>
<li><span style="font-family: Arial;">What applications are using each SQL Server(s)?</span></li>
<li><span style="font-family: Arial;">How many database do you have?</span></li>
<li><span style="font-family: Arial;">How big are the databases and total size of disk use?</span></li>
</ul>
<span style="font-family: Arial, Helvetica, sans-serif;">
Why collect metrics? Well there are many reasons, some are to help manage the SQL Servers and other are to show management what is happening. With management in mind, you should collect the date that SQL Server was installed. With installation date you can show many interesting facts to management. </span><br />
<span style="font-family: Arial;"></span><br />
<span style="font-family: Arial;">Here is a list of just what you can show to management.</span><br />
<span style="font-family: Arial;"></span><br />
<span style="font-family: Arial;">- How many SQL Servers you have</span><br />
<span style="font-family: Arial;">- How many are where installed each month, quarter and/or year</span><br />
<span style="font-family: Arial;">- How many SQL servers need to be upgrade (if you also collect versions/patch levels)</span><br />
<span style="font-family: Arial;">- What is the growth rate over any period</span><br />
<span style="font-family: Arial;"></span><br />
<span style="font-family: Arial;">You can start to see that there is metrics here that management would love to see.</span><br />
<span style="font-family: Arial;"></span><br />
<span style="font-family: Arial;">Below is a script that will show the server name, version and installation date.</span><br />
<br />
<span style="color: blue; font-family: Consolas; font-size: 12pt;">SELECT</span><span style="font-family: Consolas; font-size: 12pt;"> <span style="color: magenta;">@@SERVERNAME</span> <span style="color: blue;">as</span> <span style="color: red;">'SQL Server Name'</span><o:p></o:p></span><br />
<span style="font-family: Consolas; font-size: 12pt;"><span style="mso-tab-count: 1;"> </span><span style="mso-spacerun: yes;"> </span><span style="color: grey;">,</span> <span style="color: magenta;">@@VERSION</span> <span style="color: blue;">as</span> <span style="color: red;">'SQL Server Version'</span><o:p></o:p></span><br />
<span style="font-family: Consolas; font-size: 12pt;"><span style="mso-tab-count: 1;"> </span><span style="mso-spacerun: yes;"> </span><span style="color: grey;">,</span> <span style="color: teal;">createdate</span> <span style="color: blue;">as</span> <span style="color: red;">'Date Installed'</span> <span style="color: blue;">FROM</span> <span style="color: green;">sys</span><span style="color: grey;">.</span><span style="color: green;">syslogins</span></span><br />
<span style="font-family: Consolas; font-size: 12pt;"><span style="color: green;"></span></span><span style="color: blue; font-family: Consolas; font-size: 12pt;">WHERE</span><span style="font-family: Consolas; font-size: 12pt;"> <span style="color: teal;">[name]</span> <span style="color: grey;">=</span> <span style="color: red;">'NT AUTHORITY\SYSTEM'</span> <span style="color: grey;">AND</span>
<span style="color: teal;">[dbname]</span> <span style="color: grey;">=</span> <span style="color: red;">'master'</span><span style="color: grey;">;<o:p></o:p></span></span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">Hope you find this script useful and have a happy holiday and a Happy New Year.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">Rudy</span>Who am I?http://www.blogger.com/profile/11368611239474848109noreply@blogger.com0tag:blogger.com,1999:blog-2779587182821871417.post-11491040871057710692013-10-28T15:29:00.000-04:002013-10-28T15:29:55.270-04:00SQLCAT.com - eBooks for Free<span style="font-family: inherit;">Hello,</span><br />
<br />
Today I found out that SQLCAT has some free eBooks that are great and I highly recommend downloading them.<br />
<br />
eBooks available are:<br />
<br />
SQLCAT's Guide to BI and Analytics<br />
SQLCAT's Guide to High Availability and Disaster Recovery<br />
SQLCAT's Guide to Relational Engine<br />
<br />
Lots of good information and worth the download.<br />
<br />
Here is the link:<br />
<a href="http://blogs.msdn.com/b/sqlcat/archive/2013/10/23/sqlcat-com-ebook-downloads.aspx" target="_blank">http://blogs.msdn.com/b/sqlcat/archive/2013/10/23/sqlcat-com-ebook-downloads.aspx</a><br />
<br />
Thanks and enjoy,<br />
<br />
Rudy<br />
Who am I?http://www.blogger.com/profile/11368611239474848109noreply@blogger.com0tag:blogger.com,1999:blog-2779587182821871417.post-66128902856072782322013-10-25T10:16:00.000-04:002013-10-25T10:21:48.396-04:00Script Performance Increase with CURSOR Parameters<span style="font-family: inherit;">Recently I attended the PASS Summit 2013 and found an interesting performance tip with the CURSOR statement. Cursors are not always the best choice but when you have to use one; check out the parameters that can be used.</span> <br />
<br />
<span style="font-family: "Arial","sans-serif";"><span style="font-family: inherit;">Below
is part of my original cursor that I used in one of my script. The total script
executed in 6 seconds<o:p></o:p></span></span><br />
<br />
<span style="font-family: "Arial","sans-serif";"><span style="font-family: inherit;">…<o:p></o:p></span></span><br />
<span style="font-family: inherit;">
</span><span style="color: #5b9bd5; font-family: inherit;">DECLARE myInstances CURSOR FOR </span><br />
<span style="color: #5b9bd5;"><span style="font-family: inherit;">SELECT SQLInstanceName, SQLlvl,
SQLVer FROM SQLSrvOps.dbo.AllSQLinstancesTable<o:p></o:p></span></span><br />
<span style="font-family: inherit;">
<span style="font-family: "Arial","sans-serif";">….<o:p></o:p></span></span><br />
<span style="font-family: inherit;">
</span><br />
<div class="MsoNormal" style="margin: 0in 0in 0pt;">
<span style="font-family: "Arial","sans-serif";"><span style="font-family: inherit;">Making
the following changes by adding LOCAL FAST_FORWARD after the CURSOR statement.<o:p></o:p></span></span></div>
<span style="font-family: inherit;">
</span><br />
<div class="MsoNormal" style="margin: 0in 0in 0pt;">
<span style="font-family: inherit;"><span style="font-family: "Arial","sans-serif";"><o:p> </o:p></span><span style="font-family: "Arial","sans-serif";">…<o:p></o:p></span></span></div>
<span style="font-family: inherit;">
</span><span style="font-family: inherit;"><span style="color: #5b9bd5;">DECLARE myInstances CURSOR </span><span style="color: red;">LOCAL FAST_FORWARD</span><span style="color: #5b9bd5;"> FOR
</span></span><br />
<span style="color: #5b9bd5;"><span style="font-family: inherit;">SELECT SQLInstanceName, SQLlvl, SQLVer FROM SQLSrvOps.dbo.AllSQLinstancesTable<o:p></o:p></span></span><br />
<span style="font-family: inherit;">
<span style="font-family: "Arial","sans-serif";">..<o:p></o:p></span></span><br />
<span style="font-family: inherit;">
</span><br />
<div class="MsoNormal" style="margin: 0in 0in 0pt;">
<span style="font-family: "Arial","sans-serif";"><span style="font-family: inherit;">By
adding this change, the same script executed in 3 seconds. That’s a 50%
improvement in performance.<o:p></o:p></span></span></div>
<span style="font-family: inherit;">
</span><br />
<span style="font-family: inherit;">Books On Line and the following links provide more details as on this and additional parameters you can use with the CURSOR statement.</span><o:p><span style="font-family: Calibri;"> </span></o:p><br />
<br />
<div class="MsoNormal" style="margin: 0in 0in 0pt;">
<span lang="EN" style="mso-ansi-language: EN;"><span style="font-family: inherit;">Increase your SQL Server performance by
replacing cursors with set operations<o:p></o:p></span></span></div>
<span style="font-family: inherit;">
</span><a href="http://blogs.msdn.com/b/sqlprogrammability/archive/2008/03/18/increase-your-sql-server-performance-by-replacing-cursors-with-set-operations.aspx"><span style="color: #0563c1; font-family: inherit;">http://blogs.msdn.com/b/sqlprogrammability/archive/2008/03/18/increase-your-sql-server-performance-by-replacing-cursors-with-set-operations.aspx</span></a><o:p></o:p><br />
<span style="font-family: inherit;">
</span><br />
<span style="font-family: inherit;">
</span><span lang="EN" style="mso-ansi-language: EN;"><span style="font-family: inherit;">Understanding SQL Server Fast_Forward
Server Cursors<o:p></o:p></span></span><br />
<span style="font-family: inherit;">
</span><a href="http://blogs.msdn.com/b/sqlqueryprocessing/archive/2009/08/12/understanding-sql-server-fast-forward-server-cursors.aspx"><span style="color: #0563c1; font-family: inherit;">http://blogs.msdn.com/b/sqlqueryprocessing/archive/2009/08/12/understanding-sql-server-fast-forward-server-cursors.aspx</span></a><o:p></o:p><br />
<span style="font-family: inherit;">
</span><br />
<div class="MsoNormal" style="margin: 0in 0in 0pt;">
<span style="mso-bidi-font-family: Helvetica;"><span style="font-family: inherit;">Comparing cursor vs. WHILE loop
performance in SQL Server<o:p></o:p></span></span></div>
<span style="font-family: inherit;">
</span><a href="http://www.techrepublic.com/blog/datacenter/comparing-cursor-vs-while-loop-performance-in-sql-server-2008/1741"><span style="color: #0563c1; font-family: inherit;">http://www.techrepublic.com/blog/datacenter/comparing-cursor-vs-while-loop-performance-in-sql-server-2008/1741</span></a><o:p></o:p><br />
<span style="font-family: inherit;">
</span><br />
<div class="MsoNormal" style="margin: 0in 0in 0pt;">
<span style="font-family: inherit;">Curious
cursor optimization options<o:p></o:p></span></div>
<span style="font-family: inherit;">
</span><a href="http://sqlblog.com/blogs/hugo_kornelis/archive/2007/11/21/curious-cursor-optimization-options.aspx"><span style="color: #0563c1; font-family: inherit;">http://sqlblog.com/blogs/hugo_kornelis/archive/2007/11/21/curious-cursor-optimization-options.aspx</span></a><o:p></o:p><br />
<span style="font-family: inherit;">
<o:p> </o:p></span><br />
<span style="font-family: inherit;">
Have a look at the above links and try some testing of your own. You too will be surprised at the performance increase you can get. My own testing showed that I can get from 10% - 70% performance increase with simple changes.</span><br />
<br />
<span style="font-family: inherit;">Ensure you test these parameters fully before applying to your production server. </span><br />
<br />
Thanks,<br />
<br />
Rudy<br />
<div class="MsoNormal" style="margin: 0in 0in 8pt;">
<o:p><span style="font-family: Calibri;"> </span></o:p></div>
Who am I?http://www.blogger.com/profile/11368611239474848109noreply@blogger.com0tag:blogger.com,1999:blog-2779587182821871417.post-28382452162162248762013-08-08T15:55:00.000-04:002013-09-03T12:58:46.420-04:00SQL Server Diagnostic Information Queries Good Day!<br />
<br />
Since I didn't have this great link before, I would like to share it with you now. If you need to perform some diagnostics on your SQL Server, Glenn Berry has created an excellent SQL scripts that will help you out. There are versions for each version of SQL Server.<br />
<br />
Here is the link: <a href="http://sqlserverperformance.wordpress.com/?s=Diagnostic+Information+Queries">http://sqlserverperformance.wordpress.com/?s=Diagnostic+Information+Queries</a><br />
<br />
I highly recommend you try them out now before you actually need them. Check the site regularly as he updates the scripts as needed.<br />
<br />
Enjoy!<br />
<br />
RudyWho am I?http://www.blogger.com/profile/11368611239474848109noreply@blogger.com0tag:blogger.com,1999:blog-2779587182821871417.post-26076921162418907162013-08-08T15:36:00.001-04:002013-09-03T13:00:04.068-04:00Who is Active on the SQL ServerHello everyone,<br />
<br />
It's been a while but I'm back. <br />
<br />
Today I would like to share a link to Adam Machanic's blog page that has a very cool SQL script.<br />
<br />
The script is called sp_WhoIsActive and what it shows is everything you wanted to know about who is doing what on your SQL Server. I highly recommend it and you should try it for yourself, on a development server. <br />
<br />
Look at the link here: <a href="http://sqlblog.com/files/folders/release/tags/who+is+active/default.aspx">http://sqlblog.com/files/folders/release/tags/who+is+active/default.aspx</a><br />
<br />
Enjoy!<br />
<br />
RudyWho am I?http://www.blogger.com/profile/11368611239474848109noreply@blogger.com0tag:blogger.com,1999:blog-2779587182821871417.post-88016276457036316602013-04-17T08:58:00.000-04:002013-04-17T08:58:05.666-04:00Hidden Features of SQL ServerHello Again,<br />
<br />
Here is an interesting web page that shows hidden features of SQL Server which are not documented. There are stored procedures and other tricks which are very useful.<br />
<br />
<a href="http://stackoverflow.com/questions/121243/hidden-features-of-sql-server">http://stackoverflow.com/questions/121243/hidden-features-of-sql-server</a><br />
<br />
Below is a small list from the site:<br />
<br />
<strong><span style="font-family: inherit;">Stored Procedures</span></strong><br />
<ul><span style="font-family: inherit;">
</span>
<li><span style="font-family: inherit;"><strong>sp_msforeachtable:</strong> Runs a command with '?' replaced with
each table name (v6.5 and up)
</span>
<li><span style="font-family: inherit;"><strong>sp_msforeachdb:</strong> Runs a command with '?' replaced with each
database name (v7 and up)
</span>
<li><span style="font-family: inherit;"><strong>sp_who2:</strong> just like sp_who, but with a lot more info for
troubleshooting blocks (v7 and up)
</span>
<li><span style="font-family: inherit;"><strong>sp_helptext:</strong> If you want the code of a stored procedure,
view & UDF
</span>
<li><span style="font-family: inherit;"><strong>sp_tables:</strong> return a list of all tables and views of
database in scope.
</span>
<li><span style="font-family: inherit;"><strong>sp_stored_procedures:</strong> return a list of all stored
procedures
</span>
<li><span style="font-family: inherit;"><strong>xp_sscanf:</strong> Reads data from the string into the argument
locations specified by each format argument.
</span>
<li><span style="font-family: inherit;"><strong>xp_fixeddrives:</strong>: Find the fixed drive with largest free
space
</span>
<li><span style="font-family: inherit;"><strong>sp_help:</strong> If you want to know the table structure, indexes
and constraints of a table. Also views and UDFs. Shortcut is Alt+F1 </span></li>
</li>
</li>
</li>
</li>
</li>
</li>
</li>
</li>
</ul>
<span style="font-family: inherit;">
</span><strong><span style="font-family: inherit;">Snippets</span></strong><br />
<ul><span style="font-family: inherit;">
</span>
<li><span style="font-family: inherit;">Returning rows in random order
</span>
<li><span style="font-family: inherit;">All database User Objects by Last Modified Date
</span>
<li><span style="font-family: inherit;">Return Date Only
</span>
<li><span style="font-family: inherit;">Find records which date falls somewhere inside the current week.
</span>
<li><span style="font-family: inherit;">Find records which date occurred last week.
</span>
<li><span style="font-family: inherit;">Returns the date for the beginning of the current week.
</span>
<li><span style="font-family: inherit;">Returns the date for the beginning of last week.
</span>
<li><span style="font-family: inherit;">See the text of a procedure that has been deployed to a server
</span>
<li><span style="font-family: inherit;">Drop all connections to the database
</span>
<li><span style="font-family: inherit;">Table Checksum
</span>
<li><span style="font-family: inherit;">Row Checksum
</span>
<li><span style="font-family: inherit;">Drop all the procedures in a database
</span>
<li><span style="font-family: inherit;">Re-map the login Ids correctly after restore
</span>
<li><span style="font-family: inherit;">Call Stored Procedures from an INSERT statement
</span>
<li><span style="font-family: inherit;">Find Procedures By Keyword
</span>
<li><span style="font-family: inherit;">Drop all the procedures in a database
</span>
<li><span style="font-family: inherit;">Query the transaction log for a database programmatically. </span></li>
</li>
</li>
</li>
</li>
</li>
</li>
</li>
</li>
</li>
</li>
</li>
</li>
</li>
</li>
</li>
</li>
</ul>
<span style="font-family: inherit;">
</span><strong><span style="font-family: inherit;">Functions</span></strong><br />
<ul><span style="font-family: inherit;">
</span>
<li><span style="font-family: inherit;">HashBytes()
</span>
<li><span style="font-family: inherit;">EncryptByKey
</span>
<li><span style="font-family: inherit;">PIVOT command </span></li>
</li>
</li>
</ul>
<span style="font-family: inherit;">
</span><strong><span style="font-family: inherit;">Misc</span></strong><br />
<ul sizcache06434897332688043="3" sizset="0"><span style="font-family: inherit;">
</span>
<li><span style="font-family: inherit;">Connection String extras
</span>
<li><span style="font-family: inherit;">TableDiff.exe
</span>
<li><span style="font-family: inherit;">Triggers for Logon Events (New in Service Pack 2)
</span>
<li><span style="font-family: inherit;">Boosting performance with persisted-computed-columns (pcc).
</span>
<li><span style="font-family: inherit;">DEFAULT_SCHEMA setting in sys.database_principles
</span>
<li><span style="font-family: inherit;">Forced Parameterization
</span>
<li><span style="font-family: inherit;">Vardecimal Storage Format
</span>
<li><span style="font-family: inherit;">Figuring out the most popular queries in seconds
</span>
<li><span style="font-family: inherit;">Scalable Shared Databases
</span>
<li><span style="font-family: inherit;">Table/Stored Procedure Filter feature in SQL Management Studio
</span>
<li><span style="font-family: inherit;">Trace flags
</span>
<li sizcache06434897332688043="3" sizset="0"><span style="font-family: inherit;">Number after a <code>GO</code>
repeats the batch
</span>
<li><span style="font-family: inherit;">Security using schemas
</span>
<li><span style="font-family: inherit;">Encryption using built in encryption functions, views and base tables with
triggers </span></li>
</li>
</li>
</li>
</li>
</li>
</li>
</li>
</li>
</li>
</li>
</li>
</li>
</li>
</ul>
<span style="font-family: inherit;"> </span><br />
<span style="font-family: inherit;">Have a look and book mark the link for future use, or just come back here :)</span><br />
<span style="font-family: inherit;"> </span><br />
<span style="font-family: inherit;">Enjoy,</span><br />
<span style="font-family: inherit;"> </span><br />
<span style="font-family: inherit;">Rudy</span>Who am I?http://www.blogger.com/profile/11368611239474848109noreply@blogger.com0tag:blogger.com,1999:blog-2779587182821871417.post-71083670616863226412013-04-16T10:17:00.000-04:002013-09-03T13:00:50.128-04:00Microsoft Feature Pack and Upgrade advisor for SQL Server 2012 SP1<br />
Hello,<br />
<br />
The link below it the Microsoft site for additional feature packs that you can install onto your SQL Server 2012 SP1 installation. There are a hand full of excellent features you can add. One of them is the upgrade advisor. <br />
<br />
Here is what Microsoft says about the upgrade advisor<br />
<br />
"Microsoft Upgrade Advisor analyzes instances of SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 in preparation for upgrading to SQL Server 2012. Upgrade Advisor identifies feature and configuration changes that might affect your upgrade, and it provides links to documentation that describes each identified issue and how to resolve it. "<br />
<br />
<a href="http://www.microsoft.com/en-us/download/details.aspx?id=35580">http://www.microsoft.com/en-us/download/details.aspx?id=35580</a><br />
<br />
Upgrade advisors are also available for older versions of SQL Server, just do a search on the link above.<br />
<br />
It's a great tool to help you migrate/upgrade your older SQL Servers to the newer version. <br />
<br />
Enjoy,<br />
<br />
RudyWho am I?http://www.blogger.com/profile/11368611239474848109noreply@blogger.com0tag:blogger.com,1999:blog-2779587182821871417.post-22609184895767893322013-04-15T15:46:00.000-04:002013-04-15T15:46:51.416-04:00Microsoft Support Lifecycle for SQL ServerHello Everyone,<br />
<br />
Here is a link to find out when your version of SQL Server will be out of support.<br />
<br />
<a href="http://support.microsoft.com/lifecycle/?c2=1044">http://support.microsoft.com/lifecycle/?c2=1044</a><br />
<br />
You can save the page to a CSV file or just go to the site for updates.<br />
<br />
Enjoy!<br />
<br />
RudyWho am I?http://www.blogger.com/profile/11368611239474848109noreply@blogger.com0tag:blogger.com,1999:blog-2779587182821871417.post-69440516094761620712013-03-05T17:13:00.000-05:002013-03-05T17:13:01.481-05:00SQL Server Version Numbers and Build ListHello everyone,<br />
<br />
Ever need a quick place to find out the versions/build numbers for any SQL Server? <br />
Try this link: <a href="http://sqlserverbuilds.blogspot.ca/">http://sqlserverbuilds.blogspot.ca/</a> which is a Microsoft blog. The site have all the versions and links to each service pack and cumlative patch you will ever need.<br />
<br />
Its very handy!<br />
<br />
Rudy<br />
Who am I?http://www.blogger.com/profile/11368611239474848109noreply@blogger.com0tag:blogger.com,1999:blog-2779587182821871417.post-35497855076413419272013-02-07T13:11:00.003-05:002013-02-28T13:41:47.558-05:00<h2>
Locate Dangerous SQL Server Setting</h2>
<br />
Hello everyone,<br />
<br />
After view some information regarding dangerous SQL Server settings; I decided to write a script that will check your current SQL Server settings and point out any dangerous configurations. There are many settings that should not be used unless you are directed by Microsoft support, Executing this script will help you determine if any of the danerous settings are present.<br />
<br />
If any of the dangerous settings are detected, an explaination is provided as to why it is dangerous to use the setting(s)<br />
<br />
Enjoy!<br />
<br />
Rudy<br />
<br />
Download script here: <a href="https://docs.google.com/file/d/0B07PMQYOhF2DdzI2UlF6X3U3Q2M/edit?usp=sharing">https://docs.google.com/file/d/0B07PMQYOhF2DdzI2UlF6X3U3Q2M/edit?usp=sharing</a>Who am I?http://www.blogger.com/profile/11368611239474848109noreply@blogger.com0tag:blogger.com,1999:blog-2779587182821871417.post-28229019097536437582013-02-07T08:48:00.002-05:002013-02-28T13:41:10.069-05:00<h2>
SQL 2012 Always On Replication Monitoring</h2>
<br />
Hello everyone,<br />
<br />
A new feature in SQL 2012 is the Always On replication. It will allow you to replicate a database to a another SQL server and make it available in READ ONLY mode. This is a great addition to SQL Server and you can find out more in BOL (Books On Line) and a lot of places on the internet.<br />
<br />
Once Always On is configured and running you should monitor its status to ensure that data is replicating. I have created the script below to help perform this task. You can add to this by placing the results in a table and have a SQL job update the table. Once an error is detected, you can have a report or email alert generated. It's basically up to you.<br />
<br />
The last section of the code is commented out. This section is for testing the main script and you should modify and try it out then note the output from the main script.<br />
<br />
This script creates no tables or stored procedures but you should test it on your development system first.<br />
<br />
Enjoy!<br />
<br />
Rudy<br />
<br />
Download script here: <a href="https://docs.google.com/file/d/0B07PMQYOhF2DNy1OcVBhWmdRZFk/edit?usp=sharing">https://docs.google.com/file/d/0B07PMQYOhF2DNy1OcVBhWmdRZFk/edit?usp=sharing</a>Who am I?http://www.blogger.com/profile/11368611239474848109noreply@blogger.com0