Category Archives: Azure SQL Server

Architecture, Architecture, Azure SQL Server, Design Patterns

Multi-Tenancy

Leave a comment

What Is Multi-Tenancy?

“Software Multitenancy refers to a software architecture in which a single instance of a software runs on a server and serves multiple tenants. A tenant is a group of users who share common access with specific privileges to the software instance. With a multitenant architecture, a software application is designed to provide every tenant a dedicated share of the instance including its data, configuration, user management, tenant individual functionality and non-functional properties. Multitenancy contrasts with multi-instance architectures, where separate software instances operate on behalf of different tenants” (Wikipedia)

Continue reading Multi-Tenancy →
Azure SQL Server, SQL

How to export all tables to csv by one export job

Leave a comment

If you just want to export all the tables in a SQL database in to separate CSV files, then this is a quick and easy way of doing it.

  1. Execute below query which generates BCP commands

    SELECT ‘bcp ‘ + st.NAME + ‘ out c:\Target\’ + st.NAME + ‘.csv -c -r -d ‘ + DB_NAME() + ‘ -U user@??????.database.windows.net -S tcp:?????.database.windows.net -P ?????? FROM sys.tables st
  2. Paste the result set into text file. Make batch file and schedule it. (It can also be run in CMD manually)

 

Azure, Azure SQL Server, Security

Allow access to Azure services, what does this actually mean?

1 Comment

I did some quick research on the SQL Server Firewall “Allow Access to Azure Services” option in Azure today.

And I sorry to say that my fears were right, that by setting this option does pose a significant security risk and leaves the SQL Server vulnerable.

Here is the extract of the article I found from Gaurav Hind at Microsoft

Access within Azure: This can be toggled by “Allow access to Azure services” Yes/No button on the portal (Firewall settings page). Please note, enabling this feature would allow any traffic from resources/services hosted in Azure (not just your Azure subscription) to access the database.

https://blogs.msdn.microsoft.com/azureedu/2016/04/11/what-should-i-know-when-setting-up-my-azure-sql-database-paas/

The big question now is how do you plug this gap in the firewall?  One possible solution is to build a virtual network within Azure or Filter network traffic with network security groups, this is beyond the scope of this article.