Mobile data sync solution in SQL and NoSQL space

I was working on mobile data sync from last two years but could not find a reliable solution, but finally I found these interesting solutions in both SQL and NoSQL Space-

One in the SQL space called Zumero and other in the NoSQL space called CouchBase lite.

Zumero provides support for SQL lite at the mobile endpoint and SQL Server / SQL lite at the server endpoint.

CouchBase lite supports a NoSQL document based and key-value based databases at both the ends.

Both seems interesting & I am going to experiment further with both of these..

http://www.couchbase.com/communities/couchbase-lite
http://zumero.com/

Top 10 New Features in SQL Server Denali

I will be writing on SQL Server 12 or Code name Denali in the coming few posts.

The next release of Microsoft SQL Server 12 or code-named Denali, is the buzz now a days. Microsoft has just released Denali CTP3, and the final release is expected by the end of the year. Denali continues SQL Server's efforts into the enterprise with a number of important features. Here are the top 10 most significant new features in the SQL Server Denali release.

10. SQL Server Developer Tools—One of the most obvious improvements in SQL Server Denali is the new development environment, SQL Server Developer Tools, coded-named Juneau. Juneau uses the Windows Presentation Foundation (WPF)–based Visual Studio 2010 shell, and it unifies development for Business Intelligence Development Studio (BIDS) and Visual Studio. One goal for Juneau is to make the development environment consistent for both SQL Azure and the on-premises version of SQL Server.

9. Contained databases—Contained databases make it easy to move databases between different instances of SQL Server. With Denali, login credentials are included with contained databases. Users don't need logins for the SQL Server instance because all authentications are handled by the contained database. Contained databases have no configuration dependencies on the instance of SQL Server that they're hosted on and can be moved between on-premises SQL Server instances and SQL Azure.

8. Project "Crescent"—The new data visualization tool, code-named Project "Crescent," is Closely integrated with SharePoint 2010 and Silverlight. Microsoft has called the Crescent feature "PowerPoint for your data." Crescent makes it easy for users to create great-looking data pages and dashboards by using data models that are built using PowerPivot or from tabular data from SQL Server Analysis Services.

7. Data Quality Services—Valid data is critical for making effective business intelligence (BI) decisions. Data Quality Services lets you set up a knowledge base that defines your metadata rules. You can then run Data Quality Services projects to apply those rules to data stored in a SQL Server data source. The Data Quality Services projects cleanse the data and allow viewing of good, invalid, and corrected rows.

6. User-defined server roles—An important security-related feature in Denali is the addition of user-defined severs roles. Earlier releases had fixed server roles that were predefined by Microsoft. These roles covered most situations, but they weren't as flexible or granular as some organizations wanted. The new user-defined server roles give organizations more control and customization ability over SQL Server's server roles.

5. Change data capture (CDC) for Oracle—CDC lets you keep large tables in sync by initially moving a snapshot to a target server, then moving just the captured changes between the databases. With the SQL Server 2008 release, CDC was limited to SQL Server, but many organizations also have other database platforms they want to use CDC with. A big improvement in the Denali release is the addition of CDC for Oracle.

4. T-SQL enhancements—Two of the most important T-SQL enhancements in Denali are the addition of the Sequence object and the window functions. Unlike the similar Identity column, Sequence lets you tie unique row identifiers across multiple tables. The new window functions apply to sets of rows using the new OVER clause. You can read more about window functions in "Window Functions (OVER Clause)—Help Make a Difference."

3. Columnar store index—The columnar store index or, as it is sometimes called, the column-based query accelerator, uses the same high performance/high compression technology that Microsoft uses in PowerPivot, and it brings that technology into the database engine. Indexed data is stored according to the data of each column rather than by the rows, and only necessary columns are returned as query results for columnar indexes. Microsoft states this technology can provide up to 100 times improvement in query performance in some cases.

2. Support for Windows Server Core—The ability to run SQL Server on Windows Server Core has been missing from previous releases of SQL Server. Server Core is designed for infrastructure applications such as SQL Server that provide back-end services but don't really need a GUI on the same server. Denali's support for Server Core enables leaner and more efficient SQL Server installations and at the same time reduces potential attack vectors and the need for patching.

