I have an OLTP Database with a big table (30M records a day / 400TPS). I want to implement Partitioning & Sliding Window on that table with Monthly Partitions plus a temp Stage-Table.
The time to Switch-Out (Archive) my data is clear for me: 'End of Month' for the Last Month of my window. But my problem is when should I Switch-In new data? Because this a OLTP DB and users needs access to new data (at least after a minutes or so). It's not really a good practice for me to leave the data in Stage-Table & transfer it at the end of month
Also I'm using SQLServer version 2017, could I use a different FG for the Stage-Table other than the one in the main Paritioned-Table to make the Switch-In work?