LinkedIn as your Stepping Stone for Opportunities with Internationally-Oriented Organisations

 As some of you may have read already, Andy Warren‘s series on LinkedIn (part 1, 2, 3) and networking has sparked my interest by first listing down what I would like to share about this brilliant business networking utility.  And then proceeding to read his – from first glance, he’s done a great job at listing all the contact utility functionality,

The prerequisites, and why I’d like to add to Andy’s recommendations (really hoping I didn’t trump a part four Master WarrenJ)  for being able to really take advantage of this tool would be completing one’s profile to the 100% level and obtaining as many recommendations as possible.  Currently, I am at twelve, so I believe establishing credibility by means of online references is a significant prerequisite to mastering LinkedIn’s networking potential – because if you recommend someone online, they are taking a leap of faith in you, it’s something they are willing to state in front of the entire world basically.

You’ll be pleasantly surprised also, that if you describe the way you work exactly (e.g. personally, I described following Brad McGehee’s Exceptional DBA guide), or your preferred methodology, it will allow you to bring in qualified clients that have had the chance to filter out obvious signs or attributes from other profiles, such as stagnation, lack of recommendations, or territorialism, that can be undesirable (some of those may also depend on how long their profile has existed, so no hard/fast rule, each situation could be different).  LordAlex, my Flash Guru mate here, loves to describe it as a method to make a pillar of the all-important (in this net generation) Online Persona.

Further, it should be treated as a longer than usual Curriculum Vitae (or Resumé in N.A.) but in accordance to the format obviously, because perhaps if you place details in the wrong portion of your profile, an opportunity could easily be missed.  I love the way a mate here in Montreal (Martin Arvisais) describes it as a great place ‘pour vendre ta salade’ (cute local way of saying to sell your stuff). 

Another good reason to do it is, to be quite forthright, showing how you can contribute to your professional community – as Andy Mentioned in Part 2, just after ramping up your contacts within this tool.  There are several SQL Server related groups in LinkedIn, my recent contributions through the LinkedIn groups are part of the reason why Canada’s MVP Lead approached me over the past week for a nomination (also, thanks to a referral from SQLServerToolBox.com ‘s Scott Stauffer, and frequent speaker, a SQL DBA based in Vancouver) – therefore, what more motivation could one implore to Link themselves In.

 

SQL Server 2005/8 Database Compression Presentation in Burlington, Vermont - April 15th

Yesterday, I drove the Smart down to Burlington/Colchester, Vermont to meet up with MVP Roman Rehak and speak to the local user group about SQL Server Row, Page and VarDecimal compression, originally touched during this post.

The final version of the presentation is here.

I had much more performance gains on the SAN before (vardecimal), so for those of you with SSDs, perhaps the test scripts may not really show a big difference for the SELECT times...however, at least major disk space at least will be gained. We started with a table of 260MB and ended up dropping its size down to 80MB.

Here are the essential parts fo the script for you to test out compression on your own databases:

-- all SQL Server internal compression is done at the table level
-- testing started on a table that was 260MB, with a check on the storage used each time
-- I/O , according to the Actual Execution Plan for a normal SELECT started at 24.17 (no compression)

--first round of compression
ALTER TABLE schema.TableName REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = ROW)  -- table became 180MB

--second round of compression
ALTER TABLE schema.TableName REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = PAGE)  -- table now down to 80MB
-- I/O cost, according to the Actual Execution Plan for a SELECT down to 7.62 (just under a third)

-- after all your compression work is done, run a single console command
DBCC SHRINKDATABASE(name,0) 
– replacing the zero with the amount you want to leave free

 

Activity Monitor in SQL Server 2008: an Embedded Optimisation Gizmo for the Thrifty DBA

As promised back in December ‘08, after having spoken briefly at SQL teach here in Montreal thanks to MVP Paul Neilsen (SQLserverBible.com), here is an introduction to one of our favorite new Management Studio Enhancements: Activity Monitor.  To use AM, right click on a SQL Server 2005/8 instance in the object explorer and take a look, it’ll inspire you to take care of what’s bogging down your database engine and motivate you for some good ol’ Spring cleaning.

