Monthly Archives: August 2012
What are snapshot backups?
Snapshot backups are a specialized form of backup which minimize the use of the server resources to produce the backup. They are typically done in conjunction with solutions provided by hardware and/or software vendors.
What is snapshot replication?
Snapshot replication is similar to a database snapshot in that you can distribute a replica of the original database at a given point in time, however it does not update as the data changes going forward. This snapshot is then replicated to subscribers. This is a more appropriate solution if you have data that you want replicated but it does not change very often.
What are database snapshots?
A SQL Server database snapshot is a static view that is essentially a read-only image of an existing database. When the snapshot is taken it is consistent with the original database and must reside on the same server and as data is modified in the original database, so will it be modified in the snapshot. There are quite a few limitations in using database snapshots.
What are roles in SQL Server?
A role is essentially a group of users similar to active directory groups used by SQL Server for security purposes. In other words, you can grant permissions to the role and place users in the roles and they will inherit the permission for as long as they are placed inside of the role. There are also fixed roles that have pre-defined permissions established.
What is the activity monitor in SQL Server?
Activity monitor is a built-in monitor that provides information about the process, resource waits, data file input/output, and recent expensive queries that involve the current instance of SQL Server. It also gives you a small graphical representation of the monitoring as shown below.
Who is Codd and why do I care in regards to SQL Server ?
E.F. Codd was an English computer scientist with IBM who invented the relation model for databases. The relational model is the core of the architecture essential to today’s modern relational database management systems (RDBMS). Essentially all of the data involved in our lives is stored somewhere in an RDBMS. Want to read his paper on the relational model, it’s available here.
What is a constraint?
A constraint is simply a rule associated with a column in a table that any data entered must follow. Some examples are the unique constraint which ensures that no two records possess the same value for the column in question. Another commonly used constraint is the primary key constraint which provides a unique identifier for each row in a table. It is similar to the unique constraint but the key difference is that the unique constraint just means that ONE column is unique whereas a primary key can include up to a combination of 16 columns to make it unique. There is also the NOT NULL constraint which essentially means that the column cannot be left blank.
What are all these abbreviations: SSIS, SSMS, SSAS, SSRS, and SSMA?
I had this question the other day from a colleague who is a Windows administrator who was thoroughly confused by the alphabet soup. Thus I will break it down for you. Most of you know SSMS is the SQL Server Management Studio, the graphical interface for managing SQL Server. SSMA is Microsoft’s SQL Server Migration Assistant, which is available for download, for various migrating OTHER databases to SQL Server such as Oracle. That leaves us with the Business Intelligence arena for SQL Server: SSIS, SSAS and SSRS. SSAS is the SQL Server Analysis Services which provides data mining and OLAP (online analytical processing) functionality for the BI arena. SSIS is the SQL Server Integration Services which provides data integration solutions and ties all of the BI pieces together. SSRS is the SQL Server Reporting Services package allowing full reporting capabilities.
What is an execution plan?
Contrary to popular belief the execution plan is not a swinging rope or a firing squad, although with some developers, nevermind. The SQL Server query optimizer devises a plan of execution for your query in order to try to return the efficient results in the quickest manner possible. Keep in mind that the engine does not always choose the absolute best plan, it strives to choose the best plan in quickest amount of time using complex calculations.
What is the DAC and how do I use it?
The DAC, or Dedicated Administrator Connection, is essentially a troubleshooting backdoor into a running instance of SQL Server. The DAC is available through SSMS and the SQLCMD utility. In SQL Server Management Studio choose New Query on the toolbar (assuming of course that you are not currently connected to any other DACs) as opposed to the Connect button in the Object Explorer, which will not work with DAC. Once you are prompted for the connection enter ADMIN: in front of the Instance name\Server name (such as ADMIN:instance\server). Then enter a sysadmin level credential. If you are using the SQLCMD utility, then the command is sqlcmd -Sadmin:instance\server.