Advertisements

Archive

Archive for the ‘Others’ Category

Migrate your SQL Server database to Azure by using SysTools “SQL to Azure Migration” Tool

September 5, 2018 Leave a comment

 
Microsoft SQL Server is the most popular database management system, which provides flexibility to the database administrator to manage their database. It is a full-featured database and designed for use in corporate applications. But, many times, many users need to migrate their SQL Server database to the Azure SQL Database. Because Azure is an intelligent and fully managed relational cloud database that offers the broadest SQL Server engine compatibility.

However, at the time of SQL database to Azure migration, there are chances that we can miss some important steps or information such as trustworthy property, dependent Jobs, Linked Server, Logins etc. The main headache of any conversion is figuring out how to get the information from database and import into the server. Therefore, users need to use a professional tool to transfer SQL Server Database to Azure SQL Database.

One of the best third-party solutions I came up is SysTools SQL to Azure Migration Tool, which allows users to move all the SQL database objects like Tables, Triggers, Stored Procedures etc. to Azure SQL Database. Apart from this, there are many other features of this tool that are discussed below:
 

Key Features of SQL Server to Azure Migration Tool

 
1. Easily Transfer SQL Database to Azure

With the help of this tool, users can perform direct migration from SQL to Azure SQL database. For this, you need to provide all the credentials like server name, database name, username and password etc. After that, one can easily transfer SQL Server database to Azure.

2. Option to Add MDF/NDF Files

The tool provides an option to add MDF & associated NDF files to the software. You can also migrate all the database objects like Tables, Triggers, and Stored Procedures etc. from SQL to Azure.

3. Migrate Corrupted Database

The best part of this utility is that it can convert the corrupted database as well as the healthy file from local SQL server to Microsoft Azure SQL Database. Before migration, it scans the corrupted database files and repair them using either quick or advanced scan options.

4. Quick and Advance Scan Option

While working with this tool, the user can choose the required scan mode depending on the level of corruption out of the following modes:

– Quick Scan: This mode scans minor corrupted SQL files

– Advance Scan: This mode scan major corrupted SQL files

5. Auto Detect SQL Server File Versions

The SQL to Azure migration tool has an option to detect the versions of added MDF or NDF files automatically. For this, you just need to click on the checkbox next to this option.

6. Export Schema Option

The tool provides a feature to save the SQL database schema into Azure in any of the following ways:

– With the Only Schema: It migrates only schema of tables, views etc.

– With Schema & Date: It allows users to transfer both schema and data of all database objects.
 

Free and Licensed versions of SysTools SQL Server to Azure Migration Tool

The tool can be availed in the following two versions:

1. Demo Version: Users can freely download the trial version of software from the SysTools official website. It is available to understand the working of the software in a much better way.

2. Licensed Version: The licensed version of the tool can migrate SQL server database to Azure from MDF or NDF files. Also, it allows you to transfer the schema and schema & data.
 

System Requirements

The licensed version of SQL to Azure migration tool has been tested by the SQL experts. It will evaluate the performance of the software in terms of quality, reliability, security etc.

However, the testing has been performed in below-mentioned environment:

– Operating System: compatible with Windows 10 and all below versions

– Processor: Intel Pentium 1 GHz processor or any equivalent processor

– RAM: Around 2 GB of RAM is necessary

– Hard Disk: At least 100 MB space is required for installation
 

Advantages:

– It can also migrate corrupted or inaccessible SQL database to the Azure database.
– The software can preview all database components before migrating to Azure.
– The SQL Server to Azure migration tool has a simple and user-friendly interface.
 

Disadvantages:

– It is required to have database created on Azure SQL Server Database before migration.
– The Demo version only exports 25 records of each table.
 

Observational Verdict

After considering all the features of SQL Server to Azure Migration tool, I can say that it is a reliable and effective tool for DBAs and Developers to transfer SQL data to Azure without causing any data loss.


Advertisements

SQL Server on Linux – Best practices post installation

August 30, 2018 1 comment

 

Setup SQL Server on Linux:

1. Spinning up a new Linux VM on Microsoft Azure

2. Install and Configure SQL Server 2017 on Linux Azure VM

3. Connect SQL Server on Linux with SSMS from a Windows machine
 

Best Practices:

Here are some of the best practices post installing SQL Server on Linux that can help you maximize database performance:

1. To maintain efficient Linux and SQL Scheduling behavior, it’s recommended to use the ALTER SERVER CONFIGURATION command to set PROCESS AFFINITY for all the NUMANODEs and/or CPUs. [Setting Process Affinity]

2. To reduce the risk of tempdb concurrency slowdowns in high performance environments, configure multiple tempdb files by adding additional tempdb files by using the ADD FILE command. [tempdb Contention]

