Do you think about something when you create a new database? I guess that most of you would say no, since we all use default parameters, though they are far from being optimal. However, there is a bunch of disc settings, and they really help to increase system reliability and performance. (more…)
When we are beginning to think of migrating our on-premises databases to Azure SQL, we have to decide on a proper purchase model, a service tier, and a performance level. Before starting the Azure SQL migration process, we have to find logical and provable answers to the following questions:
- Which purchase model is suitable for my apps and business requirements?
- How much budget do I need?
- Which performance level meets my requirements?
- Can I achieve the acceptable performance of my apps?
Backup is the most important case for database administration because it provides business continuity solutions for high availability and disaster recovery. For this reason, Azure SQL database (PaaS: platform as a service) can make a backup automatically and restore it at a point in time. However, the retention period of backup files changes according to the service tiers.
Constraints on tables and columns allow you to enforce the data quality. In SQL, there are two ways of creating constraints on a table: inline and out of line.
In this article, I am going to explore these constraints and the advantages they have, as well as explain which one I recommend and why.
A recent announcement on the release of several SQL Server tools has raised expectations across various groups. Product requirements and business are almost always a trade-off, and striking the right balance in a product in terms of the toolset is a sign of a successful product. After testing the SQL Operations Studio, I feel that it’s a promising tool for many developers, administrators, and DevOps specialists. In my opinion, the mssql-cli tool adds another feature to SQL Server in order to make it a leading database product.
What is TempDB?
TempDB is a system database in Microsoft SQL Server used as a store of internal objects, row versions, work tables, temporary tables, and indexes. TempDB is available for use to all participants connected to a SQL Server instance (it is a global resource). For those familiar with other database flavors, the tempDB database is similar to the TEMP tablespace in Oracle. To put it mildly, anything that cannot fit your instance memory spills over to the tempdb data files.
As DBAs, we generally come across our clients complaining that the current Production Server is not able to hold the load on the server and whether the load may be balanced with the Secondary Server. This is possible with a database in DR Server with Read-only database in Log Shipping and Secondary SQL Server replicas in Always On Availability Group. The biggest advantage of Always On Groups is that it allows us to set up group level HA for any number of databases and we can create up to four secondary replicas and this is a combination of Clustering, Log Shipping and Database Mirroring where the data transmission is more flexible and functional.
All the database developers more or less write database unit tests that not only help in detecting bugs early but also save a lot of time and efforts when the unexpected behavior of database objects becomes a production issue.
Nowadays, there are a number of database unit testing frameworks such as tSQLt along with third-party unit testing tools including dbForge Unit Test.
On the one hand, the benefit of using third-party testing tools is that the development team can instantly create and run unit tests with added features. Also, using a testing framework directly gives you more control over the unit tests. Therefore, you can add more functionality to the unit testing framework itself. However, in this case, your team must have time and a certain level of expertise to do this.
This article explores some standard practices that can help us to improve the way we write database unit tests.
Azure SQL offers different types of business continuity solutions. One of these solutions is Geo-Replication that provides an asynchronous database copy. You can store this copy in the same or different data center locations (regions). There can be four readable database copies. In the documentation of Microsoft notes, the recovery point objective (RPO is the maximum acceptable amount of data loss measured in time) is less than 5 seconds. If we want to automate and make (users will not affect) failover mechanism transparent, we have to create the auto-failover group.