Use MongoDB in Windows Azure

PROBLEM:
You want to use MongoDB in Windows Azure

SOLUTION:
See the following links:

1. MongoDB Installer for Windows on Azure
2. Securing MongoDB on Windows Azure

If you find this useful, you are welcome to press one of the ads in this page.. Thanks!

Useful SQL Joins diagram..

Azure database on-going maintenance

PROBLEM:
Once your SQL Azure database is in production, what are the tasks required for ongoing maintenance?

SOLUTION:
1. Backing up SQL Azure data

For the latest information on SQL Azure backup and restore strategy and disaster recovery techniques, see Business Continuity in SQL Azure article in the SQL Azure MSDN library.
Native backup and restore as provided by on-premise SQL Server is not currently supported by SQL Azure.

SQL Azure natively supports a Database Copy capability. This creates a new database in SQL Azure which is a transactionally consistent copy of an existing database. This is accomplished using special syntax within the CREATE DATABASE command:

CREATE DATABASE destination_database_name

AS COPY OF [source_server_name.]source_database_name

For full discussion of this feature, see Copying Databases in SQL Azure.

The data in a SQL Azure database can be backed up on-premise, or to another SQL Azure database, using BCP, SSIS, or the .NET SqlBulkCopy API.
These approaches do not backup the structure and objects in the database, so you will need to generate build scripts to accompany the data if you wish to bootstrap the database into a new environment.
For a thorough discussion of the options for moving data in bulk out of (or into) a SQL Azure database, see this blog post and this discussion of BCP.

2. Defragmenting Indexes and other routine maintenance

In SQL Azure, as with on-premise SQL Server, indexes can become fragmented over time. They should be periodically checked and, if necessary, rebuilt. The syntax for the ALTER INDEX statement is restricted in SQL Azure, as compared to on-premise SQL Server, but the syntax for rebuilding indexes is identical. Reorganizing indexes is not supported by SQL Azure.
Both online and offline index rebuilds are supported by SQL Azure.

3. Keeping statistics current
There are no significant differences in statistics management between SQL Azure and on-premise SQL Server. The following are supported:

• UPDATE STATISTICS
• sp_updatestats
• sp_autostats

The ALTER DATABASE statement, however, cannot be used to change the database-level settings automatic statistics creation and updating. These settings both default to True (ON) for SQL Azure databases and they cannot be changed. If you truly find it necessary to suppress auto stats updates, use the sp_autostats stored procedure to control this behavior at the table/index level. In the vast majority of circumstances, you can just leave the auto update behavior unchanged.

4. Tracking Usage and Billing

Depending on the subscription model used by your customer, SQL Azure may be billed on a consumption basis. This makes it valuable to track consumption of bandwidth and space on an ongoing basis. Although there are reports within the Azure billing portal which help track this, customers often wish to do some kind of customized tracking of usage expense. SQL Azure provides DMV’s to enable this.

Bandwidth usage from your SQL Azure account is available in a DMV calledsys.bandwidth_usage. This DMV is only available in the master database. Since three-part names are invalid in SQL Azure, you will need to be connected to the master database to query the sys.bandwidth_usage view.

Database usage is available in a DMV called sys.database_usage. This DMV is also available only in the master database.

These bandwidth_usage and database_usage DMV’s are helpful, but they only report past usage. You may want to use T-SQL to check the size of a SQL Azure database as it is right now. Unfortunately, the sp_spaceused stored procedure is not available in SQL Azure. Also, sys.database_files is among those DMV’s not exposed by SQL Azure. Another DMV,sys.dm_db_partition_stats, can help here though. The following T-SQL query will report the approximate size of the database.

SELECT (8.0 * SUM(reserved_page_count)) / 1024 AS 'Database Size (MB)'

FROM sys.dm_db_partition_stats

This query reports on the current database only, so you will need to run it while connected to the specific database you are interested in. Note that permission on sys.dm_db_partition_stats is denied in the master database, even for administrators.

5. Scheduling Maintenance Tasks

Scheduling maintenance (e.g., index reorganization) will sometimes be required, especially for large or heavily accessed SQL Azure databases. Unfortunately, SQL Server Agent, the on-premise SQL Server scheduling engine, is not part of SQL Azure. This means there is no Azure-hosted method for scheduling tasks to occur.

For the time being, the only way to address this requirement is with an on-premise or non-Azure scheduling tool. A natural choice is, of course, an on-premise instance of SQL Server Agent. This works perfectly well and will make SQL Server DBA’s comfortable.

