Showing posts with label sql. Show all posts
Showing posts with label sql. Show all posts

Reading Notes #290

SEcurityCenterCloud


Programming


Databases


Miscellaneous



Reading Notes #266

Retropie_SplashSuggestion of the week


Cloud


Programming


Databases

  • SQL Database Query Editor available in Azure Portal (Ninar Nuemah) - I was looking for this since the old query tool was removed. I will probably continue to use SQL studio management or VsCode, put what a time saving, and you are investigating a problem... Open a blade right from the Azure portal and voila!

Miscellaneous

  • MVP API Intro (Daron Yöndem) - I love it! I already have few ideas in mind, and I'm curious to see what you will do guys.


Reading Notes #264

2017-01-22_21-13-48Cloud


Programming

  • Introducing Docker 1.13 (Docker Core Engineering) - This post summarizes all the great features added in the new release and shows again why Docker is such a fantastic tool in the containers' world.

Databases


Miscellaneous


Reading Notes #263

IMG_20170113_162910Cloud


Programming


Databases



Connect VSCode to Azure SQL Database from everywhere

Visual Studio Code looks like a simple text editor. However, the fact that it running from anywhere (Windows, Linux, MacOs) and that you can highly customize it, make it very special. The way you customize your VSCode is by using extensions. The last extension I installed was MSSQL. In this post, I will explain how to leverage this extension to connect from everywhere to your database.

Installation


Adding the extension is very simple you can go to the Extension menu and type mssqm in the search area; then click Install.

install_mssql

Configuration


To create your connection profile, you could just open Command Palette and type connect, the extension will then ask you to enter the server name, username, password…

connect_longway
To avoid any typo I tend to copy/paste the information, but every time the dialog was losing the focus it was closing. The workarounds to this unfortunate problem was to open the User Settings and specify my connection information. Here is the configuration:

"mssql.connections": [
    {
        "server": "{{put-server-name-here}}",
        "database": "{{put-database-name-here}}",
        "user": "{{put-username-here}}",
        "password": "{{put-password-here}}"
    },
    {
        "profileName": "Azure SQL FBoucher",
        "server": "sqlservername.database.windows.net",
        "database": "demodb",
        "user": "admin",
        "password": "",
        "authenticationType": "SqlLogin",
        "savePassword": true
    }
]

I kept password empty and savePassword equal to true. This way the first time you connect to the database you will need to enter it and it will be saved outside of the user Setting using the Password Management.
Now to connect you only need to open the Command Palette and type connect or mssql, to see the MS SQL: Connect option and select the connection profile you just created.

connect_profile


Using the mssql extension


Of course, you can type any query you like, but the extension is offering not only snippets, but a contextual auto-completion. That mean the VSCode once connected will know all the column’s names of all tables and boost as must your productivity.

snippets
To execute the query a simple Ctrl + E, and voila!

query_and_result


Wrapping up


This fantastic extension is providing a light-weight setup to execute or write any SQL scripts whatever you are on Windows, Linux or MacOS. It doesn’t have all the features of the bigger tool like SQL Server Management, when databases are not your primary focus or even to have a light setup on the go, the Visual Studio Code SQL Server extension is definitely a must.


References:




Reading Notes #252

ReadingNotesAppCloud

  • Azure Subscription Migration to CSP (Kirill Kotlyarenko) - Excellent post that explains the differences between the different types and how to migrate ... with a minimum of pain.
  • New lower Azure pricing (Takeshi Numoto) - Really good news more VMs types and lower prices.
  • Storing and using secrets in Azure (Bertrand Le Roy) - This p is great tutorial that explains all h steps to configure use the Azure Vault, when our secrets need more then sits in a config file.

Programming


Databases

  • Avoid ORDER BY in SQL Server views (Aaron Bertrand) - Sometimes we need workarounds to bend some services to our wishes, but we must keep in mind the real best practices.



Reading Notes #246

IMG_20160826_115405Cloud


