Archive

Archive for the ‘Windows & SQL Server AZURE’ Category

Creating Azure VM + SQL Server

March 16, 2015 Leave a comment

Login to the Azure portal and Navigate image  –> image –> image –>image

image

 

image

 

image

image

image

 

image

 

image

image

Working with Windows Azure Storage –RESTORE DATBASE FROM BACKUP FILE IN BLOB STORAGE- SQL Server 2014

March 12, 2015 Leave a comment

 

Restoring the Database backup from the Azure Storage blog is pretty simple.

 

Create the credentials

 

 

image

Background Process

 

image

 

RestoreDatabase: Database AdventureWorks2012_BlobRestore

Opening backup set

VDI: "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn\BackupToUrl.exe" "r" "p" "680074007400700073003A002F002F0064006200700072006F00780061007A00750072006500730074006F0072006100670065002E0062006C006F0062002E0063006F00720065002E00770069006E0064006F00770073002E006E00650074002F0063006F006E007400610069006E0065007200730074006F0072006100670065002F0041006400760065006E00740075007200650057006F0072006B007300320030003100320031005F00460055004C004C002E00620061006B00" "64006200700072006F00780061007A00750072006500730074006F007200610067006500" "01000000D08C9DDF0115D1118C7A00C04FC297EB010000008A1F704D82DFC340B81470F9C845106F000000001200000061007A007500720065006B00650079000000106600000001000020000000DA1203D7C8747E05D39E6EB11D2F604D64996C8967133EFA7FC6D34BDF075500000000000E8000000002000020000000A00306C9A38E13BDB7AEA12A3C24C0CF1B6F454E27E127C535ED723B865A685150000000E800B249F7C14428C0D69AEFD21441F08F1EBFEA1F97C7F5E2F446A3AF811974341AC77546D0968A12CBA267207E19F40FBEEF031372D56AEA7FBC143B3410900D9907FAD3F60FEC66A18F485AD5EB2F40000000E4FC1F2D05DB21D31CA753E8777D49D4F8F4032CD277BB71091B34D60582470E0565EE6397F2D4BAAE4C9297F257FC1B8CD9244F4775658BDE6CD1F39E5D6A9D" "NOFORMAT" "4D005300530051004C00530045005200560045005200" "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log" "DB" "41006400760065006E00740075007200650057006F0072006B00730032003000310032005F0042006C006F00620052006500730074006F0072006500" "NOTRACE"

Restore: Configuration section loaded

Restore: Backup set is open

Restore: Planning begins

Restore: Planning complete

Restore: BeginRestore (offline) on AdventureWorks2012_BlobRestore

Restore: Attached database AdventureWorks2012_BlobRestore as DBID=9

Restore: PreparingContainers

Restore: Containers are ready

Restore: Restoring backup set

Restore: Transferring data to AdventureWorks2012_BlobRestore

5 percent processed.
10 percent processed.
15 percent processed.
20 percent processed.
25 percent processed.
30 percent processed.
35 percent processed.
40 percent processed.
45 percent processed.
50 percent processed.
55 percent processed.
60 percent processed.
65 percent processed.
70 percent processed.
75 percent processed.
80 percent processed.
85 percent processed.
90 percent processed.
95 percent processed.
100 percent processed.
FileHandleCache: 0 files opened. CacheSize: 14

Restore: Data transfer complete on AdventureWorks2012_BlobRestore

Processed 24384 pages for database ‘AdventureWorks2012_BlobRestore’, file ‘AdventureWorks2012_Data’ on file 1.
Processed 3 pages for database ‘AdventureWorks2012_BlobRestore’, file ‘AdventureWorks2012_Log’ on file 1.
Restore: Backup set restored

Restore-Redo begins on database AdventureWorks2012_BlobRestore

Restore VLF headers for 5 headers on database AdventureWorks2012_BlobRestore

