Tag Archive: sql


One of Microsoft’s buried SQL gems.  I was working with a client with serious inter-server Dynamics GP performance issues, so slow as to be virtually unusable, but GP ran fine at the SQL Server.  We discovered SQL ODBC was communicating using Named Pipes only.

Without getting into troubleshooting detail, their IT group changed the default SQL port for Microsoft SQL 2016, and Microsoft, in all their infinite wisdom, removed port selection settings from the client ODBC DSN Configuration interface a number of versions back.  So how do you tell ODBC what port to connect to?

The answer is simple.  During configuration, when you select the server, enter the SQL Server and instance followed by a comma (,) followed by the port number it will connect with – no spaces.

For example, If your SQL Server is on CONTOSO1\GP, but needs to use TCP/IP Port 12345, enter CONTOSO1\GP,12345 in the Server line. If SQL is on the default instance, it would be CONTOSO1,12345.  It’s that simple.

GP & SQL Compatibility

I replied to a GPUG question earlier today from someone wanting to use SQL 2016 with GP 2015.  It bears repeating here and applies to similar situations as well.

When Microsoft says not compatible, they mean it. It may work, but it’s not supported. If you do run into an issue with compatibility down the road, you have just burned all bridges, since you won’t likely be able to restore it later to a supported SQL version. At that point you can only hope that you’ve kept a copy of databases from the earlier SQL version and reenter everything from that point, or start over. It’s usually a deprecated function is specified in GP coding that will be the problem, and the only fix is to rewrite the software.

For years I was the “Damn the torpedoes, full speed ahead” guy, the one that could always trick the system into making it work after a client broke it, but after seeing the destruction and mayhem it can cause down the road (read potentially hundreds of consulting hours), it’s wise to trust Microsoft’s compatibility list. It’s there for a reason.

There is a wealth of information about this, but I have to believe that some people just don’t get it.

Let’s start with the easiest, Simple Recovery mode.  You only have one choice for a restorable backup, and that’s a full backup.  When you back up the database, the log (committed transactions) are truncated, allowing the log file (.ldf) to maintain its smallest available size.  There is no transaction log backup for point-in-time restore, so you restore from latest backup.  SQL Express only runs in Simple Recovery mode.

In Full Recovery, you back up the complete database (full backup), but you MUST also back up the transaction log.  If you do not run periodic Transaction Log backups, the log file (.ldf) will continue to grow until it either reaches a preset limit or (more often) fills the drive.  The Transaction Log backup is what clears and truncates the log.  For restoring, you would need the last full backup plus any transaction logs up to the point of desired recovery.

Companies who use a software-specific backup agent for SQL backups are most commonly running full backups to their tape, the cloud, or other media.  If your database is set for Full recovery, you must back up the Transaction Log as well either to the same destination using the agent, or set up a SQL job to back up the log separately.  If this is the case, you would restore from full tape backup then any subsequent transaction log backups to the point of desired recovery.

I’ve seen too many cases where a company will choose a full recovery model to minimize data re-entry in the event of drive or human error, but then only runs full backups to external media.  The result?  A huge log file, or a drive that is out of space, stopping any additional data input until the condition is corrected.

Instructions for fixing full drives are available from Microsoft and many other sites, so I will not cover them here.

If you’re not certain what your database settings are for each of your databases, run this query in SQL 2005 or later:

select name, DATABASEPROPERTYEX(name, ‘Recovery’) Model from sys.databases

Bottom line, use the right backup for your recovery model and method.