First, let me start off by mentioning that even if you do not have SQL 2008 instances, it is worth it to install SQL 2008 Management Studio (SSMS is the client tool acronym to manage your SQL Server infrastructure) just to have this critical problem resolution feature known as Activity Monitor.  SSMS has backwards compatibility built-in; thus you can take advantage of the dynamic management views already existing in SQL 2005 while connecting from SQL 2008 SSMS’ Activity Monitor (AM).  To view the equivalent information used in the AM before, for example, I was loading information directly from the dynamic management views in Excel sheets to understand what was really going on across the specific servers’ activity. Thankfully, this is now all built into SSMS as AM and provides sortable columns, which enables exceptionally swift pin-pointing of problematic operations.

Ever since the RTM of SQL 2008 was released last summer (unless perhaps, you had beta versions) you can view real-time critical SQL Server performance details and even sort by the worst performing queries, whether it be by the number of times the offending code is run per minute, which login is running it, which database it is in, the application it is running from, the number of logical reads…you get the picture, practically everything you need to fix SQL Server tribulations – even giving the option to right click on a line in Recent Expensive Queries to get down to optimising the offending code right away!  There are four panes with graphs for each, plus collapsable details, so you can even view/filter processes, resource waits and disk activity, as well as my favorite Recent Expensive Queries.  By hovering over any of the columns within the respective information panes, one can also see which dynamic management view was used to provide the systems management information; for further investigation and perhaps even set up alerts for when thresholds are met.

If you cannot update your instances to SQL 2008 for a while, which would not be surprising considering the economic tsunami hitting the world (and now a Pandemic!), then at least you can use the updated client tools to enjoy this eye-opening and cost-effective updated feature – in my opinion a critical step in remediation.

On that note, Happy Easter / Passover to all my readers J

Dot Net Usergroup in Montreal Presentation notes - Grouping Sets in SQL Server 2008

On March 11th, 2009, I gave a talk about Grouping Sets in SQL 2008 thanks in part to the original SQL Server Central front-page blog post on the subject and Rushabh Mehta's inability to be in two places at once:) 

As promised to those of you who were at the meeting (and for anyone else of course), here are the presentation notes as well as example scripts.

Speaking is very motivating to a very receptive crowd (as it was that night), and there are two more talks I have lined up:

1) in April:  SQL Server Data Compression in Burlington, Vermont, thanks to Roman Rehak's invite. Date to be posted as soon as I find his e-mail with the schedule.

2) June Will be joining Mario Cardinal, Jean-Yves Roy, and Erik Renaud at Vancouver’s International Developer Conference - Discussion on why Developers should care about Transaction Log Files

Merci à tous qui ont prit le temps dans cette énorme crise économique de m’écouter. A bientôt.

All the Aggregates you crave with Grouping Sets in SQL Server 2008

As reporting requirements increase, it seems that aggregate functions have thankfully risen to the occasion concurrently. To maintain its competitive edge as Staples' best Canadian vendor, BaldGorilla, where I’m currently consulting, has been able to fulfill the most demanding deadlines thanks to the query results produced from Grouping Sets. This is a new facet of the typical Group By clauses most database administrators have become accustomed to, prior to this version of SQL Server.

Straight to the point, all one has to remember is to include the grouped columns in brackets after the Group By Grouping Sets ((SelectCol1),(SelectCol2),..) clause to fully enjoy what limited cube and rollup functionalities we have seen in previous versions of this database management system. Of course, Grouping Sets are not a replacement for denormalising these data and creating cubes in a true data warehouse, however went it comes to satisfying requirements under tight project deadlines for each iteration(s) of reporting deliverables for paramount decision support systems, this functionality is, without a question, your overall rollup value blessing.
Actually, you might feel overwhelmed with all the extra lines of grouped sum values, therefore I have been cutting the result sets up into several tables as to not root confusion for the client – or my fellow developers and I :). These temporary table slices (in the end dumped into actual regularly pre-populated tables), are in actual fact partitions of the Grouping Sets, which have made it easy for our designers to create many intelligent decision support graphs. These summative data tables can combined with yet another improved SQL 2008 application component that we have gotten quite used over the past five years - Reporting Services. The analysis fashioned thanks to Grouping Sets, is limited business intelligence without the prerequisite of building a (usually quite dear) fully fledged online analytical processing system.

References:

http://www.sqlservercentral.com/articles/SQL+Server+2008/65539/
http://www.databasejournal.com/features/mssql/article.php/3790436/Grouping-with-SQL-Server-2008.htm -- explains the different result sets depending on how you use the brackets
http://blogs.msdn.com/craigfr/archive/2007/10/11/grouping-sets-in-sql-server-2008.aspx
http://weblogs.sqlteam.com/derekc/archive/2008/01/31/60478.aspx