Restore VLF headers completed on database AdventureWorks2012_BlobRestore

RunOfflineRedo logIter.Init(): FirstLsn(PruId: 0): 0x54:0x38:0x4a

RunOfflineRedo logIter.Init(): LastLsn(PruId: 0): 0x54:0x68:0x1

OfflineRollforward: StopLsn/LastLsn(PruId: 0): 0x54:0x68:0x1

Rollforward complete on database AdventureWorks2012_BlobRestore

Restore: Done with fixups

Restore: Transitioning database to ONLINE

Restore: Restarting database for ONLINE

Restore: Database is restarted

Resuming any halted fulltext crawls

Restore: Writing history records

Writing backup history records

Restore: Done with MSDB maintenance

RESTORE DATABASE successfully processed 24387 pages in 87.274 seconds (2.183 MB/sec).
RestoreDatabase: Finished

How to: Install Azure PowerShell

March 8, 2015 1 comment

 

Download and install the Azure PowerShell modules by running the Microsoft Web Platform Installer. When prompted, click Run. The Web Platform Installer installs the Azure PowerShell modules and all dependencies. Follow the prompts to complete the installation

 

 

image

 

image

 

image

 

image

 

image

 

image

image

image

image

image

 

image

 

image

image

image

Creating SQL Database on Microsoft Azure

March 7, 2015 1 comment

 

A quick demo on how to create a SQL Database on Microsoft Azure.

 

Login to Azure Portal or Manage Azure Portal

From Azure Portal

image

 

From Manage Azure Portal

image

 

Create Database

image

 

image

 

image

 

Accessing the database. Click the database name it will take you to the database page.

image

 

Added the local IP address  image or IP  range. For IP Range,Click image .

Under Auditing and Security, select image  *Show security enabled Connection String.This will popup Security Enabled Connection strings.At the bottom of page you will see image .Click firewall rules. There you can define the IP Ranges.

 

image

 

image

Connecting Azure SQL Database from local SSMS.At database portal page, under Connect to your database.The database url will be displayed.

 

image

 

Connect using the url with username and password which you supplied at the time of database creation

 

image

MS SQL Server Database Backup to Azure Storage

March 6, 2015 Leave a comment

 

image

Requirement is to backup on-premises database on Windows Azure Storage. To perform this task you need the below

 

  1. Windows Azure subscription
  2. Storage Account (Login to Azure Portal –> +New –> Browse Data +  Storage –> Storage)
    image
  3. Click on Home image and browse the image .
  4. Copy the [STORAGE ACCOUNT NAME], [PRIMARY ACCESS KEY]

 

Open SSMS (SQL Server Management Studio)—> Click on connect—>Select Azure Storage

 

image

Provide the Storage Account  and Access Key (Requirement 4) then click connect

image

Once connected you will see the containers .

image

Under containers you need to create your own containers.

To create the storage container (login to the portal >>image  >>image >>image )

 

Create container window will appear. For security always choose Private.

image

After the container is created, refresh the SSMS object explorer and container should appear.

 

image

 

Now we have the [STORAGE ACCOUNT NAME],[PRIMARY ACCESS KEY] , [Containers] and [URL] .Lets configure couple of things at on-premises SQL Server Instance

The first thing you have to do here is create the SQL credential objects necessary to properly access the Azure blob container you
created before. A SQL Server credential is an object that is used to store authentication information required to connect to a resource outside of SQL Server. The credential stores the URI path of the storage container and the shared access signature key values. For each storage container used by a data or log file, you must create a SQL Server Credential whose name matches the container path. Please, read it again: the credential name string must be exactly the same as the Blob container path, otherwise SQL and Azure will not match the security information.

CREATE CREDENTIAL mycredential WITH IDENTITY = ‘STORAGE ACCOUNT NAME’
        ,SECRET = ‘PRIMARY ACCESS KEY’;

 

This credential will be used in the backup command to connect to the Azure Storage &  Container

