Browsing:

Author: Melanie koorevaar

How to use Fusion Tables in your Android app

This tutorial explains how to make a database available in an Android app with google Fusion Tables.

Fusion tables is an experimental data visualization web application to gather, visualize, and share data tables.

Create a new project on the google developers console. Now search for “Fusion Tables” in the list of API’s, click on it and enable the tables.

 

 

It ask for credentials, the next step is to create a service account associated with the project.

This service account is for troubleshooting and editing data by multiple users.

 

 

 

 

 

 

 

 

 

 

 

Now the service account is set up, you can resolve a key you need in order to use the tables in your project, in my case i’m using App Inventor. From the API Manager Credentials page select the service account and click ‘Create Credentials’.

Now we need to connect the fusion tables to our project.

Look up the Fushi0n tables in the API.

 

 

 

 

Now you can either create a fusion table, use available public data or import a data file and the fusion table is ready to be used.

My next step is to integrate the fusion tables into an android app.  As i said before I’m using app inventor for this project, since it’s very user friendly.

 

 

 

 

With the inventor blocks I retrieve the fusion data into my app with an initialize and get statement.


SQL2016 Stretch database. Yes, it’s another linked server

Your manager always wants to keep all the data…now with bigdata being a thing and algoritmes are used more efficient (IOT), people want to hoard data even more, but it needs to be online to be valuable. If you have to restore the data first, your data is not very valuable. But all this extra data also slows down your database performance…and gives the dba’ers extra challenges….but not anymore.! Well, at least when your boss got a big wallet and don’t mind spending it on a SQL buget.

In SQL2016 Microsoft introduced the stretch database. which give you live access to ‘archived’ data and makes it feel like it’s on premise. And  it’s a secretly just a linked server! A linked server between mssql and Azure.LS+azure

How does this work, and is it safe?

Yes! since it works on secure linked server technology and it stores an encryption key on the on-premises sqlserver, it’s suitable for all your cold data, not hot, because it does slow down the query speed, but it will not clogg your network and your data will be available at all times, even when the data is being stretched over to Azure the data still is fully query-able.

First ask yourself, Do you really need to keep this data? If yes, How do you create a stretchdatabase? Well, this is easy!

Hesitations? use this link for info on the MS Stretch database advisor.

I use a simple test database, the adventureworks2016 ctp3.

Before we can start, check if the data archive function is enabled, if not (0) enable it.

####Check status of Remote data archive####
SELECT @@VERSION AS 'SQL Server Version'; 
EXEC sp_configure 'remote data archive'; 
GO 
####Activate Remote data archive####
EXEC sp_configure 'remote data archive' , '1'; 
GO 
RECONFIGURE; 
GO

Next we can create a master key to encrypt all your data locally, this way, the data can’t be read in Azure.
Note: You can also create a masterkey from the stretchdb wizard, it’s up to you. I’ll show both options.

USE AdventureWorks2016CTP3; 
GO 

CREATE MASTER KEY ENCRYPTION BY PASSWORD='stretchdb2016'; 
GO

Next I will create a simple table  and populate it with some data, to keep the demo quick and simple :

CREATE TABLE dbo.Stretchtable
(
FirstName VARCHAR (50),
LastName VARCHAR (50)
);
GO


Use [AdventureWorks2016CTP3]
GO
INSERT INTO dbo.Stretchtable (FirstName, lastName)
VALUES ('Reed', 'Richards'), ('Benjamin', 'Grimm'), ('Sue', 'Storm'), 
 ('Johnny', 'Storm'), ('Victor', 'Von Doom'), ('Willie','Lumpkin');
 GO

Keep your Azure account ready! I use the SQL logical server resource, you need to create a resource before you can stretch the database.

Azure resource

 

Select the table you will be stretching to Azure. Now enable the table for stretching and the wizard will open up.

Enable stretch database

It’s a Hybride archive – You can stretch the whole table or filter out the (c)old data from a massive table.
4_EnableStretchTable

 

Next thing you create a master key, if you didn’t make it before, if you made it already, It will ask you to fill in the master password.Masterkey_StretchTable
10_LoginStretchTable

Fill in the IP range for a firewall rule.IP_Azure

12_EnableStretchTable

Hey Ho, let’s go, that was easy!

Now the table will start to stretch, if you want to see if it stretched yet, try a select query and check the executionplan.
14_EnableStretchTable

Want your data back? No problem, You can bring back the data to your on-premise server, if you don’t want to use Azure. Because, when you delete your azure account, the data will be gone too!

bringbackmydatabringbackmydata2bringbackmydata3

 

Note: Data storage can be expensive on Azure, but this function gives us a positive view on the ever growing databases and it’s possibilities.


