SQL Server Access From Linux - What Do You Think?

Microsoft is getting serious about offering SQL Server access from Linux...and your input is being requested. Actually, the request is that you provide your thoughts about Microsoft offering ODBC support for non-Windows platforms (which would provide SQL Server access from Linux) by completing this survey:

https://www.surveymonkey.com/s/SQL_Server_multi-platform_ODBC_survey

The survey is open until March 10th and takes about 10 minutes to complete. The survey questions will give you an idea of how broadly Microsoft is thinking about ODBC support for non-Windows platforms.

Please take a few minutes to complete the survey...your feedback is much appreciated and will help set the direction for an important initiative within Microsoft.

SQL Server 2008 SSIS BUG

1. Drag and Drop the foreach Loop Container to the Control Flow task
2. Double click
3. Navigate to Collection tab

By default, it shows Enumerator as foreach File Enumerator. But second dialog box shown is not the parameters you should get for foreach File Enumerator.

If you want to get them, this is the workaround,

1. Select any other Enumerator
2. Select foreach File Enumerator again. Those parameters are back.

SQL Server Destination remote server error

It is recommended to use SQL Server Destination over OLEDB Destination. However, there can be occasions where you have to use OLEDB Destination.

http://www.sql-server-performance.com/faq/sql_server_destination_remote_server_p1.aspx

Add Node to A SQL Server failover Cluster failed with invalid SKU error

This is a bug which is supposed to be fixed with SQL Server 2008 SP1, but this bug still exists and there is a workaround too.

http://www.sql-server-performance.com/faq/sku_error_add_node_failover_cluster_p1.aspx

Copy Only Backups for Adhoc Backups

Do you know that there is a new backup type called Copy Only backups in SQL Server. Copy only backups are independent of the sequence of normal SQL Server backups and are useful for ah-hoc scenarios where a backup/restore is necessary.

Read my latest article at Copy Only Backups for Adhoc Backups in www.sql-server-performance.com

Will Check Constraints Improve Database Performance?

Most of developers are in the view that check constraints are for data integrity.

Check this http://www.sql-server-performance.com/faq/check_contraints_performance_p1.aspx

Is there a difference between fill factor 0 and 100

When creating indexes in SQL Server, if you do not specify an index fill factor, the fill factor will be 0 (effectively the same as 100%). You can specify an index's fill factor percentage in a number of different ways

Read more at http://www.sql-server-performance.com/faq/fill_factor_0_100_p1.aspx

Cannot Execute SSIS packages with Excel in 64 bit version of SQL Server

When you try to execute SSIS package which has a Excel Destination/Source in SQL Server 64 bit version, you will get following error.

[Connection manager "Excel Connection Manager"] Error: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.

As error suggest Excel Connection Manager does not support 64 bit version. Depending on the environment you can execute this SSIS packages.

SQL Server Agent Job

In SQL Server 2008, there should be a checkbox on the Execution options tab Job Step page to run the package in 32-bit mode as shown in following image.

In SQL Server 2005, you have to change the Job Step from an Integration Services Step to an Operating System step, and specify a DTEXEC command line instead. The DTEXEC command line needs to specify the DTEXEC.EXE from the Program Files (x86) folders, NOT the DTEXEC.EXE from the Program Files folders which is the 64 bit version.

DTEXEC

If you're executing the package using, as said before, you need to specify the 32-bit version of DTEXEC by explicitly running the DTEXEC.EXE from the Program Files (x86) SQL Server folder.

Visual Studio

You can also set the 32 bit runtime environment using Project Properties -> Debugging -> Run64BitRuntime = False, as shown in the following image.

Will Truncate Command be carried forward to Secondary Server in Log Shipping?

Truncate command will not delete data row by row hence deleted data will not be logged in Transaction Log. In Log shipping, transaction log backups will be transferred to the secondary server. So the natural question is whether truncate command will be carried forward to Secondary Server.

Let us examine, what is happening to transaction log during the truncate command. Following is the part of transaction log you will see when you issue a truncate statement.

 

Current LSN	Operation	Context
0000001c:00000014:0005	LOP_MODIFY_ROW	LCX_PFS
0000001c:00000014:0006	LOP_MODIFY_ROW	LCX_PFS
0000001c:00000014:0007	LOP_FORMAT_PAGE	LCX_IAM
0000001c:00000014:0008	LOP_HOBT_DELTA	LCX_NULL
0000001c:00000014:0009	LOP_MODIFY_ROW	LCX_IAM

PFS – Page Free Space

IAM – Index Allocation Maps

The actual process for Truncate Table is to de-allocate the pages assigned to a table, this de-allocating part will be captured in log file. So in the, transaction log backup, this statement will be captured and will be transferred to the secondary server. This means that Truncate command will be carried forward to the Secondary Server.

Similarly, in mirroring, transaction log backup restoring, transactional replication truncate statement will be transferred to the intend targets.

PN: table is de-allocated by unhooking the IAM chains and then de-allocating the individual pages and extents using a background task – a process called deferred-drop, to avoid running out of locks during the de-allocation process.

Brad's Sure DBA Checklist

Sometimes, all a DBA needs, to help with day-to-day work, is a checklist of best-practices and dos and don’ts. It provides a handy reminder. Brad has come up with a new update to his famous checklist

http://www.simple-talk.com/sql/database-administration/brads-sure-dba-checklist/?utm_source=simpletalk&utm_medium=email&utm_content=BradChecklist-20100125&utm_campaign=SQL