How to export all tables to csv by one export job

If you just want to export all the tables in a SQL database in to separate CSV files, then this is a quick and easy way of doing it.

  1. Execute below query which generates BCP commands

    SELECT ‘bcp ‘ + st.NAME + ‘ out c:\Target\’ + st.NAME + ‘.csv -c -r -d ‘ + DB_NAME() + ‘ -U user@??????.database.windows.net -S tcp:?????.database.windows.net -P ?????? FROM sys.tables st
  2. Paste the result set into text file. Make batch file and schedule it. (It can also be run in CMD manually)

 

Microsoft Message Queuing

MSMQ (Microsoft Message Queuing) is a message queue that is available by default as part of Windows. A reliable way of sending and receiving messages across computer systems, MSMQ provides a queue that’s scalable, thread-safe, simple, and convenient to use while at the same time providing you with the opportunity to persist the messages inside the Windows database. Continue reading Microsoft Message Queuing

Azure Resource Manager Templates Tips and Tricks for a Application Service

Over the past week, I spent some time automating Azure App Service infrastructure with Azure Resource Manager (ARM) templates. I discovered a few tips and tricks along the way that I’ll describe in detail below…

  • Take an environment name as a parameter
  • Create sticky slot settings
  • Variables aren’t just for strings
  • Change the time zone of your web app

Big thanks to David Ebbo’s talk at BUILD and his sample “WebAppManyFeatures” template; they are super handy resources for anyone who is working on writing their own ARM templates.

Environment Name Parameter

ARM templates are useful for creating the entire infrastructure for an application. Often this means creating infrastructure for each deployment environment (Dev, QA, Production, etc). Instead of passing in names for the service plans and web apps, I find it useful to pass in a name for the environment and use it as a suffix for all the names. This way we can easily use the same template to set up any environment.

{
    // ...
    "parameters": {
        "environmentName": {
            "type": "string"
        }
    },
    "variables": {
        "appServicePlanName": 
            "[concat('awesomeserviceplan-', parameters('environmentName'))]",
        "siteName": 
            "[concat('awesomesite-', parameters('environmentname'))]"
    }
    // ...
}

In the above example, if we pass in “dev” as the,environmentName we get “awesomeserviceplan-dev” and “awesomesite-dev” as our resource names.

Sticky Slot Settings

App settings and connection strings for a Web App slot can be marked as a “slot setting” (i.e., the setting is sticky to the slot). It’s not well documented how to specify sticky slot settings in an ARM template. It turns out we can do this using a config resource section called “slotconfignames” on the production site. Simply list the app setting and connection string keys that need to stick to the slot:

{
    "apiVersion": "2015-08-01",
    "name": "slotconfignames",
    "type": "config",
    "dependsOn": [
        "[resourceId('Microsoft.Web/Sites', variables('siteName'))]"
    ],
    "properties": {
        "connectionStringNames": [ "ConnString1" ],
        "appSettingNames": [ "AppSettingKey1", "AppSettingKey2" ]
    }
}

Here’s how it’ll look like in the portal:

Object Variables

Variables are not only useful for declaring text that is used in multiple places; they can be objects too! This is especially handy for settings that apply to multiple Web Apps in the template.

{
    // ...
    "variables": {
        "siteProperties": {
            "phpVersion": "5.5",
            "netFrameworkVersion": "v4.0",
            "use32BitWorkerProcess": false, /* 64-bit platform */
            "webSocketsEnabled": true,
            "alwaysOn": true,
            "requestTracingEnabled": true, /* Failed request tracing, aka 'freb' */
            "httpLoggingEnabled": true, /* IIS logs (aka Web server logging) */
            "logsDirectorySizeLimit": 40, /* 40 MB limit for IIS logs */
            "detailedErrorLoggingEnabled": true, /* Detailed error messages  */
            "remoteDebuggingEnabled": true,
            "remoteDebuggingVersion": "VS2013",
            "defaultDocuments": [
                "index.html",
                "hostingstart.html"
            ]
        }
    },
    // ...
}

And now we can use the siteProperties variable for the production site as well as its staging slot:

{
    "apiVersion": "2015-08-01",
    "name": "[variables('siteName')]",
    "type": "Microsoft.Web/sites",
    "location": "[parameters('siteLocation')]",
    "dependsOn": [
        "[resourceId('Microsoft.Web/serverfarms', variables('appServicePlanName'))]"
    ],
    "properties": {
        "serverFarmId": "[variables('appServicePlanName')]"
    },
    "resources": [
        {
            "apiVersion": "2015-08-01",
            "name": "web",
            "type": "config",
            "dependsOn": [
                "[resourceId('Microsoft.Web/Sites', variables('siteName'))]"
            ],
            "properties": "[variables('siteProperties')]"
        },
        {
            "apiVersion": "2015-08-01",
            "name": "Staging",
            "type": "slots",
            "location": "[parameters('siteLocation')]",
            "dependsOn": [
                "[resourceId('Microsoft.Web/Sites', variables('siteName'))]"
            ],
            "resources": [
                {
                    "apiVersion": "2015-08-01",
                    "name": "web",
                    "type": "config",
                    "dependsOn": [
                        "[resourceId('Microsoft.Web/Sites/Slots', variables('siteName'), 'Staging')]"
                    ],
                    "properties": "[variables('siteProperties')]"
                }
            ]
        }
    ]
}

Custom Time Zone

There’s a little-known setting in Azure App Service that allows you to set the time zone on a per-app basis. It is done by creating an app setting called WEBSITE_TIME_ZONE in the portal. It means we can also do this in an ARM template:

{
    "apiVersion": "2015-08-01",
    "name": "appsettings",
    "type": "config",
    "dependsOn": [
        "[resourceId('Microsoft.Web/Sites', variables('siteName'))]"
    ],
    "properties": {
        "WEBSITE_TIME_ZONE": "Pacific Standard Time"
    }
}

For more information on the time zone setting, check out this article.

Source code: WebAppManyFeatures.json

Allow access to Azure services, what does this actually mean?

I did some quick research on the SQL Server Firewall “Allow Access to Azure Services” option in Azure today.

And I sorry to say that my fears were right, that by setting this option does pose a significant security risk and leaves the SQL Server vulnerable.

Here is the extract of the article I found from Gaurav Hind at Microsoft

Access within Azure: This can be toggled by “Allow access to Azure services” Yes/No button on the portal (Firewall settings page). Please note, enabling this feature would allow any traffic from resources/services hosted in Azure (not just your Azure subscription) to access the database.

https://blogs.msdn.microsoft.com/azureedu/2016/04/11/what-should-i-know-when-setting-up-my-azure-sql-database-paas/

The big question now is how do you plug this gap in the firewall?  One possible solution is to build a virtual network within Azure or Filter network traffic with network security groups, this is beyond the scope of this article.

Azure Key Vault Setting up

Setting up Azure Key Vault for use within a .NET application is not an easy process, as it is a secure environment and security is required to gain access, which involved the Active Directly Application registrations and Access Policies.

I’ve created a quick setup guide based on the Azure Portal that is available at the time of setting up this article.

A web-developer’s guide to help eliminate non-coding tasks and get code done faster

Microsoft have just published this great eBook which helps web developers cut right to the code scripting of environments to make all the firewall ports and get code done quickly by providing an overview of key services in the cloud, what services to use based on your needs, step by step guidance, sample code, sample applications, and a free account to get started.

ebook-a-web-developers-guide

Here is a list of areas it covers

  • When to Use It
  • Moving an Existing ASP.NET Website to App Service
  • Identity Management
  • Scaling your Web App
  • Caching for Performance
  • Better Customer Experience with a CDN
  • Detect Failures Faster
  • Building a New Website on Azure App Service

 

Deployments Best Practices

Table of Contents

Introduction

This guide is aimed to help you better understand how to deal with deployments in your development workflow and provide some best practices. Sometimes a bad production deployment can ruin all the effort you have invested in a development process. Having a solid deployment workflow can become one of the greatest advantages of your team.