Using on-premise SQL Agent is OK for customers with existing SQL Server infrastructure. Those with no existing SQL licenses, however, may wish to avoid buying database software just to get a scheduling engine. In this case, Windows Task Scheduler can be used to invoke sqlcmd scripts, Powershell scripts, or custom executables which reach out to SQL Azure databases to perform maintenance tasks.

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

Could not find property or field 'LAT' for type 'Microsoft.SqlServer.Types.SqlGeography'

PROBLEM:
You are trying to query geography data type
SELECT GEOLOCATION.LAT, GEOLOCATION.LONG FROM GEOLOCATION_TABLE
or
select geolocation.lat, geolocation.long from geolocation_table
and gets the following error:
Could not find property or field 'LAT' for type 'Microsoft.SqlServer.Types.SqlGeography'

or

Could not find property or field 'lat' for type 'Microsoft.SqlServer.Types.SqlGeography'

SOLUTION:
CLR types  are case sensitive by default. You should use "Lat" and "Long"
SELECT GEOLOCATION.Lat, GEOLOCATION.Long FROM GEOLOCATION_TABLE;

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

Azure Database Backup

PROBLEM:

You want to backup your Azure databases locally and also in Azure

SOLUTION:

The following steps are needed to copy Azure database to your local SQL Server 2008R2. Then you can backup it locally using a database job or manually.

First you need to allow your SQL Server to use and run xp_cmdshell

-- To allow advanced options to be changed

EXEC sp_configure 'show advanced options', 1

GO

-- To update the currently configured value for advanced options

RECONFIGURE

GO

-- To enable the feature

EXEC sp_configure 'xp_cmdshell', 1

GO

-- To update the currently configured value for this feature

RECONFIGURE

GO

Download and install SQL Azure Backup from here: http://www.red-gate.com/products/dba/sql-azure-backup

You are going to use this tool command line capabilities to automate and schedule your Azure backups with a simple stand-alone .exe

After installing Red Gate tool, use SSMS query window to run the following command. This will check if you configure xp_cmdshell and install Red Gate tool correctly

EXEC sys.xp_cmdshell 'C:\SQLAzureBackup\redgate.sqlazurebackupcommandline.exe'

This will bring the following

SQL Azure Backup v2.6.0.86

===============================================================================

Copyright (c) Red Gate Software Ltd 2011

Error: No argument for AzureServer and no default value

Back up a database from SQL Azure to SQL Server

Show Usage:

                  RedGate.SQLAzureBackupCommandLine.exe [/Help]

Copy to local sql server:

                  RedGate.SQLAzureBackupCommandLine.exe

                           /AzureServer:server /AzureDatabase:database

                           /AzureUserName:user /AzurePassword:password

                           [/CreateCopy] [/Skip] /LocalServer:server

                           /LocalDatabase:database [/LocalUserName:user]

                           [/LocalPassword:password] [/DropLocal] [/Verbose]

                           [/Help] [/Backup]

Create BACPAC file with Import/Export service CTP (Access Key):

                  RedGate.SQLAzureBackupCommandLine.exe

                           /AzureServer:server /AzureDatabase:database

                           /AzureUserName:user /AzurePassword:password

                           [/CreateCopy] /StorageAccount:name /AccessKey:key

                           /Container:container [/Filename:filename] [/Backup]

Create BACPAC file with Import/Export service CTP (Shared Access Signature):

                  RedGate.SQLAzureBackupCommandLine.exe

                           /AzureServer:server /AzureDatabase:database

                           /AzureUserName:user /AzurePassword:password

                           [/CreateCopy] /SignatureUrl:url [/Filename:filename]

                           [/Backup]