DECLARE @storageAccount VARCHAR(255);
DECLARE
@container VARCHAR(50);
DECLARE
@dbname VARCHAR(50);
DECLARE
@credential VARCHAR(100);   
DECLARE @filename VARCHAR(255);

SET @storageAccount = ‘dbproxazurestorage’;
SET @container = ‘containerstorage’;
SET @dbname = ‘AdventureWorks2012’;

SET @credential = ‘mycredential’
SET @filename = ‘https://’ + @storageAccount + ‘.blob.core.windows.net/’ + @container + ‘/’ + @dbname + ‘_3_FULL.bak’;

BACKUP DATABASE @dbname
    TO URL = @filename
    WITH CREDENTIAL = @credential, NO_COMPRESSION, STATS = 5;

 

image

 

image

SQL Database Service on Azure (Replication and Data Sync)

March 5, 2015 Leave a comment

This article focuses on replicating on-premises SQL Server Database data on Microsoft SQL Azure Database. We cannot use traditional  (Replication Topologies)way of replicating data to Azure SQL Server Database.

 

image

 

To set and configure we have to do the following.

 

  • Log on to Azure Portal or Manage Windows Azure
  • Browse to SQL Database from the panel image  (Create Azure Database)
  • On the right side of the panel your database will appear.Select the database by clicking the database record after the column [NAME].
  • At the bottom of the page click image  and select image
  • Register the new Sync Agent and install the [local client sync agent tool] from the link image
  • At the bottom of the page click image
  • Manage access key PREVIEW page will popup.Click generate to generate the sync agent key. This  key will be used to connect [Microsoft SQL Data Sync Agent Preview] client installed on step 5.

    image

  • Copy the key and pass it to the [Microsoft SQL Data Sync Agent Preview] client by clicking image
    Pass the copied key in Agent Key box and click ok.

    image

  • Register the On-premises database whose data has to be replicated by clicking image  on [Microsoft SQL Data Sync Agent Preview]

    image

    image 

    image 

  • You will be able to see the registered database under the sync Agent on the Azure Portal

    image 

  • Create the tables on the Azure SQL Databases whose data you want to replicate using Data Sync. Remember table name should be same and ever table should have a primary key.Azure portal will not show the table if it does not have primary key.

    Microsoft provide multiple method to transfer the database or tables to Azure .The one i used in this example is by using Migration Database to Azure Tool

  • You can see once the tables where created on Azure SQL Database
    image
  • Create Sync Group as we created Sync Agent.

    image 

    image image   image 

  • Sync Group  has been configured to sync the data from On-Premises database to Azure SQL Database

    image

    image 

  • Configure the Data Sync Rules
    Click on the Sync Group Name –> Select Sync Rules image –> image 

    DEFINE SYNC RULE will popup the select database window to select the database on which rule need to be defined. In this case i select HUB database to define the rules.

    image

  • Once you click the ok , you will be able to see all the tables in you Azure database which has primary key(Table with no primary key will not be display)

    image

    You can select the table and columns  or you can choose from options and select image
    image

    image 

  • Once the rule is provisioned and tables are in sync you can see the status of the Sync Group as below

    image

    image

 

Once Synchronization is  complete, Compare the azure SQL Database table and on-premises database table.You can also click on the LOGS on the Sync Group page

 

Message : Sync completed successfully in 35.61 seconds.     Upload:   17 changes applied    Download: 0 changes applied    For more information, provide tracing ID ‘f6876d05-63e9-4bef-b496-3ee05eb85b16’ to customer support.

 

image

SQL Database Service on Azure

March 3, 2015 Leave a comment

Login to the Azure portal

 

image

 

Click on +New

 

image

 

image

 

Custom Create SQL Database

image

 

image

 

image

 

image

 

 

image

 

 

image

 

image

 

image

 

 

image

 

Accessing SQL Azure from SSMS

 

image