Daily Archives

Articles indexed Monday August 18 2014

Page 9/20 | < Previous Page | 5 6 7 8 9 10 11 12 13 14 15 16  | Next Page >

• Want a headless build server for SSDT without installing Visual Studio? You’re out of luck!

  - by jamiet

  An issue that regularly seems to rear its head on my travels is that of headless build servers for SSDT. What does that mean exactly? Let me give you my interpretation of it. A SQL Server Data Tools (SSDT) project incorporates a build process that will basically parse all of the files within the project and spit out a .dacpac file. Where an organisation employs a Continuous Integration process they will likely want to automate the building of that dacpac whenever someone commits a change to the source control repository. In order to do that the organisation will use a build server (e.g. TFS, TeamCity, Jenkins) and hence that build server requires all the pre-requisite software that understands how to build an SSDT project. The simplest way to install all of those pre-requisites is to install SSDT itself however a lot of folks don’t like that approach because it installs a lot unnecessary components on there, not least Visual Studio itself. Those folks (of which i am one) are of the opinion that it should be unnecessary to install a heavyweight GUI in order to simply get a few software components required to do something that inherently doesn’t even need a GUI. The phrase “headless build server” is often used to describe a build server that doesn’t contain any heavyweight GUI tools such as Visual Studio and is a desirable state for a build server. In his blog post Headless MSBuild Support for SSDT (*.sqlproj) Projects Gert Drapers outlines the steps necessary to obtain a headless build server for SSDT: This article describes how to install the required components to build and publish SQL Server Data Tools projects (*.sqlproj) using MSBuild without installing the full SQL Server Data Tool hosted inside the Visual Studio IDE. http://sqlproj.com/index.php/2012/03/headless-msbuild-support-for-ssdt-sqlproj-projects/ Frankly however going through these steps is a royal PITA and folks like myself have longed for Microsoft to support headless build support for SSDT by providing a distributable installer that installs only the pre-requisites for building SSDT projects. Yesterday in MSDN forum thread Building a VS2013 headless build server - it's sooo hard Mike Hingley complained about this very thing and it prompted a response from Kevin Cunnane from the SSDT product team: The official recommendation from the TFS / Visual Studio team is to install the version of Visual Studio you use on the build machine. I, like many others, would rather not have to install full blown Visual Studio and so I asked: Is there any chance you'll ever support any of these scenarios: Installation of all build/deploy pre-requisites without installing the VS shell? TFS shipping with all of the pre-requisites for doing SSDT project build/deploys 3rd party build servers (e.g. TeamCity) shipping with all of the requisites for doing SSDT project build/deploys I have to say that the lack of a single installer containing all the pre-requisites for SSDT build/deploy puzzles me. Surely the DacFX installer would be a perfect vehicle for that? Kevin replied again: The answer is no for all 3 scenarios. We looked into this issue, discussed it with the Visual Studio / TFS team, and in the end agreed to go with their latest guidance which is to install Visual Studio (e.g. VS2013 Express for Web) on the build machine. This is how Visual Studio Online is doing it and it's the approach recommended for customers setting up their own TFS build servers. I would hope this is compatible with 3rd party build servers but have not verified whether this works with TeamCity etc. Note that DacFx MSI isn't a suitable release vehicle for this as we don't want to include Visual Studio/MSBuild dependencies in that package. It's meant to just include the core DacFx DLLs used by SSMS, SqlPackage.exe on the command line, etc. What this means is we won't be providing a separate MSI installer or nuget package with just the necessary build DLLs you need to run your build and tests. If someone wanted to create a script that generated a nuget package based on our DLLs and targets files, then release that somewhere on the web for easier integration with 3rd party build servers we've no problem with that. Again, here’s the link to the thread and its worth reading in its entirety if this is something that interests you. So there you have it. Microsoft will not be be providing support for headless build servers for SSDT but if someone in the community wants to go ahead and roll their own, go right ahead. @Jamiet

  Read the article

