[Developer's community]

SQL Server Database Export/Import operation in MS Azure

 

10 August 2017
The beauty of Azure is that it has multiple ways to do the same thing. Imagine the scenario;  y

The beauty of Azure is that it has multiple ways to do the same thing. Imagine the scenario;  you need to copy the database between subscriptions. Considering SQL Server Import/Export procedure, you can do that either from the UI, or use command line tools (like SQLPackage).

The way it works with Azure UI, you go to the SQL Server database you want to export, and you have an option available (circled in red below). It will export the database to the selected Blob storage (you need to have storage account created prior this operation in the same subscription).

In the case of the same subscription but different SQL Server instances (or if your Azure subscriptions belong to the same tenant), you can Import this database (the actual *.bacpac file) using the same storage account, as it will be visible for all of them. If these are two completely different subscriptions, but you still need to Export/Import this database, you may need to create a storage account in the second subscription and import it from there. For the purpose, go to the SQL Server instance, and you will see the “Import database” option as highlighted below:

To import database, you need to select the file (*.bacpac) from the storage account and pricing tier (along with collation, admin name, and password).

TIP: When you download the database backup file (*.bacpac) from the storage account, it will appear as a zipped archive. No worries about that, as the bacpac file is a zip archive in reality.

There is nothing wrong with this approach, but in this case, the import procedure takes quite long time (as for me) for small databases and much longer for larger ones. The import process is hidden from the user, and the only message you will receive is that “Request is submitted to import database”:

You can control it to some extent by clicking “Operations” tile on the same page below (that is called “Import/Export history”) but it doesn’t update in real time. The error messages (if you would have any) are not that informative as well:

If you need to perform the same task quicker, automate it in the script (using PS or Azure CLI), or add more visibility into the process, the right way to go is to use SQLPackage command-line utility.

As stated in the documentation, to Export/Import using SQLPackage utility, you can use the relevant parameters and properties. The utility should be installed on your machine if you used SQL Server Management Studio (the latest one) or Visual Studio Tools for SQL Server. You can also install it directly from the MS website here.

The command to Export database I have used is quite simple:

Code:

SqlPackage.exe /a:Export /tf:<filename>.bacpac /scs:"Data Source=<server name>.database.windows.net;Initial Catalog=<database name to export>; User id=<user id>; Password=<password>"

TIP: You can find the SQLPackage util using the following path: “C:\Program Files (x86)\Microsoft SQL Server\<version>\DAC\bin”. If you will be using the util from there, the exported file will be dropped into the same directory. The better way is to add SQLPackage.exe to Path in the Environment variables, so you can call anywhere using the command line.

Import operation is as simple as export. Please find the code below:

Code:

SqlPackage.exe /a:import /tcs:"Data Source=<destination server name>.database.windows.net;Initial Catalog=<db name>;User Id=<server admin>;Password=<admin password>" /sf:<file name>.bacpac /p:DatabaseEdition=Standard /p:DatabaseServiceObjective=S1

It is worth mentioning that the *.bacpac will be picked from the same directory where SQLPackage.exe lies (unless you used the tip above). “User Id” and “Password” should be SQL Server admin’s credentials in Azure. Database edition corresponds to SQL Server editions (the full list of possible values: Basic|Standard|Premium|Default), the same parameters can be found in the documentation mentioned above. Database Service Objective corresponds to SQL Server pricing tiers. Possible values are: Basic, S0-S3 (Standard tier), P1/P2/P4/P6/P11/P15 (that belong to Premium tier), PRS1-2/4/6 (IO and compute-intensive instances, still in preview).

Like it was mentioned before, this way is quicker and gives you more control and visibility over the process. The error messages are more informative in this case as well:

So, using these two simple commands, you can Export/Import database for the same or different Azure subscription without the overhead and automate the process if necessary, or use SQLPackage as part of your script for some other tasks.

 

What I learned at Microsoft Build 2017 - Part 1

 

17 May 2017
asdasd

What I learned at Microsoft Build 2017 – Part 1

In this topic, I want to share what I learned at MS Build and explain the key takeaways from this event. I won’t be focusing on the keynotes much, as you can watch the relevant videos on channel 9, but the key message, as Microsoft’s VP Scott Guthrie said – “The success of your solution on the Azure platform is our primary goal!”. That means that Microsoft keeps concentrating its efforts around Azure services… but hold on, there is much more than that!

It is rare when I so excited about the conference. I was surprised and even baffled by some new releases, and news Microsoft has thoroughly prepared to present at this conference. Let’s do everything one by one, so I can make sure I didn’t forget anything important. So, first and foremost - MS finally released long-awaited Visual Studio for Mac (release date is 8th of May 2017). Finally, the developers received a familiar instrument for solutions development on Mac, using NET Core and Xamarin (or Unity), but the tooling, comparing to the same IDE for Windows still requires some work. Especially disappointing is the absence of Docker support.

Continuing topic on development instruments, Microsoft announces a new Cloud Shell in Azure (command line) that works with your Azure account right from the browser. Every session is in sync with a $Home directory that is stored in Azure, which, in turn, gives a possibility to access files, VM’s and other artifacts you deal with via UI. PowerShell is not yet supported and coming soon, although, you can sign up for a private preview here. The App for iOS is also available in the App Store. I gave it five stars, as it pretty neat and quick (command line is not yet available though), but it was demonstrated at the conference by Corey Sanders (see “Azure Compute – new features and roadmap” on Channel 9).

Infrastructure

According to Scott Hanselmann, MS has announced 38 regions/data centers around the globe (and nine countries where disaster recovery is possible because of multiple regions: USA, Canada, UK, France, Germany, India, China, Japan, Australia). Cross-Region Disaster Recovery (Automatic DR to another area, i.e. Azure to Azure), so, we can finally take advantage of multiple regions within the country. This feature is coming soon.

So, to summarize, we going to have In-Country DR (9 countries as mentioned above), Multi-Instance SLA (99.95%), Single-Instance SLA (99.99%). In comparison to other clouds, Microsoft is far ahead.

Instance metadata service is another new feature on the Azure Platform that exposes an endpoint, so you can manage upcoming maintenance events, bootstrap VM with identity and provide VM context in a fully programmatic way. It also provides the status of all the instances running.

Containers

There were lots of topics at the conference pertaining to containers overall and their interaction with OS components and Azure services in particular. Microsoft Azure Container Service provides Docker tooling and API support along with provisioning of Kubernetes, DC/OS, and Docker Swarm. I have inserted the links for those who don’t know what this is all about. In short, these are all the container orchestration, deployment automation, and scale tools, based on Mesos DC/OS, Kubernetes or Docker Swarm that provide the best Docker experience. Apart from this, Microsoft on-boards Deis team for assistance on Kubernetes (and Helm tool in particular, that simplifies pre-configured Kubernetes resources management). See the demo by Corey Sanders (I have provided the most interesting links below).

Service Fabric

Another interesting topic is Azure Service Fabric. There is nothing new in Azure-based Microservices, but the fact you can now deploy them in containers (surprise-surprise ). Azure Service Fabric now represents stateful and stateless services (.NET and Java APIs on Windows Server and Linux) that can be deployed in Azure, Azure Stack, On-premises, in OpenStack and AWS even. The application (Music Store) shown by Corey on this presentation can be found on GitHub, along with explanations on how to run it in Docker for Windows. 

Azure Batch

This service has been around for a while. It offers Job Scheduling as a service at the first place, and the other platform offerings have become very easy having Azure Batch service. It allows you to focus on the major question, i.e. what infrastructure you want, when/where and pricing. Microsoft has announced a low priority batch VMs where you pay 80% less and, sure thing, it is deeply integrated with the job scheduling (right now is available in preview mode). Another aspect of a batch is being able to take advantage of rendering capabilities (using compute instances to render images/videos/3D models). Senior Product Manager from Autodesk has shared his experience in this regard.

Serverless

Function side of the house J For those who don’t know what this is about, it is an Event-based way of programming that doesn’t require infrastructure (and has a billing model by usage/demand/call/execution, etc.) Microsoft’s offering here is Azure Functions (I highly recommend to watch a recorded presentation by Eduardo Laureano, PM on Azure Functions and will talk about all this stuff a bit later). Microsoft has announced the integration with Visual Studio on that day (on 8th of May a lot of stuff were announced), so, it simplifies editing and debugging of the functions. A new feature that pertains to this set of technologies is Azure Functions Runtime. What’s cool about it, is that it allows to install runtime and use Functions in a container outside Azure environment. You can download a preview version of it from here. It can run anywhere where containers work or on top of the Azure Stack (which is in turn, the Azure functionality outside Azure environment).

Azure Managed Applications and Service Catalog

Package and seal the Azure functionality for the other folks to use. It means that the application which is being developed can be packaged and deployed by someone else, using ARM experience, or, sold/exposed to a third-party, etc. What is interesting about it – the catalog uses an enhanced security model, so the app in the catalog can be exposed or pre-deployed to a certain personality/account (after approval or without it) and has a configurable amount of parameters that are required for successful deployment. Right now it’s in preview mode (just like many other things in this topic), and link to documentation leads to a general Azure website (I was unable to try it myself due to the missing documentation).

Compute

Microsoft has expanded the set of compute instance sizes (by adding six new ones):

  • F – Compute intensive
  • NC – NVIDIA GPUs K80 compute
  • NV – NVIDIA GPUs M60 Visualization
  • H – Fastest CPU IB Connectivity (very high throughput between instances)
  • L – Large SSDs
  • SAP – SAP Large instances

Four more were announced during the conference:

  • ND (P40) – NVIDIA Gear towards deep learning, scale-out computation
  • NCv2 (P100s) – computational SKU (scale-out compute with InfiniBand connectivity)
  • Dv3 (SSD storage, fast CPU) and Ev3 (high memory) – next generation of existing SKUs with nested virtualization (meaning that they’ll be based on Windows 2016 and support VMs inside VMs deployed into the cloud)

Azure Logic Apps

Azure Logic App is not a new service, but obviously known not well enough. It takes advantage of existing BizTalk servers (connects to logic apps through an adapter) to connect to SaaS and invoke logic apps. It makes it easier to connect to trading partners using EDI standards and B2B capabilities.

Microsoft PowerApps

This service is around for a while, and I know people who heavily use it in development.  There is nothing new in the way it works (aside from the grooving number of different adaptors, which is 140 now) but it allows to build an app very quickly and doesn’t require much of mobile development experience, which was perfectly demonstrated by the guys (Jeff Hollan, Eduardo Laureano) on the very last day of the conference. I think you will enjoy the presentation as much as I did. Please watch “How to build serverless business applications with Azure Functions and Logic Apps for PowerApps” to get more info on the topic (the link to Channel 9 video below).

There is still a lot to tell about, so, I had to split my topic into two parts. I will publish the second one shortly. Stay tuned :)

 

 Mentioned Videos on Channel 9 