Meet the Buttonfactory – September 15

  • tbf_powershellkopieThursday, September 15, 2016

    to

    Beercoo

    Weg en Bos 51, 2661 DL Bergschenhoek

    You are invited to join us at our first meetup!
    There will be 2 presentations. One is about Powershell and the other about SQL Server 2016.

    Planning:

    19:00 A drink and some food

    19:30 Powershell Objects by Jacqueline
    Powershell is an object oriented scripting language, but what does that even mean? In this presentation you’ll get to learn all about objects and how you can supercharge your scripts leveraging them. No Powershell skills required!

    20:15 What is new in SQL Server 2016? By Melanie
    SQL Server 2016 is the biggest leap forward in Microsoft data platform history!
    OK, I copied that line from the Microsoft site. But the coolest new feature is the stretched database. We are going to tell you all about it.

    21:00 Another drink.

    The meetup is free of charge and will be dutch spoken.
    No prior knowledge is required!

    Dutch PowerShell Meetup

    Rotterdam, NL
    26 Powershellerines

    Know Ada Lovelace? Grace Hopper? This group firmly believes that programming, scripting and system administration is for women! But where are you? Let’s get together and learn…

    Check out this Meetup Group →


Linked server #Back to Basics

Really back to basic, but here is another example how to move data from one server to the other in a scheduled job.

First make sure, when you use an sql account to connect both servers, the account exist on both ends of the line.

Now we create a linked server, after all my former tutorials, I don’t have to go into detail, either script it or use SSMS gui, un this case I’m using it for a MSSQL to MSSQL this is easy peasy.

LS_make

Create a database and table if it doesn’t exsist on the server you want to insert the data in.

If all is setup, write a simple INSERT INTO script:

INSERT INTO [FunFactory_II].[dbo].[Accounts_NAWT]
(AccountNumber]
,[Name]
,[Street]
,[PostalCode])
SELECT
[AccountNumber]
,[Name]
,[Street]
,[PostalCode]
FROM [FunFactory_I].[dbo].[BSN].[Accounts]

And the last step is to create a job, to start the import automatically and schedule it to run on a given time.

This is my really #back to basic linked server tutorial, but you can add and tweak functions into the script and built fully automated solutions for your data.

For example a scheduled sql job to insert data from the linked server.
insertIntoFF


Linked server to Microsoft Azure #Error 405150

When Azure was launched, we missed one important feature, the linked server, It seemed impossible to do cross database queries from your local database to the Azure database, but Microsoft listened and made it possible to do database queries that spans multiple databases across platforms.

There is plenty of hits when googled on the errors, but none of these are complete! I will write down the  process of linking your server to Azure.

when I first created a linked server to Azure, I got this error  “reference to database and/or server name is not supported in this version error: 405150”, When trying to add a linked server from SSMS, you are not given option to set a default database. So I used Tsql instead, because without a specific catalog, your linked server will not work.LS_Azure

Azure doesn’t allow you to alter the master db, so you have to connect directly on the database you are going to use.

What people never tell LS newbies and make you google this error “Msg 2812, Level 16, State 62, Line 1 Could not find stored procedure ‘sp_addlinkedserver’ ” and read tons of blogs and tutorials all providing different queries, but still no work, follow these steps”.

The Tutorial, the Solution:

Open up ODBC, or simply run odbcad32.exe. and start by making a new system DNS with the Native client.

ls_SYSDNS

In the next step, you provide your datasource name and the complete server name as you can find on the Azure server.

LS_makedns

Also provide the database you want to connect to, don’t skip this one!

LS_Choosedb

 

Test the connection, see it succeed! if not, go through the above printscreens you probably missed a step.

LS_Testsuccesf

Now we can create the linked server on your database, by creating a query on your SSMS and can run your linked server query, it should look similar to this:

EXEC master.dbo.sp_addlinkedserver @server = N’Azure_server’, @srvproduct=N”, @provider=N’sqlncli’, @datasrc=N’t20ko02v18.database.windows.net’, @catalog=N’Teletraan IV’ EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N’Azure_server’, @useself=N’False’,@locallogin=NULL,@rmtuser=N’skyscream’,@rmtpassword=’password’

To test if the LS is added, you could run exec sp_helpserveror just test the connection.LS_SPhelpserver

These are all the steps to a Linked Azure server. Congratulations, you are now connected to the Azure mothership of data.

Data manipulation

Next step is to insert data from the Azure linked server into our local database. Btw, this query works both ways, you could also insert data into the Azure database.

Create an INSERT statement

BEGIN TRAN INSERT INTO [Azure_demo].[dbo].[Demo] (Initials,Prefix,LastName,Gender,Street,HouseNumber ,Zipcode,City,PhoneNumber,MobileNumber,EmailAddress) SELECT Initials,Prefix,LastName,Gender,Street,HouseNumber ,Zipcode,City,PhoneNumber,MobileNumber,EmailAddress FROM [Azure_server].[Teletraan IV].[dbo].[AzureData] WHERE InsertDate BETWEEN ‘2008-08-22 00:00:00.000’ AND ‘2009-02-06 00:00:00.000’ ROLLBACK TRAN –COMMIT TRAN

Ls_Qresult

Now all rows have been added through our linked server into our local database. LS_Check
If this blog saved your ass or just made your day, please let me know 🙂