“SQL Administrator based Frequently Asked Questions in various SQL Administrator job interviews by interviewer. These professional questions are here to ensures that you offer a perfect answers posed to you. So get preparation for your new job hunting”
Primarily, recovery model is chosen keeping in view the amount of data loss one can afford to. If one expects to have minimal or no data loss, choosing the Full recovery model is a good choice. Depending on the recovery model of a database, the behavior of database log file changes. I would recommend you read more material on log backups and log file behavior and so on to understand in depth.
SQL profiler is the SQL Server utility you can use to trace the traffic on the SQL Server instance. Traces can be filtered to narrow down the transactions that are captured and reducing the overhead incurred for the trace. The trace files can be searched, saved off, and even replayed to facilitate troubleshooting.
There are quite a lot of changes and enhancements in SQL Server 2005. Few of them are listed here:
☛ Database Partitioning
☛ Dynamic Management Views
☛ System Catalog Views
☛ Resource Database
☛ Database Snapshots
☛ SQL Server Integration Services
☛ Support for Analysis Services on a Failover Cluster.
1.Profiler being able to trace the MDX queries of the Analysis Server.
2.Peer-toPeer Replication
3.Database Mirroring
System databases are the default databases that are installed when the SQL Server is installed. Basically, there are 4 system databases: Master, MSDB, TempDB, and Model. It is highly recommended that these databases are not modified or altered for a smooth functioning of the SQL System.
A user database is a database that we create to store data and start working with the data.
Data loss is a high-pressure situation for a database administrator, especially if a migration project falls behind schedule. You get to see their thought process, how they handle stress and the strategies they use for disaster recovery. What to look for:
☛ Level-headed reactions
☛ Detailed technical answer
☛ Examples of how they handle pressure
Yes, it is possible to have various configurations in a Replication environment.
☛ SQL Server Service
☛ SQL Server Agent service
☛ SQL Server Analysis Service
☛ SQL Server Browser service
☛ SQL Server Integration Service
☛ SQL Server Reporting Service
☛ SQL Server VSS Writer service
☛ Distributed Transaction Coordinator
☛ SQL Server Active Directory Helper
☛ SQL Full-Text Filter Daemon Launcher
☛ SQL Server Distributed Replay Client service
☛ SQL Server Distributed Replay Client service
Some of the third-party tools are:
☛ SQL CHECK – Idera – Monitoring server activities and memory levels
☛ SQL DOC 2 – RedGate – Documenting the databases
☛ SQL Backup 5 – RedGate – Automating the Backup Process
☛ SQL Prompt – RedGate – Provides IntelliSense for SQL SERVER 2005/2000,
☛ Lite Speed 5.0 – Quest Soft – Backup and Restore
☛ Benefits of using third-party tools:
☛ Faster backups and restores
☛ Flexible backup and recovery options
☛ Secure backups with encryption
☛ Enterprise view of your backup and recovery environment
☛ Easily identify optimal backup settings
☛ Visibility into the transaction log and transaction log backups
☛ Timeline view of backup history and schedules
☛ Recover individual database objects
☛ Encapsulate a complete database restore into a single file to speed up restore time
☛ When we need to improve upon the functionality that SQL Server offers natively
☛ Save time, better information or notification
SQL Server has three types of replication: Snapshot, Merge, and Transaction. Snapshot replication creates a snapshot of the data (point-in-time picture of the data) to deliver to the subscribers. This is a good type to use when the data changes infrequently, there is a small amount of data to replicate, or large changes occur over a small period of time.
Merge replication uses a snapshot to seed the replication. Changes on both sides of the publication are tracked so the subscriber can synchronize with the publisher when connected. A typical use for this type of replication is in a client and server scenario. A server would act as a central repository and multiple clients would independently update their copies of the data until connected. At which time, they would all send up their modifications to the central store.
Transaction replication also begins with a snapshot only this time changes are tracked as transactions (as the name implies). Changes are replicated from publisher to subscriber the same as they occurred on the publisher, in the same order as they occurred, and in near real time. This type of replication is useful when the subscriber needs to know every change that occurred to the data (not point-in-time), when the change volume is high, and when the subscriber needs near real-time access to the changes.
Failover Clustering, Database Mirroring, Log Shipping, and Replication are the High-Availability features available in SQL Server. I would recommend reading this blog of mine which explains the differences between these 4 features.
I would go to the SQL Server Configuration Manager.In the left pane of the tool, I would select SQL Server Services, the right side pane displays all of the SQL Server Services/components that are installed on that machine. If the Service is displayed as (MSSQLSERVER), then it indicates it is a default instance, else there will be the Instance name displayed.
Drop database deletes the database along with the physical files, it is not possible to bring back the database unless you have a backup of the database. When you take a database offline, you the database is not available for users, it is not deleted physically, it can be brought back online.
Scale. If you were to take a (singular) spreadsheet and a (singular) table and place them side by side, there would be effectively no difference in the data you are seeing or what you could do with it. As you go bigger and bigger with more and more tables and spreadsheets, if you have a black belt in spreadsheet-fu you can accomplish many of the same tasks that a database could do as well. The problem is, as you go larger and larger and larger, that it becomes much more difficult to be human-friendly and still be efficient when it comes to processing data. So should you replace every single spreadsheet with a database? Not necessarily, but if the data on that spreadsheet needs to be accessed quickly by multiple users simultaneously and is growing rapidly, it may be time to consider going to the dark side (they have cookies).
DCL stands for Data Control Language.
☛ Preferring NT authentication
☛ Using server, database and application roles to control access to the data
☛ Securing the physical database files using NTFS permissions
☛ Using an unusable SA password, restricting physical access to the SQL Server
☛ Renaming the Administrator account on the SQL Server computer
☛ Disabling the Guest account, enabling auditing using multiprotocol encryption,
☛ Setting up SSL, setting up firewalls, isolating SQL Server from the web server
You can set the fill factor on your indexes. This tells SQL Server how much free space to leave in the index pages when re-indexing. The performance benefit here is fewer page splits (where SQL Server has to copy rows from one index page to another to make room for an inserted row) because there is room for growth built into the index.
Replication can be configured in any topology depending keeping in view of the complexity and the workload of the entire Replication. It can be any of the following:
☛ Publisher, Distributor, and Subscriber on the same SQL Instance.
☛ Publisher and Distributor on the same SQL Instance and Subscriber on a separate Instance.
☛ Publisher, Distributor, and Subscriber on individual SQL Instances.
SQL Server has 2 Authentication modes; Windows Authentication and SQL Server and Windows Authentication mode also referred as Mixed Mode.
☛ High-Safety Mode is to ensure that the Principal and Mirrored database are synchronized state, that is the transactions are committed at the same time on both servers to ensure consistency, but there is/might be a time lag.
☛ High-Performance Mode is to ensure that the Principal database run faster, by not waiting for the Mirrored database to commit the transactions. There is a slight chance of data loss and also the Mirrored database can be lagging behind (in terms being up to date with the Principal database) if there is a heavy load on the Mirrored Server.
GRANT, DENY and REVOKE.
rendc.org
Whether it is technological solutions or user-oriented innovations, We have put together for you the best sources of HR Interview Questions and Answers, Tech Ideas, and Programming Tutorials."
Get all the latest information on rendc.org
© 2022 | All rights reserved.
RENDC
welcome to www.rendc.org