Azure Compute – new features and roadmap (Corey Sanders)

How to build serverless business applications with Azure Functions and Logic Apps for PowerApps (Jeff Hollan, Eduardo Laureano)

Оператор JOIN для чайников

 

21 January 2011
Данная статья будет полезна новичкам и поможет в освоении оператора JOIN и&amp;nbsp; в этом примере он б

Данная статья будет полезна новичкам и поможет в освоении оператора JOIN и  в этом примере он будет рассмотрен в контексте языка T-SQL. Для визуализации работы запросов были также использованы диаграммы Венна, которые, как я надеюсь помогут вникнуть в смысл JOIN-ов. Для начала работы над примерами - предположим, что у нас есть 2 таблицы ('Таблица_1' слева и 'Таблица_2' справа), давайте заполним их тестовыми данными:

 

Таблица_1 Таблица_2
id name id name
-- ---- -- ----
1 Машина 1 Паром
2 Грузовик 2 Машина
3 Самолет 3 Велосипед
4 Поезд 4 Самолет

 

Теперь, когда мы разобрались с условностями - можно переходить к рассмотрению примеров:

1. INNER JOIN выводит только те записи, которые совпадают в обеих таблицах

SELECT * FROM Table_1
INNER JOIN Table_2
ON Table_1.name = Table_2.name