3. Use mssql-conf to configure the memory limit and ensure there’s enough free physical memory for the Linux operating system.

sudo /opt/mssql/bin/mssql-conf set memory.memorylimitmb 1024
sudo systemctl restart mssql-server

4. On multi-node Non-Uniform Memory Access (NUMA) installations, auto NUMA balancing needs to be disabled to allow SQL Server to operate at maximum efficiency on a NUMA system.

sysctl -w kernel.numa_balancing=0

5. You can also change the kernel settings value for virtual address space to 256K, as the default value of 65K may be insufficient for a SQL Server installation.

sysctl -w vm.max_map_count=262144

6. Use the noatime attribute to disable last accessed timestamps with any file system that is used to store SQL Server data and log files.

7. For the most consistent performance experience, you must leave the Transparent Huge Pages (THP) option enabled.

8. Virtual machine (VM) features like Hyper-V Dynamic Memory shouldn’t be used with SQL Server installations. When using VMs, be sure to assign sufficient fixed-memory sizes.

9. Make sure you have a properly configured swapfile to avoid any out of memory issues.


Get started with Python on SQL Server – Run Python with T-SQL on SSMS

August 10, 2018 1 comment

 
With SQL Server 2016 Microsoft added Machine Learning support with R Language in SQL engine itself and called it SQL Server R Services.

Going ahead with the new SQL Server 2017 version Microsoft added Python too as part of Machine Learning with existing R Language, and thus renamed it to SQL Server Machine Learning Services.
 

Installation/Setup

Here are few steps to get you started with Python programming in SQL Server, so that you can run Python scripts with T-SQL scripts within SSMS:
 

1. Feature Selection: While installing SQL Server 2017 make sure you’ve selected below highlighted services


 

2. Configure “external scripts enabled”: Post installation run below SQL statements to enable this option

sp_configure 'external scripts enabled'
GO
sp_configure 'external scripts enabled', 1;
GO
RECONFIGURE; 
GO
sp_configure 'external scripts enabled'
GO


 

3. Restart SQL Server service: by “services.msc” program from command prompt, and run below SQL statement, this should show run _value = 1

sp_configure 'external scripts enabled'
GO


 

If still you don’t see run _value = 1, then try restarting the Launchpad service mentioned below in Step #4.

4. Launchpad Service: Make sure this service is running, in “services.msc” program from command prompt. Restart the service, and it should be in Running state.

Its a service to launch Advanced Analytics Extensions Launchpad process that enables integration with Microsoft R Open using standard T-SQL statements. Disabling this service will make Advanced Analytics features of SQL Server unavailable.

Post restarting this service, it should return run _value = 1 on running the query mentioned at Step #3
 

Run Python from SSMS

So as you’ve installed SQL Server with ML services with Python & R, and enabled the components, now you can try running simple “Hello World!” program to test it:

EXEC sp_execute_external_script 
	@language = N'Python', 
	@script = N'print(''Hello Python !!! from T-SQL'')'


 

Let’s do simple math here:

EXEC sp_execute_external_script 
@language = N'Python', 
@script = N'
x = 5
y = 6
a = x * y
b = x + y
print(a)
print(b)'

 

If you still face issues you can go through addition configuration steps mentioned in [MSDN Docs link].

 


All about Blockchain – Introduction

June 25, 2018 2 comments

 
Blockchain is a disruptive technology that is going to transform many industries in near future. Bitcoin is one of its implementation. Let’s check what is Blockchain is all about.
 

What is Blockchain?

A Blockchain is an immutable, secure, digital, distributed ledger without a central authority, which uses public/private signature technology to validate and record transactions in near real-time. It is nothing but a data structure which works as decentralized database or a distributed ledger that stores a registry of assets and transactions over a peer-to-peer network of computers.

Blockchain’s distributed digital ledger contains cryptographically signed transactions that are grouped into blocks. Each Transaction recorded in the database is digitally signed and mathematically guaranteed to be authentic and resistant to fraud. Each block is cryptographically linked to the previous one after validation and undergoing a Proof of Work or Consensus decision. As new blocks are added, older blocks become more difficult to modify, thus immutable. New blocks are replicated across all copies of the ledger within the network/nodes, and any conflicts are resolved automatically using established rules.

 

Blockchain Internals:

1. Blockchain uses a Distributed Ledger to track transactions

2. A Ledger is a Immutable (append only) database most commonly used in accounting

3. Same copy of the data distributed across all the participating Nodes (Decentralized)

4. All new transactions are securely encrypted and then broadcast across the Blockchain network to be added to the system

5. Participants in the Blockchain verify the transaction is valid and then writes it to the Ledger

6. Transactions are grouped together in Blocks. Blocks are linked to previous Blocks, which make the blockchain.

