Source, Ingest, Prepare, Analyze and Consume
All about code tracking and management for example, Azure DevOPS branching and merging, Release management, Continuous integration (CI) and Continuous deployment (CD).
Install SSIS DevOps Tool in Azure DevOps organization.
Click on “Browse marketplace” and Search for “SSIS DevOps Tools”. Here is the link.
If you want to install extensions using CLI, here is the link;
Select “SSIS DevOps Tool”;
Click Install and you are done.
Install a second extension “Azure DevOps Open in Excel”. This helps to view work items in Excel and VS.
Go back to your Azure DevOps organization. The installed extensions are here;
Follow this article to create SSIS DevOps pipeline.
This is tricky. Azure PaaS (Platform as Service) SQL Database has master database only. If programmer is taking any advantage of MSDB databases or synonymous, it will not work there directly.
Use Deploy Database to Microsoft Azure SQL Database
The simple method to restore to Azure SQL Database is;
This will create a new database on Azure SQL Server. You can rename it to your actual database.
Migrate to Azure SQL by restoring from Database
This method requires Azure storage account.
Create a blob storage. Upload database backup. From SQL Management Studio, right-click to [YourLOCALDatabase] and open Tasks>Export Data-tier Application. You can export your database to Azure Blob Storage on this wizard. After backup, connect your Azure SQL Server from SQL Management Studio. Go to your [YourREMOTEDatabase], right-click Databases folder from treeview and go to Import Data-tier Application. Choose your backup file from Blob Storage and enjoy!
This will create a new database on Azure SQL Server. You can rename it to your actual database.
Use Azure Data Migration Assistant Tool
This is unlike other methods mentioned above. This is the most efficient tool for migration databases to Azure SQL. This gives you a chance to select individual objects (tables, stored procedures, views, functions etc). Here is the link;
https://www.microsoft.com/en-us/download/details.aspx?id=53595
This does not create a new database but copy schema and data to existing database.
Use 3dr party tool
3rd party tools can be used to backup/restore Azure SQL database. Here is the list;
The tool allows us to create azure backup on local/remote. We can restore those backups on local/remote machine. I have a schedule job on my local that run everyday 10:00AM to create full backup in Azure blob container “myContainer”.
This tool also allows you to backup on your local computer.
Other methods that can be used with Azure SQL Database;
Use if you need to move data from Azure to a specific destination (e.g. your old SQL Server) or in a particular format (e.g. flat file) with SQL Server Management Studio tools
Similar to SQL Server Import and Export Wizard, but enables automatic process
Use when you need to create BACPAC file with SQL Server Management Studio tools
Use if you need to create BACPAC file from a command line
Use if you need to save data from one or several tables in a readable format
Use when you need to perform automatic backups into BACPAC file regularly
Suitable if you only have a browser
Use AzCopy
This tool can be used to transfer data between storage accounts. Here is the link;
https://adamtheautomator.com/azcopy-setup/
Azure Storage Explorer
This is a GUI that can be used to move files between storage accounts and premises. Here is the link;
https://azure.microsoft.com/en-us/features/storage-explorer/
Today I have received this error on a remote server connecting from my local computer;
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and the SQL server is configured to allow remote connections. (provider: Named Pipes, Provider, error:40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2).
To fix this error goto start menu–> go to Microsoft Sql Server –> go to configurations folder and click on sql server configuration manager. check below image.
Expand Sql Native client 11.0 Configuration manager. In client protocols you will see TCP/IP, named Pipes,Via disabled, enable those
Expand Server Network Configuration In Protocols for Sql Server here Enable Shared,Named,TCP/IP
Expand Sql Native client 11.0 Configuration manager. In client protocols you will see TCP/IP, named Pipes,Via disabled, enable those and restart the Sql related services. Now the error fixed.
Resources
https://www.c-sharpcorner.com/article/resolve-error-40-could-not-open-a-connection-to-sql-server/
Follow these steps to verify Access Database Engine (ACE Driver) installation;
Double check to make sure you did install the x64 version of Microsoft Access Database Engine here – http://www.microsoft.com/download/en/details.aspx?id=13255. Please note that only one version x64 –OR– x86 can be installed, not both.
Here is a SQL Server x64 server with the Microsoft Access Database Engine x32 installed – see it does not show up.
Here is the same server with the Microsoft Access Database Engine x64 installed – see how it shows up.
I do not know of a query to get a list of the providers – however if you look in the registry at
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\Providers and check for the key Microsoft.ACE.OLEDB.12.0
This will tell you that it is installed. If you have a 32-bit version installed on a 64-bit box you would need to look under the Wow6432Node, that key would be HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\Providers.
You would have to make sure that both SQL Server and the ODBC/OLEDB driver are the same bitwise either both x64 or x32.
How and where to take backup;