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

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