OMG, I was asked to speak at SQLteach.com - by Paul Neilsen

Mister/Master of SQL Server himself, author of many versions of the SQL Server Bible (SQLserverBible.com) was talking to us today at SQLteach here in Montreal. One of his top ten favorite new features in SQL 2008 are the Management Studio enhancements - which lead me to open my yap a little too much about Activity Monitor (right click on a server in object explorer to select AM), so much so that he invited me to come up and give an explanation of why I like it so much (I'll follow up on that in a future post). Basically, I explained how you can view real-time critical SQL Server performance details and even sort by worst-performing queries. Then, for the worst performing queries, right-clicking gives you the option to view the actual offending code - so that you can get to optimising right away! Furthermore, the Activity Monitor really takes advantage of all the dynamic management view data - to think only months ago in SQL 2005 production I had all this loaded on several sheets in Excel dynamically to understand what really was going on...now all built-in. I would suggest upgrading to SQL 2008 for just this practically.


Thanks again to Jean-René Roy for organising a great week-long event - never had a chance to meet so many MVPs, such as Brad McGehee and Paul Nielsen, in the photo to the right with local DBAs Paolo de Rosa and Pollus Brodeur. Also had lunch/dinner/beers with Adam Machanic, Scott Stauffer, Roman Rehak, Itzik Ben-Gan all in one week! Actually, to be honest, it felt like a DBA therapy session at times :)

SQL 2008 Row and Page compression – or SQL 2005 post SP2 vardecimal conversion

One of the great new features in SQL 2008 is Row and/or Page Compression. Plus, still good news for those of you on SQL 2005 in production who might be there for a long while: there’s a decent feature you can take advantage of too that is very expensive to do in SQL 2008. Why? Well, Compression in SQL 2008 requires the Developer or Entreprise edition to be installed, so if you want to benefit from this mega space saver (like 1.3TB down to 650GB as I have seen, plus queries running at as little as 40% the time they took before) you will be happy with VARDECIMAL conversion in SQL 2005 (SP2 required though). Both have stored procedures that you can run to estimate how much space you will save: In 2008 it's sp_estimate_data_compression_savings -- which randomly takes rows and gives you quite an accurate estimation of space saved/gained.
Set your statstics and io on just for details of the page reads while doing the comparison before and after. See Brad's [McGehee] Compression examples here.

In SQL 2005, from sp2 onwards, you can do this:
exec sys.sp_db_vardecimal_storage_format ''DatabaseName'', ''ON''
exec sp_tableoption ''dbo.BigAssTableLoadedWithDecimals'', ''vardecimal storage format'', 1
DBCC SHRINKDATABASE(DatabaseName,0)
-- 0 can be replaced with the amount of space you want to leave free for expansion, normally if you have huge decimal types in tables with millions or rows, you'll see a huge difference in size of the db after the shrink.
References: http://msdn.microsoft.com/en-us/library/bb508963.aspx

Please take care while you are doing your big table conversions and make sure that you have enough space for the whole table to be added onto the MDF file and relatively heavy load on the LDF [log] data file. What you can do is switch to bulk mode (set recovery level) while you are doing the compression, then re-enable Full/Bulk or whatever recovery mode you were using before.
Basically what you are doing is clamping down on the wasted space per line with respect to Row level compression in SQL 2008 (characters even get dictionary compression, and also prefix compression), which is what SQL 2005 SP2 calls vardecimal (limited to that column type only however). Obviously, the 2008 higher-end editions have upped the cost for you to have this functionality, but it goes with the territory – a large organisation has major space to gain usually, and don’t forget that it’s not only the space in the database you are gaining, it’s all the cumulative space in backups over tie you'll save, plus and all the increased time (performance) for execution of queries - one could argue simply that by taking the huge number of times a query costs in CPU time, multiplied by its frequency of execution to get the CPU hours saved. Hey boss, were's my bonus, I just saved the company n,000 hours of CPU execution tme:) Remember that clients will get their data twice in less than half the time!