Before you start, I recommend reading our Developing and Deploying with Branches guide first to get a general idea of how branches should be setup in your repository to be able to fully utilize tips from this guide. It’s a great read!

Note on Development Branch

In this guide you will see a lot of references to a branch called development. In your repository you can use master (Git), trunk (Subversion) or default (Mercurial) for the same purpose, there’s no need to create a branch specifically called “development”. I chose this name because it’s universal for all version control systems.

The Workflow

Deployments should be treated as part of a development workflow, not as an afterthought. If you are developing a web site or an application, your workflow will usually include at least three environments: Development, Staging and Production. In that case the workflow might look like this:

  • Developers work on bugs and features in separate branches. Really minor updates can be committed directly to the stable development branch.
  • Once features are implemented, they are merged into the staging branch and deployed to the Staging environment for quality assurance and testing.
  • Once testing is complete, feature branches are merged into the development branch.
  • On the release date, the development branch is merged into production and then deployed to the Production environment.

Let’s take a closer look at each environment to see what are the most efficient way to deploy each one of them.

Development Environment

If you make web applications, you don’t need a remote development environment, every developer should have their own local setup.

Many customers have Development environments set up with automatic deployments on every commit or push. While this gives developers a small advantage of not installing the site or the application on their computers to perform testing locally, it also wastes a lot of time. Every tiny change must be committed, pushed, deployed, and only then it can be verified. If the change was made by mistake, a developer will have to revert it, push it, then redeploy.

Testing on a local computer removes the need to commit, push and deploy completely. Every change can be verified locally first, then, once it’s more or less stable, it can be pushed to a Staging environment for proper quality assurance testing.

However, what this does provide, is an environment that can ensure the auto-deployment to environment is successful and runs in an independent installation process far removed from the developers’ environment.

We do not recommend using deployments for rapidly changing development environments. Running your software locally is the best choice for that sort of testing.

We recommend to deploy to the development environment automatically on every commit or push.  This will ensure that the build process is full working.

Staging Environment

Once the features are implemented and considered fairly stable, they get merged into the staging branch and then automatically deployed to the Staging environment. This is when quality assurance kicks in: testers go to staging servers and verify that the code works as intended.

It is very handy to have a separate branch called staging to represent your staging environment. It will allow developers to deploy multiple branches to the same server simultaneously, simply by merging everything that needs to be deployed to the staging branch. It will also help testers understand what exactly is on staging servers at the moment, just by looking inside the staging branch.

We recommend always deploying major releases to staging at a scheduled time, of which the whole team is aware of.

Production Environment

Once the feature is implemented and tested, it can be deployed to production. If the feature was implemented in a separate branch, it should be merged into a stable development branch first. The branches should be deleted after they are merged to avoid confusion between team members.

The next step is to make a to show the difference between the production and development branches to take a quick look at the code that will be deployed to production. This gives you one last chance to spot something that’s not ready or not intended for production. Things like debugger breakpoints, verbose logging or incomplete features.

Once the diff review is finished, you can merge the development branch into production and then initialize a deployment of the production branch to your production environment by hand. Specify a meaningful message for your deployment so that your team knows exactly what you deployed.

Make sure to only merge development branch into production when you actually plan to deploy. Don’t merge anything into production in advance. Merging on time will make files in your production branch match files on your actual production servers and will help everyone better understand the state of your production environment.

We recommend always deploying major releases to production at a scheduled time, this should be a MANUALLY process not automated (This can be as simple as clicking a link to start the process going or moving some files, it just needs to be a human who activates the process), of which the whole team is aware of.  Find the time when your application is least active and use that time to roll out updates. This may sound obvious, but make sure that it’s not too late in the day, because someone needs to be around after the deployment for at least a few hours to monitor the application and make sure the deployment went fine. Urgent production fixes can be deployed at any time.

After deployment finishes make sure to verify it. It is best to check all the features or fixes that you deployed to make sure they work properly in production. It is a big win if your deployment tool can send an email to all team members with a summary of changes after every deployment. This helps team members to understand what exactly went live and how to communicate it to customers. Beanstalk does this for you automatically.

