Month: March 2014

Cashier’s Order from DBS online banking

Recently I needed to pay some fees to some authorities. In Singapore, typically you pay somebody by cash, ATM transfer, online transfer, NETS etc. However there are cases when you need to pay by cheque. It was such a case. But if you are having savings accounts with the banks they are unlikely to give you a cheque book. Cashier’s order (CO) comes handy as an alternative to cheque. Especially, if you want to avoid all the hassles and fees (small though) to open a current account that provides you with a cheque book.

You can either go down to a branch, fill out a form, pay a fee and collect a CO. However some banks like DBS allows you to apply online for a CO. If you do so, you still need to go to a branch (only certain branches – Jurong Point is one of them, you can chose one while applying online) to collect it (last time I did not need to wait in a queue, a front desk lady collected it from inside and gave it to me) but you save the fee. You can also choose the bank to send it to you by post.

When I tried to apply for a CO online I found I can do so for up to SGD 10,000. I needed more. Without finding a way I went down to the branch and was informed that I can do as many as possible. If the payee is the same they would call me and give an aggregate one (I needed a single one). Had they mentioned it here it would have been better.

By this time I became more careful. When I was issuing the COs one by one I was keeping the references manually as there was no report or anything to figure out how many I have issued so far. Well, I could log out and during that time they indeed show all activities I did in that session.

Overall, DBS provides a nice online experience. Especially I like the e-statement that saves paper.

PS. Last time, I bought a CO of S$ 35. However, I could not manage time to collect it. They called me to collect it. When I said I would not require it anymore, they canceled it, free of cost!

Index

FILESTREAM – Setup

To use FILESTREAM feature both Windows administrator and SQL Server administrator have to enable it. While enabling from Windows there are three options to choose from:

  1. Enable FILESTREAM for Transact-SQL Access check box.
  2. Enable FILESTREAM for file I/O streaming access. This is to read and write FILESTREAM data from Windows. Enter the name of the Windows share in the Windows Share Name box.
  3. Select Allow remote clients to have streaming access to FILESTREAM data. This is to let remote clients access the FILESTREAM data stored on this share.

For SQL Server instance there are three options to choose from:

  1. 0 – Disable FILESTREAM support for this instance
  2. 1 – enable FILESTREAM for Transact-SQL access only
  3. 2 – enable FILESTREAM for Transact-SQL and Win32 streaming access

Note that FILESTREAM would perform worse for 1MB+ size files for full read/write if Transact-SQL is chosen (and Win32 streaming is not chosen) than no FILESTREAM BLOB.

Now that FILESTREAM is enabled, we can go ahead to do the rest of the setups.

First, create a file group to keep the FILESTREAM data. There is a 1:1 mapping between data containers and file groups.

       ALTER DATABASE MyDatabase ADD
       FILEGROUP MyFileStreamGroup1 CONTAINS FILESTREAM;

 Second, define data container. In the below example, “D:\Production” must exist already. “D:\Production\FileStreamDataContainerRoot” is going to be the root of the data container.

       ALTER DATABASE MyDatabase ADD FILE (
       NAME = FileStreamGroup1File,
       FILENAME = 'D:\Production\FileStreamDataContainerRoot')
       TO FILEGROUP MyFileStreamGroup1 ;

Third, create a table with a FILESTREAM column. All FILESTREAM columns of a table must go to a single FILESTREAM group that can be specified, defaulting to default FILESTREAM filegroup. Table must have a column of the non-null uniqueidentifier data type with ROWGUIDCOL attribute and either a UNIQUE or PRIMARY KEY single-column constraint. FILESTREAM data container will have a directory for a table with subdirectory corresponding to the FILESTREAM column of the table.

       USE MyDatabase ;
       GO

       CREATE TABLE TableWithFileStreamColumn (
       DocumentID INT IDENTITY PRIMARY KEY,
       Document VARBINARY (MAX) FILESTREAM NULL,
       DocGUID UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL UNIQUE DEFAULT NEWID ())
       FILESTREAM_ON MyFileStreamGroup1;

Now it is time to use FILESTREAM using standard Transact-SQL or Win32 APIs.

Reference

Prev          Next

Index

FILESTREAM – Hardware/OS Consideration

There should be some considerations regarding hardware selection/configuration and OS configuration like:

  • Hard disk – SCSI is better for reliability and performance
  • RAID level – RAID 10 is recommended even though at least 50% space is redundant depending on configuration
  • Storage layout – all data in one volume may not be the right choice if load is high
  • Cluster size – 64KB and above to reduce fragmentation, also defragment periodically
  • Compression – cannot be enabled if cluster size is 4KB or less, may not be effective for all kinds of data, comes at the expense of CPU
  • Anti-virus configuration – AV can need to be setup properly so that it does not delete FILESTREAM files
  • Disable 8.3 naming for NTFS – if number of files are high then NTFS has to work harder to get a unique name
  • Turn off last access time update – since files are not accessed by explorer rather SQL Server turning off this feature is OK and it saves a lot of time
  • Security – SQL server need to be configured to use integrated security (Win32 file APIs to work that uses Windows token associated with the client’s thread accessing files)

Reference

Prev          Next

Index

FILESTREAM – What and When

If you have to store files (BLOB) in MS SQL Server database, you might consider using FILESTREAM (started from MS SQL Server 2008). This is essentially enabling FILESTREAM attribute of varbinary (max) type column. Some other setups are necessary prior to that.

FILESTREAM combines MS SQL Server with NTFS file system. Database is good at reading/writing/processing small pieces (rows and columns) of information scattered around in the database files while file system is good at reading/writing files as a whole. File system start performing better when the files are larger than 1 MB.

By combining the two you can get the best of the both worlds, like getting past the low max size limit of database BLOB and use max NTFS size, good streaming performance of file system, integration with structured data (transactional consistency), better fragmentation management, manageability (backups etc can be done together) etc.

However if you are doing frequent small updates FILESTREAM performs poor. This is because here it has to work more – guarantee transactional consistency as well write to file system that essentially creates/writes a new copy and delete the old one. It also takes more time to take backup.

FILESTREAM data can be accessed in two ways: standard Transact-SQL and by using Win32 APIs. If you want the best performance of File System you need to use Win32 APIs. There are provisions for the Win32 APIs to work within the context of database transaction.

FILESTREAM BLOBS use file system cache and does not use database buffer pool. This improves query processing and overall database performance.

However do note that certain features like Transparent Data Encryption (TDE) are not supported with FILESTREAM.

To summarize, if you are to save files 1 MB or larger in database that will be read a lot with few small updates and that a middle tier is available where you can use Win32 APIs for accessing them then FILESTREAM is a good choice.

Reference

Next

Index