Answer:- Some of the third-party tools used in SQL Server are: • SQL Check (Idera): For monitoring server activities and memory levels • SQL Doc 2 (Redgate): For documenting databases • SQL Backup 5 (Redgate): For automating the backup process • SQL Prompt (Redgate): For providing IntelliSense for SQL Server 2005/2000 • LiteSpeed 5.0 (Quest): For backup and restore processes Benefits of using these third-party tools: • Faster and flexible backup and recovery options • Secure backups with encryption • An enterprise view of the backup and recovery environment • Easy identification of optimal backup settings • Visibility into the transaction log and transaction log backups • A timeline view of backup history and schedules • Recovery of individual database objects • Encapsulation of a complete database restore into a single file to speed up restore time • Improving SQL Server functionality • Saving time and proving better information or notification

Answer:- Hotfixes are software patches applied to live systems (the ones still running). A hotfix is a single, cumulative package that includes one or more files used to address a problem in a software product (i.e., a software bug). In the Microsoft SQL SERVER context, hotfixes are small patches designed to address specific issues, most commonly to freshly-discovered security holes. For example, if a select query returns duplicate rows with aggregations, the result may be wrong. This can be fixed by a hotfix.

Answer:- Instantiating COM objects is a time-consuming process and since it is done within a trigger, it impedes the data insertion process. The same is the case with sending emails from triggers. This rundown can be better implemented by logging all the necessary data into a separate table and having a job that checks this table and does the needful.

Answer:- AES (256-bit). If we choose a longer key, then encryption will be better. Hence, we need to choose longer keys for more encryption. However, there is a larger performance penalty for longer keys. DES (Data Encryption Standard) is a relatively old and weaker algorithm than AES (Advanced Encryption Standard).

Answer:- To enable named pipe connections, we need to open Port 445. Named pipes communicate across TCP Port 445.

Answer:- • We have to store this data as XML-based files, not in the MSDB database. • With the configuration files, we can point the packages from prod to dev (and vice versa) in just a few seconds. • The packages and the config files are stored in a directory of our choice. • Resources permit to create a standalone SSIS server away from the primary SQL Server

Answer:- Cluster Administrator checks the SQL Server group where we can see the current owner. The current owner is the active node and the other nodes are passive nodes.

Answer:- Deadlock Information: 1204, 1205, and 1222 Network Database Files: 1807 Log Record for Connections: 4013 Skip Startup Stored Procedures: 4022 Disable Locking Hints: 8755 Forces uniform extent allocations instead of mixed page allocations 1118 (SQL Server 2005 and 2008) to reduce TempDB contention

Answer:- No. The only way if we want to get a backup is by using Windows backup for option resource MDF and IDF files.

Answer:- Upgrade Advisor can analyze remote instances of SQL Server, except for SQL Server Reporting Services. To analyze Reporting Services, Upgrade Advisor must be installed and executed on the Report Server.

Answer:- To upgrade SQL Server 2000 to SQL Server 2008, the safest approach would be a side-by-side upgrade. We can do this either by using backup and restore or by detaching/attaching the database files. However, it is suggested to use the former as it is a safer approach. The steps are as follows: • Run the Upgrade Analysis tool from Microsoft. Address any issues raised there, first • Identify DTS packages. These must be migrated manually unless we buy Pragmatic Works • Rebuild the DTS packages as SSIS • Script out all SQL Agent jobs • Script out all security • Backup the systems and validate the backups (preferably by restoring them to another system) • Run the security script on the new system • Run restore on the new system • Validate the databases by running DBCC • Manually update all statistics • Run the SQL Agent script

Answer:- Yes. SQL Server 2005 databases are compatible with SQL Server 2008. Attaching a SQL Server 2005 database to SQL Server 2008 automatically upgrades the SQL Server 2005 database to the latter, and the database is then no longer usable by the SQL Server 2005 installation.

Answer:- No. The only way to move a SQL Server 2008 database to a SQL Server 2005 is by transferring the data using methods, such as Data Transformation Services (Import/Export), SSIS, BCP, etc., or by using a query between linked servers.

Answer:- If applications or services have opened ODBC connections to SQL Server 2005 during the conversion process, they may not allow SQL Server to shut down completely. The conversion process will not proceed to the next step if it does not receive verification that SQL Server has been completely stopped.