7. The Transaction chain tracks how ownership changes, while the Block-Chain tracks the order of transactions.
 

Example: how Blockchain works?

1. A person X transfers $100 to person Y. Both persons X & Y have their Account numbers and corresponding Private Keys.

2. A Transaction record is created which contains the transaction details and digital signatures from both persons/parties.

2. The Transaction is broadcasted to the network for verification by the various computer nodes to make sure if the transaction details are valid.

3. On successful validation of Transaction is accepted in the network and added to a Block. Each block contains a unique code called a Hash, it also contains the hash of the previous block in the chain.

4. The verified Block is added to the Blockchain. The Hash codes connect the blocks together in a specific order.

… However this does not look as simple as mentioned above. There goes lot of computations and verifications to make sure what transactions are valid and to add them to the Blockchain.
 

Blockchain vs Traditional Ledgers:

– Traditional Ledgers are centralized and thus requires third Party authority and middlemen to authenticate, approve and record Transactions.

– But Blockchain safely distributes ledger across the entire network and does not require any middlemen.

Blockchain implementations:

– Eliminates Intermediaries: Allows industries to redefine or create new business models.

– Reduces Fraud: Highly secure and transparent, making it nearly impossible to change historical records.

– Increases Efficiency and Speed: Simplifies transactions and enables T+Zero settlement time.

– Increases Revenue and Savings: Potential savings and new revenue opportunities through more efficient processes and reduced costs.
 

Blockchain Use Cases:

1. Ownerships
    – Land registries
    – Property titles
    – Other physical assets

2. Identities
    – Blockchain e-identities to citizens
    – Use services like voting
    – Healthcare records

3. Verification
    – Licenses
    – Proofs of records (degrees, grades, etc)
    – Transactions
    – Processes or events

4. Movement of assets
    – Transferring money from one person/entity to another.
    – Enabling direct payments, once a work condition has been performed.
 

Blockchain implementations:

1. Bitcoin or BTC serves as the cryptographically secured unit of value, numeraire (standard for currency exchange) and currency in the case of the Bitcoin protocol and hybrid fuel/currency used as a Cryptocurrency.

2. Ethereum or ETH serves as the cryptographically secured unit of value, numeraire and hybrid fuel/currency for the Ethereum protocol.

3. Ripple, LiteCoin, etc.
 

Blockchain Network Types:

1. Public:
– Many unknown participants, like Banks, Traders, Financial firms etc.
– Writes by all participants
– Reads by all participants
– Consensus by Proof of Work

2. Private:
– Known participation from one organization, like a Bank
– Write permission centralized
– Reads may be public or restricted
– multiple algorithms for consensus

3. Consortium:
– Known participation from multiple organization
– Write requires consensus from several participants
– Reads may be public or restricted
– multiple algorithms for consensus
 

Microsoft Blockchain offering:

Microsoft has a Blockchain offering on Microsoft Azure as Ethereum Blockchain as a Service (EBaaS) so Enterprise clients and developers can have a single click, low cost, ready-made, cloud based blockchain dev/test/production environment.
 

We will see more on Blockchain in next posts !!!


SQL Server 2017 Setup error – VS Shell installation has failed with exit code 1638

May 14, 2018 Leave a comment

 
Today while installing SQL Server 2017 on my PC I got this popup on the Installation Progress tab. Earlier I had installed SQL Server 2017 but had to uninstall it for some reason. Now today while re-installing it I was getting this error.

TITLE: Microsoft SQL Server 2017 Setup
——————————
The following error has occurred:

VS Shell installation has failed with exit code 1638.

——————————
BUTTONS: OK
——————————

 

… and once you click OK, the installation resumes, but ends in error again, with following error details:

Error details:
§ Error installing Microsoft Visual C++ 2015 Redistributable
VS Shell installation has failed with exit code 1638.
Error code: 1638
Log file: C:\Program Files\Microsoft SQL Server\140\Setup Bootstrap\Log\20180824_225215\VCRuntime140_x64_Cpu64_1.log
Visit https://go.microsoft.com/fwlink?LinkId=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000 &ProdVer=14.0.1000.169&EvtType=VCRuntime140_x64%40Install%400×1638 to get help on troubleshooting.

 

Resolution:

– As per the second error, the SQL Server setup tries to install Microsoft Visual C++ 2015 Redistributable, but as I already have Visual Studio 2017 installed, thus it is failing to install a previous version of VC++, so you need to:
   1. Uninstall Visual Studio 2017.
   2. Install SQL Server 2017
   3. Install Visual Studio 2017 back again.

– Or, rather than uninstalling whole VS 2017, you can simply:
   1. Uninstall the VC++ 2017 Redistributable
   2. Install SQL Server 2017
   3. Install the VC++ 2017 Redistributable (download: x86, x64)