1. AlwaysOn—Without a doubt, the most important new feature in SQL Server Denali is the new SQL Server AlwaysOn feature. AlwaysOn is essentially the next evolution of database mirroring. AlwaysOn supports up to four replicas, the data in the replicas can be queried, and backups can be performed from the replicas. Although it's still early, AlwaysOn seems more complicated to set up than database mirroring because it requires Windows Failover Clustering, but the advantages appear to make it well worth the extra effort.

Log Shipping vs. Mirroring vs. Snapshot vs. Replication in Databases

Recently, I have been taking more interests and started learning in Databases. I will keep sharing my deep dive learning outcomes in my blog. Keep following..

Log Shipping	Log Shipping is an old technique available since SQL SERVER 2000. Here the transactional log (ldf) is transferred periodically to the standby server. If the active server goes down, the stand by server can be brought up by restoring all shipped logs. Usage Scenario: You can cope up with a longer down tim   e. You have limited investments in terms of shared storage, switches, etc. Log shipping is based on SQL Server Agent jobs that periodically take log backups of the primary database, copy the backup files to one or more secondary server instances, and restore the backups into the secondary database(s).  Log shipping supports an unlimited number of secondaries for each primary database. Database mirroring is preferable to log shipping in most cases, although log shipping does have the following advantages: 1. it provides backup files as part of the process 2. multiple secondaries are supported 3. it is possible to introduce a fixed delay when applying logs to allow the secondary to be used for recovering from user error
Database Mirroring	Database mirroring is functionality in the SQL Server engine that reads from the transaction log and copies transactions from the principal server instance to the mirror server instance. Database mirroring can operate synchronously or asynchronously If configured to operate synchronously, the transaction on the principal will not be committed until it is hardened to disk on the mirror. Database mirroring also supports automatic failover if the principal database becomes unavailable. The mirror database is always offline in a recovering state, but you can create snapshots of the mirror database to provide read access for reporting which was introduced with 2005 edition, works on top of Log Shipping. Main difference is the uptime for the standby server is quite less in mirroring. Standby server automatically becomes active in this case (through help of a broker server which is called as Witness in SQL SERVER parlance), without having to restore logs (actually logs are continuously merged in this scenario – no wonder it’s called Mirror ).
Snapshot	Snapshot is a static read only picture of database at a given point of time. Snapshot is implemented by copying a Page (8KB for SQL SERVER) at a time. For e.g. assume you have a table in your DB, & you want to take a snapshot of it. You specify the physical coordinates for storing snapshot & when ever original table changes the affected rows are pushed first to the the snapshot & then changes happen to the DB. Usage Scenario: You have a separate DB for report generation, and want to ensure that latest data for that is available. You can periodically take snapshot of your transactional database.
Replication	Replication is used mainly when data centers are distributed geographically. It is used to replicate data from local servers to the main server in the central data center. Important thing to note here is, there are no standby servers. The publisher & subscriber both are active. Usage Scenario: A typical scenario involves syncing local / regional lookup servers for better performance with the main server in data center periodically, or sync with a remote site for disaster recovery.
Failover Clustering	Failover Clustering  is a high availability option only (unlike others above which can be used for disaster recovery as well) used with clustering technology provided by hardware + OS. Here the data / databases don’t belong to either of servers, and in fact reside on shared external storage like SAN. Advantages of a SAN storage is large efficient hot pluggable disk storage. You might see DR options like Mirroring used quite frequently with failover clustering. Here’s a good article on adding geo redundancy to a failover cluster setup.
Few links for further Reference	http://blogs.msdn.com/b/mikewat/archive/2007/07/28/database-mirroring-and-log-shipping-which-is-better.aspx http://stackoverflow.com/questions/525637/what-are-the-scenarios-for-using-mirroring-log-shipping-replication-and-cluster http://sqldbpool.com/2010/02/15/database-mirroring-vs-log-shipping/ http://msdn.microsoft.com/en-us/library/ms187016.aspx http://social.msdn.microsoft.com/Forums/en-US/sqldatabasemirroring/thread/ee05954e-0934-4305-8936-b9226e231d06/

