Table partitioning for archiving data Scenario:


two databases: DB_A and DB_Archive  with one very big table called
tableA.
every day, records older than 60 days are deleted from DB_A  and moved
to DB_Archive mainly to leave thing "separated" because tableA is heavily queried on DB_A for records of the past 2 months.


I want to get rid of this process because it is slow and consumes a lot of resources.
I'm thinking of implementing table partitioning on DB_A with a partition function on a date column and storing all records &lt; 2 month on one partition and all records > 2 months on another partition. My questions:


is this scenario going to behave like if I had 2 different databases? If I
query my tableA for records > getdate() - 30, is it going to read the archiving partition?
I supposed I have to partition the indexes as well, right?
How do I deal with the fact that tomorrow my partition function will
"change", I mean, if I create the function today (2nd of July, its
range will be 2nd of May, but tomorrow would be the 3rd of May). Can
I create a dynamic partition function?
With partitioning you would have to do a partition per day, which puts the Pre-SQL 2012  limit of 1000 paritions in a new perspective as it would only allow for 3 years archive. With SQL Server 2012 you get 15000 partitions which is plenty for 1 partition per day.

Every day you would add a new partition. If you want to move the 61st past day partition you can do it efficiently, but is still an offline operation. See Move a Partition to a Different File Group Efficiently. 

All your indexes would have to be aligned, see Special Guidelines for Partitioned Indexes.

Buying into partitioning is not an easy decission and it may be quite a big bite to chew... see How To Decide if You Should Use Table Partitioning. Specifically you should not expect performance improvements from partitioning. You should approach performance problems on time seriest by clustering by datetime.