I love the database programming language SQL, and I don’t mind admitting it. Ever since I first started to write T-SQL statements in Microsoft Query 2001 for my ICT A-Level exams I’ve loved the syntax, the structure of the language itself (hence Structured Query Language) and its similarity to the English language.
I was thrilled therefore when I attended a week-long SQL 2014 training course in December to fill any previous gaps in my knowledge of the Microsoft SQL environment. I filled in so many little gaps, that I have since begun running half-day knowledge sharing sessions at K3 Syspro to share the little tit-bits that I picked up so that our consultants, technical and support teams are aware of the tools available to them to help do their job and pass on their knowledge of these tools to customers and on-site SYSPRO Administrators. SQL issues are often raised by customers through our Helpdesk, dealing with the use of SQL views (to display Custom Form Fields on SRS Stationary), Excel spreadsheets using the ODBC connection to the SYSPRO database, or simply struggles with general SQL performance or permissions when using SYSPRO.
I’ve outlined some of the most useful tools below:
- SQL Traces & SQL Profiler – I’d been running SQL traces for years but didn’t actually realise that the SQL Profiler and SQL Trace technologies are actually completely de-coupled in all versions of SQL Server since traces were first introduced in SQL Server 2005. Put simply, the SQL Trace is the collection of data about transactions that have gone through your SQL Server; SQL Profiler is the GUI tool that helps us read this data in a friendly manner. This means that customers that do not have SQL Profiler installed can still capture a trace using T-SQL code.
- SQL Audit – This is a much more granular way of tracing SQL transactional activity, and one I hadn’t seen or used before attending the course. SQL Audit is a very powerful way of tracking what happens on your SQL server within a period of time. There’s a very useful article here.
- Dynamic Management (DM) Views – DM Views are automatically created with every database on your SQL Server and provide ready-made SQL views on important parts of your SQL environment. There are over 200 DM Views included in SQL 2014 as Microsoft keep adding to the list with each release (there were only around 80 included with SQL 2005). Some of the most useful I’ve found up to now are sys.dm_server_services (displays status of all SQL services running on your SQL Server), sys.dm_tran_locks (gives us information on current locks and blocks) and sys.dm_db_index_physical_stats (shows us the state of all indexes in a database and any recommended actions required to improve them).
- SQL Agent and Alerts – I’d come across SQL Agent before as we use them to carry out backups and consistency checks to databases in our current environment here at K3 Syspro. What I didn’t realise, was that we could set up alerts based on a particular trigger in the SQL environment and email to specific Operators when such trigger happens. For example, we can set Alerts to inform us when our Transaction Log reaches a particular size, or even on a specific error message number such as 1205 (this is the error message raised when SQL encounters a ‘dead-lock’).
It’s important to understand all aspects of a live production system, and in SYSPRO administration terms, that of course includes the underlying SQL database.
As with most systems that we use day-to-day, it’s impossible to know everything, so if you’ve found any great features or tips, please share them and I’ll publish some of these in a later blog!
