SQL Azure vs SQL Server 2016

here is a list of some of the differences between SQL Azure and SQL Server 2016

No

Difference by

SQL Azure

SQL server 2016

1

Backups

In SQL Azure backups are automatically scheduled. It does not support conventional backups and restore statements.

SQL server has support for conventional backups and restore statements.

2

Recovery Models

Recovery Models are not available in SLQ Azure However Azure SQL Database offers the following capabilities for recovering from an outage

· Active geo-replication

· Geo-restore

SQL server supports three different recovery models

· Simple

· Full

· Bulk logged

3

SQL Server Agent

SQL Azure does not support SQL Server Agent but Azure Automation can be used for simplifying cloud management through process automation.

SQL Server Agent is a component of SQL Server which schedules jobs and handles other automated tasks. It runs as a Windows service so it can start automatically when the system boots or it can be started manually.

4

FileStream

Due to the nature of PaaS service, SQL Azure does not support FileStream.

SQL server has support for FileStream

5

Table Partitioning

Table Partitioning is allowed in Azure SQL Server. However, because of the PaaS nature of the product you cannot allocate partitions to different file groups. You would have to create partition scheme on Primary and the storage will be taken care by SQL Azure database itself. You will surely benefit from partition elimination here.

In SQL server has full support for table partitioning and can create and allocate partitions to different file groups.

6

Link Server

In SQL Azure you cannot access link servers.

In SQL Server you can create a linked server and access data from another SQL Server.

7

Default Transaction

The default transaction isolation level in SQL Azure is READ_COMMITTED_SNAPSHOT.

The default transaction isolation level in SQL Server is READ_COMMITTED.

8

Global Temporary

Tables

In SQL Azure you can create only local temporary tables. It does not support global temporary tables.

SQL Server has support for both local temporary tables and global temporary tables.

9

Stored Procedures

· Extended SP = NO

· User defined SP = yes

· System SP = Some

· Extended SP = yes

· User defined SP = yes

· System SP = yes

10

SQL Trace and Profiler

In SQL Azure SQL Trace and Profiler cannot be used to trace the events. As of now there is not any direct alternate other than using DMVs, monitoring using Azure Portal and extended events.

In SQL Server you use SQL Trace and Profiler to trace the events.

11

SQL CLR

In SQL Azure Common Language Runtime (SQL CLR) was first supported and then removed concerning security issues.

SQL Server still has support for SQL CLR

12

USE Statement

USE [DatabaseName] statement to switch the database context is not supported in SQL Azure.

Supported in SQL Server 2016

13

Log Shipping

SQL Azure does not support Log shipping

SQL Server supports log shipping.

14

Resource Governor

This feature is not available in Azure SQL Database.

Azure SQL Database comes with different services tiers each suitable for different workloads. You should first evaluate the performance tier your application workload will fit in and accordingly provision the database for that performance tier.

Supported in SQL Server. Resource Governor allows you to throttle/limit resources (CPU, Memory, I/O) as per different SQL Server workloads.

15

Attach Database

This feature is not supported in SQL Azure

Supported in SQL Server

16

Database Mail

This feature is not supported in SQL Azure

Supported in SQL Server

17

Data Quality Services

Not Supported in SQL Azure

Yes this feature available since SQL server 2012.

18

Event Notifications

Event notification is not supported in SQL Azure. However, One of the easiest ways to gain some automated insight into what is going on in your Azure SQL Databases is to set up alerts on your databases.

Supported in SQL server.

19

Master Data Services

This feature is not supported in SQL Azure

It is supported in SQL server.

20

Analysis Services (SSAS)

It is now supported in the cloud as azure analysis services.

It is supported in SQL server.

21

Integration Services

It is not supported directly but Microsoft provide SQL Server Integration Services (SSIS) Feature Pack for Azure.

It is supported in SQL server.

22

Failover Clustering

Not supported in SQL Azure. However, an alternative option is available which is Failover groups and active geo replication

It is supported in SQL server.

23

Auditing

Server level auditing is not supported in SQL Azure but you can use SQL Azure database auditing which tracks database events and writes them to an audit log in your Azure storage account.

It is supported for SQL server and SQL server database.

24

Server Configuration Settings

In SQL Azure you cannot manage server configuration settings. You only have rights to manage database settings.

It is supported.

Reference Links:

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-features

https://dataplatformlabs.com/difference-between-sql-server-azure-sql-database/

http://blogical.se/blogs/jahlen/archive/2010/11/23/sql-azure-why-use-it-and-what-makes-it-different-from-sql-server.aspx

Advertisements

Move BizTalk Server Databases from one SQL server to another

