Monday, December 29, 2008
CREATE PROCEDURE [dbo].[usp_GenerateRandomString]
@sLength tinyint = 10,
@randomString varchar(50) OUTPUT
SET NOCOUNT ON
DECLARE @counter tinyint
DECLARE @nextChar char(1)
SET @counter = 1
SET @randomString = ''
WHILE @counter <= @sLength
SELECT @nextChar = CHAR(ROUND(RAND() * 93 + 33, 0))
IF ASCII(@nextChar) not in (34, 39, 40, 41, 44, 46, 96, 58, 59)
SELECT @randomString = @randomString + @nextChar
SET @counter = @counter + 1
There are of course other ways to accomplish this – a very simple one would be to use newid() and grab whatever number of characters you need from it.
Monday, December 15, 2008
You can download the latest build of xSQL Bundle from: http://www.xsql.com/download/sql_server_comparison_bundle/
Thursday, December 4, 2008
If you are experiencing this issue with xSQL Data Compare either install the SP1 for .NET Framework which you can get from: http://www.microsoft.com/downloads/details.aspx?familyid=79BC3B77-E02C-4AD3-AACF-A7633F706BA5&displaylang=en or email us if for some reason you do not want to install SP1 and would rather just go back to xSQL Data Compare version 2.5 which does not require SP1.
Monday, December 1, 2008
It is for that reason that xSQL Data Compare provides a little known “switch” called “Synchronize with bulk insert” which will change the default behavior and instead of generating one INSERT statement for each row will generate only one bulk insert statement for each table in the comparison making for a much more efficient synchronization script.
The “Synchronize with bulk insert” option can be found under the main menu Compare / Data Options.
In case you are not familiar with xSQL Data Compare – it is a simple tool that allows you to compare and synchronize the data in two SQL Server databases and supports SQL Server 2008, SQL Server 2005 and SQL Server 2000. It is free for SQL Server Express databases with no limitations and it is also free for other editions of SQL Server but with some limitations. You can read more and download your copy of xSQL Data Compare from our website at: http://www.xsql.com
Thursday, November 20, 2008
We wish DeWitt and the Jim Gray Systems Lab great success!
Wednesday, November 12, 2008
- Support for SQL Server 2008
- Support for Sybase SQL Anywhere 10.0 and up
- Adds a “choose objects” page in the GUI where you can specify what object types you want to document.
- Support for all DBMS platforms in the "exclude objects" GUI. Previously this GUI supported only SQL Server.
- Support for arbitrary HTML in database comments – see /pt command line switch.
- Analysis Server: support for XML comments in MDX scripts. This enables documentation of calculated members.
- Support for cross database and cross server dependencies (through linked servers)
- Support for column dependencies (which columns of which table a stored procedure selects or updates.
- SQL Server: support for XML comments on objects that don't have their DDL stored on the server, such as tables. This is implemented with extended properties.SQL Server: add extended properties filter. Using this you can restrict by name the extended properties that are fetched by xSQL Documenter.
xSQL Documenter generates compiled CHM and html database documentation for virtually all DBMS platforms including SQL Server, DB2, Oracle, MySQL etc.
You can read more about xSQL Documenter and download your copy from: http://www.xsql.com/products/database_documenter/
Wednesday, November 5, 2008
- when you define a custom comparison key for a table in xSQL Data Compare the database schema (structure) is not affected - no changes are made to the schema;
- the custom comparison key can include multiple columns from the table in question;
- the key must be unique - that is the combination of the values of the columns included in the key should uniquely identify a row in that table.
- the comparison key defined on one of the tables in the comparison should match the comparison key defined on the other table - the rows from both tables in the comparison are paired based on the key values.
Tuesday, November 4, 2008
And remember your mail-in ballot must be received by the county election officials before the polls close otherwise your vote won't count. So, don't drop the ballot at the post office today - you are too late - instead, hand-deliver it to any precinct in your county (the one where you are registered to vote).
Monday, November 3, 2008
A survey conducted by SQL Server Magazine showed that about 81 percent of the participants are still on SQL Server 2000 and only about 40 percent are using SQL Server 2005. I am willing to bet that one year from now over 50% of those same people will be already using SQL Server 2008 - there are just too many cool and very useful features in SQL Server 2008 that make it really hard to pass.
All xSQL Software tools support SQL Server 2008.
Friday, October 31, 2008
- Simple: is the fastest batch parsing mode. It simply identifies batches in the source script with very little overhead.
- Common: the source scripts are parsed first to remove T-SQL comments, and then batches parsing takes place
- Thorough: the source scripts are parsed to remove comments, string constants, dynamic scripting and then the batch parsing takes place. The "Thorough" mode can take significantly longer than the other parsing modes. If you know that the source files do not contain dynamic scripting, Common or Simple mode will do the job well.
xSQL Script Executor is a free utility that provides for executing multiple T-SQL scripts against SQL Server. You can download your copy of xSQL Script Executor from: http://www.xsql.com/download/script_executor/
Thursday, October 30, 2008
- significantly improves the process of uploading trace data from the servers being traced to the central repository; we had received reports from users indicating that under stress conditions the pulling of the trace data from each individual server into the central repository could time out and hence there would be gaps in the trace data collected. The improved process performs significantly better and should eliminate any issues experienced with the previous build.
- allows the user to see the status of a given trace on each one of the servers on which the trace runs. In cases when xSQL Profiler may loose the connection with a server it will not be able to stop the traces that it has started on that server. Of course it would stop them when the connection is re-established but there was no easy way for the user to see if the trace was still running on some of the servers while stopped on some others. Now the user can simply "drill down" on a trace and see what is happening on each server.
xSQL Profiler allows you to define a trace and schedule it to run on any number of SQL Servers simultaneously while automatically collecting trace data from all servers into a central repository. xSQL Profiler supports SQL Server 2008, SQL Server 2005 and SQL Server 2000.
Tuesday, October 28, 2008
Why do programmers do this?
- resistance to change - the way you are used to doing things makes sense the new way usually doesn’t (to you that is);
- forgetting that your objective as a software developer is not just to “get it done” but to “get it done right”;
- not giving enough consideration to the potential future costs of the short-cuts you take today.
I would like to remind all developers out there (especially those that do take unnecessary, easily avoidable “short-cuts”):
- it may be you who will have to go back and spend a portion of your life doing the most annoying and boring job of all – that of tweaking your own code to make it compliant instead of spending the time creating new and exciting things;
- the costs associated with those kind of changes can be staggering for the company and if such costs could have been avoided, but for your negligence, then you really did not do right by the company. Or, to put it in simpler terms: you did not do your job and did not deserve the compensation you received, let alone any praise that your superiors lavished on you.
What’s the lesson? If it says “avoid using it” you better not use it, take the time and change your “ways” – it is not fair to the recipient of your work and it may come to haunt you!
Monday, October 27, 2008
- log errors;
- jobs and replication;
- help desk systems;
- disk space;
- up time
Check out Chad's presentation and download your copy of RSS Reporter today - it is completely free with no strings attached for one SQL Server instance.
Friday, October 24, 2008
while (1 < 1000)
This sparked my curiosity and I asked him: why not do “while (1 < 2)”? I was expecting something like “I just like it that way” but no, to my utter surprise his answer was – “I just want to make sure”! I guess he was afraid that after a few loops 1 could grow and become bigger than 2! I contained my laugh and did not say anything but the puzzled look on my face probably gave him a cue that something was wrong with his reasoning.
It is funny and it does make one laugh (a couple of colleagues couldn’t stop laughing when I told them the story) but I also find it very sad for a college student to be at that level.
- The most important thing is to have a standard that is accepted by the whole team and is followed (possibly enforced). The actual standard that is adapted is not very important. You should remember that there are two main goals that the standard (naming convention) is intended to achieve:
- Avoid potentially costly mistakes (for example dropping a table while you think you are dropping a view)
- Improve the productivity of the developers by making the code much easier to read (understand) and reducing the time it takes to locate an object.
- Given those two simple objectives it follows that the name of the object should tell us:
- What type of object it is;
- What is the base object on which this depends (if applicable)
- What does it do (Insert, Delete, Update, Create etc.)
- Other group identifiers if necessary
As far as specifics the only warning I am aware of (this was valid in SQL Server 2000 and I am not sure it applies to 2005) is that you should not prefix the stored procedures with sp_ - there is an article by Brian Moran on SQL Server Magazine (http://www.sqlmag.com/Article/ArticleID/23011/sql_server_23011.html) that explains why.
Thursday, October 23, 2008
What do you need xSQL Profiler for when you have SQL Profiler? Well, here are three important things you can do with xSQL Profiler that you can’t with SQL Profiler:
- you can define a trace once and run it on multiple servers simultaneously;
- you can schedule the traces to start and stop automatically;
- trace data from all servers is automatically collected into one central repository
xSQL Profiler supports SQL Server 2000, SQL Server 2005 and SQL Server 2008 all editions including MSDE and SQL Server Express.
Thursday, October 16, 2008
By default change tracking is NOT compared and neither is synchronized however the user can turn that on easily. It is important to note that Change Data Capture, which is different from Change Tracking, is not supported in this version of xSQL Object and xSQL SDK.
xSQL SDK and xSQL Object recognize, compare and synchronize data compression property for the following database objects:
- Primary Keys
- Unique Keys
By default data compression is compared and synchronized.
Wednesday, October 8, 2008
Following is the list of the new and enhanced SQL Server 2008 data types
- date: a date/time data type that stores only the date portion.
- time: a date/time data type that stores only the time portion.
- datetime2: similar to existing datetime data type, but with a larger fractional seconds and date range.
- datetimeoffset: a date/time data type that has time zone awareness.
- geography: a new data type for geodetic spatial data defined latitude and longitude coordinates.
- geometry: a new data type for planar spatial data defined by coordinate on a plane (used mostly for maps).
- hierarchyid: used to store hierarchical relationship.
- FILESTREAM varbinary(max): a variation of varbinary(max) that stores the BLOB data as file in the file system outside the database.
You can download those products from http://www.xsql.com/download/
Tuesday, October 7, 2008
We are very excited to announce a major new release of the following tools which now provide full support for SQL Server 2008:
- xSQL Object V3 (free for SQL Server Express)
- xSQL Data Compare V3 (free for SQL Server Express)
- xSQL SDK V3
- xSQL Builder V2
- RSS Reporter V3 (free for one SQL Server instance)
- xSQL Object Search V2 (free tool)
- xSQL Script Executor V2 (free tool)
What's new - a detailed description of changes and enhancements on this new release can be found at: <
- Support for all new or enhanced data types such as geography, geometry, hierarchyID etc.
Support for the table level change tracking feature introduced by SQL Server 2008.
- Data compression support for tables, primary key constraints, unique constraints and indexes.
- Support for the enhanced full-text catalogs and indexes.
- Support for Remote Service bindings for Service Broker.
- Improved object dependency handling.
You can download those tools from: http://www.xsql.com/download/
Wednesday, October 1, 2008
Here is the story. I was consulting for this marketing company who was working on a project for a big customer. An experienced data guy, let’s call him Frank (not the real name), on the customer’s side had provided the data for the project as well as some important pointers on what he thought we should be focusing on. Well during one of the meetings at this marketing company’s site when the discussion was going on a direction that completely ignored Frank’s suggestions I reminded them of what Frank had said. The person in charge, forgetting for a moment that I am a “data guy” by trade as well, said “Frank is just a data guy, what the …. does he know!”. As the last words were coming out of his mouth he realized the mistake but it was too late.
This marketing company has a small IT department which is “coincidentally” made up of “data guys” and I had seen this person tell those data guys how important they were to the company and how much their hard work was appreciated and blah, blah, blah… I was just glad those guys were not in the room at that moment.
As for me personally, I don’t hold any animosity towards this person with a marketing degree who for whatever reason (other than the fact that this guy makes twice as much as those data guys) thinks that he is smarter and better than those “data guys” who have computer and math degrees. I simply doubled my rate for this client and I am sure that in the next assignment (if it ever comes) I will be twice as smart J
There, now I can forget about this!
Wednesday, September 17, 2008
The performance reports included in this release are:
- Top N queries by number of times executed
- Top N queries by CPU time
- Top N queries by duration
- Top N queries by number of Reads
- Top N queries by Read/Writes
- Top N queries by number of Writes
Completely free with no restrictions or limitations for up to two SQL Server instances. Download your copy today from: http://www.xsql.com/download/sql_server_profiler/
Friday, August 29, 2008
SQL Server 2008 Express
SQL Server 2008 Express with Tools
SQL Server 2008 Express with Advanced Services
What are the key differences? Which one should I download?
First let’s look at SQL Server 2008 Express and SQL Server 2008 Express with Tools – there is virtually no difference in functionality – the only difference is that the download package for the SQL Server Express 2008 with Tools includes a bare bones version of Management Studio to allow you to perform some admin functions from a nice interface. Well, there is one more little difference – SQL Server Express with Tools includes PowerShell Integration which otherwise would have to be installed separately. So which one should you download? Well, if you already have SSMS (SQL Server Management Studio) installed on your machine then there is no reason to download the SQL Server Express 2008 with Tools.
When it comes to SQL Server Express 2008 with Advanced Services things are different – there are a few critical (this is relative term – those are critical services if you need them) services included in this edition that you otherwise don’t get:
- Integrated Full Text Search
- Reporting Services (increase RS memory limit; RS Word/Rich Text Export; IIS agnostic report deployment; Enhanced gauges & Charting; Business Intelligence Developer Studio)
Also, the SQL Server 2008 Express with Advanced Services includes Synchronization Services which in the other two editions would require a separate installation.
So, if you need any of those advanced services your choice is clear – download the SQL Server Express with Advanced Services.
You can learn more about SQL Server 2008 Express and download your copy from Microsoft’s official site at: http://www.microsoft.com/express/sql/default.aspx
Wednesday, August 27, 2008
PS not only can you trace SQL Server 2000, SQL Server 2005 and SQL Server 2008 but you can also trace SQL Server Express and MSDE instances.
Tuesday, August 26, 2008
This got my attention fast – consider the number of PCs in use and just imagine the impact this could have! I decided to dig deeper and inform myself so that I could potentially become an advocate of this cause – after all I would much rather convert my PCs to Vista than give up driving my car to save the environment (well, as the melting of the arctic ice continues we may have to soon give up the car and much more but if there is anything that would help us postpone that I am all for it).
As I started reading the paper the first thing that struck me was the disclaimer section which in essence says “if you believe anything we say here don’t blame us it’s your own fault” – I found this a bit discouraging but given that nowadays everybody and their uncle seems to have some claim against Microsoft I thought that this was the prudent thing for them to do and continued reading the paper.
So, what did the paper tell me? It told me that yes, potentially, assuming your PC stays on for 24 hours with neither the monitor nor the PC ever being turned off or going to “sleep mode” then yes the energy savings you could realize by either turning the PC off for the 16 hours you don’t use it or by replacing the operating system with Windows Vista, which by default will make the PC go to sleep mode when not in use, are significant. Based on published and accepted averages the carbon dioxide (CO2) you would keep off the atmosphere is about 1,178 lb which is the equivalent of about 10% of the CO2 emitted by an average car on one year. So, yes with those calculations 10 PCs converted to Vista equal one average car off the road. But, is the scenario considered in these calculations realistic? Absolutely not! Even in enterprises where the employees maybe instructed to leave their machines on so that they are available for software installs, patching and remote access the monitors are likely set to go to sleep mode so that alone removes a good portion of the presumed savings. Furthermore, what’s really critical to remember is that Windows Vista may make realizing energy savings easier and more convenient but those potential savings are available and can be realized without having to upgrade to Windows Vista.
Please note that this is not a case against upgrading to Windows Vista – the reasons for upgrading are abundant in many scenarios – but it is rather taking aim at bold and not always well founded marketing claims.
Is this smart marketing? I would say yes, in a World that is becoming more and more aware of the environment a claim like this has a high chance of getting the attention of the potential customers – it certainly worked on me.
Does it work beyond getting attention? The answer to this is a bit more complicated. While it may sometimes backfire, often it works. Here is why. For a mid level manager that is trying to make the case for upgrading, such claims made by reputable companies are a huge help. These managers don’t need convincing for themselves - they may have a lot of other, well founded but hard to explain to upper management, reasons for upgrading; what they need is ammunition that will help make their case a simple no-brainer and such claims give them exactly that ammunition.
Is it ethical? If the claim was false then this wouldn’t be a question but it is not false. I think the ground is a bit shaky here and so is my answer. To be completely on the safe side the statements should highlight the average scenario instead of the extremes but I can't quite say it is unethical to highlight the extremes.
Thursday, August 21, 2008
Please submit your vote on the right hand panel here and if you wish explain your rational on a comment to this posting.
Please note that this is completely hypothetical – no one’s fate is being decided by your response here but it would be very interesting to see if there is an overwhelming opinion in favor of one or the other.
Tuesday, August 19, 2008
Here are some simple rules:
- the complimentary licenses you receive are for your own personal use only (includes your own consulting practice).
- The offer is limited to a single user / single machine license for xSQL Object, xSQL Data Compare, xSQL Builder, xSQL Documenter.
- The offer is limited to a single installation and five SQL Server instances for xSQL Profiler and RSS Reporter.
Thursday, August 14, 2008
After I trashed his resume I started thinking and came to question my reasoning – maybe it is not this guy's fault, maybe it is the stupid recruiters and managers who often make the requirements section a laundry list of technologies and this person, like many others who are trying to find a job, is simply responding to this madness the best he can: by trying to cover all bases. Maybe this guy who has the courage to list everything is trying to convey the message: “I am a programmer. I can deal with any technologies you can through my way.” Or maybe his message is: “since you don’t really know what you are looking for I am as good as any other candidate out there”.
For the moment I don’t have an answer, just questions in my mind. However, one thing I know is that those type of job ads and resumes make for a very inefficient hiring process. When I am hiring a programmer I have two basic cases – case 1: my excellent resource planning provides me with sufficient time and money to find a great programmer and give him/her adequate time to catch up with the technologies he will need to use. If he is already experienced on those technologies all the better but that is not my main concern, my goal is to find the best programmer I can find with the particular technology being a distant second; case 2: due to my poor resource planning I need a programmer “yesterday” who must be productive on day one (like you know who… pun intended) – in this case, unfortunately I may have to sacrifice on the quality of the programmer and go for someone who has experience with the particular technology. So, the requirements section for the case one would focus on degrees, certifications and years of programming experience and I would simply indicate the technologies you are expected to work with. What I care about is that you are a great programmer and that you are willing to work with those technologies even if you have never worked on them before. Whereas the requirement section for case 2 would look something like: x years of hands on experience with C# and SQL Server. In other words just what I need you to work on the first day you come in the office. You may have a PhD in computer science but unfortunately it is not going to do me any good in the short term if you have never worked with C# and SQL Server.
I believe that a well written job ad that tells the potential candidates exactly what you are looking for will significantly reduce the amount of useless job applications while increasing the quality of the applications that come in. So, to all the recruiters and hiring managers out there – please take the time to write clear, concise, fluff-less job ads - you will be doing yourselves, and all those looking for a job, a big favor.
Finally, thanks for reading this and whether you agree with it or not, if you are a programmer and/or database administrator don't forget to check out our cool SQL Server tools at http://www.xsql.com/ - almost all of them have a fully functional totally free version that never expires and never asks you to register or activate - just download and save yourself hours of otherwise tedious work.
Tuesday, August 12, 2008
What a surprise! So, who did Microsoft have in mind when developing the other releases of SQL Server!?
We all misspeak at times but I find it very surprising (in a bad way that is) that a Microsoft corporate VP would do so on such a major occasion as the release of SQL Server 2008!
Tuesday, July 8, 2008
Why can’t we use the daily full database backups to always go back and figure out what changed when – after all the database backup does not only capture the data but it also captures the database schema? Yes, BUT:
- full database backups are in many cases only available for the last few days / weeks and you are not able to go back beyond that period;
- it takes a long time to let say restore 20 different versions of a large database and figure out what changed in the schema from one version to the other
in short, even if it is possible it is not practical.
A nifty feature in xSQL Object called “schema snapshot” addresses this issue in very elegant and practical way – it allows you to take snapshots of the database schema that:
- capture 100% of the database structure – not the data, just the schema;
- have a very small footprint thus enabling you to store them forever – the database itself could be in the tenths or hundreds of Gigabytes in size whereas the schema snapshot could be a few Kilobytes in size;
- can be compared with each other and with the live database instantly without having to restore anything – instead of taking days restoring and comparing trying to track down a change you can do that in minutes using the snapshots.
xSQL Object is free for SQL Server Express with no limitations and is also free for other editions of SQL Server as long as the number of objects in the database does not exceed certain limits that you can find here…
Friday, July 4, 2008
RSS Reporter for SQL Server is a great little utility that allows you to generate aggregate RSS feeds containing job status information from multiple SQL Servers eliminating the need for the annoying email alert notifications. A really cool feature is also the ability to right any T-SQL query and with a click of a button generate a standard RSS feed containing the results of that query.
To claim your free license go to: --this offer is no longer valid--
Wednesday, July 2, 2008
Tuesday, July 1, 2008
That’s where xSQL Object and xSQL Data Compare come in – both of those tools allow you to script an individual object in the database or the whole database schema and the script you get is properly ordered respecting the dependencies between objects so that it executes flawlessly.
Furthermore, those tools allow you to script the data as well or you can script both the schema and the data in one place. Scripting both schema and data together is very cool as it allows you to transfer a whole database from one server to another even if you can’t use a backup and restore. For example, you have a database hosted on a web server somewhere and you can execute T-SQL scripts against the database but you don’t have access on the server to be able to place a copy of your database backup which you can then restore. No worries, you simply generate a schema and data script of the whole database and execute that on the target server and there, you have transferred the whole database.
Best part: the scripting functionality included in the xSQL Object and xSQL Data Compare is completely free – no limitations, no expiration.
Tuesday, June 24, 2008
It is for that reason that we included the ability to synchronize with bulk insert – to do that the user simply checks the “synchronize with bulk insert” option and specifies a folder where the bulk insert files will be stored temporarily. The resulting sync script is easy to manage and will perform a lot better.
Wednesday, June 18, 2008
We have mentioned that one of the best things about xSQL Profiler is that it is free for up to two SQL Server instances but what we neglected to specifically mention is that not only does xSQL Profiler support higher editions of SQL Server 2000, SQL Server 2005 and SQL Server 2008 but it also supports MSDE and SQL Server Express. Yes, you can now use xSQL Profiler to trace MSDE and SQL Server Express installations.
Download, enjoy and don’t forget to send us your comments and suggestions!
Thursday, June 5, 2008
- tell your boss “yes boss I will take care of it” and then when Monday morning comes you tell him “sorry, I tried but I couldn’t get it done...” and then wait for him to fire you?
- tell your boss “yes boss I will take care of it”, throw your professional standards and pride off the window and “proudly” deliver a shoddy piece of work on Monday morning and wait for him to shower you with appreciation before he realizes that what you delivered was crap and fires you?
- tell your boss “this can’t be done” and that he is an idiot for trying to impose such an unreasonable order and just resign from your job?
- you assure your boss that you understand the urgency of the matter and politely ask for permission to go sit down in your corner, come up with a game plan and then meet with the boss to go over your plan?
Well, this is a no brainer you would say – yes, I know I don’t need to tell you that #4 is the best choice but I have seen enough programmers choose 1 and 2 and less frequently choose 3 that I believe a reminder can’t hurt. While number 4 is the obvious choice it is not necessarily the first thing that comes to mind when you are under pressure.
A small clarification - when I say that all three options 1, 2 and 3 will result in you getting fired I am exaggerating a bit to make the point but even if you don’t get fired at the very least your reputation will be severely damaged.
Choosing option 4 is a testament to your professionalism and experience. It gives you the much needed time to break the job down into more manageable and easier to estimate tasks. While doing that you may discover that it is possible to get it done by the deadline if certain resources are made available to you which would be great! But, even if your initial intuition / estimate is proven right and the job can not possibly be done you go back to your boss with an action plan that details what can be done by the deadline and when the whole job can be completed. At that point your boss has the ammunition he needs to go back to his boss and secure an extension of the deadline or modify the requirement / scope of the job so that you can deliver what may be the most critical part of the job by the deadline. Details aside, this approach leads to “happy endings” – the boss will respect you a lot more and chances are that he will never again tell you that “it must be done by Monday” but will rather ask for your professional opinion on what maybe doable and what not.
Tell me what you think – feel free to leave your comments here.
Wednesday, June 4, 2008
Here is the source of my frustration: we have a cool tool for documenting databases, xSQL Documenter that allows the user to automatically generate compiled CHM and HTML documentation for databases on virtually any platform – you can document SQL Server databases, Oracle databases, DB2 databases, MySQL databases, Informix databases, Access databases, PostgreSQL databases.
Now one would think (at least I think) that the keyword/phrase “document database” is as relevant as it can possibly get and a browser who plugs in “document database” on the google search box would be well served when directed to a page like the xSQL Documenter page. But, unfortunately the google algorithm does not “think” so – it keeps pushing me to increase quality or raise the bid and since there isn’t much I can do to improve the quality I am left with the other choice, that is, “raise the bid”! Are they price-gauging or is it just an imperfect algorithm?
Please feel free to comment or tell your google adwords story.
Here is a common scenario I run into:
A while back you have developed a web site that uses SQL Server on the back-end. The initial deployment was a breeze and everything worked perfectly. Now it is time to tweak and enhance the website with new features and functionality. In addition of making changes and additions in your code you start making changes to the database as well:
- added a couple of new tables
- modified a handful of tables (added columns, changed column types, added indexes, added /changed constraints, added new relationships etc.)
- added a few new stored procedures and views and modified some more of them
- added / modified a dozen user defined functions
- added / modified triggers
- etc., etc…
And all the while you diligently kept track of every change you were making knowing that you would soon need that log of changes.
After two months of hard work and sleepless nights, after testing and retesting everything you are ready to deploy your new version of the website – you know that deploying the application is a click of a button but the application is not going to work unless all the database changes you made are correctly propagated to the production database. You look at your long list of changes and realize that it is going to take hours to manually apply those changes. You wish you could just back up your development database and restore it on the production server but the production database contains live data which must be preserved.
So, you tell your client that you need to take the website off-line from 10 PM on Saturday night to 6 AM on Sunday morning. And when that day comes you take the site off-line, make a full backup of the database and start applying those changes one after the other. The cascading “can’t change this 'object' because it references this other 'object'” messages start to drive you insane but you have to do this so you keep moving along. After hours of frustration you seem to have reached the end of your list and as the sun is dawning you start getting more cheerful. You click on that “button” and publish your application – you run through some tests and everything seems to be working great!
You write an email to all people involved to let them know that you successfully published the new version of the website – pet yourself on the back and go to take a few hours of much deserved sleep. You have just fallen asleep when the phone rings and you get up again to take your client’s frantic call - customers are getting errors and not being able to complete their transactions – something is very wrong! Your nightmare scenario has come true! You jump into action again, quickly look at the error logs and try to determine if the cause is something you can fix quickly or whether you need to restore the old version of the database and application. Fortunately for you this time you discover that the culprit is a single Stored Procedure that had been modified but which somehow you missed. You change that SP and everything is fine – you are finally relieved, it could have been a lot worst!
The question is why anyone would go through this pain when they can use a tool like xSQL Object which in matter of minutes can identify all the database changes that were made and generate a safe SQL change script that will propagate those changes to the production server. It appears to be even more puzzling when considering that xSQL Object is completely free for SQL Server Express and it is also free for other editions of SQL Server as long as the database contains less than a certain number of objects. Even if you were in the small group of users (less than 10% of our user base) who work with larger databases and have to pay a couple hundred dollars xSQL Object more than pays for itself in just one time you use it. The answer unfortunately lies in our inability to reach out to all those potential users to let them know what they are missing. So, if you read through this please help spread the word – let all your friends and colleagues know that they can download this tool and use it to save many valuable hours of their time.
Tuesday, June 3, 2008
- load the app with annoying messages trying to push you to purchase the commercial version of their software;
- severely limit the functionality of the application and every time you click on let's say a button that presumably gives you access to some critical functionality you are invited to purchase the commercial version;
- implement trial periods after which you can't use the software anymore.
Enter xSQL Object (schema) and xSQL Data Compare (data) two of the best database comparison and synchronization tools in the market - when you download xSQL Bundle that includes those two awesome tools you get a truly free set of very helpful tools.
- no requirement to register or activate;
- no annoying reminders inviting you to purchase the higher version ;
- full functionality
Judging from our statistics over 90% of our users fall into the free category that never needs to pay a dime for those tools and is never bothered by reminder messages.
Friday, May 30, 2008
No more wasting time updating your many bosses with the results of whatever ad-hoc queries they keep asking you to run for them, no more IT meetings to decide whether you need to take the time to implement a report that you think you will be asked to generate again and again. With RSS Reporter you write your query once and you send your bosses the link to the automatically generated RSS feed and they can get an update anytime they want, on whatever device they want without having to bug you ever again for that same query.
Is this cool tool expensive you ask? How does free sound to you? Yes, it is completely free for one SQL Server instance and only $99 for 5 SQL Server instances.
You can read more about it and download your free copy from: http://www.xsql.com/download/rss_reporter/
Wednesday, May 28, 2008
So, what is a DBA to do? Well, fortunately the solution is very simple – it is called RSS Reporter for SQL Server (http://www.xsql.com/products/rss_reporter/) - it is free for one SQL Server instance and extremely affordable for more instances. You simply add each server you need to monitor into the RSS Reporter workspace, set the options to filter only the jobs and conditions you want and the RSS Reporter automatically generates an aggregate feed that gives you a complete view of all the jobs from all the servers and even better, it allows you to drill down on each job and view the details. You can subscribe to and read the feed from any device without having to worry that the alerts are not coming. You go on vacation – no worries – someone else can easily monitor the job status feed – nothing to setup or configure, the other user simply subscribes to the same feed you get. Now that’s beautiful!
Check it out for yourself and spread the word to the community – let’s not keep it a secret anymore.
Thursday, May 22, 2008
Before we get to "what sets xSQL Profiler apart" we need to first explain what it does so here is the brief list of what it allows you to do:
- register SQL Server instances and databases in the xSQL Profiler workspace;
- define highly granular events that you may want to trace (a predefined set comes with the product);
- define traces (choose the servers / databases you wish to trace; choose the events; set filters so that you can trace exactly what you want nothing more, nothing less);
- schedule traces - you can define as many start/stop intervals as you wish and set those intervals to occur only once or recur daily, weekly, monthly;
- view trace data using the xSQL Profiler predefined viewing panel that allows you to filter sort and group the events;
- query the central repository directly using T-SQL.
Now, how is this different from the rest of the tools out there:
- you can deploy this monitoring and auditing platform in minutes days, weeks or months - there are no agents on the servers that will be traced - you simply install the xSQL Profiler on a workstation or server where the xSQL Profiler service will also be running and you are done with the installation.
- allows a high level of granularity in defining events / traces thus eliminating the unnecessary load placed on servers when you trace more than you need to.
- It is so simple even a "cave man could do it" - these geico commercials are getting to me :)
- IT IS ABSOLUTELY FREE (only for up to two SQL Server instances that is - what did you think, someone's got to pay for all that work we have done) <- -="" any="" couldn="" fine="" finer="" here="" i="" it="" li="" make="" print="" read="" t="" than="" the="" this="">->
Tuesday, May 20, 2008
Now, at this point, this is not a moral dilemma for us yet as we have a long way to go to get to the position where we may have enough power to get to such exclusive advertising deals - of course not with google but with small user group sites etc., but we have more than once experienced knocking on some doors and getting turned down with a "sorry, we can't advertise your products as they compete with the products of one of our special sponsors". Of course that does not feel good... but when reason overpowers those bad feelings we find ourselves asking the question "wouldn’t we do the same thing if we could?".
So the question to all of you is: if you had the power would you do it or not, and why yes or no?
Thanks for your comments!
Friday, May 16, 2008
The user can choose to wrap all the scripts in one big transaction, wrap them in separate transactions or execute them without wrapping them in transactions.
So how is xSQL Script Executor helpful for database administrators? Well, here is a simple scenario:
- you are the database administrator in charge of executing the scripts that individual developers and dbas from different units in your organization place in a repository daily.
- Each unit has its own “folder” where its developers and dbas place their scripts.
- Every night you are faced with this simple but daunting task – execute those scripts against one or multiple databases. What are your options:
o Manually open those scripts one after the other and execute them and hope to be done by next morning; OR
o Get xSQL Script Executor, spend a few minutes creating the list of folders/scripts to be executed, order them, automatically generate command line commands – one for each server/database the scripts need to be executed against, put those in a batch file, schedule the batch to run when you want it to run and you are done. There, that part of your job has been converted from many boring hours into… well, whatever you want it to be – you can still pretend you are busy running those scripts though.
Don’t forget to do your friends a favor – tell them about this life saving (well for some it is) tool – here is the link again: http://www.xsql.com/products/script_executor/.
Wednesday, May 14, 2008
Project x is in jeopardy! While you may have been led to believe that the project is near completion that is as far from the truth as it can get, not only is the project nowhere near completion but if things continue the way they are it will never be complete…”
I can only guess that hundreds of commanders on the ground in Iraq have in their mind probably written a similar letter addressed to the Commander In Chief when he declared “Mission Accomplished” at a time when the mission had just started. Tenths of engineers and workers at Boeing subcontractors have probably done the same as Boeing suffered one embarrassing delay after the other on its Dreamliner.
At one capacity or another we have all, especially those of us in the IT field, likely been actors in similar “plays” at some point in our careers and we know how those usually unfold. The question I wish to discuss is: what are the factors that create the conditions for such “doomed from the start” projects and how can an organization reduce or eliminate the likelihood of such conditions forming?
Before we get to the answer of that question let’s clarify something that has the potential to confuse the analysis / discussion. There are “do or die times” when a critical business need dictates what would normally be considered unrealistic deadline – in such cases everyone from the very top to the very bottom is aware of the situation – if the people involved are able to pull it off they will be considered, deservedly so, heroes – if they fail everyone understands. Such scenario is not subject of this article – what I wish to analyze and discuss here is unreasonably tight deadlines that are not unequivocally dictated by a true business need but are brought about by other factors.
Here are what I believe to be some of the main contributing factors:
- ignorant management that has no idea of the effort required coupled with an autocratic style (one may argue that ignorance of management often leads to autocracy);
- lack of trust on the commitment of the team coupled with lack of productivity measuring tools and techniques;
- overambitious and cowardly managers trying to please their bosses at any cost;
- failure of the management to recognize that team members are humans and have lives beyond work;
- insecure team members choosing to deal with failure later rather than facing the management now;
- inexperienced team ready to “bite” any size project oblivious of its “chewing” capability;
An occasional project failure may be an indication that one or more of those factors came into play when that project was defined and approved but the organizations self-correcting mechanisms that are derived from that organization’s culture are working properly. A frequent occurrence of project failure on the other hand would be an indication of a much deeper problem related to the culture of the organization in question which is hard to change.
How can an organization cultivate the right culture – the one that produces success rather than failure?
There are two ways to cultivate a certain culture amongst a social grouping:
- the “bible way” – that is, if you do or don’t do something you are going to hell / heaven; In this case the motivation of the individuals to do or not do something is their natural desire to secure a better future for their next life.
- The “reason way” – that is if you do or don’t do something those are the direct consequences and this is how those consequences ultimately affect you. As a reasoning being you are of course expected to choose to do / not do what benefits you the most or hurts you the least in this life.
For the purpose of this discussion I would classify the “if you do/don’t do this you will be fired” under the “bible way”.
While the “bible way” has been extremely effective in cultivating a culture where stealing, cheating, hurting others etc. are unacceptable I believe that it wouldn’t be very effective in cultivating the right culture in an organization. An organization must rely on the “reason way”. You could put a big banner in the main entrance of your headquarters building stating: “Autocracy is not the remedy for ignorance, education is!” but is that going to create a culture where autocracy is not acceptable – no, it is not going to accomplish anything. A long and persistent educational campaign dissecting the autocratic style and identifying the advantages and disadvantages of it and how it affects all involved is needed.
The same is true for any and all aspects of an organizations culture – slogans don’t produce culture, reason does.
This just scratches the surface of this deep and very interesting subject but it makes the point I wanted to make, namely that when a project fails the organizations culture should be scrutinized and furthermore it should be recognized that adjusting the organization’s culture can not be mandated – it can only be accomplished through a long and persistent educational process.
Feel free to leave your comments here – I can’t promise to respond but I will definitely read them all.
Thursday, May 1, 2008
Another thought flashes through your mind – how could Query Analyzer/Enterprise Manager / SSMS do this – shouldn’t I get a warning of the type "hey you are trying to update this table but I don't see a where clause. Are you sure you want to do it?"? Sure, you can blame the tool for your stupidity but that’s not going to help – the tool is just that, a tool and if it does what you tell it to do then it is really doing its job.
Then, you start beating yourself up: "why o why didn't I wrap this in a transaction" - after seeing that all rows were affected I could have simply rolled back and no harm would have been done. Well, you deserve the beating!
Are you completely doomed? Not necessarily, it is not time to run yet so pull yourself together and think this through. First if the table/columns you updated contain data of critical importance it is likely that those columns are being audited – so you can look for that audit table and use that to clean up the mess you just made. If no such thing is available then your next and likely last lifeline is the backup – restore the most recent full backup as a new database, apply the subsequent differential backups and the logs up to the last log backup before you made the mess - after you do that you can safely repair most of the damage. If for whatever reason you don’t have a backup either then you are officially doomed – just run for cover.
I emphasized here the most extreme case, that of a completely missing where clause but this applies equally to the more likely case of an incorrect “where clause” that filters the wrong set of rows. The difference is the scope (the number of rows affected) and the fact that while it is very likely that you will immediately realize what you did in the case of the missing where clause in the case of an incorrect where clause you may not even realize that you messed up until a future event uncovers the mess.
So, what’s the lesson here:
- if at all possible do not run ad-hoc update queries on a production database; A production database should only be updated through thoroughly tested interfaces that implement proper checking and warning mechanisms to avoid un-intentional changes to the database.
- if you must (that should be rare) then:
o make a copy of what you are about to change – if you have to reverse the changes this copy will save you a lot of time and frustration;
o always wrap your statement /script on a transaction and before you commit the transaction make sure to verify the number of rows being affected. You may also want to spot check some of the rows that were updated to ensure that what happened is what you intended to happen.
o Insert detailed comments/explanation in the query/script you executed including the server and database against which you executed it and the exact data and time you executed it and save it.
- Always keep in mind that updating a production database is no joking matter so be safe, don’t take shortcuts!
Wednesday, April 30, 2008
A shortsighted Lou Dobbs’ type analysis would conclude that offshoring is hurting the American people – high paying jobs are moving overseas at a fast pace and the Americans are getting the short end of the stick! The people who conduct such analysis are strong advocates of protectionism as the remedy – they want the government to intervene and close the gates to offshoring. While to many programmers and people from other professions who have lost their jobs to their offshore equivalents this may sound great, in the long term such policy would have devastating consequences.
Let’s rewind a few decades and draw a parallel (albeit not a perfect one) with the time when the government intervened to protect the once flourishing auto industry by restricting the imports of the foreign cars to the country. In the short term that was great for the American auto industry and consequently for the armada of people that the industry employed – our great auto companies continued to prosper un-challenged. That is exactly what set them up for the huge catastrophic failure that we have been “watching” unfold in the last few years. By shielding them from the competition in essence the government ensured that they would gradually turn into impaired, intellectually challenged, slow reacting, bureaucratic organizations incapable of competing in the open market. How would that have played if the auto industry was left to face the challenge on its own without government help? I am sure there would have been some “bruising” but the American ingenuity and entrepreneurial spirit would have certainly emerged victorious from the “battle” and we would all be now driving reliable, safe, comfortable, elegant, and highly efficient, American made cars.
Is it painful when the lower cost of the overseas resources puts downward pressure on the compensation of American employees or even worst when an American employee’s job moves offshore? Yes, it is very painful but it is a pain we have to endure for the sake of the long term survival. The government intervention would be like delaying an urgent and absolutely necessary surgery for a patient and giving him painkillers instead. At some point the painkillers loose their power and the patient is in great pain but unfortunately at that point it is too late for surgery…
It must be understood that this phase we are going through is not a common, normal phase – the World has moved somewhat abruptly from a World of multiple closed National Economies with some level of strictly controlled exchange between each other into an open market / global economy that is currently undergoing the greatest transition it has ever seen. It is like having some 200 separate bodies of water all at different levels and each of them connected to each of the other ones through a highly complex system of locks – if all of the sudden all the lock gates were open one can only imagine the kind of havoc that event would wreak, but eventually a new equilibrium state will be reached and things will be peaceful and normal once again although completely different. Until the water settles all we citizens of the World (not just we Americans) will have to learn to swim on those water currents.
Feel free to leave your comments here whether you agree or not and if you are a programmer or a database administrator and work with SQL Server please don’t forget to check out our great SQL Server tools here: http://www.xsql.com/ and read about our “pay if you can” licensing here: http://xsqlsoftware.blogspot.com/2008/04/unique-hassle-free-software-licensing.html.
Tuesday, April 22, 2008
Almost every software company out there gives away some version of their software but usually the give away version is either a watered down version with severely limited functionality or a version loaded with annoying messages trying to get the user to pay for the full version.
Our approach however was fundamentally different. First, we “discovered” that not everyone gains the same amount of benefit from our software – while for some it pays off with a single use for some others it may take multiple times to get a return. Second, while for some a couple of hundred dollars for a useful piece of software is nothing, for some others it is indeed a big deal and it so happens that, most likely, the ones who would really think twice before spending any money on software are the ones who whose benefit would be relatively smaller. So, we decided that anyone, individual or company, for whom a couple of hundred dollars are indeed a big deal should not have to pay at all – only if and when they would reach a certain threshold then they would have to pay.
The challenge then became how to determine who should and should not pay for our software. Well, while that may not generally be a trivial task in our case it wasn’t very hard since our products are tightly related to SQL Server and we know that:
- if you are using the free SQL Server Express then there is a high likelihood that you have not yet made it in the big league and you are counting every penny;
- if the databases you work with, regardless of what edition of SQL Server you may be using, have a small number of objects in them (tables, views, stored procedures etc) then not only is that generally an indication of a small business but the benefit you gain from utilizing our tools is directly correlated with the size and complexity of the databases you work with.
- everyone downloads a fully functional version of our software from our website – no hassle, no registration, no reminders, no limitations.
- after 2 weeks have passed the software checks the edition of the SQL Server and if it is SQL Server Express then it simply continues to work without hassling the user in any way shape of form; If it finds that you are trying to compare and synchronize a database that is on another edition of SQL Server then it performs another simple check – it looks at the number of objects you have on the database and if those are below a certain limits then again it does not bother the user at all - in fact the user does not notice anything the product simply continues to work.
- Only if the 2 weeks have passed AND it is a SQL Server edition other than SQL Server Express AND the number of objects in the database exceeds certain limits THEN we kindly remind the user that this edition of our software does not support comparing those databases. At this point we have established two things – first that in this user’s case our software will pay off for itself right away (in fact it has likely paid off already during the first two weeks) and second that this user can certainly afford it:)
A few words about the tools discussed above: xSQL Object allows programmers and database administrators that work with SQL Server to compare the structures (schemas) of two databases, see where the differences are, generate the change script that synchronizes the structures and execute the script. It supports SQL Server 2005, SQL Server 2000 and will soon support SQL Server 2008. All objects are supported – tables, views, stored procedures, user defined functions, users, permissions, roles, assemblies, service brokers, synonyms, assemblies etc. xSQL Data Compare provides for comparing and synchronizing the data in two SQL Server databases.
Saturday, April 19, 2008
Extending the trial period of a product without permission is an immoral shameless act of stealing that does not merit any discussion but what caught my attention was one of this idiot’s assumed reasons as to why one would want to go through the trouble of engaging in such activity. Says he: “Or you may be decided that the shareware is not worth to pay…”! Truly astounding! In today’s World we are constantly trading – trading our time and energy for money, our money for goods and services we need etc., and we make hundreds of “is it worth decisions” but most of us understand that worthiness is relative and the decision we make in that regard simply determines whether we choose to engage in the trade or not but it does not, it can not, entitle us to anything.
Monday, April 14, 2008
If you work for such organization and would like to obtain a license please contact us.
Wednesday, April 9, 2008
- Trace multiple SQL Servers - no agents to install
- Use predefined events or define custom events
- Powerful filtering capability, precise tracing
- Schedule traces to run when and where you want
- Flexible reporting interface
- Support for SQL Server 2008/2005/2000
Download a copy of the xSQL Profiler from: http://www.xsql.com/download/sql_server_profiler/ and help us make this a great tool by telling us of any issues you may discover as well as any suggestions you may have - post your suggestions/issues at: http://www.xsql.com/suggestfeature.aspx
Tuesday, April 8, 2008
Short of finding someone who has both the degree and the experience would you hire someone with a degree and no experience or someone with experience but no degree?
Is there a rational behind the equivalency of a degree with certain number of years of experience?
To make those questions more specific and relevant for this audience: would you rather hire someone with 5 years of programming experience but no degree or someone with a Computer Science degree but no programming experience? How many years of programming experience does it take to make up for a computer science degree?
Before attempting to answer such questions it is critical to identify and analyze the differences between a degree (the work to obtain one) and the real life experience.
The most fundamental difference between the two is the focus that each has. The real life experience focuses on HOW things work – years of experience will help one become a master of a given trade and remain a master for as long as that trade does not experience any radical changes. A college degree on the other hand focuses on WHY things work the way they work. The HOW part of a degree is generalized – how things should work but not necessarily how they work in a given implementation.
An analogy that makes the difference clearer would be that of the experienced mechanic versus the mechanical engineer. A mechanic that has spent 5 years working on fixing and maintaining BMWs is likely a master of the trade – he can diagnose and fix BMW problems very quickly and efficiently. A mechanical engineer on the other side may have no clue of the particular BMW implementation – he knows how cars in general are put together and why they are put together that way but not how to fix a BMW.
Now we can begin to answer the questions we posed at the beginning.
Which one would you hire? Clearly the answer is: it depends! If you need someone to handle a well defined task that does not require creativity but simply diligence and efficiency then an experienced person is clearly better. But, if you need someone that can easily adapt to different scenarios, if you need someone who can learn new technologies faster and consequently have lower probability of becoming obsolete despite the evolution of the trade in question, if creativity is important to the job then the degree has a clear edge over the experience.
Can one make up for a degree with number of years of experience? No, it is not possible! Please do not confuse this with the case when an individual dedicates an equivalent amount of time to studying the trade in question, in essence doing the work that is required to obtain a degree – I consider such person a degreed person regardless of whether a formal degree was obtained or not. When I say ‘No’ I am speaking strictly in terms of work experience. A mason can spend a lifetime laying bricks and never learn how to calculate the load on a load bearing wall or beam. Experience provides one with what I call “superficial knowledge” that lacks the foundation and can not be used to derive new knowledge – for example: you know that if you touch an electrical wire (household voltage) with a dry wooden pole you will not be electrocuted but you can not extrapolate from that the new knowledge that if you get on top of an wooden chair you can safely touch a single wire with your bare hands (remember, a single wire only – if you grab two you will be the light bulb!). A degree provides solid knowledge that can be used for generating new knowledge – experience teaches you that touching the wire with a dry wooden pole is safe whereas school teaches you what the electricity is and how it works. An electrical engineer does not need to be told about the dry wooden pole he can deduct that knowledge and a lot more from the foundational knowledge that he has obtained in school.
There, I have made my point.
Before you react to this please keep in mind that:
- I am comparing degree versus experience on a specific trade;
- A degree does not necessarily mean a formal degree – but rather that one has paid his dues in time and energy spent studying the trade;
- I am comparing two individuals with similar levels of intelligence/aptitude.
Feel free to leave your comments here.
Monday, March 31, 2008
Thursday, March 27, 2008
Not trying to make any point here – just an observation on one of the aspects of the evolution of the profession. Do you agree with this observation?
Wednesday, March 12, 2008
Background: I was recently at the site of a client who was concerned that the CPU utilization had shot up in the last few months without any apparent reason. Since this was a node on an Active/Active cluster and was supposed to be the failover node for their more important node they had moved quickly and upgraded from a 64-bit dual processor to a 64-bit quad. The CPU utilization had dropped from over 70% to somewhere in the 30-35% range however the concern was still there – they wanted to find out what caused the trouble in the first place.
Trouble starts: After spending a bit of time understanding the environment, looking at the trends and documenting the current status I decided to start a trace on the effected node and save a few minutes of data on a SQL Server table. I was feeling pretty good about it until I clicked on “start” and the trace fired away. I looked at the CPU utilization and I noticed, to my utter surprise that it dropped significantly and it was staying down – I did not like that and stopped the trace immediately (it must have been running for about 65 seconds).
There, now I had another puzzle on my hand – was that a simple coincidence that as soon as I started the trace it so happened that the “demand” on the server dropped, maybe a great number of users just logged off and went to eat lunch or was it something else?! Soon after a very concerned user came buy and was telling the resident IT person in charge that this third party application had gone haywire – it had started assigning case numbers from the beginning of time creating duplicate entries in the system etc…
Now I am not going to go into how totally unacceptable it is for a professional software application to behave that way… regardless, it was my trace that somehow caused this trouble that ended up taking hours of manual labor to clean up.
So, now I am stuck, I am looking at all kinds of reports but without current trace data I can’t see what’s going on and after what happened I don’t dare start another trace.
Solution: After I ran out of alternative options I decided to give it another try – this time I was not going to run a trace from SQL Server Profiler on my remote client machine but instead a server side trace. Furthermore, instead of storing the trace data on SQL Server I directed it to file on another drive. I got the script ready and keeping my fingers crossed executed it while watching the CPU utilization – this time I was pleasantly surprised to see that nothing seemed to change, the trace was not affecting the CPU utilization at all – I also had someone monitoring that third party application to make sure that it wasn’t going haywire again – everything seemed good.
I let the trace run for a whole hour. I downloaded the trace files (a couple of gigs of data) and uploaded all the data in a SQL Server database on my local machine. Now things were a lot easier – it did not take more than a few minutes to identify the query that was causing all the trouble - a trigger from that third party had failed and a couple of tables had ballooned to hundreds of thousands of rows when they were supposed to have no more than something like 50 rows! Trigger was fixed and CPU utilization went down to 10% and it is staying at that level.
Lesson: Overall it was a great success but not without some bruising on the way. The lesson learned – never run a client side trace on a sensitive production system it can bring the server to its knees.
Related Info: In case you don’t have much experience and are wondering how to run a server side trace here is a quick guide:
- Start SQL Server Profiler on your local machine;
- Click on “New Trace” and connect to a non-sensitive server – if you have an instance of SQL Server on your local machine all the better – just connect to that one;
- Define your trace;
- Start the trace;
- Stop the trace;
- Go to File / Export / Script Trace Definition / For SQL Server [2005 / 2000];
- Open the just saved script and change the path/file where you want your real trace data to go. Also, make sure all the other parameters are what you want them to be.
- When you are ready to start the trace on the “real” server simply execute the script against the target server.
- Note down the trace id so that you can stop and delete it when you are done.
Lastly, check out our products a lot of them are free - see the left panel here.
Wednesday, March 5, 2008
- Supports all objects in all major dbms-es including SQL Server, Oracle, DB2, MySQL
- Generates HTML and compiled CHM output
- Generates dependency and primary/foreign key graphs
- Shows DDL/XMLA code for all documented objects
- You can run it from the command line
- You can easily brand the documentation