tag:blogger.com,1999:blog-89508872692443043132024-03-18T23:04:14.418-05:00Adventures in Database AdministrationKevinhttp://www.blogger.com/profile/09120001050238915178noreply@blogger.comBlogger67125tag:blogger.com,1999:blog-8950887269244304313.post-90852100864539496032017-05-08T09:07:00.003-05:002017-05-08T09:15:27.848-05:00How to check if Replication components are installed on your SQL Server instanceIf you need to check as to whether or not SQL Replication components are installed on a specific instance of your SQL Server, executing the following command on that instance will give you the answer.<br />
<br />
<b>EXEC sp_MS_replication_installed</b><br />
<br />
<u>Not installed</u>:<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhS6HP8cTfJbJTzgc8wAwzIolcKYNrZW_fi_LOftuBEfeDOp4By93eqdiT48FRS1usDSvcyjZPezoGj9idV6LUVwK0RYkEeBy-MiLtAilq0nzEjPY148BV193nU0nSuoyonhKtn07co9O-Z/s1600/Replication+Components+Not+Installed.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="116" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhS6HP8cTfJbJTzgc8wAwzIolcKYNrZW_fi_LOftuBEfeDOp4By93eqdiT48FRS1usDSvcyjZPezoGj9idV6LUVwK0RYkEeBy-MiLtAilq0nzEjPY148BV193nU0nSuoyonhKtn07co9O-Z/s640/Replication+Components+Not+Installed.png" width="640" /></a></div>
<br />
<u><br /></u>
<u><br /></u>
<u><br /></u>
<u><br /></u>
<u><br /></u>
<u><br /></u>
<u><br /></u>
<u><br /></u>
<u><br /></u>
<u>Installed</u>:<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjWAOSo64p0ivTHsAGqoZnKc8B5WclTxDiyycFsDHbvemC3K40rROXBYsEZVfRSAd-nGfo3bPhhwrjSrLmny3KlFiXhZTzj58H4cMInSl0UkM7zpwZC842l90ulic28jm0rvIkTVZlwfqYG/s1600/Replication+Componentst+Installed.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="118" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjWAOSo64p0ivTHsAGqoZnKc8B5WclTxDiyycFsDHbvemC3K40rROXBYsEZVfRSAd-nGfo3bPhhwrjSrLmny3KlFiXhZTzj58H4cMInSl0UkM7zpwZC842l90ulic28jm0rvIkTVZlwfqYG/s640/Replication+Componentst+Installed.png" width="640" /></a></div>
Kevinhttp://www.blogger.com/profile/09120001050238915178noreply@blogger.com5tag:blogger.com,1999:blog-8950887269244304313.post-39389603267981841902017-03-01T14:47:00.000-06:002017-03-01T14:47:35.552-06:00The Way to Love by Anthony de Mello<a href="https://www.goodreads.com/book/show/138878.The_Way_to_Love" style="float: left; padding-right: 20px"><img border="0" alt="The Way to Love" src="https://images.gr-assets.com/books/1320487578m/138878.jpg" /></a><a href="https://www.goodreads.com/book/show/138878.The_Way_to_Love">The Way to Love</a> by <a href="https://www.goodreads.com/author/show/54195.Anthony_de_Mello">Anthony de Mello</a><br/><br />
My rating: <a href="https://www.goodreads.com/review/show/1898340779">4 of 5 stars</a><br /><br /><br />
A book to be read slowly and thoughtfully. Each chapter asks the reader to be different than almost everyone they've met or ever will meet; to leave the world of desire and attachment behind and enter the world with a fresh perspective. Armed with this new perspective. de Mello asserts that true love for others can now begin to take hold and it will be totally different than any love previously experienced. Only through this rebirth can true love and happiness be experienced. <br />
<br/><br/><br />
<a href="https://www.goodreads.com/review/list/50120376-kevin-eckart">View all my reviews</a><br />
Kevinhttp://www.blogger.com/profile/09120001050238915178noreply@blogger.com0tag:blogger.com,1999:blog-8950887269244304313.post-20110440926545849632017-02-28T09:56:00.000-06:002017-02-28T09:56:26.818-06:00SQL Replication on a Table That Contains a SQL_Variant DatatypeI recently tasked with setting up <a href="https://msdn.microsoft.com/en-us/library/ms151176.aspx" target="_blank">Transactional Replication</a> in SQL 2008 R2. While this in and of itself isn't necessarily complicated, I did run into an issue that kept the initial snapshot from being created. One of the articles (tables) in the publication had two columns that were defined with a <a href="https://msdn.microsoft.com/en-us/library/ms173829.aspx" target="_blank">SQL_Variant</a> type and the snapshot agent could not convert those columns to create the snapshot. I tried the various column convert settings in the article properties, but they did not help. Only after changing the snapshot format from Native SQL Server to Character was the snapshot able to be created. All of the other article properties were left as default. Since I want all the other articles in this publication to retain the Native SQL Server snapshot format, I created a specific publication for tables that contain SQL_Variant columns.<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjmRlqtTuUM9LSlfuLr2yHUrfZaNf2nkfzg87X3JPeukhEkkcXw0-gZP9JU0hGtlOW6wEq8pHBJipMy4FEjZ7avkw6UaVDi2FJlbpFRVN7B621RuzFy6NWfrjxxZrWlIdgybc0B3uo8ZK9Q/s1600/SQL_Variant_Replication.png" imageanchor="1"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjmRlqtTuUM9LSlfuLr2yHUrfZaNf2nkfzg87X3JPeukhEkkcXw0-gZP9JU0hGtlOW6wEq8pHBJipMy4FEjZ7avkw6UaVDi2FJlbpFRVN7B621RuzFy6NWfrjxxZrWlIdgybc0B3uo8ZK9Q/s1600/SQL_Variant_Replication.png" /></a>Kevinhttp://www.blogger.com/profile/09120001050238915178noreply@blogger.com0tag:blogger.com,1999:blog-8950887269244304313.post-19239855862644929212017-02-15T16:58:00.000-06:002017-02-15T16:59:11.775-06:00Memoirs of Hadrian by Marguerite Yourcenar, Grace Frick (Translation) << Read<a href="https://www.goodreads.com/book/show/12172.Memoirs_of_Hadrian" style="float: left; padding-right: 20px"><img border="0" alt="Memoirs of Hadrian" src="https://images.gr-assets.com/books/1416448158m/12172.jpg" /></a><a href="https://www.goodreads.com/book/show/12172.Memoirs_of_Hadrian">Memoirs of Hadrian</a> by <a href="https://www.goodreads.com/author/show/7732.Marguerite_Yourcenar">Marguerite Yourcenar</a><br />
<br />
My rating: <a href="https://www.goodreads.com/review/show/1761895252">3 of 5 stars</a><br />
<br />
<br />
A multi-faceted look into the life of one of the 5 good emperors who ruled Rome for close to 100 years. This book details Hadrian's rise to power through the military and civil administration, his early years as emperor of "the world", middle age (where he loved and lost), and his final years as the most powerful man in the world. Two themes prevalent through Hadrian's life (with the exception of his final years) were the pursuit of pleasure and meaning, which I believe with his Hellenistic leanings, was trying to find one through the other. Throughout the book his actions probably seem strange or base when viewed from a Christian morality that had not yet permeated Western civilization, but in the context of the times, I can only guess that his actions were seen as normal for someone in the upper class of Rome. <br />
<br />
<br />
<br />
<a href="https://www.goodreads.com/review/list/50120376-kevin-eckart">View all my reviews</a><br />
Kevinhttp://www.blogger.com/profile/09120001050238915178noreply@blogger.com0tag:blogger.com,1999:blog-8950887269244304313.post-20313706133907737162017-02-13T10:32:00.002-06:002017-02-13T10:33:32.590-06:00The Discourses by Epictetus << Read<a href="https://www.goodreads.com/book/show/1045017.The_Discourses" style="float: left; padding-right: 20px"><img border="0" alt="The Discourses" src="https://images.gr-assets.com/books/1389393276m/1045017.jpg" /></a><a href="https://www.goodreads.com/book/show/1045017.The_Discourses">The Discourses</a> by <a href="https://www.goodreads.com/author/show/13852.Epictetus">Epictetus</a><br />
<br />
My rating: <a href="https://www.goodreads.com/review/show/1714504561">5 of 5 stars</a><br />
<br />
<br />
There's so much to learn from the Roman Stoics that a review here wouldn't come close to capturing the essence of what this book is about. Those who do read it will be amazed at the amount of 2000 year old wisdom they can apply to their lives today.<br />
<br />
<br />
<br />
<a href="https://www.goodreads.com/review/list/50120376-kevin-eckart">View all my reviews</a><br />
Kevinhttp://www.blogger.com/profile/09120001050238915178noreply@blogger.com0tag:blogger.com,1999:blog-8950887269244304313.post-67032189360525327072016-11-18T14:32:00.002-06:002016-11-18T14:32:26.298-06:00Query for Change Data Capture (CDC) tables and columnsReal quickly, I was in need of a query that would give me a listing of the tables and columns involved in Change Data Capture (CDC). I wanted to see the tracked columns in one column to the right of the table name so I utilized FOR XML PATH. Below is what I came up with.<br />
<br />
SELECT OBJECT_NAME(source_object_id) [Table Name],<br />
(SELECT name + ',' FROM sys.columns SC WHERE SC.object_id = CT.object_id AND name NOT LIKE '__$%' FOR XML PATH('')) [Columns],<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span> capture_instance,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span> supports_net_changes,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span> filegroup_name<br />
FROM CDC.change_tables CT<br />
<div>
<br /></div>
<div>
<br /></div>
<div>
It's certainly not perfect but it should give you a good start if you are ever in need of such a query.</div>
Kevinhttp://www.blogger.com/profile/09120001050238915178noreply@blogger.com0tag:blogger.com,1999:blog-8950887269244304313.post-56941580884410429562016-02-23T14:23:00.002-06:002016-02-23T14:23:35.485-06:00List of Users Who Recently Changed Their PasswordThere may come a time when you need to generate a list of SQL logins and the last time their password was changed. By using the <a href="https://msdn.microsoft.com/en-us/library/ms345412.aspx" target="_blank">LOGINPROPERTY</a> of the name in <a href="https://msdn.microsoft.com/en-us/library/ms188786.aspx" target="_blank">sys.server_principals</a> we can generate such a list. <br />
<br />
Note: This will only work with SQL logins.<br />
<br />
The following query will generate the results below.<br />
<br />
SELECT SP.name<br /> ,LOGINPROPERTY(SP.name,N'PasswordLastSetTime') 'LastPWReset'<br />FROM sys.server_principals SP<br />WHERE SP.[type] = 'S'<br />
<br />
<img src="https://dl.dropboxusercontent.com/u/63117338/LAstPWReset.png" />Kevinhttp://www.blogger.com/profile/09120001050238915178noreply@blogger.com1tag:blogger.com,1999:blog-8950887269244304313.post-9796483802969126422016-01-14T08:18:00.000-06:002016-01-14T08:18:10.187-06:00Using Extended Events to Find the Actual Execution Plan for a Table Valued FunctionWhile finding the estimated Execution Plan for a Table Valued Function (TVF) isn’t all that difficult, getting to the <em>actual </em>Execution Plan is somewhat of a challenge. Take this example from the AdventureWorks database. <br />
<span style="font-family: Consolas;">SELECT * FROM ufnGetContactInformation(3)</span><br />
By clicking on the Display Estimated Execution Plan we get the following:<br />
<img src="https://dl.dropboxusercontent.com/u/63117338/TVF_EstimatedPlanButton.png" /><br />
<strong><span style="font-size: small;">Estimated</span></strong>:<br />
<img height="521" src="https://dl.dropboxusercontent.com/u/63117338/TVF_Estimated.png" width="679" /><br />
To get the actual plan, make sure the following option is selected and execute the query. <br />
<img src="https://dl.dropboxusercontent.com/u/63117338/TVF_IncludeActualPlan.png" /><br />
<strong><span style="font-size: small;">Actual</span></strong>:<br />
<img src="https://dl.dropboxusercontent.com/u/63117338/TVF_Actual.png" /><br />
While the estimated gives us all kinds of information, the actual plan keeps the underlying operations hidden in favor of a Clustered Index Scan and a TVF operator. This isn’t very useful when it comes to troubleshooting performance issues especially if your query has multi-table joins to the TVF. <br />
Thankfully, this is where Extended Events (EE) comes into play. By using EE, we can capture the Post Execution Showplan that will give us the actual full plan behind the Clustered Index Scan and TVF operators.<br />
We’ll use the following code to setup the EE:<br />
<span style="font-family: Consolas;">CREATE EVENT SESSION QueryPlanForTVF ON SERVER<br />ADD EVENT sqlserver.query_post_execution_showplan (<br /> ACTION (sqlserver.database_name,sqlserver.plan_handle,sqlserver.sql_text)<br /> WHERE (sqlserver.session_nt_user = N'<span style="background-color: yellow;"><Your User Name></span>'))<br />ADD TARGET package0.ring_buffer<br />WITH (MAX_MEMORY = 4096 KB<br /> ,EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS<br /> ,MAX_DISPATCH_LATENCY = 30 SECONDS<br /> ,MAX_EVENT_SIZE = 0 KB<br /> ,MEMORY_PARTITION_MODE = NONE<br /> ,TRACK_CAUSALITY = OFF<br /> ,STARTUP_STATE = OFF);<br />GO</span><br />
<strong>NOTE: This EE can several decrease performance if used on a high transaction environment. It is not recommended to use this in a production environment.</strong><br />
<span style="font-family: Trebuchet MS;">This session will capture the Showplan XML event, as well as the database name, plan handle, and SQL text of the query. The session is filtered on whatever name you choose (I used my NT login name). Finally, the session sends its output to the ring buffer so we can watch it live in SSMS.</span><br />
Once the session has been created and turned on, right click on the EE and choose the “Watch Live Data” option. <br />
<img height="249" src="https://dl.dropboxusercontent.com/u/63117338/TFV_WLD.png" width="137" /><br />
And the following window will open in SSMS.<br />
<img height="449" src="https://dl.dropboxusercontent.com/u/63117338/TFV_LiveData.png" width="578" /><br />
Now, in another SSMS window, we’ll execute the query with the TVF again and see the following data flow into the live data viewer. <br />
<img src="https://dl.dropboxusercontent.com/u/63117338/TVF_LiveDetails.png" /><br />
By clicking on the Query Plan tab, we’ll finally see the full execution plan. Since this TVF has several code blocks in it, there are several plans, but I’ll only show the main one here with the knowledge there are several plans.<br />
<img height="331" src="https://dl.dropboxusercontent.com/u/63117338/TVF_QueryPlanFinal.png" width="680" /><br />
Check out the following posts for further reading on using Extended Events and the Query_Post_Execution_Showplan event:<br />
<a href="http://sqlperformance.com/2013/03/sql-plan/showplan-impact" target="_blank">Impact of the query_post_execution_showplan Extended Event in SQL Server 2012</a><br />
<a href="https://www.simple-talk.com/sql/database-administration/getting-started-with-extended-events-in-sql-server-2012/" target="_blank">Getting Started with Extended Events in SQL Server 2012</a>Kevinhttp://www.blogger.com/profile/09120001050238915178noreply@blogger.com0tag:blogger.com,1999:blog-8950887269244304313.post-55465808702421785502016-01-11T14:37:00.001-06:002016-01-11T14:42:41.422-06:00Why Isn’t My Filtered Index Working?<p>With the introduction of <a href="https://msdn.microsoft.com/en-us/library/cc280372.aspx" target="_blank">filtered indexes</a> in SQL 2008, DBA’s were finally able to create small, well defined indexes with a simple predicate that would allow queries to search a subset of a rows on a table rather than all the rows on a table. While the technology has been around for some time, I haven’t seen them mentioned too often in forums, blogs or newsletters. That said, just the other day I found an occasion to use a filtered index, and after creating it, was left wondering why the query didn’t take advantage of it.</p> <p>First, let’s set up our test scenario. We’ll be using the Sales.SalesOrderHeader and Sales.SalesOrderDetail tables in the AdventureWorks database. In this case, I’m getting the Sum of each order during a specified time period while specifying whether or not it was an online order.</p> <p>Here’s the query:</p> <p><font face="Consolas">DECLARE @OnlineFlag BIT = 1<br>DECLARE @StartDate DATETIME = '7/1/2001'<br>DECLARE @EndDate DATETIME = '7/31/2002'</font></p> <p><font face="Consolas">SELECT SalesOrderHeader.SalesOrderID,<br> SUM(SalesOrderDetail.LineTotal)<br> FROM Sales.SalesOrderHeader INNER JOIN Sales.SalesOrderDetail<br> ON SalesOrderHeader.SalesOrderID = SalesOrderDetail.SalesOrderID<br> WHERE SalesOrderHeader.OnlineOrderFlag = @OnlineFlag<br> AND SalesOrderHeader.OrderDate BETWEEN @StartDate AND @EndDate<br> GROUP BY SalesOrderHeader.SalesOrderID</font></p> <p>The resulting execution plan:</p> <p><img src="https://dl.dropboxusercontent.com/u/63117338/FIPost_NoIndex.png" width="574" height="139"></p> <p>From here, I thought I might be able to use a filtered index on the OnlineOrderFlag in the SalesOrderHeader table. </p> <p>Here’s the index creation statement:</p> <p><font face="Consolas">CREATE NONCLUSTERED INDEX IX_SalesOrderHeader_OnlineOrderFlagFiltered<br> ON Sales.SalesOrderHeader(OrderDate,SalesOrderID,OnlineOrderFlag) <br> <font style="background-color: #ffff00">WHERE OnlineOrderFlag = 1</font></font></p> <p>Note the Where statement, this is where the filter comes in to play. Functions like <font face="Consolas">getdate()</font> or <font face="Consolas">dateadd() </font><font face="Trebuchet MS">are not available to use in this context.</font></p> <p>Once the filter is added, I think I’m in business, so I execute the query again and get the following execution plan.</p> <p><img src="https://dl.dropboxusercontent.com/u/63117338/FIPost_WithIndex.png" width="576" height="132"></p> <p>But instead of seeing the use of my shiny new filtered index, the query is still using the Clustered Index Scan in addition to a new warning on the SELECT operator. If I click the operator and check the warnings, Unmatched Indexes is showing True.</p> <p><img src="https://dl.dropboxusercontent.com/u/63117338/FIPost_Warnings.png" width="575" height="150"></p> <p>This warning is telling me that <strong>Parameterization</strong> is to blame for the filtered index not being used. From here, I see 3 options.</p> <ol> <li>Remove the parameters and use literals. (not practical) <li>Use Dynamic SQL <li>Use <font face="Consolas">OPTION(RECOMPILE)</font> at the bottom of the query.</li></ol> <p>For the purposes of this exercise, I’ll be using <font face="Consolas">OPTION(RECOMPILE)</font> so that the query can take advantage of the filtered index.</p> <p>Here the query with the added hint:</p> <p><font face="Consolas">DECLARE @OnlineFlag BIT = 1<br>DECLARE @StartDate DATETIME = '7/1/2001'<br>DECLARE @EndDate DATETIME = '7/31/2002'</font></p> <p><font face="Consolas">SELECT SalesOrderHeader.SalesOrderID,<br> SUM(SalesOrderDetail.LineTotal)<br> FROM Sales.SalesOrderHeader INNER JOIN Sales.SalesOrderDetail<br> ON SalesOrderHeader.SalesOrderID = SalesOrderDetail.SalesOrderID<br> WHERE SalesOrderHeader.OnlineOrderFlag = @OnlineFlag<br> AND SalesOrderHeader.OrderDate BETWEEN @StartDate AND @EndDate<br> GROUP BY SalesOrderHeader.SalesOrderID<br>OPTION (RECOMPILE)</font></p> <p>And the resulting execution plan:</p> <p><img src="https://dl.dropboxusercontent.com/u/63117338/PIPost_Working.png" width="575" height="130"></p> <p>This solution for your filtered index may or may not be ideal for you because of the added CPU/Compile time added to each execution of the query. That said, if you have wildly varying parameters for each execution of your query, you may have already added <font face="Consolas">OPTION (RECOMPILE)</font> to deal with issues like parameter sniffing.</p> <p>Here are some fantastic resources if you would like to read further on Filtered Indexes:</p> <p><a href="https://www.simple-talk.com/sql/performance/introduction-to-sql-server-filtered-indexes/" target="_blank">Introduction to SQL Server Filtered Indexes</a></p> <p><a href="http://www.brentozar.com/archive/2013/11/what-you-can-and-cant-do-with-filtered-indexes/" target="_blank">What You Can (and Can’t) Do With Filtered Indexes</a></p> <p><a href="http://sqlfool.com/2009/04/filtered-indexes-what-you-need-to-know/" target="_blank">Filtered Indexes: What You Need To Know</a></p>Kevinhttp://www.blogger.com/profile/09120001050238915178noreply@blogger.com0tag:blogger.com,1999:blog-8950887269244304313.post-86810270774138043642015-12-21T14:56:00.000-06:002015-12-21T14:56:37.986-06:00Using Powershell to Manipulate TFS Get Latest, Check In, and Merge<p>In our environment, changes made in the Test branch have to travel through the Main branch and into the Release branch to be deployed into production. Sometimes changes need to move through quickly without regard to other changes, especially in an environment where there may be a single coder. <strong>Note: the following code will merge all checked in code regardless of who checked it in. Be careful in multi-coder environments.</strong></p><p>While PowerShell is being used to write this process, many of the commands below are actual command line directives. These older, mature commands have more features than the native PowerShell TFS commands and are documented more extensively.</p><p>The first line in the PowerShell script should be the Set-Location command. This will set the scripts working location to the location of the TFS workspace. In my instance it is “C:\Development”.</p><p><a href="https://technet.microsoft.com/en-us/library/hh849850.aspx" target="_blank">set-location</a> c:\development</p><p>Next, the comment that I want attached to the “Check in” of the changed files to each branch.</p><p>$Comment = """123456"""</p><p>In order to keep from typing the full path of the TF.exe on each Merge and Checkin, a variable is used to hold the file location. </p><p>$CommandLocation = "C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\TF.exe "</p><p>Since there are multiple branches that need to be merged and checked out, I’m hard coding the <a href="https://msdn.microsoft.com/en-us/library/fx7sdeyf(v=vs.110).aspx" target="_blank">GET</a> command for each branch. Basically, it gets the latest version of each file (not including your changes) from each branch. This is done to keep merge confilcts to a minimum. </p><p>$GetDevCommand = " get $/<TeamProjectName>/<DevBranch>/<Directory> /recursive /force "<br />
$GetMainCommand = " get $/<TeamProjectName>/<MainBranch>/<Directory> /recursive /force "<br />
$GetReleaseCommand = " get $/<TeamProjectName>/<ReleaseBranch>/<Directory> /recursive /force "</p><p><TeamProjectName> is the name of the Team Project you are working in under the Team Collection.</p><p><xBranch> is either Dev, Main, or Release in this instance.</p><p><Directory> is the name of a directory (and possible subdirectories) below the branch. This can be as granular as needed in that <directory/subdirectory/subdirectory> can be specified. The /recursive flag recurses through all subdirectories under the <Directory> and /force causes them to be overwritten. </p><p>The same hard coding occurs in the <a href="https://msdn.microsoft.com/en-us/library/bd6dxhfy(v=vs.100).aspx" target="_blank">Merge</a> and <a href="https://msdn.microsoft.com/en-us/library/c327ca1z(v=vs.110).aspx" target="_blank">Checkin</a> commands below</p><p>$MergeToMain = " merge $/<TeamProjectName>/<DevBranch>/<Directory> $/<TeamProjectName>/<MainBranch>/<Directory>/recursive /version:T"<br />
$CheckinToMain = " checkin /comment:$comment /recursive /noprompt ""c:\development\<TeamProjectName>/<MainBranch>/<Directory>"""</p><p>$MergeToRelease = " merge $/<TeamProjectName>/<MainBranch>/<Directory> $/<TeamProjectName>/<ReleaseBranch>/<Directory> /recursive /version:T"<br />
$CheckinToRelease = " checkin /comment:$comment /recursive /noprompt ""c:\development\<TeamProjectName>/<ReleaseBranch>/<Directory>"""</p><p>Once you enter the TeamProjectName, Branch, and Directory, the work of moving files in TFS can begin. This is accomplished with the <a href="https://technet.microsoft.com/en-us/library/hh849848.aspx" target="_blank">Start-Process</a> command. </p><p>By using the –wait and –RedirectStandardOutput flags, each command executes sequentially and redirects the output from each command to its own txt file for troubleshooting later.</p><p>#Get Lastest Files From Sources Control<br />
Start-Process "$CommandLocation" "$GetDevCommand" -wait -RedirectStandardOutput c:\temp\Get-TFS.txt<br />
Start-Process "$CommandLocation" "$GetMainCommand" -wait -RedirectStandardOutput c:\temp\Get-TFS.txt<br />
Start-Process "$CommandLocation" "$GetReleaseCommand" -wait -RedirectStandardOutput c:\temp\Get-TFS.txt</p><p>#Merge and Checkin to Main<br />
Start-Process "$CommandLocation" "$MergeToMain" -nonewwindow -wait -RedirectStandardOutput c:\temp\MergePreProd-TFS.txt<br />
Start-Process "$CommandLocation" "$CheckinToMain" -nonewwindow -wait -RedirectStandardOutput c:\temp\CheckinPreProd-TFS.txt</p><p>#Merge and Checkin to Release<br />
Start-Process "$CommandLocation" "$MergeToRelease" -nonewwindow -wait -RedirectStandardOutput c:\temp\MergeProd-TFS.txt<br />
Start-Process "$CommandLocation" "$CheckinToRelease" -nonewwindow -wait -RedirectStandardOutput c:\temp\CheckinProd-TFS.txt<br />
</p><p>In this instance the output for each command is directed to a file the in the C:\Temp directory for viewing later.</p><p>Enjoy!</p><p><a href="https://dl.dropboxusercontent.com/u/63117338/TFSMergeCheckin.ps1" target="_blank">TFSMergeCheckin.ps1</a></p>Kevinhttp://www.blogger.com/profile/09120001050238915178noreply@blogger.com6tag:blogger.com,1999:blog-8950887269244304313.post-13089762800184567722015-12-17T15:13:00.001-06:002015-12-17T15:13:14.255-06:00SQL Reporting Services 2014 Save, Refresh, and Print Buttons on Separate Lines in IE<p>In SSRS 2014 there is a bug in rendering pages in Internet Explorer where the Save, Refresh, and Print buttons will all show on different lines in IE. </p> <p>Below is a screenshot of an report rendering on an unpatched server.</p> <p><img src="https://dl.dropboxusercontent.com/u/63117338/SSRS_Unpatched.png"></p> <p>By moving to build <a href="http://support.microsoft.com/kb/3075950/en-us" target="_blank">12.00.4422 (CU2 for SQL Server 2014 Service Pack 1)</a> the problem is corrected.</p> <p><img src="https://dl.dropboxusercontent.com/u/63117338/SSRS_Patched.png"></p> <p>Enjoy!</p>Kevinhttp://www.blogger.com/profile/09120001050238915178noreply@blogger.com0tag:blogger.com,1999:blog-8950887269244304313.post-31472790407291935992015-12-03T14:56:00.001-06:002015-12-03T14:56:29.585-06:00Quickly Flip Database to Mirroring Partner<p>While Database Mirroring is certainly not a new technology, I’ve only started using it for HA on some of our older SQL instances. In doing so, I’ve found the need to quickly (and manually) flip the databases using mirroring to the mirrored instance. While you can accomplish this slowly using the GUI, if you have more than 1 or 2 databases to flip it can get old very quickly.</p> <p>With that in mind, I’ve created a script where the output of the script can be used to quickly flip the databases that meet certain criteria. In this case, any databases that are currently SYNCHRONIZED and are the PRINCIPAL in the mirroring session will be selected. </p> <div id="codeSnippetWrapper"> <div id="codeSnippet" style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4"><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white"><span id="lnum1" style="color: #606060"> 1:</span> <span style="color: #0000ff">SELECT</span> <span style="color: #006080">'ALTER DATABASE ['</span> + DB_NAME(database_mirroring.database_id) + <span style="color: #006080">'] SET PARTNER FAILOVER'</span> <span style="color: #0000ff">AS</span> <span style="color: #006080">'Script'</span></pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4"><span id="lnum2" style="color: #606060"> 2:</span> ,DB_NAME(database_mirroring.database_id) <span style="color: #0000ff">as</span> <span style="color: #006080">'DB Name'</span></pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white"><span id="lnum3" style="color: #606060"> 3:</span> ,database_mirroring.mirroring_partner_name <span style="color: #0000ff">AS</span> <span style="color: #006080">'Mirroring Failover Partner'</span></pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4"><span id="lnum4" style="color: #606060"> 4:</span> <span style="color: #0000ff">FROM</span> sys.database_mirroring</pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white"><span id="lnum5" style="color: #606060"> 5:</span> <span style="color: #0000ff">WHERE</span> database_mirroring.mirroring_state_desc = <span style="color: #006080">'SYNCHRONIZED'</span></pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4"><span id="lnum6" style="color: #606060"> 6:</span> <span style="color: #0000ff">AND</span> database_mirroring.mirroring_role_desc = <span style="color: #006080">'PRINCIPAL'</span></pre><!--CRLF--></div></div><br /><p>The meat of the script is in column 1. By running the commands generated in column 1 in another window with the same connection, you can quickly flip the databases to the mirrored servers. Columns 2 and 3 are there for extra information, namely the name of the database to be flipped and the name of the mirrored server that will serve primary instance of the database.</p><br /><p>Enjoy!</p> Kevinhttp://www.blogger.com/profile/09120001050238915178noreply@blogger.com0tag:blogger.com,1999:blog-8950887269244304313.post-13329662951657182212015-10-14T08:13:00.001-05:002015-10-14T08:13:58.041-05:00Using SQLCMD in SSMS to quickly check sync jobs<p>For some of us, an unfortunate side effect of using 3rd party applications to run different segments of your business, is the need to sync information between databases. Whether it be orders, employees, or something else, data needs to be synced between these systems to allow for each business segment to run. </p> <p>As a DBA, running down issues in these sync processes can be very difficult with one SSMS query window looking in each system, constantly switching back and forth between results sets to look for differences. </p> <p>Enter SQLCMD mode in SSMS. </p> <p>Using SQLCMD mode inside your query allows you to open up a connection to a server, query a database and return a result from as many different servers as needed all within the same window.</p> <p>In this example, I’ve created an Employee database on 3 different instances of SQL Server. Inside those DB’s, there is an Employee tables that hold all of my employees. At the top of the query, I’ve created a variable called “Emp” and set it to “Gail Huff” so it can be used across all the connections.</p> <p><strong>NOTE: In order to run this query, you must turn on SQLCMD mode by going to Query –> SQLCMD mode</strong></p> <div id="codeSnippetWrapper" style="overflow: auto; cursor: text; font-size: 8pt; border-top: silver 1px solid; height: 257px; font-family: 'Courier New', courier, monospace; border-right: silver 1px solid; width: 97.5%; border-bottom: silver 1px solid; padding-bottom: 4px; direction: ltr; text-align: left; padding-top: 4px; padding-left: 4px; margin: 20px 0px 10px; border-left: silver 1px solid; line-height: 12pt; padding-right: 4px; max-height: 200px; background-color: #f4f4f4"> <div id="codeSnippet" style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4"><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white"><span id="lnum1" style="color: #606060"> 1:</span> :SETVAR Emp "Gail Huff"</pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4"><span id="lnum2" style="color: #606060"> 2:</span> </pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white"><span id="lnum3" style="color: #606060"> 3:</span> :<span style="color: #0000ff">CONNECT</span> SQLDBA\SQL2008R2</pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4"><span id="lnum4" style="color: #606060"> 4:</span> </pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white"><span id="lnum5" style="color: #606060"> 5:</span> <span style="color: #0000ff">USE</span> EmployeeDB2008R2</pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4"><span id="lnum6" style="color: #606060"> 6:</span> <span style="color: #0000ff">GO</span></pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white"><span id="lnum7" style="color: #606060"> 7:</span> <span style="color: #0000ff">SELECT</span> <span style="color: #006080">'EmployeeDB2008R2'</span> <span style="color: #0000ff">AS</span> DB,* <span style="color: #0000ff">FROM</span> Employee <span style="color: #0000ff">WHERE</span> Employee.Name = <span style="color: #006080">'$(Emp)'</span></pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4"><span id="lnum8" style="color: #606060"> 8:</span> <span style="color: #0000ff">GO</span></pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white"><span id="lnum9" style="color: #606060"> 9:</span> </pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4"><span id="lnum10" style="color: #606060"> 10:</span> :<span style="color: #0000ff">CONNECT</span> SQLDBA\SQL2012</pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white"><span id="lnum11" style="color: #606060"> 11:</span> </pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4"><span id="lnum12" style="color: #606060"> 12:</span> <span style="color: #0000ff">USE</span> EmployeeDB2012</pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white"><span id="lnum13" style="color: #606060"> 13:</span> <span style="color: #0000ff">GO</span></pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4"><span id="lnum14" style="color: #606060"> 14:</span> <span style="color: #0000ff">SELECT</span> <span style="color: #006080">'Employee2012'</span> <span style="color: #0000ff">AS</span> DB,* <span style="color: #0000ff">FROM</span> Employee <span style="color: #0000ff">WHERE</span> Employee.Name = <span style="color: #006080">'$(Emp)'</span></pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white"><span id="lnum15" style="color: #606060"> 15:</span> <span style="color: #0000ff">GO</span></pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4"><span id="lnum16" style="color: #606060"> 16:</span> </pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white"><span id="lnum17" style="color: #606060"> 17:</span> :<span style="color: #0000ff">CONNECT</span> SQLDBA\SQL2014 </pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4"><span id="lnum18" style="color: #606060"> 18:</span> </pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white"><span id="lnum19" style="color: #606060"> 19:</span> <span style="color: #0000ff">USE</span> EmployeeDB2014</pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4"><span id="lnum20" style="color: #606060"> 20:</span> <span style="color: #0000ff">GO</span></pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: white"><span id="lnum21" style="color: #606060"> 21:</span> <span style="color: #0000ff">SELECT</span> <span style="color: #006080">'Employee2014'</span> <span style="color: #0000ff">AS</span> DB,* <span style="color: #0000ff">FROM</span> Employee <span style="color: #0000ff">WHERE</span> Employee.Name = <span style="color: #006080">'$(Emp)'</span></pre><!--CRLF--><pre style="border-top-style: none; overflow: visible; font-size: 8pt; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; border-left-style: none; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4"><span id="lnum22" style="color: #606060"> 22:</span> GO</pre><!--CRLF--></div></div><br /><p>Note the SETVAR and CONNECT keywords. Both do exactly what they sound like and allow SQLCMD mode to use variables and quickly connect to different SQL servers. After running the query, my result set is as follows and you can see that Gail has the same status across all servers.</p><br /><p><img src="https://dl.dropboxusercontent.com/u/63117338/SQLCMDEmployee.png"></p><br /><p>Happy querying! </p> Kevinhttp://www.blogger.com/profile/09120001050238915178noreply@blogger.com0tag:blogger.com,1999:blog-8950887269244304313.post-1644295900829894762015-09-28T13:09:00.001-05:002015-09-28T13:10:30.614-05:00Intermittent Network Connectivity Failures in Windows 2012 on VMWare with VMXNET3 Driver<p>Over the past several weeks we’ve had issues with some servers sporadically dropping off the network and quickly regaining connection. Network connectivity was checked and tests were ran but nothing was found. One commonality among the servers is that each is running in VMWare and use the vmxnet3 driver as the main NIC driver. After reading a couple of articles about potential issues surrounding this driver we opened up perfmon to check for discarded packets and found that it was discarding them at an alarming rate.</p> <p><img src="https://dl.dropboxusercontent.com/u/63117338/PerfmonVMXNET3.png" width="795" height="548"> </p> <p>After reading the following article:</p> <p><a href="http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=2039495" target="_blank">Large packet loss at the guest OS level on the VMXNET3 vNIC in ESXi</a></p> <p>we made the prescribed changes and haven’t experienced issues since.</p> <p><img src="https://dl.dropboxusercontent.com/u/63117338/vmxSmallRX.png"><img src="https://dl.dropboxusercontent.com/u/63117338/vmxRXRing.png"></p> Kevinhttp://www.blogger.com/profile/09120001050238915178noreply@blogger.com0tag:blogger.com,1999:blog-8950887269244304313.post-39996843804789800932015-09-25T11:54:00.001-05:002015-09-25T11:54:53.875-05:00Visual Studio Solution is Missing or Can’t Add Another Project to Solution<p align="left">If you’ve ever opened up a Visual Studio Project and were left wondering where the solution file is, specifically because you need to add another project to that solution, the answer lies in the IDE options. </p> <p align="left">By navigating to Tools – Options –Projects and Solutions – General, you’ll find the “Always show solution” check box. After selecting that, the top level solution will appear in the Solution Explorer and you’ll be able to add another project to it.</p> <p align="left"><img src="https://dl.dropboxusercontent.com/u/63117338/ProjectOptions.png"></p> Kevinhttp://www.blogger.com/profile/09120001050238915178noreply@blogger.com0tag:blogger.com,1999:blog-8950887269244304313.post-4242051302571289372015-09-24T15:26:00.001-05:002015-09-24T15:26:17.986-05:00Re-add Grouping to Tablix After Deleting it in SSRS<p>If you’ve ever accidentally deleted the default row grouping on a tablix in SSRS, you have two options to get it back. </p> <ol> <li>Recreate the tablix. (Which isn’t fun if you’ve have several columns or lots of formatting) <li>Recreate the grouping that’s done by default during the wizard. (You used the wizard, didn’t you?)</li></ol> <p>Rather than starting from scratch with a new tablix, I going to show how to get the default grouping back up and running.</p> <p>The steps are as follows:</p> <ol> <li>Right click on the detail line that needs the grouping re-added. <li>Select “Add Group … Adjacent Above…” <img style="float: none; margin-left: auto; display: block; margin-right: auto" src="https://dl.dropboxusercontent.com/u/63117338/addgroup.png"> <li>Then select the first column in the detail line as the “Group By” <img style="float: none; margin-left: auto; display: block; margin-right: auto" src="https://dl.dropboxusercontent.com/u/63117338/addtablix.png"> <li>After the grouping is created, cut and paste each of the columns to the newly create line above it. This will keep the formatting and formulas intact. After all the columns have been moved, you can delete the old detail line.</li></ol> Kevinhttp://www.blogger.com/profile/09120001050238915178noreply@blogger.com0tag:blogger.com,1999:blog-8950887269244304313.post-85257443428296701732015-09-03T09:46:00.001-05:002015-09-03T10:43:46.077-05:00Sending Availability Group replication traffic through a dedicated network.<p>We recently set up a SQL 2012 HADR solution that took advantage of Availability Groups (AG’s). In this instance, we utilized a multi-subnet cluster to allow for the primary replica to live in our primary data center, while having the secondary replica in another data center. </p> <p>Note: In this setup, only asynchronous replicas are allowed.</p> <p>On each of the servers in the cluster, we have 3 network connections. </p> <p>Public: All user traffic will flow through this connection</p> <p>Private: For internal cluster communication</p> <p>Replication: All AG traffic will be routed through this</p> <p>After those dedicated connections are setup, database mirroring endpoints are used to receive connections other instances in the AG. You can read more about database endpoints <a href="https://msdn.microsoft.com/en-us/library/ms179511.aspx" target="_blank">here.</a> Usually, when the endpoints are created in an AG, the following script is executed without specifying a specific IP to use. </p> <p><font size="1">--Replica1<br>CREATE ENDPOINT Hadr_Endpoint<br>AS TCP(LISTENER_PORT = 5022)<br>FOR DATA_MIRRORING(ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES)<br>GO</font></p> <p><font size="1">ALTER ENDPOINT Hadr_Endpoint STATE = STARTED<br>GO</font></p> <p><font size="1">--Replica2<br>CREATE ENDPOINT Hadr_Endpoint<br>AS TCP(LISTENER_PORT = 5022)<br>FOR DATA_MIRRORING(ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES)<br>GO</font></p> <p><font size="1">ALTER ENDPOINT Hadr_Endpoint STATE = STARTED<br>GO</font></p> <p>Without specifying an IP that the endpoint is listening on, the listener will accept a connection on any valid IP. But, if we want to make sure our AG and user traffic are segregated, we must specify the IP of the Replication network connection. So the script would look like so:</p> <p><font size="1">--Replica1<br>CREATE ENDPOINT [Hadr_endpoint] <br> STATE=STARTED<br> AS TCP (LISTENER_PORT = 5022, LISTENER_IP = (<font style="background-color: #ffff00">10.10.10.89</font>)) --<-- Your Replication IP here<br> FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE<br>, ENCRYPTION = REQUIRED ALGORITHM AES)<br>GO<br>ALTER ENDPOINT Hadr_Endpoint STATE = STARTED<br>GO</font></p> <p><font size="1">--Replica2<br>CREATE ENDPOINT [Hadr_endpoint] <br> STATE=STARTED<br> AS TCP (LISTENER_PORT = 5022, LISTENER_IP = (<font style="background-color: #ffff00">10.10.20.89</font>)) --<-- Your Replication IP here<br> FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE<br>, ENCRYPTION = REQUIRED ALGORITHM AES)<br>GO<br>ALTER ENDPOINT Hadr_Endpoint STATE = STARTED<br>GO<br></font></p> <p>You can reference the BOL article for creating an endpoint <a href="https://msdn.microsoft.com/en-us/library/ms181591.aspx" target="_blank">here</a></p> <p><strong>NOTE: </strong>Equally important when using the CREATE AVAILABILITY GROUP statement is to specify the replication IP in the ENDPOINT_URL section. By default, when scripting the AG, the FQDN will show in that section. </p> <p><font size="1">CREATE AVAILABILITY GROUP MyAG <br>FOR DATABASE MyDB1, MyDB2 <br>REPLICA ON <br>'COMPUTER01' WITH <br>( ENDPOINT_URL = 'TCP://10.10.10.89:5022', <br> AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,<br> FAILOVER_MODE = MANUAL ), <br>'COMPUTER02' WITH <br>( ENDPOINT_URL = 'TCP://10.10.20.89:5022',<br> AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,<br> FAILOVER_MODE = MANUAL );</font> </p><pre><font face="Arial">You can read up on the CREATE AVAILABILITY GROUP statement </font><a href="https://msdn.microsoft.com/en-us/library/ff878399.aspx" target="_blank"><font face="Arial">here.</font></a></pre><br /><p>After this done, and you’ve completed the rest of the AG setup, replication traffic will be routed through your Replication network. A quick and easy test of this is to open up Windows Task Manager and watch the traffic on the three different Ethernet connections. </p><br /><p><iframe style="height: 381px; width: 323px" height="320" src="https://onedrive.live.com/embed?cid=7523B17C471A02B7&resid=7523B17C471A02B7%21212&authkey=AD7TQn1t6L8l-Fs" frameborder="0" width="276" scrolling="no"></iframe><img style="margin: 0px" src="https://onedrive.live.com/redir?resid=7523B17C471A02B7!212&authkey=!AJAey3KlzplC32U&v=3&ithint=photo%2cjpg"></p><br /><p><font color="#424242"><strong>Note the adapter name for each connection.</strong></font> </p><br /><p>To test this open up an SSMS connection to the AG listener and send a large amount of DML statements to a database within the AG. If you setup the endpoint with the default LISTENER_IP = ALL, you’ll most likely see a high amount of send and receive traffic on Public interface. But, if you’ve setup the endpoint to listen on the Replication IP, you’ll see a large amount of receive traffic on the Public interface (DML) and a high amount of send traffic on the Replication interface (sending the changes to the secondary replica).</p><font size="1"></font> Kevinhttp://www.blogger.com/profile/09120001050238915178noreply@blogger.com0tag:blogger.com,1999:blog-8950887269244304313.post-66743817617712674942015-08-27T16:05:00.000-05:002015-08-27T16:05:03.279-05:00Nodes are not consistently configured with IPv4 and/or IPv6 addresses on network adapters that are usable by the cluster.During cluster validation of a multi-subnet SQL Server 2012 Availability Group cluster, the validation report kept returning the following warning:<br />
<br />
"Nodes are not consistently configured with IPv4 and/or IPv6 addresses on network adapters<br />
that are usable by the cluster."<br />
<br />
Below that entry was as follows:<br />
<br />
Node <Server1> configured with IP addresses from protocol IPv4<br />
Node <Server2> is configured with IP addresses from protocol IPv4 and IPv6<br />
<br />
After comparing ipconfig /all and device manager (show hidden devices) entries on both nodes, Server2 included 4 Microsoft ISATAP Adapters, while Server1 didn't.<br />
<br />
<table cellpadding="0" cellspacing="0" class="tr-caption-container" style="float: left; margin-right: 1em; text-align: left;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjjk8-Y8lJMajMBRD5OSyaYRq0Y_TNZdsJ19j9cdYDK6nXuwBqJGPzXQiCA5KXQPtD5SR_I1UL6IHHHf7fTLkps-c4fXSzi1oXvh2yYpKiW2PIIXgCXGH7Zsewt-FqviRFyesYs-t9uTX3V/s1600/8-27-2015+3-59-24+PM.jpg" imageanchor="1" style="clear: left; margin-bottom: 1em; margin-left: auto; margin-right: auto;"><img border="0" height="84" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjjk8-Y8lJMajMBRD5OSyaYRq0Y_TNZdsJ19j9cdYDK6nXuwBqJGPzXQiCA5KXQPtD5SR_I1UL6IHHHf7fTLkps-c4fXSzi1oXvh2yYpKiW2PIIXgCXGH7Zsewt-FqviRFyesYs-t9uTX3V/s320/8-27-2015+3-59-24+PM.jpg" width="320" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Shown here as already disabled</td></tr>
</tbody></table>
After a couple hours of research and verification that we aren't using IPv6 on our network, I disabled the ISATAP devices inside the device manager and the cluster was able to validate properly.Kevinhttp://www.blogger.com/profile/09120001050238915178noreply@blogger.com2tag:blogger.com,1999:blog-8950887269244304313.post-32138458839946722962014-09-24T09:44:00.000-05:002014-09-24T09:44:46.228-05:00How to keep your remote desktop session active when it's minimized<span style="font-family: inherit;">If you have an interactive application running on a machine that you have open in an RDP session and you want the app to continue to run when the RDP session is minimized you'll have to make some changes to the registry on your computer (not the remote computer).</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;"><a href="http://blog.smartbear.com/software-quality/testcomplete-tip-running-tests-in-minimized-remote-desktop-windows/" target="_blank">Original blog post</a></span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;"><span style="background-color: white; color: #333333; line-height: 17.4720001220703px;">The general steps are to modify the registry by doing the following:</span></span><br />
<span style="font-family: inherit;"><br style="background-color: white; color: #333333; line-height: 17.4720001220703px; margin: 0px; padding: 0px;" /><span style="background-color: white; color: #333333; line-height: 17.4720001220703px;">locate the key:</span></span><br />
<span style="font-family: inherit;"><span style="background-color: white; color: #333333; line-height: 17.4720001220703px;"><br /></span><span style="background-color: white; color: #333333; font-weight: bold; line-height: 17.4720001220703px; margin: 0px; padding: 0px;">HKLM\Software\Microsoft\Terminal Server Client\</span></span><br />
<span style="font-family: inherit;"><b><br style="background-color: white; color: #333333; line-height: 17.4720001220703px; margin: 0px; padding: 0px;" /></b><span style="background-color: white; color: #333333; line-height: 17.4720001220703px;">add a DWORD value in this key, naming it "RemoteDesktop_SuppressWhenMinimized", and give it the value </span><span style="background-color: white; color: #333333; font-weight: bold; line-height: 17.4720001220703px; margin: 0px; padding: 0px;">2</span><span style="background-color: white; color: #333333; line-height: 17.4720001220703px;">.</span><br style="background-color: white; color: #333333; line-height: 17.4720001220703px; margin: 0px; padding: 0px;" /><span style="background-color: white; color: #333333; line-height: 17.4720001220703px;"><br /></span></span><br />
<span style="font-family: inherit;"><span style="background-color: white; color: #333333; line-height: 17.4720001220703px;">If you're a 64-bit client, add that value to this key:</span><span style="background-color: white; color: #333333; font-weight: bold; line-height: 17.4720001220703px; margin: 0px; padding: 0px;">HKLM\Software\Wow6432Node\Microsoft\Terminal Server Client\</span></span>Kevinhttp://www.blogger.com/profile/09120001050238915178noreply@blogger.com0tag:blogger.com,1999:blog-8950887269244304313.post-85924929447371809292014-02-14T16:42:00.001-06:002014-02-14T16:42:54.296-06:00SESSION: Skewed Data, Poor Cardinality Estimates, and Plans Gone Bad (Ki...<iframe allowfullscreen="" frameborder="0" height="270" src="//www.youtube.com/embed/li5HwaZF8tc" width="480"></iframe>Kevinhttp://www.blogger.com/profile/09120001050238915178noreply@blogger.com0tag:blogger.com,1999:blog-8950887269244304313.post-18452805132545050842014-01-21T14:42:00.001-06:002014-01-21T14:42:11.888-06:00Schema-only Backups and RestoresGreat article about SQL Server schema only backups and restores<br />
<br />
<a href="http://www.sqlsoldier.com/wp/sqlserver/schemaonlybackupsandrestores">Schema-only Backups and Restores | SQLSoldier</a>Kevinhttp://www.blogger.com/profile/09120001050238915178noreply@blogger.com0tag:blogger.com,1999:blog-8950887269244304313.post-74080400024497587842014-01-14T10:38:00.001-06:002014-01-14T10:38:29.105-06:00Recent SQL Server Build ListHere is a good link for recent SQL Server build numbers.<br />
<br />
<a href="http://sqlmag.com/sql-server/recent-sql-server-build-numbers" target="_blank">Build List</a>Kevinhttp://www.blogger.com/profile/09120001050238915178noreply@blogger.com0tag:blogger.com,1999:blog-8950887269244304313.post-65001787665023841022014-01-09T14:29:00.001-06:002014-01-09T14:29:39.219-06:00SQL Server Quickie #9 - Clustered Indexes From SQL PassionThis is a great introduction to clustered indexes.<br />
<br />
<iframe allowfullscreen="" frameborder="0" height="270" src="//www.youtube.com/embed/C4_tqLsF9cM" width="480"></iframe>Kevinhttp://www.blogger.com/profile/09120001050238915178noreply@blogger.com0tag:blogger.com,1999:blog-8950887269244304313.post-19557135783521157122013-12-11T10:01:00.003-06:002013-12-11T10:01:27.401-06:00SQL 2005: SQL Server Configuration Manager Error: Connection to target machine could not be made in a timely fasion.If you get this error trying to open SQL Server Configuration Manager, give this blog a shot:<br />
<br />
<a href="http://blogs.msdn.com/b/psssql/archive/2009/03/31/sql-2005-sql-server-configuration-manager-error-connection-to-target-machine-could-not-be-made-in-a-timely-fasion.aspx">http://blogs.msdn.com/b/psssql/archive/2009/03/31/sql-2005-sql-server-configuration-manager-error-connection-to-target-machine-could-not-be-made-in-a-timely-fasion.aspx</a>Kevinhttp://www.blogger.com/profile/09120001050238915178noreply@blogger.com0tag:blogger.com,1999:blog-8950887269244304313.post-25364225937970328952013-12-11T09:59:00.000-06:002013-12-11T09:59:17.940-06:00SQL 2005 TDSSNIClient initialization failed with error 0x32, status code 0x1c (Clustered environment)If you get this error and your clustered instance will not start, give this MSDN blog a shot:<br />
<br />
<a href="http://blogs.msdn.com/b/sql_protocols/archive/2005/12/07/500820.aspx">http://blogs.msdn.com/b/sql_protocols/archive/2005/12/07/500820.aspx</a>Kevinhttp://www.blogger.com/profile/09120001050238915178noreply@blogger.com0