In this article we will explain how to migrate or move BizTalk databases from one server to another server. Let’s assume we want to move our BizTalk primary databases from local server to remote server or any other server on the network. Same method can also be used to move databases to SQL server cluster.

Assumptions:

· You should have admin user account access to both server, source and destination server.

· You should have basic knowledge of SQL Server.

· You should have basic knowledge of configuring BizTalk Server.

Step by step procedure

There are few major steps to follow which are described below. We will also explain these steps in detail in later.

1. Stop all BizTalk server services.

a. Perform a partial stop of all BizTalk services, do not perform a full stop!

b. Stop all host instances.

c. Stop BizTalk server services.

i. BizTalkServerApplication

ii. Enterprise Single Sign-On Service

iii. Rule Engine Update Service

iv. Any other BizTalk service that is running

clip_image001

clip_image002

2. Stop the SQL Server Agent service.

clip_image003

3. Stop IIS.

4. Backup following BizTalk databases using Backup BizTalk Server job.

a. BAMPrimaryImport

b. BAMAlertsApplication

c. BAMAlertsNSMain

d. BizTalkDTADb

e. BizTalkMgmtDb

f. BizTalkMsgBoxDb

g. BizTalkRuleEngineDb

h. SSODB

For configuring Backup BizTalk Server job please follow instructions mentioned at https://msdn.microsoft.com/en-us/library/aa546765.aspx

you may need to run sp_ForceFullBackup before running the job.

5. Restore above backup databases on the remote server. – NOTE: do not use simple restore task, use cs script method instead.

Open SQL server management studio and restore all the backed up databases. In the SampleUpdateInfo.xml file, comment out all of the databases except for those you have moved to the new server. Edit C:\Program Files\Microsoft BizTalk Server 2006\Schema\Restore\SampleUpdateInfo.xml

a. Do a “Replace All” and change SourceServer to ….
b. Do a “Replace All” and change DestinationServer to ….

Copy the edited SampleUpdateInfo.xml file to the following folder, open a command prompt, and go to computer: %SystemDrive%\Program Files (x86)\Microsoft BizTalk Server <version>\Bins32\Schema\Restore and run

cscript UpdateDatabase.vbs SampleUpdateInfo.xml

Copy the edited SampleUpdateInfo.xml file to the following folder computer: Copy to %SystemDrive%\Program Files (x86)\Microsoft BizTalk Server <version>\Bins32\Schema\Restore open a command prompt, and go to computer: %SystemDrive%Program Files (x86)Microsoft BizTalk Server <version>Bins32SchemaRestore and run

cscript UpdateRegistry.vbs SampleUpdateInfo.xml

6. Script all SQL server users from source server and run those script on destination server.

7. Script all SQL server agent jobs from source server and run those script on destination server.

8. Restart Windows Management Instrumentation service under services from services.msc

9. Re configure BizTalk server administrator

a. Remove current BizTalk group

b. Connect to existing group

c. Configure destination SQL server by giving server name

d. Configuring databases

Your BizTalk Server databases is now moved or migrated to another database server and BizTalk should be running properly.

Feel free to contact in case of any query

References:

https://msdn.microsoft.com/en-us/library/ee378546.aspx

Author: Jahanzaib.khan@alliedc.com

Posted in BizTalk, SQL. 1 Comment »

SQL Server Restrict Memory Usage

EXEC sp_configure ‘show advanced options’, 1

RECONFIGURE WITH OVERRIDE

EXEC sp_configure ‘max server memory (MB)’, 200

RECONFIGURE WITH OVERRIDE

SQL: Get Comma Seperated Values

 

Select @CSV = isnull(@TRX_REF + ‘,’,”) + seqID from table

Calling webservice from SQL 2000

 Sending  XML data over HTTP to a web service, using XMLHTTP object

 Sqlteam.com/forums/topic.asp?topic_ID=63292

SQL: Group data by weeks

– Group by weeks
starting on Mondays

select count(OrderID) as numOrders,

min(dateadd(day, datediff(day,’19000101′,OrderDate)/7*7, ‘19000101’)) as WeekStarting,

min(OrderDate) as EarliestOrder, max(OrderDate) as LatestOrder from
Northwind..Orders

group by datediff(day,’19000101′,OrderDate)/7

order by WeekStarting

 

via [SQLTeam]

 

SQL Server: Data Source name not found

Recently, I had a problem when SQL Server 2000 Enterprise Manager fails to register (local)database; the error message was “Data Source name not found and no default driver specified”

 

 

This was a problem with faulty installation of SQL Server or somehow ODBC/Data Access Components configurationgone bad. The solution thatworked in my case is to export and import HKLM\Software\ODBC\ODBC.INST\SQL Server registry key from a normal system with ODBC drivers installed to the faulty SQL machine’s registry