How to Perform and schedule SSAS Backups ?
Steps to configure schedule SSAS Cube \ OLAP database backups :-
1. For each SQL Server Analysis Services Environment. In order to create recovery strategy, we need to have backups of the metadata that describes the databases. SQL Agent jobs must be created to run backups remotely. Typically, one of the SQL Server Engine that are related to the SSAS environment is used to host the SQL Server Agent job.
2. We need to schedule job to run on predefined intervals. We can discuss the job schedule based on business/app team requirements.
3. You can also plan to move backups from disk to tape using Tape backup client like NetBackup, if used in environment.
4. We need separate step for each OLAP database and should be setup to continue to the next step(s) on failure, unless it’s the last step in the job, in that case it should be setup to either end successfully or with failure. The name should include the name of the database being backed up.
E.g. Backup DBNAME_SSAS
5. Each job step contents are as follows, replacing the DatabaseName, with the database id of the OLAP database requiring regular database backups.
<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>DatabaseName</DatabaseID>
</Object>
<File><Backup Location>\DatabaseName.abf</File>
<AllowOverwrite>true</AllowOverwrite>
</Backup>
6. You can also have job email notification for job success or failure. Also, make sure SQL Job is owned by sa.
7. SQL Server Agent service account needs to have administrative permissions on the SSAS environment that will be backed up.
Reference: Rohit Garg (http://mssqlfun.com/)
You can find and follow MSSQLFUN:-
http://www.facebook.com/mssqlfun
Other Linked Profiles :-
http://social.msdn.microsoft.com/Profile/rohitgarg
http://www.sqlservercentral.com/blogs/mssqlfun/
http://www.toadworld.com/members/rohit-garg/blogs/default.aspx
What is DBCC CLONEDATABASE ?
DBCC CLONEDATABASE is new feature comes up with SQL Server 2014 Service Pack 2 and above. DBCC CLONEDATABASE is used to create read only copy of an existing database. Cloned database includes the schema and statistics of the source database but no data.
· Source database should be online & readable while creating clone database.
· Clone database name must not be used by any other database.
· Clone database will be created as READ-ONLY, You can change clone database to read write mode as & when needed.
· Clone database created using copy of MODEL system database and later source database schema and statistics are copied to clone database.
· Creating a cloned copy of a system database is not allowed
· Clone database feature is available with SQL Server 2014 Service Pack 2 and above versions.
· File names for the Clone database will follow the source_file_name _underscore_random number convention. In case file name already exists, DBCC command will fail.
How DBCC CLONEDATABASE works?
· Validate the source database is online and readable
· Get S lock for the source database
· Create snapshot of the source database
· Creates a new destination clone database that uses the same file layout as the source but with default file sizes as the model database (this is an empty database which inherits from model)
· Get X lock for the clone database
· Copy the metadata to the clone database
o Copies the system metadata from the source to the destination database
o Copies all schema for all objects from the source to the destination database
o Copies statistics for all indexes from the source to the destination database
· Release all DB locks
· Drop snapshot of the source database
How to Create clone database?
You can easily create clone database by running below DBCC command. You just need to change source and destination clone database.
DBCC CLONEDATABASE (‘Source_Database’, ‘Destination_Clone_Database’)
Close Database created with Read Only. The database can now be used and you can change it to a Read-Write state easily by applying the ALTER DATABASE statement below:
USE [master]
GO
ALTER DATABASE [Destination_Clone_Database] SET READ_WRITE WITH NO_WAIT
GO
As mentioned above, the cloned database will have only schema and statistics as the source database, but no data.
How to check Database is cloned?
A new database property called IsClone can be used to check if the database is a cloned copy.
SELECT DATABASEPROPERTYEX(‘Database_Name’, ‘isClone’) AS IsClonedDB
Reference: Rohit Garg (http://mssqlfun.com/)
You can find and follow MSSQLFUN:-
http://www.facebook.com/mssqlfun
Other Linked Profiles :-
http://social.msdn.microsoft.com/Profile/rohitgarg
http://www.sqlservercentral.com/blogs/mssqlfun/
http://www.toadworld.com/members/rohit-garg/blogs/default.aspx
Database snapshot could not be created and the database or table could not be locked
DBCC CheckDB failed with error “database snapshot could not be created and the database or table could not be locked”
Error: The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details. [SQLSTATE 42000] (Error 50000). The step failed.
Msg 1823, Level 16, State 2, Line 1
A database snapshot cannot be created because it failed to start.
Msg 7928, Level 16, State 1, Line 1
The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.
Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.
Msg 5128, Level 17, State 1, Line 1
Write to sparse file ‘E:\MSSQL\DATA\master.mdf:MSSQL_DBCC9’ failed due to lack of disk space.
Issue: SQL Server itself creates DB snapshot from SQL Server 2005 onwards at time of executing DBCC checkDB. DBCC CheckDB is extensive activity which take lock on database and can cause blocking and slowness.
Microsoft write DBCC CheckDB code again in SQL Server 2005, which create DB snapshot while executing DBCC CheckDB and use snapshot during checkDB.
Due to disk space issue, SQL Server is not able to create Database snapshot and job failed with mentioned error. We have clear some space in drive and job completed successfully in next run.
Reference: Rohit Garg (http://mssqlfun.com/)
You can find and follow MSSQLFUN:-
http://www.facebook.com/mssqlfun
Other Linked Profiles :-
http://www.sqlservercentral.com/blogs/mssqlfun/
http://social.msdn.microsoft.com/Profile/rohitgarg
http://www.toadworld.com/members/rohit-garg/blogs/default.aspx
Cumulative Update #2 for SQL Server 2016 SP1
The 2nd cumulative update release for SQL Server 2016 SP1 is now available for download at the Microsoft Downloads site. Please note that registration is no longer required to download Cumulative updates.
To learn more about the release or servicing model, please visit:
- CU#2 KB Article: https://support.microsoft.com/en-us/help/4013106
- Understanding Incremental Servicing Model for SQL Server
- Microsoft® SQL Server® 2016 SP1 Latest Cumulative Update: https://www.microsoft.com/en-us/download/details.aspx?id=54613
- Update Center for Microsoft SQL Server: http://technet.microsoft.com/en-US/sqlserver/ff803383.aspx
Previous CUs :
- CU#1 KB Article: https://support.microsoft.com/en-us/help/3208177
Those who are facing severe issues with their environment, they can plan to test CU2 in test environment & then move to Production after satisfactory results.
To other, I suggest to wait for SP2 final release to deploy on your production environment, to have consolidate build.
Reference: Rohit Garg (http://mssqlfun.com/)
You can find and follow MSSQLFUN:-
http://www.facebook.com/mssqlfun
Other Linked Profiles :-
http://www.sqlservercentral.com/blogs/mssqlfun/
http://social.msdn.microsoft.com/Profile/rohitgarg
http://www.toadworld.com/members/rohit-garg/blogs/default.aspx
Always on\Database Mirroring Automatic Page Repair
A special thanks to Daniel Jones [daniel.jones.0543@gmail.com] for showing up interest and writing informative article for our blog (http://mssqlfun.com/).
Always on\Database Mirroring Automatic Page Repair
One of the most useful as well as hottest feature in SQL Server is mirroring of database. It is helpful for numerous of organizations that implements the successful as well as relatively high-availability of inexpensive strategies. However, in all the latest versions of SQL Server its service is being updated in numerous of ways such as database mirroring automatic page repair. It repairs the corrupt pages of the database. The automatic page repair is maintained by the database mirroring and Always on Availability Groups. After some specific types of errors, which had occurred in the database further make it in non-readable form. An availability replica tries to automatically repair the pages. The replica, which is unable to tread the page from another replica. If the request is implemented then, all the non-readable pages are replaced by the readable copies. It mainly resolves the occurrence of error.
Note: The automatic repair is completely differing from DBCC repair as all the database is stored by an automatic recovery of page.
Errors Occurred by Automatic Page-Repair
Database Mirroring of Automatic page repair efforts to repair in the data file through which an operation is failed as mentioned below:
- Error 883
Description: Action is executed only if the OS implement CRC (cyclic redundancy check), which fails on the data.
Instances: ERROR_CRC. The operating system value for this error is 23
- Error 824
Description: Logical
Instances: Logical data errors like checksum of torn write or bad page.
Techniques to Resolve Errors
- Primary Database
The primary database of automatic page repair is tried when the state of database is SYNCHRONIZED. Moreover, the principal is still transferring log records for the database to the secondary. The elementary sequence of actions in an automatic attempt of page-repair are as mentioned:
- When an error of read comes up on a data page in the primary database then, the primary inserts row in the suspect_pages table with the correct status of an error. For database mirroring, the principal then, requests a copy of page from the mirror. For the Always On Availability Groups, the primary broadcasts the appeal to all the secondary’s and acquires the page from the first to react. The request identifies the ID and LSN, which is presently at end of flushed log. The page is marked as pending restore. This marks it inaccessible throughout the automatic page-repair effort. Attempts to utilize this page in the course of the repair attempt will fail with an error code 829.
- After getting page request, the secondary gaps till it has recreated the log up to the LSN stated in the request. After that, the mirror efforts to utilize the page in its database copy. If the page can be used, the mirror directs the copy of page to the principal. Otherwise, the secondary proceeds an error to the principal and the automatic page-repair effort fails.
- The primary methods the response, which holds the fresh copy of page.
- After the automatic page-repair effort fixes a suspect page, the page is noticeable in the suspect_pages table as reinstated.
- Secondary Database
The input and output errors on pages, which happen on the secondary database are held in generally the similar way by mirroring and Always On Availability Groups.
- With mirroring of database, if the mirror comes upon one or more page errors of I/O when it rebuilds a log record, the session of mirroring arrives with the SUSPENDED state. Through Always On Availability Groups, if a replica of secondary meets one or more page errors of I/O when it recreates a log record, the secondary database enters the state of SUSPENDED. At that point, the mirror insets a row in the suspect_pages table with the suitable error status. The secondary then, requests a copy of page from the primary.
- The principal attempts to use the page in its database copy. If the page can be edited, the primary directs the copy of page to the secondary.
- If the mirror obtains copies of each page it has demanded, the secondary efforts to restart the mirroring session. If an automatic page-repair go fixes a suspect page, the page is clear in the suspect_pages table as reinstated.
If secondary does not obtain a page, which is requested from the principal, the automatic page-repair effort fails. With mirroring of database, the mirroring session leftovers suspended. With Always On Availability Groups, the secondary database leftovers suspended. If the mirroring session or secondary database is restarted manually, the corrupted pages will be again hit at the time of synchronization phase.
View Attempts of Automatic Page-Repair
The mentioned dynamic management previews return rows for the up-to-date automatic page-repair efforts on a given availability database, with maximum of 100 rows per database.
- Always On Availability Groups:
Gives a row for each automatic page-repair effort on any database availability on availability replica, which is hosted for any group availability by server instance.
- Database mirroring:
sys.dm_db_mirroring_auto_page_repair
Returns row for all automatic page-repair try on any database mirrored on the server instance.
Conclusion
It is important to repair the page to maintain the continuity of the work. Therefore, we have discussed Database mirroring automatic page repair in the above discussion that makes easy for users to remove the hurdle and understand it clearly.
Reference: Rohit Garg (http://mssqlfun.com/)
You can find and follow MSSQLFUN:-
http://www.facebook.com/mssqlfun
Other Linked Profiles :-
http://www.sqlservercentral.com/blogs/mssqlfun/
http://social.msdn.microsoft.com/Profile/rohitgarg
http://www.toadworld.com/members/rohit-garg/blogs/default.aspx