Результат работы запроса:

 

id   name id name
--   ---- -- ----
1   Машина 2 Машина
3   Самолет 4 Самолет

 

 

 2. FULL OUTER JOIN выводит набор записей, которые совпадают в обеих таблицах (с двух сторон), там, где нет совпадения вставляется значение NULL (сравнение записей ведется с Таблицей_1, т.е. той, что с левой стороны).

SELECT * FROM Table_1
FULL OUTER JOIN Table_2
ON Table_1.name = Table_2.name

Результат работы запроса:

 

id   name id name
--   ---- -- ----
1   Машина 2 Машина
2   Грузовик NULL NULL
3   Самолет 4 Самолет
4   Поезд NULL NULL
NULL   NULL 1 Паром
NULL   NULL 3 Велосипед
 

 3. LEFT OUTER JOIN выводит полный набор записей из первой таблицы (в нашем случае Таблица_1), и совпадающие записи (где это возможно) со второй таблицы (Таблица_2). Если совпадений нет - в поле вставляется значение NULL.

SELECT * FROM Table_1
LEFT OUTER JOIN Table_2
ON Table_1.name = Table_2.name
id   name id name
--   ---- -- ----
1   Машина 2 Машина
2   Грузовик NULL NULL
3   Самолет 4 Самолет
4   Поезд NULL NULL
 

 4. RIGHT OUTER JOIN выводит полный набор записей из второй таблицы (в нашем случае Таблица_1), и совпадающие записи (где это возможно) из первой таблицы (Таблица_1). Если совпадений нет - в поле вставляется значение NULL. Как мы видим этот оператор похож на предыдущий, только в данном случае "ведущей" будет вторая таблица (с правой стороны).