Programming


Databases


Miscellaneous


Reading Notes #243

valutoBusinessSuggestion of the week


Cloud


Programming


Miscellaneous



Reading Notes #242

mapCloud


Programming

  • Exploring dotnet new with .NET Core (Scott Hanselman) - I discover the different types in dotnet new command during Julie Lerman's talk at DevTeach and now this post shows a list of incredible opportunities.

Miscellaneous


Reading Notes #222

hourglassSuggestion of the week


Cloud


Data



Reading Notes #220

Logic-AppSuggestion of the week


Cloud


Programming


Data

Book

  • Software Development Book Giveaway! - Cool! A great opportunity, 3 free books are given: Building Microservices, Working Effectively with Legacy Code, and Javascript: The Good Parts.

Miscellaneous



Reading Notes #201

balanceCloud

  • Tracing and logging with Application Insights (Andrei Dzimchuk) - You know the 101 about App Insights and you are looking for something more specific? This post if a must it shows how to transform an ordinary logger in a great source of information.

Programming


Databases


Miscellaneous



Reading Notes #191

Image by FutUndBeidl / FlickrSuggestion of the week


Cloud


Programming

Miscellaneous




Image by FutUndBeidl / Flickr


Reading Notes #185

IMG_20150503_181242Suggestion of the week


Cloud


Programming


~Frank



Reading Notes #182

post-it_AzureBootcamp2015Suggestion of the week


Cloud

released the DocumentDB Data Migration tool, an open source solution that imports data from a variety of sources, including JSON files, CSV files, SQL Server, MongoDB and existing DocumentDB collections.

Programming


Miscellaneous


~Frank B.


Upgrade an Application Windows Azure OS Family

Recently I add to upgrade an web site running in Azure Webrole from Azure OS famille 1.6 to a more recent version. While the migration was not complicated I encounter some little particularity that I found could be interesting to share.

The Context

The website was a Visual Studio 2010 project using Azure SDK 1.6 and a library call AspNetProvider that was part of Microsoft's sample few years ago to manage session and membership. Using the AspNetProvider library the session was saved in Azure blob storage, and the membership was saved in an SQL database.

The Goal

The application must stay a Visual Studio 2010 project, but using the most-recent Azure SDK and Azure Storage Client as possible.

The Solution

  • Azure SDK 2.1
  • Azure.StorageClient 4.0
  • Universal Provider version 2.1
  • OS famille 4

The Journey


Migration from SDK 1.6 to SDK 2.1


Azure SDK version 2.1 is the higher version compatible with Visual Studio 2010. And can be downloaded from Microsoft's website. Once it is installed, just open the project in Visual Studio and right-click on the Azure Project. By clicking on the upgrade button the magic will happen. Some errors could stay but the hard work will be done for you.


Migration from AspNetProvider to UniversalProvider


we need to remove all reference to the AspNetProvider library. Just expand the resources node in the Solution Explorer and delete the reference. One thing important is that since we are using Visual Studio 2010 the latest version of the UniversalProvider we can use is 1.2. More recent version are using .Net 4.5 and this is not compatible with the present solution. To get the reference added to the project just execute the following Nugget command:
Install-Package UniversalProvider -version 1.2

Check the web.config file to clean the membership connections.

Migration of the Azure Storage Client


This one is the easiest, just remove the reference in the reference node and then execute the following Nugget Command:
Install-Package Azure.Storage.Client

Migration of the membership data


The AspNetProvider was using prefixed SQL tables: aspnet_user, aspnet_membership, etc. The new membership manager is using another sets of tables. We must migrate the data from one set to the other one. Here a SQL script that will to exactly that. The script can be run multiple times because it will only copie the unmoved data.
-- ========================================================
-- Description:    Migrate data from asp_* tables 
--                 to the new table used by Universal provider
-- ========================================================

DECLARE @CNT_NewTable AS INT
DECLARE @CNT_OldTable AS INT