You could do this in the temp database also, rebuild using the temp while adding the compression to the specific object during creation, but make sure your temp is as big as the object you are rebuilding – such as the index+table data size, with a good fifty percent on top of that to be sure you’re not going to run out for other applications.
The Data Copmression Wizard in SQL Server Management studio demystifies much of this, if you are already lost. Mind you, it’s not very efficient if you have to do this on a whole bunch of objects, in that case you would be better off using T-SQL. Sort your tables by the largest first (data pages should be in the thousands), and evaluate the space savings. If you are in the millions of data pages, use this compression – as mentioned before even in SQL 2005 post Service Pack 2 build you can take advantage of row compression by means of VARDECIMAL (but not page compression sadly).
Another condition you should be aware of, If you are input/output bound on your system, meaning that you are waiting on your disks, then you can benefit from compression – Brad McGehee has stated that he prefers Row compression for OLTP environments (today at SQLTeach.com) – and if the e-DBA guru's mentioning it, then it’s really worth looking into.
If you are using tables with multiple non-clustered indexes, only compress those indexes that are used occasionally. Heavily used indexes are to be avoided therefore – so, as always, to be sure TEST, TEST, TEST....on several servers, your test environment, your disaster recovery environment, your dev., and even your laptop (still enjoying disk performance for databases on Dell’s XPS Samsung SSD – holding back on a 32GB SSD express card for logs/swap/temp in the New Year). Do it on your development environment just to save space on large tables –b/c Dev environments are usually starved for data, and then just watch what happens over the next while....check and see if queries are taking forever to run, and let a few of the developers know – maybe they’ll see something you missed too. SQL DBAs - get out there and compress!

How Certification can help you stay current with technology, as well as continuing your education

If you'd like to keep up with your profession as a SQL DBA, I thoroughly recommend certification. It has certainly given me an edge with respect to several mandates over the past years, and even at the least, a decent boost of confidence with respect to being able to quickly process through complex database infrastructure problems.
Unfortunately, just having a University degree is not enough these days to be competitive in the job market, you have to get loads of experience as well as keep up post–graduate studies in what my father likes to describe as 'waves'. Reading up on new methods to maintain the fast pace of change, especially in the database world, is great, but putting yourself under pressure to pass an exam takes the integration of that knowledge to the next step. You'd be surprised how much one can accomplish. This year, with some fatigue of course, I have taken the equivalent of five certification exams (due to a retake of the MCDBA upgrade exam) and wow; there are so many things I've picked up by studying for them. It will take me months to go through and apply what I've learned while preparing for the tests. I'm the kind of DBA who keeps perhaps too many notes, as a consequence of this studying.
I'd like to diverge to draw a centuries old parallel here: One of the main points of a school founded in the middle of the nineteenth century, the Working Men's College (of London) - was to encourage education for life. In fact the college has been serving local people and employers for over 150 years. Therefore, for the enrichment of the community with respect to professional development, certification is important since these are individuals who are seeking to obtain skills and qualifications that enhance their career prospects.Many of those who study and obtain certification are consultants that pay out of their own companies' money for classes, since reinvesting in your resources shouldn't be ignored (QC govt. here actually encourages 1% global budget for education). Others who take courses receive direct support from a sponsoring employer who sees that one of the best ways to improve their firm’s productivity and customer satisfaction, is by having better skilled, qualified and motivated employees. Therefore, continuing education in this way, is a win-win for both sides.Many reasons for certification are the same as that of the Working Men's College since they are:
-Relevant to the skills needed by local employers
-Easy to access, so that learning can take place at the employer’s premises or at the college’s classroom.
-Flexible, so that learning can fit around the needs and schedules of business. An individual can simply take the exams or courses when things are quiet - often different from semester system schedule.
-Effective in producing, in the shortest possible time, skilled and qualified staff.

P.S. Keep up those practice tests (if you are on your way to certification), since once you're cool with the material, test prep. is your best way to feel relaxed on the big day of your exam.

Password Best Practises / Meilleures pratiques au niveau des mots de passe

When you are in the process of creating accounts to access your SQL Server databases, it is best to make sure you use alphanumeric passwords that are at least ten characters long. I also recommend local accounts that are given the least amount of priveliges neceeary when in a multi-server environment. Please don't use a domain account, unless it is for an individual user's access, for database access to applications. Lors du création des comptes SQL, il faut s'assurer que le mot de passe lui-meme, est composé des chiffres et lettres, et au moins dix touches du clavier. Donnez ce compte les privilèges le plus faibles possibles aux applications qui sert de la b.d, et ne surtout pas un compte domaine (AD) avec accès sur plusieurs serveurs. A worst practise would be to create a generic SQL user account with the level of access included in its name and having the password be the same as the account name (which, given the forces of complacency in a work place, will end up never being changed).