SELECT * FROM Table_1
RIGHT OUTER JOIN Table_2
ON Table_1.name = Table_2.name
id   name id name
--   ---- -- ----
NULL   NULL 1 Паром
1   Машина 2 Машина
NULL   NULL 3 Велосипед
3   Самолет 4 Самолет
 

 5. Извлечение уникальных записей из таблицы посредством оператора WHERE. В данном примере мы выведем только те записи из Таблицы_1, которых нет в Таблице_2.

SELECT * FROM Table_1
LEFT OUTER JOIN Table_2
ON Table_1.name = Table_2.name
WHERE Table_2.id IS null
id   name id name
--   ---- -- ----
2   Грузовик NULL NULL
4   Поезд NULL NULL
 

 6. Извлечение уникальных записей из обеих таблиц посредством оператора WHERE. В данном примере мы выведем уникальные записи из Таблицы_1 и Таблицы_2.

SELECT * FROM Table_1
FULL OUTER JOIN Table_2
ON Table_1.name = Table_2.name
WHERE Table_1.id IS null 
OR Table_2.id IS null
id   name id name
--   ---- -- ----
2   Грузовик NULL NULL
4   Поезд NULL NULL
NULL   NULL 1 Паром
NULL   NULL 3 Велосипед
 

 7. CROSS JOIN. Для полноты изложения материала следует упомянуть еще об одном операторе - CROSS JOIN. Этот оператор используется довольно редко и для визуального представления нет подходящей диаграммы Венна. С помощью CROSS JOIN-а мы можем сделать перекрестную выборку всех записей из обеих таблиц (Таблицы_1 и Таблицы_2) и в нашем случае мы получим 4х4=16 строк данных. Возьмите на заметку, что лучше не применять этот опреатор для больших таблиц, т.к. это может серьезно повлиять на производительность СУБД.

SELECT * FROM Table_1
CROSS JOIN Table_2
   

Полезные скрипты (T-SQL), полное удаление данных из таблиц БД

 

20 January 2011

Иногда, во время тестирования, бывает нужно удалить все данные из какой-нибудь конкретной БД (сценариев использования на самом деле больше). Вручную это делать неинтересно и временами нереально (при больших объемах данных), к тому-же,  было-бы неплохо обнулить нумерацию в таблицах. Для это вводим следующий скрипт:

 

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'  
GO  

EXEC sp_MSForEachTable '  
  IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1  
  DELETE FROM ?  
  else   
  TRUNCATE TABLE ?  
'  
GO  
  
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'  
GO  

EXEC sp_MSForEachTable '   
IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1   
DBCC CHECKIDENT (''?'', RESEED, 0)   
'   
GO

 

Первая часть скрипта отключает контроль ссылочной целостности, затем удаляет все данные из всех таблиц (строки 1-10). Вторая чать скрипта включает контроль ссылочной целостности (строки 11-12). И последний кусок кода кстанавливает начальные значения счетчиков в 0 (оператор RESEED).