The need to do things in an asynchronous way – that is, dividing big tasks between multiple working units – was present long before the appearance of computers. However, when they did appear, this need became even more obvious. It is now 2019, and I’m writing this article on a laptop powered by an 8-core Intel Core CPU which, in addition to this, is simultaneously working on hundreds of processes, with the number of threads being even larger. Next to me, there lies a slightly outdated smartphone which I bought a couple of years ago – and it also houses an 8-core processor. Specialized web resources contain a wide variety of articles praising this year’s flagship smartphones equipped with 16-core CPUs. For less then $20 per hour, MS Azure can give you access to a 128-core virtual machine with 2 TB RAM. But, unfortunately, you cannot get the most out of this power unless you know how to control interaction between threads. Read More
Keeping two databases in sync is a task we often encounter when working on SQL database development and maintenance. One particular case is this – the versions of a database present on testing and development environments need to be consistently synchronized to make sure that tests are running on the most recent version. This should be done by detecting schema changes in the development version of a database and then automatically synchronizing them with the version from the testing environment on a scheduled basis.
How to achieve it
We’ll look into how automated synchronization of two SQL databases can be achieved by combining a schema comparison tool with PowerShell scripts and scheduling. Read More
The skills of writing different types of SQL Server queries require you to have good knowledge in the SQL Server T-SQL language. T-SQL stands for Transact Structure Query Language, which is a database procedural programming language that is extending the SQL language for Microsoft SQL Server RDBMS product. Read More
This article talks about using database schema snapshots to maintain different versions of a database to be deployed to different environments.
Database schema snapshots are point-in-time copies of the current state of the database which are normally used to reconcile the differences when deploying changes from one environment to another environment.
This article will be focused on a particular scenario where database schema snapshots are more than just point-in-time copies of the database rather they are used to create fresh versions of specific environments.
This article is a walkthrough of how to use the working folder option of source control for managing SQL Server databases.
In this article, I am also underlining some of the benefits and limitations of using a working folder as compared to other available options to use with source control.
Let us discuss some key concepts before delving into the technical details of this article. Read More
When testing the functionality of your application or the performance of a specific stored procedure or an ad-hoc query in the development environment, you need to have data stored in your development databases typical or similar to the data stored in the production databases. This is because the performance of a query that is processing 50 records will be different from the performance of the same query that is processing 50M rows. Restoring a copy of the production database to the development database server for testing purposes is not always a valid option, due to the critical data that is stored in these databases and should not be open for all employees to see, unless you are developing a new application and there is no production database yet.
The best and most secure alternative is to fill the development database tables with testing data. Test data generation is useful for testing the performance of the application or a new functionality without changing the production data. There is no single straight-forward way to generate test data that will fit all scenarios, especially when you need to generate large amount of data to test the performance of complex queries and transactions in which you should cover all possible combinations of testing cases. Read More
Starting from SQL Server 2008, Microsoft introduced a new feature in the SQL Server Management Studio that helps the database developers and the database administrators writing the T-SQL commands faster by reducing the typing effort and providing a quick access to the syntax information via listing all available database objects with their properties. This feature is called IntelliSense.
SQL Server provides us with different solutions to replicate or archive a database table or tables to another database, or the same database with different names. As an SQL Server Developer or Database Administrator, you may face situations when you need to check that the data in these two tables are identical, and if, by mistake, the data is not replicated between these two tables, you need to synchronize the data between the tables. In addition, if you receive an error message, that breaks the data synchronization or replication process, due to schema differences between the source and destination tables, you need to find an easy and fast way to identify the schema differences, ALTER the tables to make the schema identical in both sides and resume the data synchronization process. Read More
The most common approach to developing database solutions is to start creating database objects based on business requirements which is also known as “Conventional Database Development”.
In this article, we are going to explore the implementation of such approaches as conventional database development and test-driven database development on the particular examples.
To begin with, have a closer look at the conventional database development.
Copying or moving databases is one of the most common tasks for data professionals who regularly deal with deploying scripts or new solutions across different environments. With SQL Server, we have multiple ways by which we can accomplish this natively without using third-party tools.