-- --------------------------------------------------------
-- Applications -------------------------------------------

INSERT INTO dbo.Applications (ApplicationName, ApplicationId, Description)
    SELECT    n.ApplicationName, n.ApplicationId, n.Description 
    FROM    dbo.aspnet_Applications o 
    LEFT    JOIN dbo.Applications n ON o.ApplicationId = n.ApplicationId
    WHERE    n.ApplicationId IS NULL

SELECT @CNT_NewTable = Count(1) from dbo.Applications 
SELECT @CNT_OldTable = Count(1) from aspnet_Applications

PRINT 'Application Count: ' + CAST(@CNT_NewTable AS VARCHAR) + ' = ' + CAST(@CNT_OldTable AS VARCHAR)

-- -------------------------------------------------------- 
-- Roles --------------------------------------------------

INSERT INTO dbo.Roles (ApplicationId, RoleId, RoleName, Description)
SELECT    o.ApplicationId, o.RoleId, o.RoleName, o.Description 
FROM    dbo.aspnet_Roles o
LEFT JOIN dbo.Roles n ON o.RoleId = n.RoleId
WHERE n.RoleId IS NULL

SELECT @CNT_NewTable = Count(1) from dbo.Roles 
SELECT @CNT_OldTable = Count(1) from aspnet_Roles

PRINT 'Roles Count : ' + CAST(@CNT_NewTable AS VARCHAR) + ' = ' + CAST(@CNT_OldTable AS VARCHAR)

-- --------------------------------------------------------
-- Users --------------------------------------------------

INSERT INTO dbo.Users (ApplicationId, UserId, UserName, IsAnonymous, LastActivityDate)
SELECT o.ApplicationId, o.UserId, o.UserName, o.IsAnonymous, o.LastActivityDate 
FROM dbo.aspnet_Users o LEFT JOIN dbo.Users n ON o.UserId = n.UserID 
WHERE n.UserID IS NULL

SELECT @CNT_NewTable = Count(1) from dbo.Users 
SELECT @CNT_OldTable = Count(1) from aspnet_Users

PRINT 'Users count: ' + CAST(@CNT_NewTable AS VARCHAR) + ' >= ' + CAST(@CNT_OldTable AS VARCHAR)

-- --------------------------------------------------------
-- Memberships --------------------------------------------

INSERT INTO dbo.Memberships (ApplicationId, UserId, Password, 
PasswordFormat, PasswordSalt, Email, PasswordQuestion, PasswordAnswer, 
IsApproved, IsLockedOut, CreateDate, LastLoginDate, LastPasswordChangedDate, 
LastLockoutDate, FailedPasswordAttemptCount, 
FailedPasswordAttemptWindowStart, FailedPasswordAnswerAttemptCount, 
FailedPasswordAnswerAttemptWindowsStart, Comment) 

SELECT o.ApplicationId, o.UserId, o.Password, 
o.PasswordFormat, o.PasswordSalt, o.Email, o.PasswordQuestion, o.PasswordAnswer, 
o.IsApproved, o.IsLockedOut, o.CreateDate, o.LastLoginDate, o.LastPasswordChangedDate, 
o.LastLockoutDate, o.FailedPasswordAttemptCount, 
o.FailedPasswordAttemptWindowStart, o.FailedPasswordAnswerAttemptCount, 
o.FailedPasswordAnswerAttemptWindowStart, o.Comment 
FROM dbo.aspnet_Membership o
LEFT JOIN Memberships n ON  o.ApplicationId = n.ApplicationId
                      AND o.UserId = n.UserId
WHERE n.UserId IS NULL AND n.ApplicationId IS NULL


SELECT @CNT_NewTable = Count(1) from dbo.Memberships 
SELECT @CNT_OldTable = Count(1) from aspnet_Membership

PRINT 'Memberships count: ' + CAST(@CNT_NewTable AS VARCHAR) + ' >= ' + CAST(@CNT_OldTable AS VARCHAR)