• LobsterPot Solutions in the USA

  - by Rob Farley

  We’re expanding! I’m thrilled to announce that Microsoft Gold Partner LobsterPot Solutions has started another branch appointing the amazing Ted Krueger (5-time SQL MVP awardee) as the US lead. Ted is well-known in the SQL Server world, having written books on indexing, consulting and on being a DBA (not to mention contributing chapters to both MVP Deep Dives books). He is an expert on replication and high availability, and strong in the Business Intelligence space – vast experience which is both broad and deep. Ted is based in the south east corner of Wisconsin, just north of Chicago. He has been a consultant for eons and has helped many clients with their projects and problems, taking the role as both technical lead and consulting lead. He is also tireless in supporting and developing the SQL Server community, presenting at conferences across America, and helping people through his blog, Twitter and more. Despite all this – it’s neither his technical excellence with SQL Server nor his consulting skill that made me want him to lead LobsterPot’s US venture. I wanted Ted because of his values. In the time I’ve known Ted, I’ve found his integrity to be excellent, and found him to be morally beyond reproach. This is the biggest priority I have when finding people to represent the LobsterPot brand. I have no qualms in recommending Ted’s character or work ethic. It’s not just my thoughts on him – all my trusted friends that know Ted agree about this. So last week, LobsterPot Solutions LLC was formed in the United States, and in a couple of weeks, we will be open for business! LobsterPot Solutions can be contacted via email at [email protected], on the web at either www.lobsterpot.com.au or www.lobsterpotsolutions.com, and on Twitter as @lobsterpot_au and @lobsterpot_us. Ted Kruger blogs at LessThanDot, and can also be found on Twitter and LinkedIn. This post is cross-posted from http://lobsterpotsolutions.com/lobsterpot-solutions-in-the-usa

  Read the article

• Surface Pro 3 first impressions

  - by John Paul Cook

  I traded in my Surface 2 (the trade-in program is now over) and bought a Surface Pro 3 with an i7 processor and 8 GB of ram. I greatly prefer the 3 by 2 aspect ratio of the Surface 3. After only one day of ownership, I’ve decided to purchase a docking station. I have a 7 year old desktop with a quad core Q6600 processor overclocked to 3.0 GHz and 8 GB of ram. It has a Plextor 512 MB SSD as the primary drive. It’s a very capable machine, but it does have a little bit, and I do mean only a little bit,...(read more)

  Read the article

