Project 31-A

There are several blog posts on this site answering how to copy an Azure SQL database. Since they are older or in some cases lost (Wayne's Microsoft blog posts are gone), I'll link to the Microsoft docs.  Enterprise data or functionality? Don't use these processes if you need change detection or other enterprise-level requirements. Copy the Azure SQL database and keep it in the cloud. Make a copy of your Azure Cloud database To copy an existing Azure SQL Database in the Azure portal, you can copy or export. Generally, I choose to export to Azure Storage Blob container as a bacpac file . This allows me to use it as either a cloud or local database. The bacpac file includes data and schema. Watch for export completion To watch for export completion, from the Azure Server (not database) in the portal, use the  Import/Export History in the Data Management section. Download bacpac file from Azure Storage To download the bacpac file, Azure Storage container in the Azure portal...

I recently moved an Azure SQL database back to a local development database and ran into a few issues. I took these notes so that they might help the next person that hits the problem. In my local SSMS, I use the Import/Export wizard with the datasource using SQL Server native client 11.0. This moves the tables and data. Any destination tables will not have IDENTITY as the source tables did.  Solution # 1  Move away from INT Identity to use GUIDS. This requires work in the database and client code but is the better choice if you need to move data out of the source datatabase then back into the source database.  Solution #2 More immediate fix to get past the block that your inserts don't autoincrement.  Steps: The following steps are completed in SSMS in the destination (local) database and should have all the data but not the IDENTITY column. Rename the mytable to mytable2. Generate CREATE, INSERT, and SELECT scripts for the table. Modify the CREATE script to use the...

I recall being asked that question at an interview long ago. In the circles that I had been working we had never used that (academic) term and had been using the diagramming tools in SQL Server Management Studio(SSMS). It was simply what we used. So what are crow’s feet?   It is a diagramming notation which, incidentally, is not supported in SQL Server Management Studio but is supported in third party tools. Since I had been working for Microsoft’s ITG SQL Server Team for several years prior without any third party tools --- I could not return a snappy answer. The origin of this was that the early adapters moved to Oracle, so this because an attitude issue with Microsoft.   Today, crow’s foot notation has become the non-Microsoft industry standard in stead of SSMS infinity key notation. The comical thing is that Microsoft Visio does support crow’s foot notation. A few example tools that uses this notation are: ARIS , System Architect , PowerDesigner , Toad Data Modele...

This blog post by Wayne Berry shows how to migrating your Windows Azure SQL Database Using Data-Tier Application Framework (DacFX) Technology to an on-premise SQL Server. With the Window Azure Portal, you can easily create a Data-Tier Application logical backup package (BACPAC), store it to your Windows Azure Blog Storage; and then, using SQL Server Management Studio 2012, you can import that package to your local database server. Read: Migrating your Windows Azure SQL Database Using Data-Tier Application Framework (DacFX) Technology {6230289B-5BEE-409e-932A-2F01FA407A92}

This is a more complex version of a reported Amazon question , where there is only a single one. There are two questions based on constraints: Minimize the time to get the array of numbers. Minimize the memory requirements. Minimize the time to get the array of numbers: Step 1 The first step of the solution is to use two bit maps : Map A is Int.MaxValue +1 in size Map B is Int.MinValue in size Both are initialized to zero (the default ). Walk the array. If not negative, toggle Map A If negative, take the negative value (you should raise the issue that the language should be tested for the fastest way to do this, i.e. Math.Abs(), using binary operations, x=-x, etc). NOTE: We could use 1 bitmap, but this means that we will need to do an arithmetic operation for 50% of the array. In general bit operations perform better, hence the design should be bit-operation centric. Once the array has been walked, then walk the bitmaps for non-zero values....

This is my final comments on the book below (click to go to publisher site). First, I’m likely a tough reviewer having been a professional technical writer for Microsoft since the mid 1990’s and still doing that occasionally. Second, I’m a pedagogue (ex-teacher for those that are vocabulary challenged) and tend to read stuff at several levels – including suitability for teaching or mentoring. The first question is what type of book is this? This book will be a useful book on my bookshelf because it touches enough area in sufficient depth to serve as a cookbook for first recipes . The problem is that it try to span too many target audiences and as a result does not make it in any area well. Is it a Cookbook? The number of items covered and the crispness of the coverage suggests that it is. The problem is that if I compare it to the classic Cookbooks from O’Reilly, it is both too shallow and too sparse. It’s more a collection of recipes clipped from ‘Women’s Journal’ (or sho...

I’m continuing onwards with my review of . So far my biggest grip is that IMHO this is not an “Enterprise” book I would suggest Microsoft SQL Azure: Introduction to Application Development” – with that name, I would give the book good ratings as such (80%ile – better than 4 out of 5 similar books). With the existing time, it disappoints against what I expected.   Chapter 6: SSIS and SSRS Applications Using SQL Azure The author tried to kludge a solution to a problem in this chapter without doing analysis or coming up with a good solution.  A sharded solution for security is clumsy at best, there are better solutions for column level security. The issues of update and remedying  inconsistencies arising from sharding are neither raised nor addressed.  A simpler solution given that the end deliverable was a Microsoft Access database would be to just do pass through tables to the two SQL data bases and do an appropriate join in Microsoft Access. The second part...

I’m continuing onwards with my review of . For those that are interested, Microsoft is offering a 30-Day Pass:   USA Developers: Windows AzurePlatform 30-Day Pass We're offering a Windows Azure platform 30-day pass, so you can put Windows Azure and SQL Azure through their paces. Use promo code MSDNT1. No credit card required. With the Windows Azure platform you pay only for what you use, scale up when you need capacity and down when you don't. One Caveat : Microsoft’s “accept this license page” is case sensitive for your first and last name --- I suspect poor quality controls of their contractors… Chapter 3: Working with SQL Azure Databases from Visual Studio 2008 For a book published in 2010 to not use the latest edition of Visual Studio available (2010) is a little disappointing. What is confusing is that on p.106, we suddenly jumped to VS 2010 Express – suggesting that the technical editing needs to be tougher.   The structure diagram on pag...

I have been involved with SQL Server since before the first beta versions went out – I was working as a consultant to Microsoft’s Internal Technology Group and we were the bleeding edge folks in those days. I was heavily involved with stressing and performance analysis (given the nick name on my door of “Dr.Science” because of my statistical analysis) and we had the joys of getting up to two different builds a day from the Dev Divisions when we encountered issues.   Today, I have several projects that are likely needed to be cloud-supportable in the near future so it’s time that I get up to speed and hope they don’t re-invent the technology before I need to build commercial systems on it. Two books have come across my desk recently that on first read appear ideal. The first one is on SQL Azure and is not focused on the “Hello World” style of book often seen. A second aspect is that it’s a new-kid-on-the-block publisher, and often they do a better job then the old folks who find...

Knowing your data might be the most important quality of the new database administrator (DBA). Just like a marketing person needs to know their customers, a CEO needs to know their market; a DBA needs to truly understand their data. This article is about how knowing your data becomes even more important with cloud databases like SQL Azure Database , and NoSQL style data stores like Cassandra and Windows Azure Storage . SQL Server Using a scale up approach for your on-premise SQL Server database, the SQL Server DBA needed: A good working understanding behind the relationships of the data, i.e. how tables related to each other. This allowed the DBA to develop foreign key to primary key relationships, one of the mantras of a relational database. Understanding how data was being inserted and updated into the table allowed the DBA to optimize their queries for locking, partitions for performance, and transaction logs for growth. Understanding how data was queried from th...

  Scaling up an on-premise SQL Server is the concept of adding more computing resources to your existing hardware, like memory, processing power or storage. The purpose is to allow your SQL Server to handle more queries, gain performance or store more data. When you do decided to go to the cloud you should realize that SQL Azure doesn’t scale up like SQL Server – it scales out. Scaling out is adding additional machines with the same data and dividing the computation between them. This article will discuss the differences between scaling up and scaling out and how you need to plan for scale out on SQL Azure to gain performance. Due to hardware and licensing costs of SQL Server, it is much easier to add resources to existing hardware, than it is to deploy an additional replicated SQL Server on new hardware to gain performance.   The problem with scaling out (adding more machines) in an on-premise SQL Server installation is that this adds a degree of complexity to the des...

Compared to SQL Server here is the list of things that you can be completely ignorant about and still be a SQL Azure DBA: Hard Drives : You don’t have to purchase hard drives, hot swap them, care about hard drive speed, the number of drives your server will hold , the speed of SANs or motherboard bus, fiber connection, hard drive size . No more SATA vs. SCSI debate. Don’t care about latest solid state drive news . Everything you know about Hard Drives and SQL Server – you don’t need it. RAID : How to Implement RAID, choose a RAID type , divide physical drives on a RAID – don’t care. RAID is for hornets now. Backup and Restore: You need to know nothing about Back up SQL Server data files , transaction logs, or how the database model effects your backup. You don’t need a backup plan/strategy – nor answer questions the about risk factor for tsunami in Idaho. No tapes, tape swapping, or tape mailing to three locations including a hollowed-out mountain in the Ozarks. R...

I am running the SQL Azure Migration Wizard v3.2 on my 15 year old SQL Server 2005 database in order to transfer it to SQL Azure.  As it ran it outputted these two errors:   UPDATETEXT is not supported in current version of SQL Azure TEXTPTR is not supported in current version of SQL Azure   When the wizard tried to script this stored procedure: ALTER PROCEDURE [dbo].[AppendToAreaText] ( @Area_Id int , @Area_Text text ) AS DECLARE @ptrval binary (16) SELECT @ptrval = TEXTPTR(Area_Text) FROM Area WHERE Area_Id = @Area_Id IF (@ptrval IS NULL ) UPDATE Area SET Area_Text = @Area_Text WHERE Area_Id = @Area_Id ELSE UPDATETEXT Area.Area_Text @ptrval NULL 0 @Area_Text The stored procedure sets the Area_Text to the incoming text if the column is currently null or concatenate the text onto the text in the column (saving the all the text) back into the column.   Doing some research and I came upon ...

I found the SQL Azure Migration Wizard v3.2 on CodePlex and I am excited to use it.  I was looking for a one-click solution to get my 15 year-old database running on SQL Server 2005 database onto SQL Azure.  I wanted to run the tool on the database server itself; the server is in a colocation facility with a fast pipe to the Internet.  It would make the transfer faster running it right off the SQL Server to SQL Azure. The Story However, after I downloaded  the Migration Wizard I got this error:   Could not load file or assembly 'Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies.   I realize that it requires SQL Server 2008 SQL Management Objects (SMO).   Because I am running SQL Server 2005, version 10 of that assembly wasn’t on my box all I had was version 9.X.X.X.  The easiest way to get it was to install SQL Server 2008 Management Studio Expre...

For sometime I have been been meaning to move my 15 year old web site to offsite hosting.  Currently it is hosted on a dedicated server running Windows 2003 and SQL Server 2005.  One of the issues is that over the years I haven't really put much time in to maintaining the technology.  I did upgrade from classic ASP to ASP.NET and did redo some of the pages in ASP MVC.  Lately as the site has slowed I have done some performance optimization in SQL Server like query optimization and index building.  However, it is still running SQL Server 2005.  When I heard about SQL Azure at the PDC two years ago I knew this is where I want to go.  However, I was waiting for R2 of SQL Server 2008 to make migrating a little easier.  In summary there is always an excuse.  So today I thought I would get started and see how far I could get before getting stuck.   “The first thing I learned is that you need to update your database level.”   S...