Last week we released a new OSS note regarding a new feature of SQL Server 2008 which so far is hardly known. Additionally we updated a well known OSS note to SQL Server 2008 and the fact that SAP is using the Microsoft JDBC driver theses days
The new OSS note #1238993 Proportional File Autogrowth with SQL Server 2008 describes a change of the way SQL Server 2008 grows the files of a filegroup. The new functionality is not switched on by default, but needs to be enabled with trace flag 1117. What is the basic difference one experiences with this trace flag? As described in this document on page 19/20 we recommend to use a set of data files of the same size within the default filegroup for a SAP database. Due to SQL Server’s proportional fill feature, we would achieve an even fill since all files start out with the same amount of freespace. That is exactly what we want. Increasing the amount of data, we would just extend the data files by the same portion every time and that is it. SQL Server also has the ability to autogrow data files. However our recommendation so far was to have it enabled as fallback only, but instead to monitor freespace and then manually grow the files. The reason was that SQL Server did not grow all files at a point when all the files were filled up. But SQL Server just grows one file, fills it up, grows the next file, fills it up and so on. This kind of algorithm completely undermines the proportional/even fill we want to have. SQL Server 2008 with traceflag 1117 enabled will grow ALL data files of the filegroup by the portion which is set in the properties of the files if all data files of a filegroup are full (or in SAP case all data files since only the default filegroup is used). Sure, the expectation is that we have the autogrowth rate of each of the files set to the same percentage or value. More details are described in the OSS note. A great feature we recommend to use. Thanks to Mark Weber, one of our SQL Server Onsite Support Engineers at SAP, for writing this OSS note.
One OSS note which needed some updates was OSS note #965908. The first update concerned the usage of Database Mirroring with the SAP JAVA stack. the note stated that this was not possible since SAP was using the DataDirect JDBC driver. However since Netweaver 7.0 SR3 this changed with SAP using the Microsoft JDBC driver 1.2 for SQL Server 2005. This version of our JDBC driver does support Database Mirroring with all its three different configurations including automatic failover. Pointers to other OSS notes are explaining how to change to the Microsoft JDBC driver.
Another necessary change resulted out of SAP changing the programming interface for the ABAP stack from OLE DB to ODBC for SQL Server 2008 and beyond. Unfortunately the spelling of the ‘Failover Partner’ is different for every one of SQL Server’s Programming interfaces (please don’t ask me why). Hence I documented the difference in the Failover Partner setting for ODBC and also showed how to set the Failover Partner in the JDBC URL.