Browsing:

Categorie: SQL

Create and Export a bacpac file to Azure Storage

Next in my Azure database series is the bacpac file, Azure works with bacpac files, we can up- and download to azure. We need a storagespace in order to up and download files.

We start creating storage space. This may come in handy when we need file tranfer, and again you can access the files from your local device.
1.storage

To acces your storage account, you need the access keys, which are generated when creating the storage account. On the footer of the page, where the red arrow is visible in the screenshot below, you can open up and acces the keys. 2.storage

This key you need for the net step, we will download a windows Azure storage explorer to be able to acces the files. Azure has a broad choise in downloads for this option. I downloaded the Azure storage explorer from codeplex storage explorer.

Now I will walk you through on how to create a bacpac file on your local database. Choose the Export Data-tier Applicationbacpac1

Now we have to options, you can create the bacpac file locally and upload it with the storage explorer or link it to azure directly, we choose tthe latter. Fill in the storage credentials we just created by connecting the storage account with the access keys. Name a container and filename and thunderbirds are GO!

bacpac2bacpac4

 

 


Connect your Azure database from your local SSMS #Error 40615

Ofcourse we wanna access our fresh imported database on Azure locally!

And this is so easy if you just follow these simple steps.

In order to be able to access the database located on your Azure cloud, you have to know the Azure address, which you can find on the Windows Azure Management Portal. Click the database icon and open up the database. Here you will find the connectionstring.

azure_link

Next step is to make sure Azure knows your device by adding it to the trusted devices in Azure, if your device is unknow to Azure it will bring you the following message:1error-whenlocal_connection

you can add the device by going to the database icon in Azure and click the CONFIGURE tab. Here you add and save your device IP.2local_adTosavelist

Now you can login to your Azure cloud with the credentials you made while creating the database on Azure.
3Local_db connect


SQL database migration to Microsoft Azure with codeplex

sql-database-windows-azureThere are multiple ways to migrate your database to the Azure cloud. Today, I am testing this sql azure plugin by codeplex. I unzipped it to my local folder and run it as admin.

1.InstallAzurePlugin

Now fire up the SQLAzureMW.exe and the script wizard starts up, which is very intuitive, choose the available options, In my case, I will migrate from SQL database to Azure SQL Database. Fill in your database details and run the export. This might take a while depending on how big your SQL Database is. You can also make a selection of tables/views you want to export, or just export the whole database.

Azure1

Azure2 Azure3   Azure4 Azure5 Azure6   Azure7

Make sure you fill in the correct details for your target server (you can find your sql connection data on your azure Database configure page.) Azure10  Azure11

Now the database will be brought to the Azure cloud, again this might need some time, but you can see the progress on screen.

Azure11 Azure12

 

I’m a big fan of this sql azure plugin by codeplex , Like i said it’s very intuitive and gives you several options to work with your local and azure data.


Move SQL Tempdb files – Common errors

Do you keep getting errors and timeouts when doing queries with sort?
My first guess wil be to check out the location of my tempdb files, is the disc space running out?db

Run query on the correct database
EXEC sp_helpfile GO
Tip: you can also view it under properties.

USE master; GO ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = ‘F:\MSSQL\DATA\tempdb.mdf’); GO

 

ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = ‘F:\MSSQL\DATA\templog.ldf’); GO

Restart sql services: ‘stop and start’

The file “tempdev” has been modified in the system catalog. The new path will be used the next time the database is started.
The file “templog” has been modified in the system catalog. The new path will be used the next time the database is started.

rerun the sp query to verify the correct location fort he tempdb files
Now delete the old files or in my case rename them before finally remove them.
Next we are gonna change the properties for the autogrowth.
Error cant acces properties of tempdb:

DBCC UPDATEUSAGE(tempdb)
temp_1

Important Note on common ERRORS: SQL Server doesn’t support moving TempDB Database using backup/restore and by using detach database methods.

Error Message Received when you try Backup and Restore Method

Msg 3147, Level 16, State 3, Line 1
Backup and restore operations are not allowed on database tempdb.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

Error Message Received when you try Detach Method

Msg 7940, Level 16, State 1, Line 1
System databases master, model, msdb, and tempdb cannot be detached.


Linked server setup – Back to basics (error 7399)

I recently received this question:

Why am I getting an error ‘Invalid authorization specification’. I used my SA account, but i keep getting the error that the credentials specified are incorrect.

There is a very simple answer to this question, which I will share with you.

The error you get when the credentials are invalid:LSSLinked server is a connection between two servers in your network. If you want to connect the servers, the specified credentials need to be known on both servers, So, unless you use the same password for sa acounts on all your network server, which is never recommended in the first place! I advise you to create a specific Linked server sql account, for example I created a LS_2014 account on both servers and specified on which databases the Linked server is allowed to read and select data, as you can see in the example below.

ls_account

ls_acc

This keeps your databases secure and easier to manage.

You could also set it to use your current logged in account, but you need enough permissions on all servers and depending on who is using the SQL server, the linked server might not be available.

If you use the account specifically created for the Linked server and test the linked server account, the connection will be successful and you will see the table you granted read permissions on is available in the linked server dropdown.