Insights on Business Management Software and ERP
SYSPRO Smarter ERP Blog

Subscribe to Email Updates

SQL – The perfect database partner

Posted on 17 February 2015 by Chris Grunwell

Find me on:

chris_g_perfect_database_partnerI 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! 

 


Chris Grunwell

Chris Grunwell is a Specialist Services Consultant at K3 SYSPRO, and joined the company in 2012. Chris thrives on being a technical ‘doctor’, diagnosing, testing and fixing all things technical.

During his career Chris has been involved in IT Management, IT Support, Software Development, IT Sales and Web Development. Before joining K3 SYSPRO he worked at Steel Software Solutions as their Commercial Manager, where he was responsible for managing both the support and development team, sales and marketing of the flagship ERP product, sales, budgeting, and HR. In his current role at K3 SYSPRO he constantly pushes himself to help customers, test products, write user guides (and whatever else gets thrown at him).

Chris obtained a BSc degree in Information & Communication Management from Northumbria University in 2005, and also holds a Foundation PRINCE2 qualification.

 

Subscribe to Email Updates