-- -------------------------------------------------------
-- UsersInRoles ------------------------------------------
TRUNCATE TABLE dbo.UsersInRoles
INSERT INTO dbo.UsersInRoles SELECT * FROM dbo.aspnet_UsersInRoles


SELECT @CNT_NewTable = Count(1) from dbo.UsersInRoles 
SELECT @CNT_OldTable = Count(1) from aspnet_UsersInRoles

PRINT 'UsersInRoles count: ' + CAST(@CNT_NewTable AS VARCHAR) + ' >= ' + CAST(@CNT_OldTable AS VARCHAR)


Migration from OSFamilly 1 to 4

Open the file .cscfg and edit the OS Family attribute. It's in the ServiceConfiguration node.
<ServiceConfiguration servicename="MyApp" osFamily="4" osVersion="*" ...>    


Wrapping up

The only step left is to deploy in the staging environment to see if everything is working as expected. would recommend also to plan to upgrade as soon as possible because the Azure SDK 2.1 official retirement date is November 2015. I hope this post could help you, even if you are migrating from and to a different version. Any comments, suggestions and/or questions are welcome.


~ Frank Boucher


Let's play with Azure SQL Database backup and the Point in Time Restore

Did you know that you can have: a full database backup once a week, a differential database backups once a day, and a transaction log backups every 5 minutes of your Azure SQL Database? Did you know that all this is done automatically when you are using the new Azure SQL Basic, Standard or Premier service tiers? Even more, you will have access to the Point in Time Restore self-service. In this post, I will show how to "configure" the database to get the automatic backups, and how to do a restore.

Setup the automatic backup


If you are like me, your Azure SQL Databases are set to Web or Business edition.  The first thing to do will be to change that. You will need to do it anyway since the Web and Business service tiers will be retired in September 2015[1].  That the only required since backup service are built-in the Basic, Standard and Premium tiers.
For the demo purpose, I will use the Basic tier. To change the tier of the database, go on the Azure Portal. In the left panel click on the SQL Databases et select the database that you want to update (ie: FrankDemo). Once the right section is updated, select the Scale tab and change the Service Tiers for: Basic.

Tiers_Basic

Now, if you return in the Dashboard tab, a new option will be available.

Restore_button

Your database now has built-in backups to support self-service Point in Time Restore and Geo-Restore. Azure SQL Database automatically creates backups using the following schedule:
  • Full database backup once a week
  • differential database backups once a day
  • transaction log backups every 5 minutes.
The full and differential backups are replicated across regions.

The retention period will vary between 7 and 35 days base on the selected tiers.[2]

Restore an Azure SQL Database


Restoring a database is really easy. Remember that new button at the bottom of the screen, it's now time to click on it.


Restore_setings

This will bring the settings options. It's now time to type the name of your restored database. Note that you must use a different name than the original.  It's always a good practice to double-check that you are pointing on the good database on the correct server. Pick a restore point using the slider or by filling the date and time fields. When you are done click the button. The portal will let you know that the restore is successfully completed by a notification.


Restore_done

I hope that this post shows you how easy it is to use the backup/restore with Azure SQL Database. Thank you for ready, Any comments, suggestions and/or questions are welcome.



[1]: Web and Business service tiers will be retired in September 2015, more
details on the Windows Azure site.
[2]: Detail about the retention period on Azure SQL Database Backup and Restore



~ Frank Boucher




Reading Notes #152

Suggestion of the week


Cloud


Programming


Databases


UX


Miscellaneous


~Frank


Reading Notes #129

OlympicsSuggestion of the week


Cloud


Programming


Databases


Miscellaneous

Reading Notes #127

Suggestion of the week

Cloud

Programming

Databases

Integration

  • Streaming Xml Transformations (Christos Karras) - Great tutorial that explains very clearly what are our solutions when we are in front of a complex XML transformation.

Miscellaneous