Solved

What is equivalent of Oracle  Archiver/Read only tablespaces in SQL server

Posted on 2011-09-15
4
239 Views
Last Modified: 2012-06-27
Hi All,
What does SQL server offer for oracle Read only tablespaces & transportable tablespaces?

I am also getting lost reading more on SQL server trasnaction logs.
Most posts recommend keeping just one transaction log file for each database. Duplexing logs can have issues they say which is not true in Oracle & highly recommended.

If the database is under FULL recovery mode I need to backup transaction log after defined interval. I want to know if there is something like Oracle Archiver process which can do this without dba intervention in background?

Many Thanks
0
Comment
Question by:crazywolf2010
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 4

Accepted Solution

by:
rbride earned 500 total points
ID: 36541930
A tablespace in oracle corresponds roughly to a database in SQL Server (and Sybase).

What you want sounds a bit like READ ONLY filegroups. A filegroup is a set of files where you place your tables. Some of these can be marked read only.
[http://msdn.microsoft.com/en-us/library/ms190257.aspx]

You can place your tables or individual partitions of a table on such a read only filegroup.
[http://msdn.microsoft.com/en-us/library/ms174979.aspx]

You can also have databases completely read only.

Regarding transportable tablespaces. You can attach / detach databases in SQL Server. This is no problem with one caveat: the database users don't always match up to the server logins across servers. Tables that are partitioned can also be switched in and out.

regarding transaction log: It is only possible to have one transaction log (it is a table, syslogs) for each MSSQL database. But it is possible to have this log spread out over several devices (e.g. RAID) or to have the device that the log is on mirrored.
0
 

Author Comment

by:crazywolf2010
ID: 36542200
Hi,
Many thanks for your response. I have added my comments

>>> regarding transaction log: It is only possible to have one transaction log (it is a table, syslogs) for each MSSQL database. But it is possible to have this log spread out over several devices (e.g. RAID) or to have the device that the log is on mirrored.
-- How is this done? Is it done by creating filegroup which will spread files against underlying spindles?

>> If the database is under FULL recovery mode I need to backup transaction log after defined interval. I want to know if there is something like Oracle Archiver process which can do this without dba intervention in background?
-- How transaction log backup is done at SQL server? Please consider this as a highly x'actional OLTP system creating too many logs entries (insert/update/delete) every minute.
0
 
LVL 4

Assisted Solution

by:rbride
rbride earned 500 total points
ID: 36542802
Re: spreading out log.
If you are using e.g. a RAID 5 hardware disk, then the log will be spread out for you by the hardware.
(See: http://msdn.microsoft.com/en-us/library/ms190764.aspx).

There is an SQL Agent service [http://msdn.microsoft.com/en-us/library/ms189237.aspx] which can run all sorts of jobs, including backups.

You schedule jobs with the SQL Agent to backup databases [http://technet.microsoft.com/en-us/library/ms186865.aspx] and the log to disk.

You can also create maintenance plans [http://msdn.microsoft.com/en-us/library/ms187658.aspx] with the maintenance plan wizard instead of scripting the backup jobs yourself.

Databases and transaction logs can be backed up while the database is on line.
0
 

Author Closing Comment

by:crazywolf2010
ID: 36543024
Pointers Provided
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question