Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-09-15
4
Medium Priority
?
243 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 2000 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 2000 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

715 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