Answer:- A collection is a user-defined name that works as the anchor for packages but has no physical existence. It is used for the grouping of packages.

Answer:- Dynamic SQL is the SQL statement created at the time of the execution of a program.

Answer:- SQL Server • Windows and Linux • T-SQL (Transact-SQL) • Simpler and easy to use • Simple Oracle • Windows, Solaris, Linux, and Unix • PL/SQL (Procedural Language/ SQL) • Complex but powerful • Complex

Answer:- To hide a SQL Server instance, we need to make a change in SQL Server Configuration Manager. To do this, we have to follow the below steps: • First, in SQL Server Configuration Manager, we have to expand ‘SQL Server Network Configuration’ • Right-click on Protocols for and select ‘Properties’ • Once we do that, we will find a ‘HideInstance’ box in which, on the ‘Flags’ tab, we have to select ‘Yes’ • After that, click on ‘OK’ Note: While hiding a named instance, we need to provide the port number in the connection string so that even if the browser is running it is possible to connect to the hidden instance.

Answer:- Yes. we can add CPUs physically by adding new hardware, either logically by online hardware partitioning or virtually through a virtualization layer. Starting with its 2008 version, SQL Server supports CPU Hot Add. There are a few requirements to use CPU Hot Add: • Hardware that supports CPU hot add • 64-bit edition of Windows Server 2008 Datacenter or Windows Server 2008 Enterprise Edition for Itanium-based system OS • SQL Server Enterprise Once the CPU is added, we need to run RECONFIGURE, and then SQL Server recognizes the newly added CPU.

Answer:- TELNET PORTNUMBER TELNET PAXT3DEVSQL24 1433 TELNET PAXT3DEVSQL24 1434 Common Ports: MSSQL Server: 1433 HTTP TCP 80 HTTPS TCP 443

Answer:- • Single-user Mode (-m): • sqlcmd –m –d master –S PAXT3DEVSQL11 –c –U sa –P ******* • DAC (-A): • sqlcmd –A –d master –S PAXT3DEVSQL11 –c –U sa –P ******* • Emergency: • ALTER DATABASE test_db SET EMERGENCY

Answer:- • In the SHRINKFILE command, SQL Server isn’t careful about where it puts the pages that are moved from the end of the file toward the beginning of the file. • Data becomes fragmented, potentially up to 100 percent, and hence it is a performance killer for the database. • Slow operation: All pointers, being moved to/from the page/rows, have to be fixed and the SHRINKFILE operation is single-threaded, so it can be really slow (the single-threaded nature of SHRINKFILE is not going to change any time soon) Recommendations: • First, use TRUNCATEONLY to shrink the file. It removes the inactive part of the log and then performs the shrink operation. • Rebuild/reorganize indexes once the shrink is done so that the fragmentation level is decreased.

Answer:- • Preferring NT authentication • Using server, database, and application roles to control access to data • Securing physical database files using NTFS permissions • Using an unusable SA password for restricting physical access to SQL Server • Renaming the Administrator account on the SQL Server computer • Disabling the Guest account • Enabling auditing using multiprotocol encryption • Setting up SSL and firewalls • Isolating SQL Server from the webserver

Answer:- Connect using DAC via CMD or SSMS: • Connect via CMD • SQLCMD -A –U myadminlogin –P mypassword -SMyServer –dmaster • Once you connect to the master database, run the diagnostic queries to find the problem • Correct the issue and restart the server

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).