Collection of “How do I ?” MSDN Videos–(Produced by us) on Windows Phone 7, Sharepoint 2010, Silverlight4, Cloud Services, Visual Studio 2010, SQL Server RC2, Windows 7

Windows Phone 7

1. How Do I: Implement Tombstoning in Windows Phone 7 Applications?

2. How Do I: Migrate an Android Application to a Windows Phone 7 Application?

3. How Do I: Use Page Turn Animation in a Windows Phone 7 Application?

4. How Do I: Use MVVM in a Windows Phone 7 Application?

5. How Do I: Use Touch Manipulation in a Windows Phone 7 Application?

6. How Do I: Make Windows Phone 7 Applications Support Orientation Changes?

7. How Do I: Implement Tilt Effects in a Windows Phone 7 Application?

8. How Do I: Migrate an iPhone Application to a Windows Phone 7 Application?

9. How Do I: Use XNA for Game State Management in a Windows Phone 7 Application?

10. How Do I: Use XNA for Gestures and Collision Detection in a Windows Phone 7 Application?

11. How Do I: Save Images to the Pictures Hub and Retrieve them back from the Hub in a Windows Phone 7 Application?

12. How Do I: Use Push Notifications for Tile Customization in a Windows Phone 7 Application?

13. How Do I: Use Push Notifications in a Windows Phone 7 Application?

Windows 7

1. How Do I: Use a Progress Bar on Icon in a Windows 7 Application?

2. How Do I: Create an Overlay Icon in a Windows 7 Application?

3. How Do I: Create a Custom Thumbnail for a Windows 7 Application?

4. How Do I: Create a JumpList for Common Tasks in a Windows 7 Application?

5. How Do I: Use Multitouch in a Windows 7 Application?

6. How Do I: Create an Internet Explorer 9 Pinned Site in Windows 7?

7. How Do I: Create Custom Thumbnails and Previews for a Windows 7 Application?

8. How Do I: Use Microsoft Speech API in a Silverlight Out-of-Browser Application?

SharePoint 2010

1. How Do I: Use Client Object Model from a Silverlight Application in SharePoint?

2. How Do I: Build a SharePoint Workflow Using Visual Studio 2010?

3. How Do I: Create a Reusable Workflow Using Visual Studio 2010 and SharePoint Designer 2010?

4. How Do I: Use REST Services to Access and Update Data in a SharePoint List?

5. How Do I: Use Business Data Connectivity Model in SharePoint by Using Visual Studio 2010?

Visual Studio 2010

1. How do I: Use IntelliTrace in Visual Studio 2010?

2. How Do I: Perform Exploratory Test in Visual Studio 2010?

3. How Do I: Use Build in Visual Studio 2010?

4. How Do I: Use Query Folders for Organizing Queries in Team Foundation Server 2010?

5. How Do I: Split a Team Project Collection in Team Foundation Server 2010?

6. How Do I: Use Traceability in Visual Studio 2010?

7. How Do I: Enable Symbol and Source Server Support in Team Foundation Server Build 2010?

Cloud Services

1. How Do I: Work with Spatial Data in SQL Azure?

2. How Do I: Work with Windows Azure Marketplace DataMarket?

SQL Server 2008 R2

Reseeding Identity Column in SQL Server / TSQL

Sometimes we need to change the seed value in Identity Column in a Table in SQL Server.

Here is the Alter Table Statement for doing that:

DBCC CHECKIDENT('', RESEED, )

Example:  
DBCC CHECKIDENT('Students', RESEED, 12)

Getting list of Fields and associated information from a Table

Getting list of Fields and associated information from a Table (in SQL server) also called table schema:

select column_name,* from information_schema.columns

where table_name = 'TaxiMaster'

order by ordinal_position

LINQ to SQL Samples- Must Read

I found this good repository of samples on MSDN on the topic : LINQ to SQL Samples
Useful for the beginners willing to learn LING.

http://msdn.microsoft.com/en-us/vbasic/bb688085.aspx

LINQ is Languages Integrated Query that has been ported with Dot Net Framework 3+ and is a part of Visual Studio 2008.

Here is an another introductory article from about.com on LINQ and XML
http://visualbasic.about.com/od/learnvbnet/a/LVBE_L6.htm?nl=1