If you have arrived in a new environment and have been asked to check the password strength of the existing database infrastructure, Idera's Password Checker (within the admin toolset) does the job quite well. Using several tools (up to three to be sure) to verify the security, such as Microsoft Baseline Security Analyser, will allow you to reassure the 'powers that be' of how secure your databases are.

Let's say you are configuring SQL Server to handle web server sessions, in a load balancing environment, you will probably have to create a sysadmin level account for the setup to work - in this case (since you cannot get around it as far as I know) an obscurely named account, with a more than 10 alphanumeric characters would be your safest bet.

Segregation of Duties / Ségrégation des tâches with respect to Controlled Environments

Best Pratices for a controlled environment / environnement sous contôles internes
September / Septembre 08

I diverge a little from the typical coding-based/oriented best practice to one that is focused on governance within public or government organisations with respect to the security of the data in databases used for annual reports.
 
Internal controls fall under the general COSO framework, and are more explicitly defined in the U.S. SOX Act. One of most important points of SOX compliance is the segregation of duties (SOD) - which basically means developers do not have access in production and that specific roles are followed when developing code before placing in production. Territorial as it may seem, it is necessary for Physical DBAs to control production databases, and without fail, follow change management practices such as is defined by the COBIT framework to avoid the risk of data being inadvertently, mistakenly, or maliciously changed.  Methods, in which we govern ourselves in a controlled environment, with respect to the production systems, are by following procedure and documentation ( e.g. COBIT or ITIL standards ). Each task has to be validated by a third person (code walk-through/fresh eyes), who is not writing the actual code. The database developer themselves should not be able to execute anything in production directly without an independent review of the documentation/code for the work which being performed.  Typically, the role of the developer is to pass on their code to a database administrator, which, knowing this economic climate, might not be at easy reach (at least have a peer review if a DBA is not involved). This division is to ensure that the role of the creator / executor is clearly segregated. 

If your organisation is in denial or lacks understanding of internal controls, please remind management that databases which fall under internal controls (that is the data us used for public reporting, annual reports, etc.) are subject to the segregation of duties. Furthermore, to track all changes and have recoverability, a bare minimum for a SQL database recovery mode is Bulk Recovery - so if your respective ‘friendly’ DBA states he can recover all those transactions that developers have gone into production to do a quick 'fix' while under Simple recovery mode, then it's simply not possible.  Ceci constitue un risque de ne pas pouvoir récupérer une transaction une fois exécutée - alors le problème est un risque de nature technologique au niveau de la sécurité. Make sure to backup all your transaction logs and keep them indefinitely, the auditors will be very happy that way too.

nother point of internal control is the adherence to the principle of providing the least amount of privileges, especially in production. To allow developers more access to get their work done, it is much safer to use impersonation for exceptions that require elevated privileges (see EXECUTE AS to do that temporarily), instead of being the typical lazy DBA and giving DBO to everyone:) Giving full access means you are not managing security, needless to say.  Often developers may dismiss this as 'overhead' while on their path to coding glory, or to shorten the treadmill - I do not want to judge - but please be aware that DBAs are going to do all that is considered responsible since we are the data stewards of the organisation (not to mention comply with regulations and the law).
I shall be updating this posting very shortly with more references thanks to the help of my ex-colleague from Dell, independent Senior SQL Server DBA Pollus Brodeur, since he’s the one who originally taught me all about auditing/SOD/Internal controls a couple of years back.

References:
http://www.mssqltips.com/tip.asp?tip=1300  SOX for SQL Server DBAs
http://www.microsoft.com/downloads/details.aspx?FamilyId=6E1021DD-65B9-41C2-8385-438028F5ACC2&displaylang=en Reaching Complaince (PCI/SOX)       
 http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=161&messageid=212223

  http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=161&messageid=118657&p=1
 http://www.sarbanes-oxley-forum.com/modules.php?name=Forums&file=viewtopic&t=988  
http://www.krell-software.com/omniaudit/data_auditing_for_sarbanes_oxley_compliance.asp

http://www.sqljunkies.com/Forums/ShowPost.aspx?PostID=7888

http://www.databasejournal.com/features/oracle/article.php/3650681

http://www.sqlservercentral.com/articles/Miscellaneous/3178/