• SQL Intersection Conference, Las Vegas MGM Grand 10-13 November 2014

  - by Paul White

  I am very pleased to announce that I will be speaking at the SQL Intersection conference in Las Vegas again this year. This time around, I am giving a full-day workshop, "Mastering SQL Server Execution Plan Analysis" as well as a two-part session, "Parallel Query Execution" during the main conference. The workshop is a pre-conference event, held on Sunday 9 November (straight after this year's PASS Summit). Being on Sunday gives you the whole Monday off to recover and before the...(read more)

  Read the article

• New training on Power Pivot with recorded video courses

  - by Marco Russo (SQLBI)

  I and Alberto Ferrari started delivering training on Power Pivot in 2010, initially in classrooms and then also online. We also recorded videos for Project Botticelli, where you can find content about Microsoft tools and services for Business Intelligence. In the last months, we produced a recorded video course for people that want to learn Power Pivot without attending a scheduled course. We split the entire Power Pivot course training in three editions, offering at a lower price the more introductive modules: Beginner: introduces Power Pivot to any user who knows Excel and want to create reports with more complex and large data structures than a single table. Intermediate: improves skills on Power Pivot for Excel, introducing the DAX language and important features such as CALCULATE and Time Intelligence functions. Advanced: includes a depth coverage of the DAX language, which is required for writing complex calculations, and other advanced features of both Excel and Power Pivot. There are also two bundles, that includes two or three editions at a lower price. Most important, we have a special 40% launch discount on all published video courses using the coupon SQLBI-FRNDS-14 valid until August 31, 2014. Just follow the link to see a more complete description of the editions available and their discounted prices. Regular prices start at $29, which means that you can start a training with less than $18 using the special promotion. P.S.: we recently launched a new responsive version of the SQLBI web site, and now we also have a page dedicated to all videos available about our sessions in conferences around the world. You can find more than 30 hours of free videos here: http://www.sqlbi.com/tv.

  Read the article

• Scripting out Contained Database Users

  - by Argenis

    Today’s blog post comes from a Twitter thread on which @SQLSoldier, @sqlstudent144 and @SQLTaiob were discussing the internals of contained database users. Unless you have been living under a rock, you’ve heard about the concept of contained users within a SQL Server database (hit the link if you have not). In this article I’d like to show you that you can, indeed, script out contained database users and recreate them on another database, as either contained users or as good old fashioned logins/server principals as well. Why would this be useful? Well, because you would not need to know the password for the user in order to recreate it on another instance. I know there is a limited number of scenarios where this would be necessary, but nonetheless I figured I’d throw this blog post to show how it can be done. A more obscure use case: with the password hash (which I’m about to show you how to obtain) you could also crack the password using a utility like hashcat, as highlighted on this SQLServerCentral article. The Investigation SQL Server uses System Base Tables to save the password hashes of logins and contained database users. For logins it uses sys.sysxlgns, whereas for contained database users it leverages sys.sysowners. I’ll show you what I do to figure this stuff out: I create a login/contained user, and then I immediately browse the transaction log with, for example, fn_dblog. It’s pretty obvious that only two base tables touched by the operation are sys.sysxlgns, and also sys.sysprivs – the latter is used to track permissions. If I connect to the DAC on my instance, I can query for the password hash of this login I’ve just created. A few interesting things about this hash. This was taken on my laptop, and I happen to be running SQL Server 2014 RTM CU2, which is the latest public build of SQL Server 2014 as of time of writing. In 2008 R2 and prior versions (back to 2000), the password hashes would start with 0x0100. The reason why this changed is because starting with SQL Server 2012 password hashes are kept using a SHA512 algorithm, as opposed to SHA-1 (used since 2000) or Snefru (used in 6.5 and 7.0). SHA-1 is nowadays deemed unsafe and is very easy to crack. For regular SQL logins, this information is exposed through the sys.sql_logins catalog view, so there is really no need to connect to the DAC to grab an SID/password hash pair. For contained database users, there is (currently) no method of obtaining SID or password hashes without connecting to the DAC. If we create a contained database user, this is what we get from the transaction log: Note that the System Base Table used in this case is sys.sysowners. sys.sysprivs is used as well, and again this is to track permissions. To query sys.sysowners, you would have to connect to the DAC, as I mentioned previously. And this is what you would get: There are other ways to figure out what SQL Server uses under the hood to store contained database user password hashes, like looking at the execution plan for a query to sys.dm_db_uncontained_entities (Thanks, Robert Davis!) SIDs, Logins, Contained Users, and Why You Care…Or Not. One of the reasons behind the existence of Contained Users was the concept of portability of databases: it is really painful to maintain Server Principals (Logins) synced across most shared-nothing SQL Server HA/DR technologies (Mirroring, Availability Groups, and Log Shipping). Often times you would need the Security Identifier (SID) of these logins to match across instances, and that meant that you had to fetch whatever SID was assigned to the login on the principal instance so you could recreate it on a secondary. With contained users you normally wouldn’t care about SIDs, as the users are always available (and synced, as long as synchronization takes place) across instances. Now you might be presented some particular requirement that might specify that SIDs synced between logins on certain instances and contained database users on other databases. How would you go about creating a contained database user with a specific SID? The answer is that you can’t do it directly, but there’s a little trick that would allow you to do it. Create a login with a specified SID and password hash, create a user for that server principal on a partially contained database, then migrate that user to contained using the system stored procedure sp_user_migrate_to_contained, then drop the login. CREATE LOGIN <login_name> WITH PASSWORD = <password_hash> HASHED, SID = <sid> ; GO USE <partially_contained_db>; GO CREATE USER <user_name> FROM LOGIN <login_name>; GO EXEC sp_migrate_user_to_contained @username = <user_name>, @rename = N’keep_name’, @disablelogin = N‘disable_login’; GO DROP LOGIN <login_name>; GO Here’s how this skeleton would look like in action: And now I have a contained user with a specified SID and password hash. In my example above, I renamed the user after migrated it to contained so that it is, hopefully, easier to understand. Enjoy!

  Read the article

• DIVIDE vs division operator in #dax

  - by Marco Russo (SQLBI)

  Alberto Ferrari wrote an interesting article about DIVIDE performance in DAX. This new function has been introduced in SQL Server Analysis Services 2012 SP1, so it is available also in Excel 2013 (which still doesn’t have other features/fixes introduced by following Cumulative Updates…). The idea that instead of writing: IF ( Sales[Quantity] <> 0, Sales[Amount] / Sales[Quantity], BLANK () ) you can write: DIVIDE ( Sales[Amount], Sales[Quantity] ) There is a third optional argument in DIVIDE that defines the result in case the denominator (second argument) is zero, and by default its value is BLANK, so I omitted the third argument in my example. Using DIVIDE is very important, especially when you use a measure in MDX (for example in an Excel PivotTable) because it raise the chance that the non empty evaluation for the result is evaluated in bulk mode instead of cell-by-cell. However, from a DAX point of view, you might find it’s better to use the standard division operator removing the IF statement. I suggest you to read Alberto’s article, because you will find that an expression applying a filter using FILTER is faster than using CALCULATE, which is against any rule of thumb you might have read until now! Again, this is not always true, and depends on many conditions – trying to simplify, we might say that for a simple calculation, the query plan generated by FILTER could be more efficient – but, as usual, it depends, and 90% of the times using FILTER instead of CALCULATE produces slower performance. Do not take anything for granted, and always check the query plan when performance are your first issue!

  Read the article

• SQL Server 2012 Service Pack 2 Cumulative Update #1 is available!

  - by AaronBertrand

  The SQL Server team has released SQL Server 2012 SP2 Cumulative Update #1. This cumulative updates Service Pack 2 to include the fixes from SP1 CU#10 and a few from CU#11, including the fix for the online index rebuild corruption issue I discussed recently on SQLPerformance.com . It also marks the first time in the SQL Server 2012 timeframe that both cumulative update branches are on roughly the same schedule, which makes many of us happy I'm sure. :-) KB Article: KB #2976982 Build # is 11.0.5532...(read more)

  Read the article

• SSISDB Analysis Script on Gist

  - by Davide Mauri

  I've created two simple, yet very useful, script to extract some useful data to quickly monitor SSIS packages execution in SQL Server 2012 and after.get-ssis-execution-status  get-ssis-data-pumped-rows  I've started to use gist since it comes very handy, for this "quick'n'dirty" scripts and snippets, and you can find the above scripts and others (hopefully the number will increase over time...I plan to use gist to store all the code snippet I used to store in a dedicated folder on my machine) there.Now, back to the aforementioned scripts. The first one ("get-ssis-execution-status") returns a list of all executed and executing packages along with latest successful and running executions (so that on can have an idea of the expected run time)error messageswarning messages related to duplicate rows found in lookupsthe second one ("get-ssis-data-pumped-rows") returns information on DataFlows status. Here there's something interesting, IMHO. Nothing exceptional, let it be clear, but nonetheless useful: the script extract information on destinations and row sent to destinations right from the messages produced by the DataFlow component. This helps to quickly understand how many rows as been sent and where...without having to increase the logging level.Enjoy! PSI haven't tested it with SQL Server 2014, but AFAIK they should work without problems. Of course any feedback on this is welcome. 

  Read the article

• SQL Server 2012 Service Pack 1 Cumulative Update #11 is available!

  - by AaronBertrand

  The SQL Server team has released SQL Server 2012 SP1 Cumulative Update #11. This cumulative update includes a fix for the online index rebuild corruption issue I discussed recently on SQLPerformance.com . KB Article: KB #2975396 Build # is 11.0.3449 Currently there are 32 public fixes listed (32 total) Relevant for builds 11.0.3000 -> 11.0.3448. Do not attempt to install on SQL Server 2012 RTM (any build < 11.0.3000) or any other major version. If you are on a different branch, see this blog...(read more)

  Read the article

• Pivotal change in strategy at Microsoft

  - by Sahil Malik

  SharePoint, WCF and Azure Trainings: more information Drop whatever you are doing, and watch this video. Here is what Microsoft’s new CEO Satya Nadella said, .. If there is anything that would define windows, I think of the cloud as being in a heterogeneous device world. It will not be limited to Windows. I think that is perhaps the biggest pivotal change in our strategy. We absolutely think of building services and infrastructure in the cloud that will be across Android, iOS and Windows, and we will differentiate windows family with user experience that is consistent across all these devices .. Read full article ....

  Read the article

• The perfect RDP experience from Mac to Windows

  - by Sahil Malik

  SharePoint, WCF and Azure Trainings: more information For the reasons I mentioned in my keyboard remapping blog post, I live in a Mac+PC world. This means, I find myself frequently RDP’ing to Windows Machines from my Mac. And yes, that Metro UI on RDP is even more frustrating than it is without RDP. Mac is a different OS than Windows (duh!), and the way it handles multiple screens and spaces is entirely different from windows. This means, RDP experiences are tough to make 100% seamless. You can see the frustrations in the various reviews on the Microsoft RDP app on the Mac app store. It can be difficult to find the perfect settings for the perfect user experience. Well, here they are - Read full article ....

  Read the article

• Keyboard settings for a Mac+PC world

  - by Sahil Malik

  SharePoint, WCF and Azure Trainings: more information I’m one of those weridos who lives in a Mac+PC world. I write code for both iOS and Windows platforms. I also travel quite a bit, and airlines and airport security are starting to weigh your carry ons, and beginning to frown on the powerplant of batteries you need to carry to power SharePoint on an airplane. This means, my main work machine has to be a Macbook Pro, since it is the only machine that can do both XCode and Visual Studio Virtualized and SharePoint virtualized nicely. The problem this causes of course, is you will literally pull your hair out when dealing with keyboard/shortcut differences. So here is my work setup, Running Mac for all my normal work Virtualizing using VMWare Fusion – and sometimes I move these VMs to my windows server so I can run them on VMware workstation. Frequently RDP’ing into VMs in the cloud or running on my home server. So, Read full article ....

  Read the article

• Mobile Devices and SharePoint

  - by Sahil Malik

  SharePoint, WCF and Azure Trainings: more information There is a computer in your pocket, and mine. Though lets be honest, beyond email, Organizations though are still struggling to make use of these smart devices that they are already paying for. The reason is simple, Imagine a computer, with an inferior processor, poor connectivity, smaller screen, and easily lost and used for personal purposes. Add to it, multiple platforms, and an incredibly fast release cadence. In this fast paced session, Sahil will demonstrate mobile apps targeting the SharePoint and Office 365 platforms demonstrating what you, the Microsoft developer needs to learn and be on top of going forward. And yes, you need to broaden your horizons past C# too. But don’t worry, even if you have never worked with iOS before, there is plenty to chew on in this session. Read full article ....

  Read the article

• SharePoint Powers Hell

  - by Sahil Malik

  SharePoint, WCF and Azure Trainings: more information Really? No way! Must be a typo. Really I am going to talk about PowerShell in this session. And no this is not a PowerShell basics talk. This is a practical hands on talk where Sahil will demonstrate practical usage of PowerShell in both development and production environments. This is a must attend for both Devs and IT Pros. Where: Zagreb, Croatia, SPC Adriatics When: 10/15/2014 – 9:45AM – 10:45AM More info – Full Agenda http://spcadriatics.com/2014/agenda/ and Conference site - http://spcadriatics.com Read full article ....

  Read the article

• Developing a Custom SSIS Source Component

  SSIS was designed to be extensible. Although you can create tasks that will take data from a wide variety of sources, transform the data is a number of ways and write the results a wide choice of destinations, using the components provided, there will always be occasions when you need to customise your own SSIS component. Yes, it is time to hone up your C# skills and cut some code, as Saurabh explains.

  Read the article

• The Mindset of the Enterprise DBA: Creating and Applying Standards to Our Work

  Although many professions, such as pilots, surgeons and IT administrators, require judgement and skill, they also require the ability to do many repeated standard procedures in a consistent and methodical manner. These procedures leave little room for creativity since they must be done right, and in the right order. For DBAs, standardization involves providing and following checklists, notes and instructions so that the results are predictable, correct and easy to maintain

  Read the article

• A TDD Journey: 3- Mocks vs. Stubs; Test Frameworks; Assertions; ReSharper Accelerators

  Test-Driven Development (TDD) involves the repetition of a very short development cycle that begins with an initially-failing test that defines the required functionality, and ends with producing the minimum amount of code to pass that test, and finally refactoring the new code. Michael Sorens continues his introduction to TDD that is more of a journey in six parts, by implementing the first tests and introducing the topics of Test doubles; Test Runners, Constraints and assertions

  Read the article

• A TDD Journey: 4-Tests as Documentation; False Positive Results; Component Isolation

  In Test-Driven Development (TDD) , The writing of a unit test is done more to design and to document than to verifiy. By writing a unit test you close a number of feedback loops, and verifying the functionality of the code is just a minor one. everything you need to know about your class under test is embodied in a simple list of the names of the tests. Michael Sorens continues his introduction to TDD that is more of a journey in six parts, by discussing Tests as Documentation, False Positive Results and Component Isolation.

  Read the article

• A TDD Journey: 2- Naming Tests; Mocking Frameworks; Dependency Injection

  Test-Driven Development (TDD) relies on the repetition of a very short development cycle Starting from an initially failing automated test that defines the functionality that is required, and then producing the minimum amount of code to pass that test, and finally refactoring the new code. Michael Sorens continues his introduction to TDD that is more of a journey in six parts, by implementing the first tests and introducing the topics of Test Naming, Mocking Frameworks and Dependency Injection

  Read the article

• The .NET 4.5 async/await Commands in Promise and Practice

  The .NET 4.5 async/await feature provides an opportunity for improving the scalability and performance of applications, particularly where tasks are more effectively done in parallel. The question is: do the scalability gains come at a cost of slowing individual methods? In this article Jon Smith investigates this issue by conducting a side-by-side evaluation of the standard synchronous methods and the new async methods in real applications.

  Read the article

• A TDD Journey: 1-Trials and Tribulations

  Test-Driven Development (TDD) has a misleading name, because the objective is to design and specify that the system you are developing behaves in the ways that the customer expects, and to prove that it does so for the lifetime of the system. It isn't an intuitive way of coding but by automating the specifications of a system, we end up with tests and documentation as a by-product. Michael Sorens starts an introduction to TDD that is more of a journey in six parts:

  Read the article

• Caching: the Good, the Bad and the Hype

  One of the more important aspects of the scalability of an ASP.NET site is caching. To do this effectively, one must understand the relative permanence and importance of the data that is presented to the user, and work out which of the four major aspects of caching should be used. There is always a compromise, but in most cases it is an easy compromise to make considering its effects in a heavily-loaded production system

  Read the article

• Checking who is connected to your server, with PowerShell.

  - by Fatherjack

  There are many occasions when, as a DBA, you want to see who is connected to your SQL Server, along with how they are connecting and what sort of activities they are carrying out. I’m going to look at a couple of ways of getting this information and compare the effort required and the results achieved of each. SQL Server comes with a couple of stored procedures to help with this sort of task – sp_who and its undocumented counterpart sp_who2. There is also the pumped up version of these called sp_whoisactive, written by Adam Machanic which does way more than these procedures. I wholly recommend you try it out if you don’t already know how it works. When it comes to serious interrogation of your SQL Server activity then it is absolutely indispensable. Anyway, back to the point of this blog, we are going to look at getting the information from sp_who2 for a remote server. I wrote this Powershell script a week or so ago and was quietly happy with it for a while. I’m relatively new to Powershell so forgive both my rather low threshold for entertainment and the fact that something so simple is a moderate achievement for me. $Server = 'SERVERNAME' $SMOServer = New-Object Microsoft.SQLServer.Management.SMO.Server $Server # connection and query stuff         $ConnectionStr = "Server=$Server;Database=Master;Integrated Security=True" $Query = "EXEC sp_who2" $Connection = new-object system.Data.SQLClient.SQLConnection $Table = new-object "System.Data.DataTable" $Connection.connectionstring = $ConnectionStr try{ $Connection.open() $Command = $Connection.CreateCommand() $Command.commandtext = $Query $result = $Command.ExecuteReader() $Table.Load($result) } catch{ # Show error $error[0] | format-list -Force } $Title = "Data access processes (" + $Table.Rows.Count + ")" $Table | Out-GridView -Title $Title $Connection.close() So this is pretty straightforward, create an SMO object that represents our chosen server, define a connection to the database and a table object for the results when we get them, execute our query over the connection, load the results into our table object and then, if everything is error free display these results to the PowerShell grid viewer. The query simply gets the results of ‘EXEC sp_who2′ for us. Depending on how many connections there are will influence how long the query runs. The grid viewer lets me sort and search the results so it can be a pretty handy way to locate troublesome connections. Like I say, I was quite pleased with this, it seems a pretty simple script and was working well for me, I have added a few parameters to control the output and give me more specific details but then I see a script that uses the $SMOServer object itself to provide the process information and saves having to define the connection object and query specifications. $Server = 'SERVERNAME' $SMOServer = New-Object Microsoft.SQLServer.Management.SMO.Server $Server $Processes = $SMOServer.EnumProcesses() $Title = "SMO processes (" + $Processes.Rows.Count + ")" $Processes | Out-GridView -Title $Title Create the SMO object of our server and then call the EnumProcesses method to get all the process information from the server. Staggeringly simple! The results are a little different though. Some columns are the same and we can see the same basic information so my first thought was to which runs faster – so that I can get my results more quickly and also so that I place less stress on my server(s). PowerShell comes with a great way of testing this – the Measure-Command function. All you have to do is wrap your piece of code in Measure-Command {[your code here]} and it will spit out the time taken to execute the code. So, I placed both of the above methods of getting SQL Server process connections in two Measure-Command wrappers and pressed F5! The Powershell console goes blank for a while as the code is executed internally when Measure-Command is used but the grid viewer windows appear and the console shows this. You can take the output from Measure-Command and format it for easier reading but in a simple comparison like this we can simply cross refer the TotalMilliseconds values from the two result sets to see how the two methods performed. The query execution method (running EXEC sp_who2 ) is the first set of timings and the SMO EnumProcesses is the second. I have run these on a variety of servers and while the results vary from execution to execution I have never seen the SMO version slower than the other. The difference has varied and the time for both has ranged from sub-second as we see above to almost 5 seconds on other systems. This difference, I would suggest is partly due to the cost overhead of having to construct the data connection and so on where as the SMO EnumProcesses method has the connection to the server already in place and just needs to call back the process information. There is also the difference in the data sets to consider. Let’s take a look at what we get and where the two methods differ Query execution method (sp_who2) SMO EnumProcesses Description - Urn What looks like an XML or JSON representation of the server name and the process ID SPID Spid The process ID Status Status The status of the process Login Login The login name of the user executing the command HostName Host The name of the computer where the  process originated BlkBy BlockingSpid The SPID of a process that is blocking this one DBName Database The database that this process is connected to Command Command The type of command that is executing CPUTime Cpu The CPU activity related to this process DiskIO - The Disk IO activity related to this process LastBatch - The time the last batch was executed from this process. ProgramName Program The application that is facilitating the process connection to the SQL Server. SPID1 - In my experience this is always the same value as SPID. REQUESTID - In my experience this is always 0 - Name In my experience this is always the same value as SPID and so could be seen as analogous to SPID1 from sp_who2 - MemUsage An indication of the memory used by this process but I don’t know what it is measured in (bytes, Kb, Mb…) - IsSystem True or False depending on whether the process is internal to the SQL Server instance or has been created by an external connection requesting data. - ExecutionContextID In my experience this is always 0 so could be analogous to REQUESTID from sp_who2. Please note, these are my own very brief descriptions of these columns, detail can be found from MSDN for columns in the sp_who results here http://msdn.microsoft.com/en-GB/library/ms174313.aspx. Where the columns are common then I would use that description, in other cases then the information returned is purely for interpretation by the reader. Rather annoyingly both result sets have useful information that the other doesn’t. sp_who2 returns Disk IO and LastBatch information which is really useful but the SMO processes method give you IsSystem and MemUsage which have their place in fault diagnosis methods too. So which is better? On reflection I think I prefer to use the sp_who2 method primarily but knowing that the SMO Enumprocesses method is there when I need it is really useful and I’m sure I’ll use it regularly. I’m OK with the fact that it is the slower method because Measure-Command has shown me how close it is to the other option and that it really isn’t a large enough margin to matter.

  Read the article

• Caption Competition 9: Carry on Captioning

  - by Simple-Talk Editorial Team

  This picture below – the one with the rabbits, yes – is clearly something to do with databases. But what? Tell us in the comments – the best / funniest entry wins a $50 Amazon gift card.  Some suggestions to help turn on the comedy tap: The world’s first self-replicating cryptocurrency was hit by hyperinflation almost immediately. Early punchcard computers were ineffective but adorable. Elmer Fud teams up with Wile E Coyote to create the ultimate drop database. You can beat that. A child could beat that. Prove it in the comments below.

  Read the article

< Previous Page | 5 6 7 8 9 10 11 12 13 14 15 16  | Next Page >