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, 2015How to create a database cleanup report in SQL Server
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, 2015How to clean up unused objects from a SQL Server database
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, 2015How to remove SQL Server database objects safely
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 applications
April 2, 2015How to customize the SQL Server database objects to be deployed in a database build process
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, 2015How to compare SQL Server database tables with different names
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, 2015SQL Server database backup encryption
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, 2015How to schedule a SQL Server backup
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, 2015How to automate the process of restoring a SQL database
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 play
March 31, 2015How to rename database schema and object names without breaking SQL database dependencies
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
- Warnings
- Sequence
- Dependencies
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
SQL Server Source control – Part II – Integration
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, 2015How to create Visual Studio add-ins with VSPackages
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 implement
March 23, 2015Using VSPackages to create tool windows and customize command appearance in Visual Studio
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 Studio
March 23, 2015How to generate randomized test data from a CSV file
A new SQL database is created, everything is set, but it needs some data source so it can be tested. The question would be, what data source to use in order to populate the SQL Server database with a random test data?
A commonly available data source is the CSV (comma-separated values) formatted file that is widely supported. Among its most common uses is moving tabular data between programs that natively operate on incompatible (often proprietary and/or undocumented) formats. This works because so many programs support some variation of CSV at least as an alternative import/export format.
A CSV file containing the sample data is chosen, and now it needs to be mapped and its data inserted. What are our options?
March 20, 2015How to visualize SQL database objects dependencies
ApexSQL Search is a SQL Server Management Studio and Visual Studio add-in that, among other features, provides object a database dependency diagram.
The View Dependencies feature has the ability to visualize all SQL database objects’ relationships, including those between encrypted and system objects, SQL server 2012 specific objects, and objects stored in databases encrypted with Transparent Data Encryption (TDE). Let’s take a closer look at this feature.
March 18, 2015How to refactor a SQL Server database by splitting a table
Depending on the environment, splitting a SQL table may have a positive impact on the overall database performance. For instance, in scenarios where a table contains some large but rarely used columns, moving them to a separate table will increase performance as the frequently used data will be stored in a much smaller table, and the rarely used data will be only looked up when required. The impact on performance caused by the occasional joining will be compensated just by having SQL Server look up the data that’s used more often in a table which requires less disk space leading thus to a decrease in I/O and potentially an increase in page cache hits.
March 17, 2015How to add a timestamp to SQL database documentation
Documenting SQL database is the best practice to ensure that the organizational schema, data objects, and other related information are captured for some future references, especially during development. In case of frequent documentation due to constant database structure changes, it is crucial to have documents organized in an accurate and chronological manner.
March 12, 2015How to consolidate multiple SQL scripts into a single SQL script
Having multiple SQL scripts and running them against a SQL database can be demanding. ApexSQL Build is a tool that can build databases from SQL scripts. The ApexSQL Build’s Consolidate scripts feature can combine multiple SQL scripts into a single SQL script in a very simple way, and more importantly, it can significantly decrease deployment time.
March 12, 2015How to execute SQL scripts against multiple SQL databases at the same time
ApexSQL Propagate is a SQL database deployment tool, that can create SQL scripts directly in the application and set order of SQL scripts for execution. SQL scripts can be parsed prior to execution and execution results can be reviewed by databases or by scripts.
It can also execute multiple SQL scripts against multiple SQL databases in a few simple steps:
- Run ApexSQL Propagate and click the New button from the Home tab:
- This will open the New project window in which Script list can be loaded or created. To create a new script list and load SQL scripts click the Create new script list button:
-
To add SQL scripts, click the Add scripts(s) button in the Script list window:
-
Once the list is complete, click the Save button and name the list as desired and additionally change the order of script execution using the Move up/down buttons or commands from the right-click menu:
-
Once the list is saved it will be automatically loaded in the Scripts tab:
-
Switch to the Databases tab or click the Next button from the bottom-right corner of the New project window
-
Let’s create now a database list with a click on the New database list button and the Database list window will be shown:
- To add databases to the deployment list, click the Add database(s) button and the Add database window will be shown. Click the Add server(s) button and the Connect to SQL Server window will pop-up:
-
Add as many servers as needed to connect to all databases on which scripts need to be executed. Check all desired databases and once done click the OK button to load them in the Database list window:
-
Click the Save button in the Database list window and provide the database list name:
-
After the database list is saved, it will be automatically loaded in the Databases tab:
- Once the databases and scripts are set, click the Load button
- After everything is loaded, the main window will be shown along with the Script and Database panels that contain previously created lists, and the Script preview panel in which any SQL script from the list can be reviewed and edited before the execution:
- Another useful option is controlling a deployment process in case of encountered errors. It is possible to predefine courses of action in a case that an error appears during deployment. It is a simple but handy option that helps control the process. This option is located under the Output tab in the Options window that can be accessed from the Home tab of the main application window and offers several Error handling options:
-
The next step is to simply run the process using the Execute button from the Home tab:
-
SQL scripts will be executed in a predefined order against selected SQL databases. The results (or errors, if any) will be displayed in the Results window:
-
At the end, once the Results window is closed, the same execution information can be reviewed from the main application window under the Results panel, which can be shown on a click on the Results button from the View tab:
Also, view of the execution results can be changed with a click on the View by scripts or View by databases buttons from the View tab:
|
Quick tip: The organized SQL script list can be saved and re-used if there is a need for repeatable sessions |
|
Quick tip: Database deployment lists can be saved under custom names allowing the creation of multiple deployment scenarios or even grouping SQL databases under different lists according to specific deployment needs |
The main advantage of using this feature is reflected in its simplicity and multiple functionalities: it can save time while allowing complete control over the execution of multiple scripts against multiple SQL databases.
March 12, 2015How to package database scripts into C# solutions and executable installers
For users that need to build deployment packages across environments from SQL scripts, either by using the C# solution or executable installer, ApexSQL Build offers an efficient way to accomplish this task in a few simple steps. This applies, for example, to situations where development changes to the structure and content of a production database need to be pushed as an upgrade.
March 12, 2015
