SQL Server DBA: The tools that we used together in those years

As SQL Server DBA, we often complain that Microsoft's own tools are not good, but can be used to open the box is very rare. The following three separate GUI tools are included in SQL Server 2012 for use by SQL Server DBAs and developers.

1. SQL Server Management Studio, launched in 2005

2, also launched in 2005, SQL Server Business Intelligence Development Studio

3, 2012 new launch of SQL Server Data Tools

SQL Server Management Studio

Microsoft first named SQL Server Management Studio (SSMS) as SQL Workbench, but since other vendors have registered to use that name, they have made changes. This is why the file name for SQL Server 2005 is sqlwb.exe. SSMs has always been designed for DBAs and developers, not only for the SQL Server database engine, but also for the SQL Server Analysis service engine, the report service engine, and the Integrated Services engine. All of this is implemented in the same single application, and it's great. From a product development perspective, developers can use Transact-SQL (T-SQL) to write query statements for SQL Server database engines based on their needs, or use MDX, DMX, and XMLA to SQL The query statement for the server Analysis service engine is written.

SSMS is developed from Enterprise Manager and Query Analyzer, both from SQL Server 2000 and earlier versions. In previous releases, Enterprise Manager was applied to database management, and Query Analyzer was applied to database development. Compared with today's ssms, they have completely lost their competitive edge. Enterprise Manager is a bit like the combination of the Object Explorer panel and the Object Explorer details view of today's ssms. Query Analyzer contains a basic Object Browser whose query window can run T-SQL statements.

Although many people use SSMS as a query tool, it is actually a much richer function. Through SSMs, you can run the query on a single server, or you can select a folder from the Registry window and click on "New query" to query on multiple servers. In the same folder, the query task can be completed on all servers at once. In addition, SSMS also has the ability to debug programs, step through the code in the server, check the variables, and verify the path. But be careful not to use it on a production server.

The SSMs also contains the tools IntelliSense that first appeared in SQL Server 2008. This is a smart tip tool that, when the user enters an object name, appears with a Drop-down menu that automatically prompts for information. The 2012 version of IntelliSense has gone well beyond the 2008 and 2008 R2 versions, but it's hard to say perfect, and it's missing a lot of object classes, such as SQL Service Broker. At the same time, it can only target the most basic SELECT statements. Once the command becomes complex, IntelliSense does not recognize all the objects.

Business Intelligence Development Studio

Business Intelligence Development Studio (BIDS) is a business intelligence (BI) development platform in its name. Whether it's an ETL package for SSIS, a SSRS report, or an SSAS cubes,bids can be effectively managed. In fact, it's just a small plug-in designed for the Visual Studio development environment, designed to provide a richer development platform for BI developers.

In SSIS, the Toolbox contains all the objects that SSIS supports. Developers can quickly and easily design and create ETL processes simply by dragging objects from the toolbox to the design interface. The process is powerful, not only using Microsoft SQL Server as a source and destination, but also using any ODBC appliance database, including Microsoft Office files and text files.

Creating reports in bids is also very simple, with developers putting report objects into the report columns in the toolbox and connecting them to the query results component.

SQL Server Data Tools

SQL Server Data Tools (SSDT) can be said to be a new force for Microsoft SQL Server's own tools. SSDT is a free stand-alone download tool designed for database developers to speed up and simplify the database development process in the Visual Studio integrated development environment (the work environment of most application developers). The SSDT table editor features a prominent advantage, because it can be edited in a visual editor, which is similar to a table editor in SSMs, it can also show T-SQL statements during editing, and can be modified and updated by the GUI editor in real time.

In addition, SSDT's ability to match different systems is amazing, including the SQL Server database engine, Windows Azure SQL database, and Microsoft cloud databases, but different versions of the functionality will vary. For example, if you connect to Azure, you can't use table partitioning syntax. It is similar to IntelliSense's self-contained support feature, which can only show the features of the database engine version and support all versions after SQL Server 2005, which is much more powerful than the SSMs tool.

How to choose the right tool depends on what kind of task you want to accomplish. SSMs and SSDT are for database development management, and bids is for BI development. Microsoft is willing to spend so much time and money developing so many self-contained tools that should be a blessing for SQL Server DBAs and developers. More importantly, they are free.