Migrate local MS SQL Database to Microsoft Azure SQL
In my asp.net applications I use MS SQL Databases for data storage. As I’m currently in the Free Trial period, I want to migrate / copy / sync / import / export (whatever you name it) my existing MS SQL Database to Microsoft Azure SQL Database.
Prerequisites
Microsofts MS SQL Server Management Studio has feature included to deploy a database to Azure. So I downloaded the latest version of MS SQL Management Studio which was 2015 July preview. It installs nice to my MS SQL Management Studio 2012 SP 2 so you could use both in parallel.
As seen on Azure Pricing Details, SQL Database is not available in Free Plan, only in Basic, Standard and Premium editions. So you will have to pay for it. Anyway, I’m in the free trial, so let’s start creating the database.
Create Azure SQL Server
If you are new to Azure and have not create a Azure SQL Server yet, go to your Azure Portal and click on ‘New’ -> ‘Data + Storage’ -> ‘SQL Database’. Open option ‘Server’ and click ‘Create a new server’. There you could enter the Servername you like to use. It’s always in ‘.database.windows.net’, so you might end up with something like ‘MySQLServer.database.windows.net’. Additionally you also have to create a new Server Admin Login and select the location. With ‘OK’ your first Azure SQL Server is created.
Now you could go back to your portal page. You should not create a database now, because otherwise you will later on get the error message ‘The database already exists on the target instance of SQL Server. Specify a database name that does not already exist.’
Firewall settings
By default, your new Azure SQL Server is not accessible from outside so you would not be able to connect with your local machine to it. Therefore we have to maintain the firewall settings first. So in Azure Portal, click ‘Browse all’ and find your new SQL Server and open the details. On the ‘Essentials’ tab you will find an option ‘Show firewall settings’. When you click on it you will see that there are currently no firewall rules configured but you will also see your Client IP Adress. And a button ‘Add Client IP’. How convenient! Click the button and save the new settings. Keep in mind that you have to redo this step every time your local IP changes (if you have a dynamic IP address).
Start deploying
So I started MS SQL Server Management Studio and connected to the database on my local MS SQL Server. With a right-click on the database you have a menu option ‘Deploy Database to Microsoft Azure SQL Database’ within menu ‘Tasks’. After reading the info page you have to establish the connect to the target Azure SQL Server you just created. Enter the full database name (e.g. ‘MySQLServer.database.windows.net’) and the server admin login with password. Next to it, enter the name of your new database. Click ‘Next’ to get a summary of your settings and click ‘Finish’ to start the deployment.
You will see the different steps, like ‘Extracting Schema’, ‘Validating Schema Model’ etc. If all went well: Fine, you’re finished! Unfortunately I had lots of errors. Errors like
- Error SQL71561: Error validating element [dbo].[myview]: View: [dbo].[myview] has an unresolved reference to object [mydb].[dbo].[mytable]. External references are not supported when creating a package from this platform.
- Error SQL71501: Error validating element [dbo].[myview]: View: [dbo].[myview] has an unresolved reference to an object. Either the object does not exist or the reference is ambigous because it could refer to any of the following objects:
- Error SQL71562: Error validating element [dbo].[myprocedure]: Procedure: [dbo].[myprocedure] has an unresolved reference to object [mydb].[dbo].[mytable]. External references are not supported when creating a package from this platform.
Wow. Some dozens errors.
Fortunately they were all very easy to fix: In the above mentioned view I included the database name in some queries, and the deployment process does not like that. So I opened MS SQL Server Management Studio 2012 and checked all the procedures and views mentioned in the error messages. They contain queries like ‘SELECT [ID] FROM [MyDatabase].[dbo].[MyTable]‘ even though the data is retrieved from the current database. So I removed the Database name from all these queries, resulting in e.g. ‘SELECT [ID] FROM [dbo].[MyTable]‘.
That was very easy to fix, and now the deployment ran fine. The new Azure SQL Database is now accessible in MS SQL Server Management Studio like all other databases from any other server.
My first steps using PHP with Microsoft Azure
I’m thinking about migrating to the cloud for years, e.g. after attending Microsofts Visual Studio Evolution 2012. As a .net developer I would of course prefer Microsoft Azure over Amazon Web Services (AWS), Heroku and lots of others.
But Cloud has a big disadvantage for me: As I’m doing all this in my freetime, I want to have a certain limit for the total costs per month. With dedicated hardware I have fixed limit. If there is too much traffic, website is down. Ok, that’s bad, but it’s even worser if I have to pay a monthly bill of hundreds of Euros for my free projects. During the talk on the Visual Studio Evolution with some experts from Microsoft they showed a new price calculator, nevertheless it did not meet my expectations. Of course the cloud is only for professionals, needing the scalability, CDN etc. but I’d prefer to have a small solution for a small fee so freetime developers could test and build up their knowledge. Continue reading…
Tips how to use Flatfile CMS Grav with simple PHP webhosting
I’m now using Grav for more than 6 months on my website Fussball im FreeTV so I thought it’s now about time to write a summary in order to help new users. Maybe you run into the same questions…. Continue reading…
Tags with autocomplete in asp.net textbox
My last posting showed how to use jQueryUI autocomplete with some individual formatting and an image. Now I have a similar request: I want to build an input for tags (or tokens). It should also provide suggestions from a backend using autocomplete (so that’s similar to previous posting) but the returned list does not need any special formatting or images. Instead I need to have an option to allow multivalue, and it would be nice to have some box around each tag and an ‘x’ to delete a tag.
Autocomplete in asp.net with images and formatting
Before jQuery, I used AJAX Control Toolkit and I liked it as it contained lots of helpful widgets. With jQuery there are now much more options how to implement something. Today I searched for something like the autocomplete so you could type in the first letters and then get some proposals. We all know this from Google etc. Some sides also have formatting in the result list, e.g. at Telerik, or also show images. So let’s see whether we could do the same for asp.net.
Create a Recommendation box similar to LinkedIn using asp.net
If you are a member of LinkedIn (like me) you probably know the recommendation box at the upper right. It shows you some people you might know (according to LinkedIns algorithms). I like this plugin because it’s easy to use. As I needed to add a similar functionality to a new project I’ve investigated a bit and here is how to add such a plugin to your own aspx.net website.
50000 Android Downloads!
Gestern war es soweit: Am 15. März 2015 wurde die Android-Version unserer Fussball-App zum 50.000 Mal heruntergeladen! Besonders freut uns das gleichbleibend starke Interesse an unsere App sowie die weiterhin ausgezeichneten Bewertungen: Im Durchschnitt 4,14 Sterne und jede Menge positiver Kommentare sind Ansporn für die nächsten Monate.
Use Toast Notification popups in asp.net
jQuery has some wonderful toast notification popups. In some of my web projects they are really helpful, but I did not find an easy general solution how to add them into all of my asp.net pages. Sure there are commercial solutions e.g. from Telerik but as long as you don’t need the other included stuff they are oversized (and expensive) if you just need the toast notifications. So I tried to find a solution which is
- easy to implement,
- works fine with master pages and
- free of charge.