Your deployment to production is now complete, time to pop champagne and celebrate with your team!

Rolling Back

Sometimes deployments don’t go as planned and things break. In that case you have the possibility to rollback. However, you should be as careful with rollbacks as with production deployments themselves. Sometimes a rollback brings more havoc than the issue it was trying to fix. So first of all stay calm and don’t make any sudden moves. Before performing a rollback, answer the following questions:

Did it break because of the code that I deployed, or did something else break?

You can only rollback files that you deployed, so if the source of the issues is something else a rollback won’t be much help.

Is it possible to rollback this release?

Not all releases can be rolled back. Sometimes a release introduces a new database structure that is incompatible with the previous release. In that case if your rollback, your application will break.

If the answer to both questions is “yes”, you can rollback safely. After rollback is done, make sure to fix the bug that you discovered and commit it to either the development branch (if it was minor) or a separate bug-fix branch. Then proceed with the regular bug-fix branch → staging; bug-fix → development → production integration workflow.

Deploying Urgent Fixes

Sometimes you need to deploy a bug-fix to production quickly, when your development branch is not ready for release yet. The workflow in that case stays the same as described above, but instead of merging the development branch into production you actually merge your bug-fix branch first into the development branch, then separately into production, without merging development into production. Then deploy the production branch as usual. This will ensure that only your bug-fix will be deployed to the production environment without all the other stuff from the development branch that’s not ready yet.

It is important to merge the bug-fix branch to both the development and production branches in this case, because your production branch should never include anything that doesn’t exist in your stable development branch. The development branch is where developers work all day, so if your fix is only in the production branch they will never see it and it can cause confusion.

Automatic Deployments to Production?

I can’t stress enough how important it is for all production deployments to be performed and verified by a responsible human being. Using automatic deployments for Production environment is dangerous and can lead to unexpected results. If every commit is deployed to your production site automatically, imagine what happens when someone commits something by mistake or commits an incomplete feature in the middle of the night when the rest of the team is sleeping? Using automatic deployments makes your Production environment very vulnerable. Please don’t do that, always deploy to production manually.

Permissions

Every developer should be able to deploy to the Staging environment. They just need to make sure they don’t overwrite each other’s changes when they do. That’s exactly why the staging branch is a great help: all changes from all developers are getting merged into it so it contains all of them.

Your Production environment, ideally, should only be accessible to a limited number of experienced developers. These guys should always be prepared to fix the servers immediately after a deployment went rogue.

Conclusion

We’ve been using this workflow with many customers for many years to deploy their application. Some of these things were learned the hard way, through broken production servers. Following these guidelines and all production deployments will become incredibly smooth and won’t cause any stress at all.

Orginal Article

Visual Studio Team Services Security

Using Azure can open up a can of worm around security and many customers have many concerns

Microsoft do a lot of things to keep your Team Service project safe and secure, refer to this link for details: Visual Studio Team Services Data Protection Overview.

You can deploy your own build agent which you can have full control and easy to configure your machines to only accept the deployment from that build agent.

Another URL link I found from Microsoft Virtual Learning which might be useful:

Getting Started with Azure Security for the IT Professional

Do IT security concerns keep you up at night? You’re not alone! Many IT Pros want to extend their organization’s infrastructure but need reassurance about security. Whether you are researching a hybrid or a public cloud model with Microsoft Azure, the question remains the same: Does the solution meet your own personal and your organization’s bar for security, including industry standards, attestations, and ISO certifications? In this demo-filled course, explore these and other hot topics, as a team of security experts and Azure engineers takes you beyond the basic certifications and explores what’s possible inside Azure. See how to design and use various technologies to ensure that you have the security and architecture you need to successfully launch your projects in the cloud. Dive into datacenter operations, virtual machine (VM) configuration, network architecture, and storage infrastructure. Get the information and the confidence you need, from the pros who know, as they demystify security in the cloud.

This article is very useful is you need to deploy from a remote server

http://myalmblog.com/2014/04/configuring-on-premises-build-server-for-visual-studio-online/