One of the most common ways to ensure that the recovery will be possible if a data-file corruption or any other disaster occurs is to create a recovery plans for this scenario. The most popular recovery plans include regular creation of database backups which can later be used to restore a database to a nearest available point in time, prior to disaster.May 6, 2015
In the following series of articles we will discuss what baselines are, how they work and how to apply them to everyday SQL Server performance monitoring. This article will provide a brief overview of baselines and the statistical calculations behind them. Later we’ll apply this to real information gathering techniques to allow DBAs to create their own baseline statistics and we’ll also show how to baseline performance “out of the box” with a 3rd party toolMay 4, 2015
An optimal continuous SQL Server auditing approach must include:
- Continuous auditing
- Real time data collection
- Ability to generate meaningful reports
- Alerting on unwanted activities
- Tamper proof store of audit data
In many cases, the primary requirement that must be fulfilled is that auditing must be performed with zero auditing data lost.April 28, 2015
In a multi-user database-development environment, avoiding conflicts and overwrites with edits, and ensuring all changes are audited and recorded is important. Until recently however, effective tools for SQL development management have lagged well behind their client developer equivalents, like Visual Studio. In this article, we will look on specific database source control challenges and a way to address them use new SQL developer tools that make meeting these straightforward and easyApril 22, 2015
ApexSQL Clean has, among others, a useful feature that most people are not even aware of. It provides a client SQL code analysis in C#, Delphi, VB.NET, XAML, XML, ASP.NET, HTML, CSS code etc., and detects which SQL objects are actually being used and which ones aren’t in the referenced database. The SQL code analysis is performed by inspecting the code file for objects in the SQL database. This feature helps keeping SQL databases clean and organizedApril 16, 2015
Many development teams have the need for a quick and easy but effective solution to committing SQL Server database objects to source control, but aren’t yet ready to move to full source control integration at check in/check out level.
In order to get objects under a source control in a “Poor man’s” approach, creation scripts need to be produced for each table, stored procedure, and any other object in a database. Afterwards, the scripts need to be committed to a source control system.April 15, 2015
When recovering from a SQL Server database failure, a database administrator needs to restore a set of SQL Server database backups in a logically correct and meaningful restore sequence. With this in mind, to the goal is to devise a disaster recovery strategy by creating a solid backup plan, as well as a proper database restore plan in SQL Server. This article will describe 2 different solutions for creating and scheduling a database restore in SQL Server.April 8, 2015
To prevent accidental data loss, it is always good to ensure that there is a disaster recovery solution available. This can be easily achieved by having a standby copy of a primary database on another SQL Server instance, which can be achieved via log shipping.April 6, 2015
A SQL Server database can have stored procedures, tables, defaults, views etc. that aren’t being used anymore, and unless you determine which of your objects are truly unreferenced you will be stuck with them, or risk breaking your database if you delete a wrong object. This is where ApexSQL Clean can help. ApexSQL Clean’s main features are already described in SQL code analysis – full body scan of a SQL database, but here we would like to point out and present one more useful feature: creating a sql database cleanup report. In order to have a complete database cleanup report and dependencies analysis procedure, ApexSQL Clean has a reporting mechanism, which allows keeping track of selected objects and their dependencies.April 3, 2015
It is not a rare case that a DBA inherits SQL Server databases with many unused SQL objects. By using ApexSQL Clean, it is easy to clean a SQL database from these unneeded objects and prevent extra objects from making an impact on development by slowing it down and increasing the maintenance workload (e.g. all unused objects still have to have their permissions set, be conformant with coding standards, etc.)April 2, 2015
ApexSQL Clean can help determine all dependencies in a SQL database; it also analyzes the impact of potential changes and deletions on SQL database, and determines object interrelationships within the database, between different databases, SQL scripts and even applicationsApril 2, 2015
Whenever building a SQL Server database, it must determine which objects to include in it. ApexSQL Build provides a detailed object analysis for building a database or for a database update deployment, with an ability to customize the SQL database objects and include dependent objects automatically. Selecting objects is easy and allows to easily customize SQL database objects.April 2, 2015
When comparing data sources using ApexSQL Data Diff, tables with the same name are mapped automatically. But, what happens with tables and columns with different names when SQL tables are compared to check for data differences?
By default, they are excluded from the comparison process and need to be mapped manually using the Object mapping feature. This feature also allows users to change tables paired by default, i.e. unmapping them and creating customized comparison pairs. This can be helpful in scenarios where the same tables are differently named in the development and the production database, and data needs to be pushed from the development database to the production one.April 2, 2015
A database is one of the most important parts of every information system and therefore is an often target of hackers. Encryption is the process of obfuscating data with the use of a key and/or password making the data unintelligible to anyone without a corresponding decryption key or a password.April 1, 2015
Having a good backup and restore plan is an important part of a disaster recovery strategy. This article will describe 3 different solutions/approaches for creating a scheduled backup in SQL Server
As a part of a backup strategy several types of backup can be used together.March 31, 2015
It’s very likely that you frequently refresh a development or test environment with recent production SQL server database backups. However, depending on the size and contents of a production database, this process might take a large amount of disk space and be pretty slow since the SQL server database backup needs to be fully restored. This is where ApexSQL Restore comes into playMarch 31, 2015
The main purpose of renaming an object or a column in SQL Server is to increase readability, adopt naming conventions, or to give a more meaningful name to an object. The sp_rename procedure will not rename nor update all SQL database dependencies for a renamed object or a column automatically. Therefore, all object dependencies should be found by using e.g. the sys.sql_expression_dependencies function and renamed manually.
ApexSQL Search, a SQL Server Management Studio and Visual Studio add-in among other features, provides users the Safe rename feature – the ability to rename tables, procedures, views, functions and columns without breaking SQL database dependencies
Let us show the complete process of changing the name and schema of a table at the same time.
To use the Safe rename feature:
Right-click a table to be renamed in the SQL Server Management Studio’s Object Explorer
- Select the Safe rename command from the context menu:
This will open the Safe rename table window. The first thing that can be noticed is the yellow warning icon, which informs about the existence of a table with the same name under the specified schema. If the schema and table name are properly changed, the warning will disappear:
After choosing another available schema and specifying the new table name, with a click on the Preview button, the following Preview tabs will be populated with information:
- Generated script
The Generated script tab
The preview tab displays a SQL script that will be executed in order to rename the selected table. It can be opened in a new query window by selecting the Open button, ready for editing or executing via SQL Server Management Studio:
The Warnings tab
Second in order, the Warnings tab, will provide warnings, for example, in case the rename process cannot be completed without a table rebuild. Such scenarios are very rare and complicated to demonstrate, but the Safe rename feature is aware of them and will raise a warning if this is the case.
The Sequence tab
This tab summarizes the generated SQL script in a tree-like form by showing actions in the order they will be performed. Please note that the action sequence is specifically generated in such a way as to prevent any dependency problems:
The Dependencies tab
Finally, the Dependencies tab, provides a list of dependent objects that refer to the table which will be renamed. These are handled via Alter statements in the generated SQL script by changing their references accordingly:
As it can be seen, the Safe rename feature provides a detailed preview in several categories of what’s affected and how an object rename process will be executed. These can be further analyzed, or simply and safely rename the object with a click on the Execute (F5) button.
March 26, 2015
Once become familiar with the source control basics (SQL Server Source Control – Part I – understanding source control basics), development client can be connected to both SQL Server and the version control system. This article will review 3 options: native support in SQL Server Management Studio, Visual Studio, and the 3rd party ApexSQL Source Control tool.March 25, 2015
Along with macros and add-ins, VSPackages are a common tool for extending Visual Studio. The main reason why we are writing about them is that Microsoft announced that add-ins will be deprecated in future releases of Visual Studio. VSPackages are supported in all versions of VS coming from 2005. Some would say they are hard to use and understand but once you get to know them a little better they are not that difficult to implementMarch 23, 2015
Now it’s time to extend our knowledge of VSPackages, and try to see what can be done with them. In this article, we are going to explain how to create toolbars and new windows, and customize command appearance in Visual StudioMarch 23, 2015