Microsoft SQL Server Front End Blog

Code sample: Compressed SQL Server database backup to Windows Azure Storage

2013-03-01T18:57:55Z

SMO code sample to perform a compressed SQL Server database backup to Windows Azure Storage is available at http://code.msdn.microsoft.com/Backup-SQL-Server-database-0824dbfb

You can read about Backup to Windows Azure Storage Service here - http://blogs.msdn.com/b/sqlagent/archive/2013/01/24/sql-server-backup-and-restore-with-windows-azure-blob-storage-service.aspx

Activity Monitor in SQL Server 2012 SP1

2013-02-07T21:52:00Z

In SQL Server 2012 Service Pack 1 few changes were made in Activity Monitor. We had to stop using PerformanceCounterCategory class to get information about number of logical CPUs on the remote machine and switched to using more accurate information available via WMI (Win32_ComputerSystem.NumberOfLogicalProcessors if you’re looking for technical details).

Unfortunately it caused problems for existing customers in few very specific situations. Few bugs were reported by our customers:

https://connect.microsoft.com/SQLServer/feedback/details/770897/after-installing-sp1-sql-server-2012-activity-monitor-gives-access-denied

https://connect.microsoft.com/SQLServer/feedback/details/771843/ssms-2012-sp1-activity-monitor-not-working-against-2008-r2

In all cases problems were seen when running SSMS Activity Monitor from SQL Server 2012 SP1 trying to get CPU utilization graph from the remote servers running SQL Server 2008 R2 on Windows Server 2003. We didn’t see any reports when connecting to more recent version of SQL Server and more recent version of Windows Server.

There are two different errors you might see: “Access Denied” or “Invalid Query”.

If you are experiencing "Access Denied" errors you need to enable DCOM rights on the remote machine to allow WMI connections from the machine you’re running SSMS on. Here are the steps to do this:

Click Start, click Run, type DCOMCNFG, and then click OK.
In the Component Services dialog box, expand Component Services, expand Computers and then right-click My Computer and click Properties.
In the My Computer Properties dialog box, click the COM Security tab.
Under Launch and Activation Permissions, click Edit Limits.
In the Launch Permission dialog box, select your user and group in the Group or user names box. In the Allow column under Permissions for User, select Remote Launch and select Remote Activation, and then click OK.
Under Access Permissions, click Edit Limits and give Remote Access to your user.
Go to DCOM Config(Expand My Computer), find "Windows Management Instrumentation", right-click and select Properties.
In the Security tab, click on Edit under Launch and Activation Permissions, and give your user Remote Launch and Remote Activation.

For more information, see Securing a Remote WMI Connection http://msdn.microsoft.com/en-us/library/windows/desktop/aa393266(v=vs.85).aspx

If you’re seeing “Invalid Query” error message most likely your remote machine doesn’t have updated WMI objects. This can be easily fixed by installing the following hotfix (on the remote machine): http://support.microsoft.com/kb/932370

SQL Server Backup and Restore with Windows Azure Blob Storage Service

2013-01-24T20:22:00Z

In SQL Server 2012 SP1 Cumulative Update 2, new feature is available to backup, restore SQL Server databases to Windows Azure Blob store.

T-SQL Sample:

C# Sample using SMO:

You can read more about this feature here:

Team Blog : http://sqlblog.com/blogs/sqlos_team/archive/2013/01/24/backup-and-restore-to-cloud-simplified-in-sql-server-2012-sp1-cu2.aspx

MSDN documentation, T-SQL, C# code samples: http://msdn.microsoft.com/en-us/library/jj919148.aspx

Please try out this feature and send us your feedback through SQL Server Connect https://connect.microsoft.com/sqLServer/feedback

SQL Server 2000 will no longer be supported

2013-01-22T19:57:00Z

You can refer to section "SQL Server 2000 Support Information" in link - http://www.microsoft.com/en-us/sqlserver/support/support-updates.aspx . If you are using SQL 2000, please consider upgrading to latest version of SQL Server as soon as possible.

Upgrade to SQL 2008 R2 - http://msdn.microsoft.com/en-us/library/bb677622(v=SQL.105).aspx

Upgrade to SQL Server 2012 - http://technet.microsoft.com/en-us/library/bb677622.aspx

Latest service packs and cumulative updates for SQL Server

2012-12-05T18:49:34Z

Links for Latest service pack and cumulative updates for SQL Server - http://technet.microsoft.com/en-us/sqlserver/ff803383.aspx

SQL 2012 SP1 available for download

2012-11-14T16:52:20Z

You can download SQL 2012 SP1 from http://www.microsoft.com/en-us/download/details.aspx?id=35575

Please send us your feedback / any issues to SQL Server feedback http://connect.microsoft.com/SQLServer/feedback/

Troubleshooting Windows Azure SQL Database

2012-11-02T19:38:17Z

Wayne has published blog article explaining how you could use these new DMVs to Troubleshoot SQL Azure database

http://blogs.msdn.com/b/wayneb/archive/2012/11/02/introducing-sys-database-connection-stats-for-troubleshooting-windows-azure-sql-database.aspx

MSDN Reference : http://msdn.microsoft.com/en-us/library/windowsazure/ee730906.aspx

SQL Server 2012 SP1 CTP4 Now Available

2012-09-21T15:08:20Z

SQL Server 2012 SP1 CTP4 Now Available- http://blogs.technet.com/b/dataplatforminsider/archive/2012/09/20/sql-server-2012-sp1-ctp4-now-available.aspx

Customers running SQL Server 2012 can download and test the SP1 CTP4 and send feedback to us at https://connect.microsoft.com/SQLServer/Feedback

SQL Server 2012 SP1 CTP4

SQL Server 2012 SP1 CTP4 Express

SQL Server 2012 SP1 CTP4 Feature Pack

Management Data Warehouse overview report

2012-09-04T18:18:00Z

You can view all data in Management data warehouse for all instances that upload collected data to the warehouse database using "Management Datawarehouse overview report"

You can view this report by following these steps
1) Open SQL Server management Studio, Connect to SQL Server instance that is hosting Management data warehouse database (MDW)

2) Right click on MDW database, Select Menu
Reports -> Management DataWarehouse -> Management Datawarehouse overview

3) On right pane you will see a report that list all the instances that upload collected data to this MDW.
for each instance, you can launch the hyperlinks for reports that lets you view data collected by system collection sets

Power shell to script tables and procedures (with dependencies) and permissions

2012-08-28T19:51:00Z

You can use Power shell and SMO to script out objects and its dependents in a database along with object permissions. This blogs entry has a sample power shell script that you can use to generate script in automated way.