SQL

Switch Open Data .ch and .li Domains

Hi All,

Switch the Swiss Registrar for .ch and .li Domains has published the Domains on a Open Data Policy.

You can download the top 1'000 Domains or use a Zone Transfer

You need to create the TSIG Key

# filename ch_zonedata.key
key tsig-zonedata-ch-public-21-01 {
	algorithm hmac-sha512;
	secret "stZwEGApYumtXkh73qMLPqfbIDozWKZLkqRvcjKSpRnsor6A6MxixRL6C2HeSVBQNfMW4wer+qjS0ZSfiWiJ3Q=="; 
};

then use dig to initiate the Zone Transfer

dig -k ch_zonedata.key @zonedata.switch.ch +noall +answer +noidnout +onesoa AXFR ch. > ch.txt

Looks like this

Protect your Azure SQL Database with Firewall Rules

Hi All,

In this Blog Article, i want to talk about how to protect your Azure SQL Databases with Firewall Rules at Server or Database level.

Azure SQL Database and Azure Synapse IP firewall rules


The Server Level you can find on your Server Object


You can also find this when query the master Database

--Database: master
SELECT * FROM sys.firewall_rules


At the Database level use this

sp_set_firewall_rule (Azure SQL Database)

--Database: db_home_icewolf
Select * FROM sys.database_firewall_rules


To allow Azure Services add the following

-- Enable Azure connections.  
EXECUTE sp_set_database_firewall_rule N'Allow Azure', '0.0.0.0', '0.0.0.0';


to add a custom IP or IP Range use these

-- Create database-level firewall setting for only IP 0.0.0.4  
EXECUTE sp_set_database_firewall_rule N'Example DB Setting IP', '95.143.60.18', '95.143.60.18';  
 
-- Update database-level firewall setting to create a range of allowed IP addresses
EXECUTE sp_set_database_firewall_rule N'Example DB Setting IP Range', '95.143.60.17', '95.143.60.22';



Regards
Andres Bohren


Export Azure SQL database and import on local SQL Server

Hi All,

In this Blog Article i show how you can Export an Azure SQL Database and import it back on your local SQL Server

In the Azure Portal go to your SQL database and hit "Export"


You need to set the Storage Target for your Export


I create a new container at my Storage Account



Once the export starts it takes a while until the Data is exportet


Wait until its completed


Then check your Azure Storage


Now you are able to download the *.bacpac File


If you add *.zip do the *.bacpac File you can see that it is actually a ZIP File that contains the Database Schema and the Data


For each Table is a Folder


In the Folder the Data is stored in a *.bcp file


Import the Database into your SQL Server. Select Databases and from the Context Menu choose "Import Data-tier Application".




Maybe you need to adjust the Database Name






And here it is fully restored on your local SQL Server



Regards
Andres Bohren


Install SQL Server Management Studio (SSMS)

Hi All,

In my previous Article i have explained how to install SQL Server 2019 Express Edition. That comes without a Management Interface - therefore we need Install SQL Server Management Studio (SSMS)

Download SQL Server Management Studio (SSMS)





Now it can be found in the Start Menü


You will be welcomed with the Splash Screen


Then you have to Select your SQL DB Instance


Or you can Browse for DB's on your Server or Network


Now you can do SQL Querys against the Database



Regards
Andres Bohren


SQL Server 2019 Express Installation

Hi All,

For a Project, i have installed SQL Server 2019 Express. Here are the Screenshot from the Installation.

Editions and supported features of SQL Server 2019 (15.x)





I choose "custom" to see all the Settings

















Here is where you would normally choose to place the Data on a diffrent Drive. As this is only a Test System i won't do that.











Let's see if we can connect

sqlcmd /?
sqlcmd -S ICESRV04\SQLEXPRESS -E -Q "Select @@Version"



Regards
Andres Bohren


SQL Server 2012 SP4 and Security Update

Hallo zusammen,

Auf einem Server habe ich heute noch den SQL Server 2012 mit einem aktuellen Service Pack und dem Security Update ausgerüstet.

Microsoft SQL Server 2012 Service Pack 4 (SP4)
https://www.microsoft.com/en-US/download/details.aspx?id=56040

Falls ein paar Prozesse noch Dateien in Benutzung haben, kann man die Prozesse mit PowerShell stoppen

Get-Process -ProcessName sqlcmd
Get-Process -ProcessName sqlcmd | Stop-Process -Force
Get-Process -ProcessName monAgentCore| Stop-Process -Force
Get-Process -ProcessName wmiPrvSE | Stop-Process -Force

Power BI Desktop

Hallo zusammen,

Ich habe kürzlich bei einem Projekt PowerBI verwendet um die Daten zu visualisieren. Power BI Desktop ist kostenfrei und kann über den Microsoft Store heruntergeladen werden.

Power BI Desktop

https://powerbi.microsoft.com/de-de/desktop/

Der Klick auf den Download öffnet den Microsoft Store.

Ich mache eine Verbindung zu meiner Azure SQL Database auf

Migrate SQL Database to Azure and change the connectionstring in web.conf

Hallo zusammen,

In den letzten Blog Artikeln habe ich eine SQL Datenbank in Azure angelegt und dann das SQL Server Management Studio installiert.

In diesem Artikel geht es darum, die Datenbank nach Azure zu migrieren.

Ich habe den gestrigen Abend damit verbracht die Tabellen als CSV Dateien zu speichern und ein Import PowerShell Script zu schreiben. Dabei geht das ganz einfach mit dem SQL Server Management Studio...

Install SQL Server Management Studio (SSMS) and connect to Azure SQL Database

Hallo zusammen,

Ich habe mir das SQL Server Management Studio installiert, um auf die Azure Datenbank zuzugreifen, welche ich im vorherigen Blog Artikel angelegt habe.

Download SQL Server Management Studio (SSMS)

https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15

 

Create SQL Database in Azure

http://blog.icewolf.ch/archive/2020/12/06/create-sql-database-in-azure.aspx