Copy to SQL Azure:

                  RedGate.SQLAzureBackupCommandLine.exe

                           /AzureServer:server /AzureDatabase:database

                           /AzureUserName:user /AzurePassword:password

                           [/CreateCopy] /LocalServer:server

                           /LocalDatabase:database [/LocalUserName:user]

                           [/LocalPassword:password] [/DropAzure] [/Restore]

  /sk:key,       /AccessKey:key          Primary or secondary access key

  /ad:database,  /AzureDatabase:database Database name to backup

  /ap:password,  /AzurePassword:password Azure password

  /as:server,    /AzureServer:server     Azure server

  /au:user,      /AzureUserName:user     Azure username

  /ba,           /Backup                 Do a backup

  /sc:container, /Container:container    Container where file will be placed

  /cc,           /CreateCopy             Ensure db transactional consistentency

  /da,           /DropAzure              Drop SQL Azure database if it exists

  /dl,           /DropLocal              Drop local database if it exists

  /sf:filename,  /Filename:filename      Filename to be created

  /?,            /Help                   Show usage

  /ld:database,  /LocalDatabase:database Database to be created

  /lp:password,  /LocalPassword:password Local password

  /ls:server,    /LocalServer:server     Local server

  /lu:user,      /LocalUserName:user     Local username for sql authentication

  /re,           /Restore                Do a restore

  /su:url,       /SignatureUrl:url       Shared access signature url

  /s,            /Skip                   Skip objects that fail to be copied

  /sa:name,      /StorageAccount:name    Backup to azure blob storage account

  /v,            /Verbose                Show verbose output

In SSMS query window or inside database backup job, use the following command to make local copy of SQL Azure database

EXEC sys.xp_cmdshell 'C:\SQLAzureBackup\redgate.sqlazurebackupcommandline.exe /AzureServer:MyAzureServerName.database.windows.net /AzureDatabase:MyDatabaseName /AzureUserName:MyUser /AzurePassword:MyPass /LocalServer:MyLocalServerName /LocalDatabase:MyDatabaseName /DropLocal'

Use the following command to make database backup in Azure (BACPAC file)

EXEC sys.xp_cmdshell 'C:\SQLAzureBackup\redgate.sqlazurebackupcommandline.exe /AzureServer:MyAzureServerName.database.windows.net /AzureDatabase:MyDatabaseName /AzureUserName:MyUser /AzurePassword:MyPass /Container:db-backup /Filename:MyDatabaseName /Backup /StorageAccount:MyStorageAccount /AccessKey:MyAccessKey'

To see you Azure database backups, download and install Azure Storage Explorer from here http://azurestorageexplorer.codeplex.com


If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

Clean / Delete all data in Azure database

PROBLEM:
You want to clean / delete all data form all tables in Azure database. You cannot use sp_MSForEachTable to generate delete script

SOLUTION:
Run the following code and save the result as a script, then execute it. You can use this script also in a regular SQL Server

