Post reply

Enabling FileStream in AG participated Database

  • December 10, 2020 at 10:47 pm

    #3819123

    Hi team,

    I was asked to enable FileStream for one of the existing varbinary(max) column in existing table. It is already participating in Avalability Group where we have 1 primary & 1 secondary server.

    Now as part of the change, i am doing the below

    1. Enable FileStream at instance level by enabling it via SQL configuration Manager
    2. Setting FileStream Access level to 2
    3. Restarting SQL Server Service to make sure the FileStream settings are updated both in running & configured values
    4. Created a new FileStream FileGroup like below, but I have the root path below configured to store the files and adding a subfolder for this database "FS". This will be created in both primary & secondary server.

    ALTER DATABASE [FS]

    ADD FILEGROUP [FSFileGroup] CONTAINS FILESTREAM

    GO

    ALTER DATABASE [FS]

    ADD FILE (NAME = N'FSFileData', FILENAME = N'E:\MSSQL13.SQLTST_BACH3T1\MSSQL\FS')

    TO FILEGROUP [FSFileGroup]

    GO

    Now the question, as the database is participating in Availability Group, how the files stored the above path will be in consistent with primary & secondary server.

    Say, if I insert some records when primary is active and it stores files in primary server E Drive and due to some reasons fail over happens and now secondary server is active.

    Will i be able to access the existing records when secondary is active ?

    if not, what I have to do to make sure it is accessible irrespective of active status of primary / secondary.

    Note. we mostly access the records via T-SQL not filesystem API like VNN

    Thanks in Advance.

    • This topic was modified 3 years, 11 months ago by  Mia2022.
    • This topic was modified 3 years, 11 months ago by  Mia2022.
    • This topic was modified 3 years, 11 months ago by  Mia2022.
    • This topic was modified 3 years, 11 months ago by  Mia2022.

  • December 11, 2020 at 11:10 pm

    #3819575

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply