This article is half-done without your Comment! *** Please share your thoughts via Comment ***
What is the Instance-aware and Instance-unaware services in SQL Server?
Instance-aware services are associated with a specific instance of SQL Server.
- SQL Server
- SQL Server Agent
- Analysis Services
- Reporting Services
- Full-text search
Instance-unaware services are shared among all installed SQL Server instances.
- Integration Services
- SQL Server Browser
- SQL Server Active Directory Helper
- SQL Writer
What should be your priliminary questions for Data Archive in SQL Server?
- What types of data are we storing?
- Most effective storage approach for our users – how often are they accessing/needing these data?
- Most effective storage approach for our environment – what is our environments bottlenecks, limitations and weaknesses and strengths in structure?
- Most effective storage approach when considering legal restrictions and requirements.
- Most effective storage approach when we consider costs; for an example, do we need an Enterprise license for our data archived server?
What are your different suggestions for archiving data?
If the data needs to reside in the same database:
Move the archive data to separate table(s) with a prefix to denote the tables are archive related such as ‘arc_’.
Create the table(s) in separate filegroups on separate disks to improve IO performance.
Use a view to join the old and new data if the users continue to need to access the data.
Modify the front end code to use the view rather than the base table.
If the data does not need to reside in the same database:
Move (insert and delete) the archive data to separate table(s) in another database on the server or to a separate database on another server.
Have users request access to the data for specific queries or change the application to use a linked server to access the archived data.
If the data is just not needed:
Back up the data, verify the backup is restorable, note the date of the last backup with the archived data and just delete the data.
Automation options:
Based on the application find out if the vendor has already implemented an archiving feature that just needed to be enabled.
If an automated option is not available, build a process with DTS or SSIS to move the data from the source system to the archive system.
What is the use of DBCC CHECKPRIMARYFILE?
DBCC CHECKPRIMARYFILE is one of the many undocumented SQL Server commands that we can use to do our job as DBAs.
This command is used only for reading meta data from the primary database data file on disk and to return information that is very useful, such as the database name, logical names, physical file names and more without having to attach it to an instance of SQL Server.
When you attach a database in SQL Server 2008R2 which was detached by a user with a different login you may get this error:
CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file…
The question is, why this is occured and what is the solution of this?
You get this error because two different logins did the detach and attach operations. So the files, when detached, were owned by the first login, but the attach failed because the login that was used was not the owner of the mdf and ldf files.
When we detach database files, the owner becomes the person who did the detach command, so to resolve the issue we need to change or add the other login as the owner of the mdf and ldf files.
Can we make dedicated connection for Database Administrator?
Yes: using Dedicated Administrator Connection (DAC).
SQL Server Database Administrator can connect to a SQL Server Instance when the database engine is not responding to regular connections. During such a scenario a DBA can connect to the SQL Server Instance to troubleshoot and to kill any of the SQL Server Processes which are causing the issues.
Can we move data files between filegroups?
we cannot move data files between filegroups, it is a common practice to move data for a table between filegroups in a database.
This is a straight forward method of moving data from a table from one filegroup to another. We can recreate\create the clustered index on the new filegroup thereby moving the data from one filegroup to another.
When should I start thinking about using multiple file groups?
- When disk queuing is causing application and user experience issues.
- When particular tables are 10% or more of the database.
- When non clustered index and data space are equal on large tables.
- When an almost equal percentage of read-only and read-write data exist in the database.
- When insufficient time is available to perform database maintenance.
- When the business or application will be changing significantly and the data is going to grow at a much higher rate.
- When archived data resides in the same database as the production data.
How to determine the service pack currently installed on SQL Server?
The global variable @@Version stores the build number of the sqlservr.exe, which is used to determine the service pack installed.
Why can’t I track data changes even though I already enabled the database audit on my database?
Database Audit does not track data changes. To track the data changes, you need to use another feature named “Change Data Capture” (CDC).
How do you troubleshoot errors in a SQL Server Agent Job?
Inside SSMS, in Object explorer under SQL Server Agent look for Job Activity Monitor. The job activity monitor displays the current status of all the jobs on the instance. Choose the particular job which failed, right click and choose view history from the drop down menu.
What is RAID and what are different types of RAID configurations?
RAID stands for Redundant Array of Inexpensive Disks, used to provide fault tolerance to database servers. There are six RAID levels 0 through 5 offering different levels of performance, fault tolerance.
Why can’t I log on to SQL Server using Windows Authentication in SQL Server Management Studio?
If you want to log on to the server using Windows Authentication, ensure that the current Windows account has been added to server logins.
You can check using below T-SQL.
What are the different ways of moving data/databases between servers and databases in SQL Server?
There are lots of options available, you have to choose your option depending upon your requirements. Some of the options you have are: BACKUP/RESTORE, dettaching and attaching databases, replication, DTS, BCP, logshipping, INSERT…SELECT, SELECT…INTO, creating INSERT scripts to generate data.
How To Find When SQL Server Instance was Started Last Time?
|
1 |
SELECT sqlserver_start_time FROM sys.dm_os_sys_info |