SELECT 'print ''NOCHECK CONSTRAINT ' + f.name + '''; ALTER TABLE ' + '[' + OBJECT_NAME(f.parent_object_id)+ ']'+' NOCHECK CONSTRAINT ' + '[' + f.name + '];'
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
UNION ALL
SELECT 'print ''delete ' + name + '''; DELETE [' + name + '];'
FROM sys.tables
UNION ALL
SELECT 'print ''CHECK CONSTRAINT ' + f.name + '''; ALTER TABLE ' + '[' + OBJECT_NAME(f.parent_object_id)+ ']'+' CHECK CONSTRAINT ' + '[' + f.name + '];'
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

Azure DB properties - Find what is the Azure database edition using a query

PROBLEM:

You want to find what is the Azure database edition using a query

SOLUTION:

The following query returns the current setting of the specified database option or property. Change the first parameter "database_name" to your Azure database name, connect to it and run the query

SELECT

DATABASEPROPERTYEX ('database_name','Collation') AS Collation,

DATABASEPROPERTYEX ('database_name','ComparisonStyle') AS ComparisonStyle,

DATABASEPROPERTYEX ('database_name','Edition') AS Edition,

DATABASEPROPERTYEX ('database_name','LCID') AS LCID,

DATABASEPROPERTYEX ('database_name','MaxSizeInByte') AS MaxSizeInBytes,

DATABASEPROPERTYEX ('database_name','Recovery') AS Recovery,

DATABASEPROPERTYEX ('database_name','SQLSortOrder') AS SQLSortOrder,

DATABASEPROPERTYEX ('database_name','Status') AS Status,

DATABASEPROPERTYEX ('database_name','Updateability') AS Updateability,

DATABASEPROPERTYEX ('database_name','UserAccess') AS UserAccess,

DATABASEPROPERTYEX ('database_name','Version') AS Version,

DATABASEPROPERTYEX ('database_name','IsAnsiNullsEnabled') AS IsAnsiNullsEnabled,

DATABASEPROPERTYEX ('database_name','IsAnsiNullDefault') AS IsAnsiNullDefault,

DATABASEPROPERTYEX ('database_name','IsAnsiPaddingEnabled') AS IsAnsiPaddingEnabled,

DATABASEPROPERTYEX ('database_name','IsAnsiWarningsEnabled') AS IsAnsiWarningsEnabled,

DATABASEPROPERTYEX ('database_name','IsArithmeticAbortEnabled') AS IsArithmeticAbortEnabled,

DATABASEPROPERTYEX ('database_name','IsAutoClose') AS IsAutoClose,

DATABASEPROPERTYEX ('database_name','IsAutoCreateStatistics') AS IsAutoCreateStatistics,

DATABASEPROPERTYEX ('database_name','IsAutoShrink') AS IsAutoShrink,

DATABASEPROPERTYEX ('database_name','IsAutoUpdateStatistics') AS IsAutoUpdateStatistics,

DATABASEPROPERTYEX ('database_name','IsCloseCursorsOnCommitEnabled') AS IsCloseCursorsOnCommitEnabled,

DATABASEPROPERTYEX ('database_name','IsInStandBy') AS IsInStandBy,

DATABASEPROPERTYEX ('database_name','IsLocalCursorsDefault') AS IsLocalCursorsDefault,

DATABASEPROPERTYEX ('database_name','IsMergePublished') AS IsMergePublished,

DATABASEPROPERTYEX ('database_name','IsNullConcat') AS IsNullConcat,

DATABASEPROPERTYEX ('database_name','IsNumericRoundAbortEnabled') AS IsNumericRoundAbortEnabled,

DATABASEPROPERTYEX ('database_name','IsParameterizationForced') AS IsParameterizationForced,

DATABASEPROPERTYEX ('database_name','IsQuotedIdentifiersEnabled') AS IsQuotedIdentifiersEnabled,

DATABASEPROPERTYEX ('database_name','IsPublished') AS IsPublished,

DATABASEPROPERTYEX ('database_name','IsRecursiveTriggersEnabled') AS IsRecursiveTriggersEnabled,

DATABASEPROPERTYEX ('database_name','IsSubscribed') AS IsSubscribed,

DATABASEPROPERTYEX ('database_name','IsSyncWithBackup') AS IsSyncWithBackup,

DATABASEPROPERTYEX ('database_name','IsTornPageDetectionEnabled') AS IsTornPageDetectionEnabled;

See: http://msdn.microsoft.com/en-us/library/windowsazure/ee621786.aspx

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

Create Azure database and user with basic read/write/execute permissions

PROBLEM:
You want to create Azure database and user with basic read/write/execute permissions

SOLUTION:
The following command creates a database without any parameters, this will generate the smallest database of the web edition. You must be connected to AZURE MASTER database. The CREATE DATABASE statement must be the only statement in the SQL batch

CREATE DATABASE MyDatabase;

This is the same as declaring

CREATE DATABASE MyDatabase (EDITION='web', MAXSIZE=1GB);

The database created can hold up to 1 Gigabyte of data. It will return error code 40544 when trying to add more data.
If you are not sure what will be your database size in the future, you can start with a small size and alter it when necessary

ALTER DATABASE MyDatabase MODIFY (EDITION='business', MAXSIZE=10GB);

The valid MAXSIZE settings for WEB edition are 1 and 5 GB. The valid options for BUSINESS edition are 10, 20, 30, 40, and 50 GB.

See the official pricing guide.

To create login, you must be connected to AZURE MASTER database. The following CREATE LOGIN statement must be the only statement in the SQL batch

CREATE LOGIN myLoginName WITH password='myPassword';

Now, connect with administrative permission to "MyDatabase" you just created and run the following section as a batch

CREATE USER myUserName FOR LOGIN myLoginName WITH DEFAULT_SCHEMA = dbo;

GO

CREATE ROLE myRoleName AUTHORIZATION dbo;

GRANT EXECUTE TO myRoleName;

GRANT SELECT,INSERT,UPDATE,DELETE TO myRoleName;

EXEC sp_addrolemember 'myRoleName', 'myUserName';

EXEC sp_addrolemember 'db_datareader', 'myUserName';

EXEC sp_addrolemember 'db_datawriter', 'myUserName';

GO

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

Azure database error code 40544

PROBLEM:
The size of your Azure database reaches its max size and you receive error code 40544

SOLUTION:
When this occurs, you cannot insert or update data, or create new objects (such as tables, stored procedures, views, and functions). However, we can still read and delete data. You can then update maxsize to a value larger than your current database size or delete some data to free storage space.

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!