Monday, May 12, 2014

Google Drive: notify CRUD (created, updated, deleted) files in folders

Google Drive is a good place to store files and share them with your friends or within your company. Google also developed notification mechanism for spreadsheets when changes are made to them. However that isn't enough. Users don't have only spreadsheets , they also have many types of documents. So they very need a tool to notify when these documents are changes. Many users already asked for this feature here. While waiting Google develops this feature, there is a good guy (Marcello Scacchetti from Jelly Bend) contributed a Google Apps Scripts to monitor new or deleted files in folders of Google Drive. It works like Microsoft's SharePoint. You can read and copy his code here.

In this article, I will share my modification on his code to make some additional capacities as below:

  1. Notify updated files
  2. Able to configure list of emails to receive notifications instead of only owner
  3. Able to configure subject and content of notification email for each of notification type (new, updated, deleted)
  4. Able to configure excluded folders (don't send notification if changed files are in these folders).
Before starting, pls copy this spreadsheet - Monitor Changes of Google Drive Folders. It will have a new menu Drive Folder Monitor and content like the following picture:

All things which you can do are:
1. Go to menu Drive Folder Monitor >> Configuration, then add the folders which you want to monitor and select time for interval running at Run drive folder monitor each box. Currently it just supports top level folders. I'll add a new version to support sub folders when I have time. First running, Google may ask you to authorize the script, pls click Accept.
2. Change Email To Inform (cell A2) to emails (separated by commas) which you want to send notifications.
3. Change Email Title of New Documents (cell B2), Email Title of Updated Documents (cell C2), Email Title of New Documents (cell D2) for the subject of each type of notification email.
4. Change Email Body (cell B3, C3, D3) for the content of each type of notification email. Remember don't delete $$FILE$$ string. It will be replaced by folders and files which are changed.
5. Configure Excluded Folders (cell B4), the format of folder is TOP_FOLDER/SUB_FOLDER (slash between folders), folders are separated by commas (,).
6. This tool will run automatically with interval time you set in step 1. In the case of you want to run manually, let click menu Drive Folder Monitor >> Run monitor folder.

Hope you can use this script in your work. Any comment and contribution idea are welcome.
Best regards,

Thursday, May 8, 2014

MS SQL Server 2014: 5 important new or enhanced features

Microsoft have released SQL Server 2014 with many enhanced features comparing with old versions. In this article, I'll introduce 5 new important features that you can consider to upgrade for your organization.

1. Buffer Pool Extensions

No doubt, it is the most significant new feature of SQL Server 2014, especially it is available for Standard 64 bit version. It is recommended for write-heavy applications. I have tested it, it helps to increase 30% - 50% writing performance, for reading (select) it seems can't help much. Below is the illustration of high-level architectural overview of the buffer pool relative to other SQL Server components.
Basically, this feature requires a SSD disk to host its extension file. The performance is very depended on SSD, some manufacturers have optimized their product to use with SQL Server 2014 (e.g. Fusion) so their product can get better performance.

To enable this feature, you can use the following command:
   FILENAME = 'D:\BufferPoolExtensionFile.BPE',
   SIZE = 40 GB

In which, D is SSD, min of SIZE = max_server_memory configuration, an optimal ratio SIZE:max_server_memory  is 4:1 to 8:1.

To check buffer pool configuration, run:
SELECT path, file_id, state, state_description, current_size_in_kb 
FROM sys.dm_os_buffer_pool_extension_configuration;

2. In-Memory OLTP Engine

It is also know in formerly project Hekaton of Microsoft. This feature is available only for Enterprise 64 bit version. By moving tables and stored procedures used frequently into memory, it reduces I/O and improve performance of your OLTP applications. Microsoft states that some applications can expect up to a 30x performance improvement. However, this feature just supports limited types of data, so you may need to re-design your tables and stored procedures to load them into memory. It is not an easy work. I have tested this feature and seen that it helps increasing 4x performance in my case with heavy processing data. In the case I do a simple query and get just 1 row data, it is slower than normal. So be careful what you should put into the memory.

3. Columnstore Indexes For All

It is first introduced in SQL Server 2012 to provide significantly improved performance for data warehousing types of queries. Microsoft states that for some types of queries, columnstore indexes can provide up to 10x performance improvements. However, in the original implementation of the columnstore indexes, the underlying table had to be read-only. SQL Server 2014 eliminates this restriction. The new updateable columnstore index enables updates to be performed to the underlying table without first needing to drop the columnstore index. A SQL Server 2014 columnstore index must use all of the columns in the table, and it can't be combined with other indexes.

4. Windows Server 2012 Integration

SQL Server 2014 enhances integration with Windows Server 2012 as also as Windows Server 2012 R2. SQL Server 2014 Enterprise will have the ability to scale up to 640 logical processors and 4TB of memory in a physical environment (128GB for SQL Server 2014 Standard ). In the case of using virtual machine, it can scale up to 64 virtual processors and 1TB of memory.

Its Resource Governor provides a new capability to manage application storage I/O utilization. First introduced with SQL Server 2008, the Resource Governor originally enabled you to limit the amount of CPU and memory that a given workload can consume. Now SQL Server 2014 extends the reach of the Resources Governor so that you can now manage storage I/O usage as well. The SQL Server 2014 Resource Governor can limit the physical I/Os issued for user threads in a given resource pool, allowing you to have more predictable application performance.

SQL Server 2014 also supports new Storage Spaces feature of Windows Server 2012. With Storage Spaces, you can create pools of tiered storage to improve application availability and performance. In addition, SQL Server 2014 can take advantage of Server Message Block (SMB) 3.0 enhancements to achieve high-performance database storage on Windows Server 2012 R2 and Windows Server 2012 file shares. Many enhancements were made to SMB 3.0, with the most notable being SMB Transparent Failover and SMB Direct. The new SMB Transparent Failover feature provides highly reliable SMB storage that's fully supported for applications like SQL Server and Hyper-V. With the new SMB Direct feature, you can leverage the NIC's Remote Direct Memory Access (RDMA) feature to provide access speeds for SMB file shares nearing the access speed for local resources.

5. Enhanced AlwaysOn Availability Groups

This feature has been enhanced with support for additional secondary replicas and Windows Azure integration. First introduced with SQL Server 2012, AlwaysOn Availability Groups boosted SQL Server availability by providing the ability to protect multiple databases with up to four secondary replicas. In SQL Server 2014, Microsoft has enhanced AlwaysOn integration by expanding the maximum number of secondary replicas from four to eight. Readable secondary replicas are now available for read-only workloads, even when the primary replica is unavailable. SQL Server 2014 also provides Windows Azure AlwaysOn integration. This new integration feature enables you to create asynchronous availability group replicas in Windows Azure for disaster recovery. In the event of a local database outage, you can run your SQL Server databases from Windows Azure VMs. The new Windows Azure AlwaysOn availability options are fully integrated into SQL Server Management Studio (SSMS).

Do you think what else? All comments are welcome.

Subscribe to RSS Feed Follow me on Twitter!