|Push Subscription||With a push subscription, the Publisher propagates changes to a Subscriber without a request from the Subscriber. Changes can be pushed to Subscribers on demand, continuously, or on a scheduled basis. The Distribution Agent or Merge Agent runs at the Distributor.||Data will typically be synchronized continuously or on a frequently recurring schedule.
Publications require near real-time movement of data.
The higher processor overhead at the Distributor does not affect performance.
Most often used with snapshot and transcriptional replication.
|Pull Subscription||With a pull subscription, the Subscriber requests changes made at the Publisher. Pull subscriptions allow the user at the Subscriber to determine when the data changes are synchronized. The Distribution Agent or the Merge Agent runs at the Subscriber.||Data will typically be synchronized on demand or on a schedule rather than continuously.
The publication has a large number of Subscribers, and/or it would be too resource-intensive to run all the agents at the Distributor.
Subscribers are autonomous, disconnected, and/or mobile. Subscribers will determine when they will connect and synchronize changes.
Most often used with merge replication.
1.Snapshot Agent Job Failed
Reason: Access denied to the snapshot folder
Solution: Provide full permissions to the user on snapshot folder
Change the snapshot folder path
2. Log reader agent job failed
Error: ‘The process could not execute ‘sp_replcmds’ on servername‘.
To facilitate transactional replication, Log Reader Agent moves the transactions that are marked for replication from the transaction log on the publisher to the distribution database. You may receive this error message when Log Reader Agent cannot move the transaction log within the time limit that you set in the Log Reader Agent QueryTimeout property.
To work around this problem:
- Create a new Log Reader Agent profile or modify the existing Log Reader Agent profile.
- Set the value of QueryTimeout property to 0 for the Log Reader Agent profile.
Increase the value of QueryTimeout property and decrease the value of ReadBatchSize property of the Log Reader Agent profile so that the processing of the transaction log is successful.
- Set the Log Reader Agent profile in that is referred to in step 2 as the default profile for Log Reader Agent.
Warning If you decrease the value of the Log Reader Agent ReadBatchSize property, Log Reader Agent may take longer to process the transaction log. Therefore, run enough tests before you set the value of the ReadBatchSize and QueryTimeout properties to make sure that the Log Reader Agent performance has not degraded significantly.
steps to do :
To do this from Replication Monitor (right-click the “Replication” node in Management Studio, and choose Launch Replication Monitor).
Select your publication, right click the subscription in the list to the right, and choose “Agent Profile”. You can either edit the “Default agent profile” or create a new one. Either way, you can modify the -QueryTimeout parameter if you click the little ellipsis button to edit the profile.
Final solution: (If all work arounds doent work for you then follow beloe steps)
1. sp_replcmds cant be executed during Log Agent run.
2. No matter what you do even with double coffee cup, it doesnt fix a thing.
metadata for replication got corrupted.
1. Delete all subscriptions
2. Delete all publications
3. Disable publisher replication
4. Disable distributor replication
5. Enable Distributor replication
6. Enable Publisher replication
7. Create each publication and let each snapshot finish.
8. Create subscriptions
9. Verify Snapshot Agent status on each publication
10. Verify Log Agent status on each publication
11. Verify Synchronization status on each subscription
12. Verify data on replicated table publisher and subscriber matched.
13. Once everything is verified, its confirmed its done.
error: The request failed or the service did not respond in a timely fashion
1.Recently, An unexpected shutdown of my Database server has resulted in crashing of the SQL Server Instance and I was unable to restart SQL Server.
2. SQL Server master database becomes corrupt, such as from its disks losing power while SQL Server was running.
I was unable to connect to SQL Server Instance and when I checked the SQL Server error logs, I could see the following entries
“Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. “
Now what do I do? I don’t have backups of SQL Server System databases!
Since I don’t have the system databases backups, the only option left for me was to rebuild the system databases.
- Rebuild master database when master database file corrupted but backup is not available
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName
/SQLSYSADMINACCOUNTS=accounts [/SAPWD= StrongPassword][/SQLCOLLATION=CollationName]
other Good Reference:
1. Steps to Implement Transparent Data Encryption(TDE) in SQL Server 2008
2. Configuring TDE Encryption with SQL Server 2012
TDE in AlwaysOn Availability Groups
Run below command in command prompt to verify whether port 5022 is blocked or not.
portqry -n servername -e 5022
Note: portqry Utility should be available in windows to run the above command
The best way to check if a port is blocked is to do a port scan from the client machine.
Using a PortScan utility you will get one of 3 results.
Listeningmeans the server is listening on the specified port
Filteredmeans it received a TCP acknowledgement packet with the Reset flag set which likely indicates a firewall or software issue
Not Listeningmeans it didn’t receive a response at all
Another way to check:
- There is one command to check the firewall state
(Update for Windows 7 users — as referred by
Nickbelow — use netsh advfirewall firewall)
netsh firewall show state
*******************SQL Server Error:1418******************
Refer for Workarounds:
SQL Failover Clustered Instance Installation Step-by-step process explained in the below attached ‘StarWind-SQL-Cluster’ document(with screenshots) and Video.
1.SQL Server 2012 Cluster Installation – Initial Cluster Node==> Page 23 to 35
2. SQL Server 2012 Cluster Installation – Fail over Cluster Node ==> Page 36 to 38
video (from 22nd minute): https://www.youtube.com/watch?v=VWTDbsGsBpI
************** another reference**********************
Step-by-step Windows failover cluster installation on Windows Server 2008 R2:
SQL Failover cluster installation on Windows Server 2012:
This page contains SQL Server Database Admin Interview Questions for experienced, I am sure you must have read fundamental questions like What is RDBMS, What is a view, What is a database etc etc… This list of SQL Server DBA Questions and Answers has less of those questions and more stuff that is expected to be asked in DBA interviews…
I’m sure you will find plenty of new Interview Questions and Answers for SQL Server 2008 and 2008 R2 that are useful for your Interview preparation.
1. Explain about your SQL Server DBA Experience.
- This is a generic question often asked by many interviewers. Explain what are the different SQL Server Versions you have worked on, what kind of administration of those instances has been done by you. Your role and responsibilities carried out in your earlier projects that would be of significance to the potential employer. This is the answer that lets the interviewer know how suitable are you for the position to which you are being interviewed.
2. What are the different SQL Server Versions you have worked on?
- The answer would be depending on the versions you have worked on, I would say I have experience working in SQL Server 7, SQL Server 2000, 2005 and 2008. If you have worked only the some version be honest in saying that, remember, no one would be working on all versions, it varies from individual to individual.
3. What are the different types of Indexes available in SQL Server?
- The simplest answer to this is “Clustered and Non-Clustered Indexes”. There are other types of Indexes what can be mentioned such as Unique, XML, Spatial and Filtered Indexes. More on these Indexes later.
4. What is the difference between Clustered and Non-Clustered Index?
- In a clustered index, the leaf level pages are the actual data pages of the table. When a clustered index is created on a table, the data pages are arranged accordingly based on the clustered index key. There can only be one Clustered index on a table.
- In a Non-Clustered index, the leaf level pages does not contain data pages instead it contains pointers to the data pages. There can multiple non-clustered indexes on a single table.
5. What are the new features in SQL Server 2005 when compared to SQL Server 2000?
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 a Failover Cluster.
- Profiler being able to trace the MDX queries of the Analysis Server.
- Peer-toPeer Replication
- Database Mirroring
6. What are the High-Availability solutions in SQL Server and differentiate them briefly.
- 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. Comparing the High Availability Features in SQL Server 2005
7. 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. The execution history of the job is displayed and you may choose the execution time (if the job failed multiple times during the same day). There would information such as the time it took to execute that Job and details about the error occurred.
8. What is the default Port No on which SQL Server listens?
9. How many files can a Database contain in SQL Server?How many types of data files exists in SQL Server? How many of those files can exist for a single database?
- A Database can contain a maximum of 32,767 files.
- There are Primarily 2 types of data files Primary data file and Secondary data file(s)
- There can be only one Primary data file and multiple secondary data files as long as the total # of files is less than 32,767 files
Added on Dec 30th 2010
10. What is DCL?
DCL stands for Data Control Language.
11. What are the commands used in DCL?
- GRANT, DENY and REVOKE.
12. What is Fill Factor?
Fill Factor is a setting that is applicable to Indexes in SQL Server. The fill factor value determines how much data is written to an index page when it is created / rebuilt.
13. What is the default fill factor value?
By default the fill factor value is set to 0.
14. Where do you find the default Index fill factor and how to change it?
- The easiest way to find and change the default fill factor value is from Management Studio, right-click the SQL Server and choose properties. In the Server Properties, choose Database Settings, you should see the default fill factor value in the top section. You can change to a desired value there and click OK to save the changes.
The other option of viewing and changing this value is using sp_configure.
Added on Oct 29th 2011
15. What is a system database and what is a user database?
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 smooth functioning of the SQL System.
A user database is a database that we create to store data and start working with the data.
16. What are the recovery models for a database?
- There are 3 recovery models available for a database. Full, Bulk-Logged and Simple are the three recovery models available.
17. What is the importance of a recovery model?
- 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.
Added on Nov 9th 2011
18. What is Replication?
- Replication is a feature in SQL Server that helps us publish database objects and data and copy (replicate) it to one or more destinations. It is often considered as one of the High-Availability options. One of the advantages with Replication is that it can be configured on databases which are in simple recovery model.
19. What the different types of Replication and why are they used?
- There are basically 3 types of replication: Snapshot, Transactional and Merge Replication. The type of Replication you choose, depends on the requirements and/or the goals one is trying to achieve. For example Snapshot Replication is useful only when the data inside the tables does not change frequently and the amount of data is not too large, such as a monthly summary table or a product list table etc. Transactional Replication would useful when maintaining a copy of a transactional table such as sales order tables etc. Merge Replication is more useful in case of remote / distributed systems where the data flow can be from multiple sites, for example sales done at a promotional events which might not be connected to the central servers always..
20. What the different components in Replication and what is their use?
- The 3 main components in Replication are Publisher, Distributor and Subscriber. Publisher is the data source of a publication. Distributor is responsible for distributing the database objects to one or more destinations. Subscriber is the destination where the publishers data is copied / replicated.
21. What the different Topologies in which Replication can be configured?
- 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.
Added on Nov 12th 2011
22. If you are given access to a SQL Server, how do you find if the SQL Instance is a named instance or a default instance?
- 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.
23. What are the different Authentication modes in SQL Server and how can you change authentication mode?
- SQL Server has 2 Authentication modes; Windows Authentication and SQL Server and Windows Authentication mode also referred as Mixed Mode. To change the Authentication mode, read one of my blogsChanging SQL Server Authentication Mode.
The following Question and Answers on SQL Server High Availability were Added on Nov 28th 2011
24. What are the differences in Clustering in SQL Server 2005 and 2008 or 2008 R2?
- On SQL Server 2005, installing SQL Server failover cluster is a single step process whereas on SQL Server 2008 or above it is a multi-step process. That is, in SQL Server 2005, the Installation process itself installs on all of the nodes (be it 2 nodes or 3 nodes). In 2008 or above this has changed, we would need to install separately on all the nodes. 2 times if it is a 2 node cluster or 3 times in a 3 node cluster and so on…
25. What is meant by Active – Passive and Active – Active clustering setup?
- An Active – Passive cluster is a failover cluster configured in a way that only one cluster node is active at any given time. The other node, called as Passive node is always online but in an idle condition, waiting for a failure of the Active Node, upon which the Passive Node takes over the SQL Server Services and this becomes the Active Node, the previous Active Node now being a Passive Node.
- An Active – Active cluster is a failover cluster configured in a way that both the cluster nodes are active at any given point of time. That is, one Instance of SQL Server is running on each of the nodes always; when one of the nodes has a failure, both the Instances run on the only one node until the failed node is brought up (after fixing the issue that caused the node failure). The instance is then failed over back to its designated node.
26. List out some of the requirements to setup a SQL Server failover cluster.
- Virtual network name for the SQL Server, Virtual IP address for SQL Server, IP addresses for the Public Network and Private Network(also referred as Hearbeat) for each node in the failover cluster, shared drives for SQL Server Data and Log files, Quorum Disk and MSDTC Disk.
27. On a Windows Server 2003 Active – Passive failover cluster, how do you find the node which is active?
- Using Cluster Administrator, connect to the cluster and select the SQL Server cluster. Once you have selected the SQL Server group, in the right hand side of the console, the column “Owner” gives us the information of the node on which the SQL Server group is currently active.
28. How do you open a Cluster Administrator?
- From Start -> Run and type CluAdmin (case insensitive) and the Cluster Administrator console is displayed OR you can also go to Start -> All programs -> Administrative Tools -> Cluster Administrator.
29. Due to some maintenance being done, the SQL Server on a failover cluster needs to be brought down. How do you bring the SQL Server down?
- In the Cluster Administrator, rick click on the SQL Server Group and from the popup menu item choose Take Offline.
30. What are the different ways you can create Databases in SQL Server?
- T-SQL; Create Database command.
- Using Management Studio
- Restoring a database backup
- Copy Database wizard
31. When setting Replication, can you have Distributor on SQL Server 2005, Publisher on SQL Server 2008?
- No you cannot have a Distributor on a previous version than the Publisher.
32. When setting Replication, is it possible to have a Publisher as 64 Bit SQL Server and Distributor or Subscribers as a 32 Bit SQL Server.
- Yes it is possible to have various configurations in a Replication environment.
33. What is the difference between dropping a database and taking a database offline?
- 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.
34. Which autogrowth database setting is good?
- Setting an autogrowth in multiples of MB is a better option than setting autogrowth in percentage (%).
35. What are the different types of database compression introduced in SQL Server 2008?
- Row compression and Page compression.
36. What are the different types of Upgrades that can be performed in SQL Server?
- In-place upgrade and Side-by-Side Upgrade.
37. What is Transparent Data Encryption?
- Introduced in SQL Server 2008 Transparent Data Encryption (TDE) is a mechanism through which you can protect the SQL Server Database files from unauthorized access through encryption. Also, TDE can protect the database backups of the instance on which TDE was setup.
38. Does Transparent Data Encryption provide encryption when transmitting data across network?
- No, Transparent Data Encryption (TDE) does not encrypt the data during transfer over a communication channel.
39. What are the operating modes in which Database Mirroring runs?
- Database Mirroring runs in 2 operating modes High-Safety Mode and High-Performance Mode.
40. What is the difference between the 2 operating modes of Database Mirroring (mentioned in above answer)?
- 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 Principal database) if there is a heavy load on the Mirrored Server.
- 41. How many Mirrored Servers can be setup in a Database Mirroring setup?
- Only 1 Mirrored Server can be configured in a Database Mirroring setup.
42. Can there be a 3rd Server take part in Database Mirroring?
43. What is that Server called and what is the role of that Server?
The following Question and Answers were added on Dec 24th 201144. What are Dynamic Management Objects and how are they useful?
- The 3rd Server (which is optional) is called a Witness Server, its main role is for Automatic failover of the Prinicpal and Mirrored Servers.
45. In what editions is Backup Compression available in SQL Server 2008 and 2008 R2?
- DMOs provide Server wide and Database level information on the SQL Server. DMOs include Dynamic Management Views and Dynamic Management Functions. Together, these DMOs provides internal information about the SQL Server with respect to Performance, I/O, Security, Indexes, Database Mirroring, Replication and many more. This information can be used to monitor the SQL Server and troubleshooting.
The following Question and Answers were added on Jan 2nd 2012. The questions (46-48) have been submitted by Venkat M.46. How will you know that data is copied to mirror Server in Database Mirroring?
- in SQL Server 2008, Backup Compression is supported in Enterprise Edition only (apart from Developer and Eval Editions). In SQL Server 2008 R2, the feature is available in Datacenter Edition, Enterprise Edition and Standard Edition.
47. What is the difference between Table Scan and Index Scan?
- We can monitor the status of the Principal Server and the Mirror Server using Database Mirroring Monitor from SSMS.
48. List some of the Important DMV’s used in day to day work.
- A Table Scan is performed when the table has no Indexes OR the existing Index(es) are not beneficial to fulfill the request in the query. Generally, for a large table, Table Scans are slower.
- Index Scans are usually faster than Table Scans as the Index access reduces the I/O operations.
49. What is a Trace flag?
50. How do you enable Trace flag globally?
- A Trace flags are used to change certain characteristics of the SQL Server. Trace flags can be used to switch off or enable certain feature, for troubleshooting purpose, or to diagnose performance issues such as deadlock monitoring.
The following Question and Answers were added recently (on Jun 19th 2012).51. How is Installation of SQL Server 2008 / 2008 R2 different on a failover cluster, compared to SQL Server 2000/ 2005 on a failover cluster?
- Trace flags can be set permanently using -T trace flag # as a start-up parameter or temporarily by running a command DBCC TRACEON (xxxx,-1).
52. What are the ways you can find blocking and dead locks on a SQL Server?
- Prior to SQL Server 2008, installing SQL Server on a failover cluster was a one time task, that is, when you install SQL Server on one of the nodes, the binaries are installed on all of the nodes. Whereas starting from SQL Server 2008, you have to manually install SQL Server on all of the nodes participating in the failover cluster.
53. What is Surface Area Configuration in SQL Server, and how is it different in SQL Server 2005 and beyond?
- You can use SQL Profiler (filtering only those events to find deadlocks), use System catolog view sysprocesses (there is a blocked column) and finally from Server based reports (Right click on the Server, from the pop-up menu choose Reports and then Standard Reports, choose Activity – All Blocking Transactions).
The following Question and Answers were added recently (on Sep 12th 2012).54. Can a SQL login be created with a blank password in a SQL Server?
- SQL Server by default enables only those necessary features / services or Server Options in order to reduce the risks of malicious attacks. In SQL Server 2005, Surface Area Configuration was a tool available under Configuration Tools program menu of SQL Server. Starting from SQL Server 2008, this tool is no longer available, but the same features/ services can be enabled / disabled by right clicking the SQL Server (in Object Explorer, in SSMS) and choosing Facets and selecting Surface Area Configuration Facet.
- Yes, you can create a SQL login with a blank password. This might be surprising, but it is true..
The following Question and Answers were added recently (on Sep 26th 2012).55. Which Editions of SQL Server 2012 support Master Data Services?
56. What does it mean by minimum and recommended hardware for making sure the machine is good to install SQL Server?
- Enterprise and Business Intelligence Editions support Master Data Services (as always anything supported in Enterprise Edition is available in Developer Edition).
- The minimum hardware is the bare minimum you can have on a machine to install that version of SQL Server, the drawback of having a minimum hardware is poor performance, on the other hand, recommended hardware is required to give a decent performance for the SQL Server standpoint.
SSIS Interview Questions
1. What do you mean by Microsoft Business Intelligence and what components of SQL Server supports this?
- Microsoft defines its BI solution as a platform to provide better and accurate information in a easily understandable format for quicker and better decision making. It consists of BI tools from SQL Server, SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), SQL Server Reporting Services (SSRS), Microsoft SharePoint and its Office products.
2. What is SQL Server Integration Services (SSIS)?
- SSIS is a powerful platform to build high performing, scalable packages to Extract, Transform and Load data, supporting various heterogeneous data sources as both Source and Destination of the ETL process.
Added on Nov 9th 2011
3. What is an SSIS Package?
- SSIS package is a collection of variety of tasks to perform the Extract, Transform and Load data. Though the primary functionality of SSIS packages is for ETL, they can be used for other maintenance tasks such as database backups, Index rebuild, delete old backups etc.
4. How do you create SSIS Packages? OR What tools do you use for creating SSIS Packages?
Added on Jan 2nd 2012
- You can create SSIS packages using Business Intelligence Development Studio in short “BIDS”. It can be installed using the same SQL Server installation media that is used to Install SQL Server. If all the features are selected when choosing the features at the the time of installation, then BIDS is installed along with Management Studio, Books Online and other tools..
5. What is Breakpoint and Checkpoint in SSIS Package?
- Breakpoints in SSIS packages enables us to review the values of the variables, or other components of an SSIS package.
- Checkpoints in SSIS packages enables us to rerun a SSIS package from the point of failure, so that you do not have to rerun that portion of the package which was successfully run.
6. What is the various tabs available in a SSIS Project at Design time?
- Control Flow.
- Data Flow.
- Event Handlers.
- Package Explorer.
7. What are some of the events on which you can add an Event Handler in an SSIS Package?
8. I need to have more than 1 destination in a Data Flow task, how can that be achieved?
- Using Multicast Data Flow Transformation, it is possible to direct the output to more than 1 destination.
9. How to you deploy an SSIS Package?
- Read the details in a previous blog I wrote Deploying SSIS Packages
10. What are the different destinations SSIS packages can be saved / stored for deployment?
- SSIS packages can be saved inside the SQL Server or File System destination.
11. Where inside a SQL Server are the SSIS stored?
- SSIS packages are stored inside MSDB database.
12. If I need a Stored Procedure inside a SQL Database, to be run in an SSIS package what is the way?
- You can use Execute SQL Control Flow Task to run a Stored Procedure.
What good is a backup if you do not know to restore the backup. In this tutorial we will look at what restore options are available and which options are only accessible using T-SQL commands.
As you will see there are many options that can be used, but just like the BACKUP commands there are just a few parts of the RESTORE command that are needed to do a successful restore.
Take the time to review each of these topics to get a good understanding of what is available.
You can either use the outline on the left or click on the arrows to the right or below to scroll through each of these topics.
There are several RESTORE commands and options that can be used to restore and view the contents of your backup files.
In this next section we will look at the following commands that can be used:
- RESTORE HEADERONLY – gives you a list of all the backups in a file
- RESTORE LABELONLY – gives you the backup media information
- RESTORE FILELISTONLY – gives you a list of all of the files that were backed up for a give backup
- RESTORE DATABASE – allows you to restore a full, differential, file or filegroup backup
- RESTORE LOG – allows you to restore a transaction log backup
- RESTORE VERIFYONLY – verifies that the backup is readable by the RESTORE process
Take the time to get to understand what options are available and what can be done using SQL Server Management Studio and what options are only available via T-SQL commands.